|
Hi
I have a PostgreSQL 8.4 database that makes heavy use of column privileges to limit what columns particular user roles may modify. Column access control enforcement is done using different user logins at the database level, via groups (roles in Pg speak) assigned different column privileges.
I'm building a J2SE app that uses Hibernate (3.5 via JPA2 and Annotations-style mapping) as its DB access layer, and having excellent results with it in general. A JPA2 Persistence Unit is created with the username and password the user supplies when they log in to the app, so all database work is done as that user. Database-level and table-level privileges are generally entirely fuss free, and Hibernate doesn't even need to care about them. Ditto tables where column privileges prohibit any user from setting explicit values on insert or update - Hibernate works fine with a table where all users have the same rights, as columns may be mapped as updatable= and insertable= as appropriate.
The problem is with tables where different users have different column privileges. There doesn't seem to be any way to set insertable= or updatable= on a column dynamically based on the privileges of the current database user, so I have issues with tables where different users have different column permissions. If I map a column as updatable=true and some users don't have the appropriate rights then Hibernate-generated UPDATEs may fail even if that particular column's value hasn't been changed, just because the column is in the UPDATE's column list. Ditto for INSERTs. If I map it as updatable=false, there's no way to use Hibernate to update the column even if the current user has the appropriate rights.
I can always map such columns as updatable=false / insertable=false as appropriate and fall back to JDBC to change them where necessary, but that's _ugly_ and makes the use of Hibernate much less beneficial. The alternative is modifying the DB to rip out most of the column privilege use and replace it with reams of error-prone hand-coded trigger cruft to do the same checks more slowly and less safely, which I don't find attractive. That might also confuse existing apps that expect permission errors not some random trigger failure if the user doesn't have required rights to do something.
Does anybody have any experience with issues like this - with PostgreSQL or another database?
Is there any way to dynamically alter the `insertable=' and `updatable=' values in a mapping for a given PU instance? Or otherwise achieve the same ends?
|
Login
FAQ
Search
