Using HB3 filter for row-level permission control

hackingbear · **Joined:** Mon Mar 21, 2005 12:27 am **Posts:** 17

Hi,

I attempted to use Hibernate3's filter to do row-base permission control.

(1) A user may access records that are owned by herself or readable by members of any groups she may participate in. We call such field "permission controlling field"
(2) A user may participate in zero or more groups.
(3) User ID and group ID are both Subject ID (and they don't overlap)

Example:

Code:

File {
  Title = "Hibernate secretes"
  ...
  Readable_by = dude; // permission control field
}
File {
  Title = "Guide to migrating O/R to Hibernate"
  ...
  Readable_by = programmers; // permission control field
}

User 'dude' is a member of group 'programmers'. So I may define a filter "pcf" and add it to the File class

Code:

<class name="File" ...>
    ...
    <filter name="pcf" condition=":subjectID = READABLE_BY"/>
</class>

In the code, I would attempt the set

Code:

session.enableFilter("pcf").setParameter("subjectID", ??);

The problem is:

I have multiple subject ID values: 'dude', 'programmers'. Can the current filtering mechanism support this?

Thanks

steve · **Posted:** Thu Apr 07, 2005 9:06 pm

I *think* what you are asking for is the ability to bind a number of parameters to a given filter?

Filters support in-style lists...

Code:

<class name="File" ...> 
    ... 
    <filter name="pcf" condition="READABLE_BY in (:subjectIdList)"/> 
</class>

...

session.enableFilter("pcf").setParameterList("subjectIdList", usersSubjectIdList);

Its up to you "build" the usersSubjectIdList.

The other option is to use an SQL function to determine the appropriate condition; something like:

Code:

<class name="File" ...> 
    ... 
    <filter name="pcf" condition="F_CHECK_ACCESS( READABLE_BY, OWNER, :userId )"/> 
</class>

hackingbear · **Joined:** Mon Mar 21, 2005 12:27 am **Posts:** 17

steve wrote:

I *think* what you are asking for is the ability to bind a number of parameters to a given filter?

Code:

<class name="File" ...> 
    ... 
    <filter name="pcf" condition="F_CHECK_ACCESS( READABLE_BY, OWNER, :userId )"/> 
</class>

That's correct!

Now I realized my problem is slightly more complex.

Because in some situations, we want to have Access Control List (ACL) for the objects. I plan to use component collection to store ACL entries, like:

Code:

File {
  Id = 123456
  Title = "Guide for migrating to Hibernate"
  ACL = [
    { "dude", "FULL_ACCESS" },
    { "programmers", "READ" }
  ]
}

The ACL component table, of course, looks like

Code:

create table FILE_ACL {
  FILE_ID primary key ref FILE.ID
  SUBJECT_ID
}
create unique index on (FILE_ID, SUBJECT_ID)

How do I effectively write the condition query?

You remind me now that I can use stored procedure. While it works, is it efficient enough? It does not seem to be able to take advantage of the index associated with the ACL table as the logic expression is hidden in a procedure. If the DB has to load every record and execute the procedure, that wouldn't be very performant. Am I right for the worry? Is there a way to express the filtering condition in declarative SQL expression for this more complex case?

Thanks
[/code]

steve · **Posted:** Thu Apr 07, 2005 11:45 pm

The issue is that resolving the query the way you want to do it (or the way I'd want to do it) is that you'd need a hierarchical query (or recursive query) and that is not a standardized feature in the SQL language spec; afaik, oracle is the only vendor that even supports such a query construct.

As for the effeciency concern of using a stored proc/function, it really depends on the size of the data sets you're talking about. The reason I suggested it is that using a function like this will typically be faster than your java code looping over nested collections (forcing db calls!) and collecting all the pertinent subject-ids to bind to the filter parameter list.

Quote:

Is there a way to express the filtering condition in declarative SQL expression for this more complex case?

The sql expression for a filter is just that, its a sql expression. Anything that a valid sql where condition fragment can be placed in there. Just ask yourself how you'd do it in sql.

hackingbear · **Joined:** Mon Mar 21, 2005 12:27 am **Posts:** 17

steve wrote:

The issue is that resolving the query the way you want to do it (or the way I'd want to do it) is that you'd need a hierarchical query (or recursive query) and that is not a standardized feature in the SQL language spec; afaik, oracle is the only vendor that even supports such a query construct.

Do you mean subquery?

Actually I think of this filter condition

Code:

exist (select * from FILE_ACL acl where acl.FILE_ID = ID 
          and acl.SUBJECT_ID in :subjctIds)

But I'm not sure if I need to put the table/alias name in from of ID. As all subquery I saw and created explicit qualify the parent query's table/alias

Or how about

Code:

ID =  (select unique acl.FILE_ID from FILE_ACL acl where
                     acl.SUBJECT_ID in :subjctIds)

This is supported in MySQL and Oracle and probably most other databases of interest.

hackingbear · **Joined:** Mon Mar 21, 2005 12:27 am **Posts:** 17

[quote="hackingbear]Or how about

Code:

ID =  (select unique acl.FILE_ID from FILE_ACL acl where
                     acl.SUBJECT_ID in :subjctIds)

This is supported in MySQL and Oracle and probably most other databases of interest.[/quote]

Soory, I meant "ID in ( ... )"

steve · **Posted:** Fri Apr 08, 2005 2:32 am

Sure, subqueries are valid in where conditions ;)

That's fine; they're still not hierarchical queries which are not the same as subqueries. If you know oracle, check out its "CONNECT BY" queries.