Hi,

I am new to nHibernate and have Googled for an answer with no success yet.

Can nHibernate generate derived table syntax, and if so, how?

And example of the final SQL statement I need

SELECT e.EmployeeID, e.FirstNames, e.LastName
FROM
Employee e
INNER JOIN (SELECT x.EmployeeID FROM Employee x) ie ON (ie.EmployeeID = e.EmployeeID)

The above SQL statement is just an example of what we need to do, ultimately the derived table (the one with the x alias) would contain a "dynamic" security query injected into the SQL statement.

If there is any other way of achieving the above behaviour, I'm also open to other ideas.

Thanks,
Steve

I'm not sure what you meant with "derived table". If I understand you correctly you want to inject the sql with some dynamic joins to enforce security. I don't know a way of real sql injection, but you can use filters. But they only work on the actual table you are accessing (meaning joins to other tables are not possible). Have a look at the docs:

http://www.hibernate.org/hib_docs/nhibernate/1.2/reference/en/html/filters.html

Hi,

First, to answer your questions,
The derived table syntax is in bold red below:
SELECT e.EmployeeID, e.FirstNames, e.LastName
FROM Employee e
INNER JOIN (SELECT x.EmployeeID FROM Employee x) ie ON (ie.EmployeeID = e.EmployeeID)

Secondly, what I meant by SQL injection is that we do not want to rewrite or parse SQL (it's just too dynamic for that). But what we will accept is to inject a SQL clause into a special statement (hence the syntax above).

I don't think filters are going to help us in this situation, because the security might actually be on a different table. I think the best would be for me to give a proper example of what we are doing.

Let's say that we have a payroll system, and you have various managers across different departments. Each manager has a system login which defines the security rights within the system. Each manager can only see the employees in their department.

So a typical SQL statement might be, show all employees (green shows unchanged SQL statement, red shows various security injections):
SELECT e.EmployeeID, e.FirstNames, e.LastName
FROM Employee e

Now to inject the security (without rewriting the SQL statement) would be to use a derived table syntax, example below:
SELECT e.EmployeeID, e.FirstNames, e.LastName
FROM Employee e
INNER JOIN (SECURITY CLAUSE) ie ON (ie.EmployeeID = e.EmployeeID)

Using this template, we can do the following:
Get all employees for Manager A (who is manager of Department Sales)
SELECT e.EmployeeID, e.FirstNames, e.LastName
FROM Employee e
INNER JOIN (
SELECT x.EmployeeID
FROM Employee x
INNER JOIN Department d ON x.DepartmentID = d.DepartmentID
WHERE d.DepartmentCode = 'Sales'
) ie ON (ie.EmployeeID = e.EmployeeID)

Get all employees for Manager B (who is manager of Department Support)
SELECT e.EmployeeID, e.FirstNames, e.LastName
FROM Employee e
INNER JOIN (
SELECT x.EmployeeID
FROM Employee x
INNER JOIN Department d ON x.DepartmentID = d.DepartmentID
WHERE d.DepartmentCode = 'Support'
) ie ON (ie.EmployeeID = e.EmployeeID)

And we can even do fancier security stuff, for example, let's say that each department belongs to an internal company, and that you can have 2 sales departments, one in each internal company.
Get all employees for Manager A, in the department that exists within the internal company that the manager is in
SELECT e.EmployeeID, e.FirstNames, e.LastName
FROM Employee e
INNER JOIN (
SELECT x.EmployeeID
FROM Employee x
INNER JOIN Department d ON x.DepartmentID = d.DepartmentID
INNER JOIN InternalCompany ic ON ic.InternalCompanyID = d.InternalCompanyID
INNER JOIN Employee me ON (me.EmployeeCode = "Manager A" AND ic.InternalCompanyID = me.InternalCompanyID)
WHERE d.DepartmentCode = 'Sales'
) ie ON (ie.EmployeeID = e.EmployeeID)

So the derived syntax allows us to be extremely creative in terms of securing data, but I am battling to get the same concept in nHibernate.

Thanks for your response. If we cannot do it, then I guess we will probably have to look at using filters.

Thanks,
Steve

The only thing I can think of at the moment is using criteria queries with subqueries for the security:



You may want to post that question also to the nhuser group at google. There are more of the developers watching:

http://groups.google.com/group/nhusers?hl=en

Hi Wolfgang,

Thanks for your help. I'll look into your suggestions and see if they'll work for us.

Regards
Steve