CODEWITHSUNDEEP
javaspringoracle-databasehibernatejpa
Chris Williams
Chris Williams

Reputation: 12481

How can I use Hibernate/JPA to tell the DB who the user is before inserts/updates/deletes?

Summary (details below):

I'd like to make a stored proc call before any entities are saved/updated/deleted using a 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 record history of some tables into a set of history tables (one history table per table we want audited). Each of these entities has a modifiedByUser being set via a class that extends EmptyInterceptor on update or insert. When the trigger archives any insert or update, it can easily see who made the change using this column (we're interested in which application user, not database user). The problem is that for deletes, we won't get the last modified information from the SQL that is executed because it's just a plain delete from x where y.

To solve this, we'd like to execute a stored procedure to tell the database which app user is logged in before executing any operation. The audit trigger would then look at this value when a delete happens and use it to record who executed the delete.

Is there any way to intercept the begin transaction or some other way to execute SQL or a stored procedure to tell the db what user is executing the inserts/updates/deletes that are about to happen in the transaction before the rest of the operations happen?

I'm light on details about how the database side will work but can get more if necessary. The gist is that the stored proc will create a context that will hold session variables and the trigger will query that context on delete to get the user ID.

Upvotes: 4

Views: 3487

Answers (3)

Alan Hay
Alan Hay

Reputation: 23226

From the database end, there is some discussion on this here:

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 side of things see section 8.2 at the 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 docs uses XML config. If you are using Java config 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 via a Configuration class:

@Configuration
@EnableAspectJAutoProxy
public class SomeConfigurationClass
{

}

Note that while this is hidden away in a section specific to Spring's Oracle extensions it seems to me that there is nothing in section 8.2 (unlike 8.1) that is Oracle specific (other than the Statement executed) and the general approach should be feasible with any Database simply by specifying the relevant procedure call or SQL:

Postgres for example as the following so I don't see why anyone using Postgres couldn't use this approach with the below:

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

Upvotes: 2

raminr
raminr

Reputation: 804

Unless your stored procedure does more than what you described, the cleaner solution is to use Envers (Entity Versioning). Hibernate can automatically store the versions of an entity in a separate table and keep track of all the CRUD operations for you, and you don't have to worry about failed transactions since this will all happen within the same session.

As for keeping track who made the change, add a new colulmn (updatedBy) and just get the login ID of the user from Security Principal (e.g. Spring Security User)

Also check out @CreationTimestamp and @UpdateTimestamp.

Upvotes: 1

Maciej Kowalski
Maciej Kowalski

Reputation: 26522

I think what you are looking for is a TransactionalEvent:

@Service
public class TransactionalListenerService{

   @Autowired
   SessionFactory sessionFactory;

   @TransactionalEventListener(phase = TransactionPhase.BEFORE_COMMIT)
    public void handleEntityCreationEvent(CreationEvent<Entity> creationEvent) {
          // use sessionFactory to run a stored procedure
    }
}

Registering a regular event listener is done via the @EventListener annotation. If you need to bind it to the transaction use @TransactionalEventListener. When you do so, the listener will be bound to the commit phase of the transaction by default.

Then in your transactional services you register the event where necessary:

@Service
public class MyTransactionalService{

     @Autowired
     private ApplicationEventPublisher applicationEventPublisher;

     @Transactional
     public void insertEntityMethod(Entity entity){
         // insert 

          // Publish event after insert operation
          applicationEventPublisher.publishEvent(new CreationEvent(this, entity));

         // more processing
     }

}

This can work also outside the boundaries of a trasaction if you have the requirement:

If no transaction is running, the listener is not invoked at all since we can’t honor the required semantics. It is however possible to override that behaviour by setting the fallbackExecution attribute of the annotation to true.

Upvotes: 0

Related Questions