Hi,

I'm using NHibernate 2.0.1 and SQL 2005

I've spent a while investigating using NHibernate with a legacy database we have. One of the key issues is figuring out the best way to incorporate row-level security into the access to our domain objects.

The basic db structure looks like this...

CREATE TABLE [Project]
[ProjectID] [int] NOT NULL,
[Description] [varchar](100) NOT NULL,
[Comments] [varchar](500) NULL,

CREATE TABLE [Security](
[UserGroupID] [int] NOT NULL,
[Type] [char](1) NOT NULL,
[objectid] [int] NOT NULL,
[Authority] [tinyint] NOT NULL

The Security table stores security for a variety of objects including data rows which are indicated with a Type = 'P'.
The Security table has composite primary key over UserGroupID, Type and ObjectID.

So in order to filter the rows a user is allowed to see, we would execute sql similar to

SELECT * FROM Project P
INNER JOIN Security S ON P.ProjectID = S.ObjectID
AND S.Type = 'P' AND S.UserGroupID = @MyUserGroup

---------

So in investigating how I would achieve this with NHibernate. I've managed to get one solution, but it seems a bit hacky to me.
So I'm interested if anyone knows any better ways.

The solution I currently have is...

<class name="Project" where="exists (select 1 from Security S where S.UserGroupID = @MYUSERGROUPID and ObjectID = @OBJECTID)">
...

Then I have used an Interceptor and modified the sql in OnPrepareStatement, replacing the parameters @MYUSERGROUPID and @OBJECTID.
@OBJECTID is replaced with 'this_.ProjectID', @MYUSERGROUPID is replaced with a value we could extract from the user's session.

Have a look at hibernate's filter mechanism: http://www.nhforge.org/doc/nh/en/index.html#objectstate-filters. I'm not sure if joining another table is possible, but you can at least do the "exists" query without the need of the interceptor. YOu just have to enable the filter in the session and set the paramter.

That's great thanks.

Actually I had been all round the houses trying all sorts of strategies including filters. Mainly around trying to join to the Security objects, but was stumped trying to create a join over multiple columns. I hadn't realised i could just put a filter directly in the class. So now, for the benefit of any other NHibernate newbies, my class looks like...

<class name="Project">
...
<filter name="security" condition="exists (select 1 from Security S where S.UserGroupID = :currentUserGroupID and ObjectID = this_.ProjectID)
...
</class>

And i can filter it by applying the filter to the session ie..

session.EnableFilter("security").SetParameter("currentUserGroupID ", 10);