Re: Column privileges and Hibernate (SOLVED)

Craig Ringer wrote:
> One of the apps using the database uses the Hibernate ORM system for
> Java. While excellent in most ways, it's giving me some trouble when it
> comes to inserts/updates on tables with column privileges.
>
> It's easy enough to tell Hibernate that certain columns are not
> insertable or not updatable, and it'll honour that, so there's no issue
> if all users have the same rights on a column. However, if rights vary
> depending on the roles and grants of the logged-in user, it's not clear
> how to inform it of that.

Hi folks

I thought I'd follow up with a solution to this issue, so that anyone
else looking for it later doesn't go completely mad.

The short answer:
=================

On entities where column privs vary based on role, set:

selectBeforeUpdate=true, dynamicInsert=true, dynamicUpdate=true

and breathe a sigh of relief.

The explanation:
================

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:

UPDATE test
SET no_update_permission_column = no_update_permission_column;

... will fail, because permissions are checked before values are
evaluated and compared. I didn't clearly explain that earlier, 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. By setting all fields
each time it doesn't need to record the original state of the entity or
SELECT from the database to compare with the in-memory copy before
UPDATE. Anyway, to do so it must include all fields that may ever change
in its UPDATE and INSERT queries. 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, (1) at PU init time, rewrite the Hibernate
mappings to include knowledge of user permissions, or (2) dynamically
generate INSERT and UPDATE SQL to only include columns that have
actually been set/changed.

(2) is by far the easiest. 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:

import javax.persistence.Entity;
import javax.persistence.Table;
@Entity
@Table(name = "myEntityTableName")
@org.hibernate.annotations.Entity(selectBeforeUpdate=true,
dynamicInsert=true, dynamicUpdate=true)
class MyEntity {
// ....
};

(1) requires that you use Hibernate's Ejb3Configuration instead of the
usual javax.persistence.Persistence when creating an EntityManagerFactory.

// Assuming you have a Map of connection properties - otherwise use the
1-arg ctor:
Ejb3Configuration cfg = new Ejb3Configuration().configure("PuName",
connectionPropertiesMap);
Iterator it = cfg.getClassMappings();
// Do something with the class mappings or their attributes
// say, walk them and alter their insertable= and updatable= properties
// based on the results of a JDBC query for database column ACLs
// [your code to do that]
// then build the EntityManagerFactory based on the new configuration:
EntityManagerFactory factory = cfg.buildEntityManagerFactory();

I've not tested (1). (2) works fine for my purposes.

If you don't want to use selectBeforeUpdate in (2), you may instead
track the original state of your objects yourself, and provide that to
Hibernate at persist-time so it doesn't have to query the database to
find out which properties are dirty. See, eg:

https://forums.hibernate.org/viewtopic.php?f=1&t=999937

I don't need this myself and haven't bothered testing it; I'm trying to
keep my Hibernate-specific (non JPA standard) code to a minimum.

Anyway, hope this helps someone out. It's really just a reformatting of
what I wrote on the (third) Hibernate Forum question I posted about this:

https://forum.hibernate.org/viewtopic.php?f=1&t=1001854&p=2423099#p2423099

--
Craig Ringer