Java – How do I use Hibernate/JPA to tell who the database user is before inserting/updating/deleting?

Summary (details below):

I want to make a stored procedure call before saving/updating/deleting any entity using the Spring/JPA stack.

Boring details:

We have an Oracle/JPA (Hibernate)/Spring MVC (with Spring Data repos) application that is set up to use triggers to put the history of some tables into a set of history tables (one history table for each table we want to audit). EmptyInterceptor class to set. When the trigger archives any inserts or updates, it can easily see who made the change using this column (which application user we are interested in, not the database user). The problem is that for deletes, we don’t get the last modified information from the executed SQL because it’s just a normal delete from x where y.

To solve this problem, we want to execute a stored procedure to tell the database which application user logs in before performing any operation. The audit trigger then looks at this value as the deletion occurs and uses it to record who performed the deletion.

Is there a way to intercept the start transaction or some other way to execute SQL or stored procedures to tell the database what user is performing inserts/updates/deletes, and those inserts/updates/deletes will happen in the transaction before the rest of the operation occurs?

I

don’t know much about how the database side works, but I can get more information if necessary. The point is that the stored procedure will create a context to hold the session variable, and the trigger will query that context to get the user ID on deletion.

From the database side, here are some discussions:

https://docs.oracle.com/cd/B19306_01/network.102/b14266/apdvprxy.htm#i1010372

Many applications use session pooling to set up a number of sessions
to be reused by multiple application users. Users authenticate
themselves to a middle-tier application, which uses a single identity
to log in to the database and maintains all the user connections. In
this model, application users are users who are authenticated to the
middle tier of an application, but who are not known to the
database….. in these situations, the application typically connects
as a single database user and all actions are taken as that user.
Because all user sessions are created as the same user, this security
model makes it very difficult to achieve data separation for each
user. These applications can use the CLIENT_IDENTIFIER attribute to
preserve the real application user identity through to the database.

From the Spring/JPA perspective, see Section 8.2 below:

http://docs.spring.io/spring-data/jdbc/docs/current/reference/html/orcl.connection.html

There are times when you want to prepare the database connection in
certain ways that aren’t easily supported using standard connection
properties. One example would be to set certain session properties in
the SYS_CONTEXT like MODULE or CLIENT_IDENTIFIER. This chapter
explains how to use a ConnectionPreparer to accomplish this. The
example will set the CLIENT_IDENTIFIER.

The example given in the Spring documentation uses XML configuration. If you are using a Java configuration, then it looks like:

@Component
@Aspect
public class ClientIdentifierConnectionPreparer implements ConnectionPreparer
{
  @AfterReturning(pointcut = "execution(* *.getConnection(..))", returning = "connection")
  public Connection prepare(Connection connection) throws SQLException
  {
    String webAppUser = //from Spring Security Context or wherever;

CallableStatement cs = connection.prepareCall(
                 "{ call DBMS_SESSION. SET_IDENTIFIER(?) }");
    cs.setString(1, webAppUser);
    cs.execute();
    cs.close();

return connection;
  }
}

Enable AspectJ: through the configuration class

@Configuration
@EnableAspectJAutoProxy
public class SomeConfigurationClass
{

}

Note that while this is hidden in the section of the Spring-specific Oracle extension, it seems to me that nothing in section 8.2 (unlike 8.1) is Oracle-specific (except for the statements executed), and the general approach should be to simply specify the relevant procedure call or SQL, which will work for any database:

For example Postgres

is as follows, so I don’t see why people using Postgres can’t use this method below:

https://www.postgresql.org/docs/8.4/static/sql-set-role.html