> Most modern databases support user defined functions that return tables. We use them
with both MS-SQL Server and Postgres to implement a rather complex/convoluted security
scheme in the database. Thus instead of doing this:
> SELECT * FROM clients
> we do this:
> SELECT * FROM secure_clients( <user_id> )
the problem for this is that this means you have to generate *all* sql dynamically and
programmatically; you can't pregenerate it and i'm not sure if hibernate's
core is open enough for that (yet).
I'm still curious if using secure_clients('max') as the table name directly
actually works for you (then you could *in theory* have a SF per user ;)
> Where <user_id> would be the ID of the user who is querying
the client table. The "user" is not a database user but rather an application
user, who in turn has a record in the users table. Based on the contents of the user
table and various security tables we can determine what "clients" the user can
"see" and only return those. This allows the application programmer to ignore
the implementation of SELECT security and allows other apps that may touch the same
database such as import/export utilities and business intelligence tools to all use the
same security logic.
> The fun part with hibernate will be the parameters to the UDFs. They will need to be
session specific. My initial though was to add some mechanism to allow properties to be
assigned to the session, IE:
> session.setProperty("user_id", "1");
> Then in the mapping:
> <class table="secure_clients(#user_id#)" name="...">
> Then when hibernate pulls from secure_clients it could do a simple string replace
based on the session properties.
> ----- Original Message -----
> From: "Max Rydahl Andersen" <max.andersen(a)redhat.com>
> To: richard(a)bowmansystems.com, hibernate-dev(a)lists.jboss.org
> Sent: Tuesday, December 18, 2007 11:11:41 AM (GMT-0500) America/New_York
> Subject: Re: [hibernate-dev] Using hibernate with UDFs
> haven't heard about anyone working on this.
> With UDF I guess you mean user defined functions and on SQL Server or ?
> Have you tried just using the UDF as table name directly ?
>> I asked in the forums a while back about using Hibernate to tie back to
>> set-returning UDFs instead of a table or a view.
>> I have looked at Hibernate alternatives solutions and they all come up
>> short in some way. I'm interested in hacking Hibernate to add support
>> for tying back to UDFs at this point. I was just curious if this was
>> something anyone else had looked at in the past, was on someone's TODO
>> list, was something there are strong feelings about, etc before I got
>> hibernate-dev mailing list
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/