Hi folks

UPDATE: See the follow-up post below for a more detailed explanation of the issue and two ways to handle it with Hibernate.

Original post:

It seems that Hibernate isn't presently well equipped to deal with schema where column privileges vary depending on the user that Hibernate's JDBC connections are logged in as. Columns that for one user are insertable may not be insertable for another user, and so on.

There's a partial solution where you can use XML overrides for entity definitions when creating the persistence unit. However, this means you need to know the rights you have before creating the PU, so either (a) they're embedded in the app or (b) you query the database using plain JDBC to determine them before creating the PU. It also works poorly with annotation-driven schema, because if any attribute of a mapped property is overridden, all annotations on the property are ignored. OK if your mapping is done in XML; less appealing if it's done as annotations so you must repeat it all as XML for properties that may be overridden.

It would be very helpful if a future version of Hibernate could more gracefully handle column privileges, perhaps by supporting changes of the insertable and updatable attributes after PU creation, and ideally per-dialect queries for fetching column privileges from the database. Alternately, if Hibernate omitted rows that hadn't changed or were null from the column-list in INSERT statements, and from the SET list in UPDATE statements, it wouldn't need to know about column privileges at all.

By the way: A workaround does exist. You can use Hibernate's Ejb3Configuration class as an entry point instead of the standard Persistence class, then modify the loaded configuration before creating the EntityManagerFactory. eg:



Unfortunately you don't have database access yet, so you need to already know the column privs of the user who's logging in, or need to establish a plain JDBC connection to the database to determine their rights.

... and a reply-to-self again:

The issue with column privs is that Hibernate lists all columns, even ones it hasn't set or altered, in the INSERT and UPDATE statements it issues. Column privileges are checked based on the INSERT or UPDATE column list, not the actual values being changed, so even:



... will fail, because permissions are checked before values are evaluated and compared. I didn't clearly explain that above, but that's why I wanted to alter the insertable= or updatable= settings on entity properties - to prevent the associated columns from being included in the INSERT or UPDATE list.

The reason Hibernate lists all columns in INSERT and UPDATE statements is that it pre-generates SQL for these operations and uses the same SQL for each operation, often as a prepared statement. To do so it must include all fields that may ever change. If you don't have permission to alter all those fields, they'll still be included in the SQL as Hibernate doesn't know that, so all operations will fail - even when you're only actually changing fields you're allowed to - since you're not allowed to even set columns you don't have permission for to their current values.

Two options exist: Either, at PU init time, rewrite the Hibernate mappings to include knowledge of user permissions, or dynamically generate INSERT and UPDATE SQL to only include columns that have actually been set/changed.

The latter option may be achieved much more easily than the former. Hibernate supports generation of dynamic SQL for every individual UPDATE and INSERT, and must simply be told to use it. If you're using JPA2:



It's much the same for XML mappings and for non-JPA Hibernate Annotations.

If you need crazy-fast INSERTs and UPDATEs on tables with column privs that vary based on logged-in role, you'll want to go through the hassle of modifying the mapping at PU creation time. Otherwise, just use dynamic SQL, (or make sure your column privs are set so that all Hibernate users have the same rights so you can set updatable= and insertable= statically in your mappings).