Hibernate Interceptor Session

[fabrii]

Description

Is it possible to access the Session object inside an Hibernate Interceptor? https://quarkus.io/guides/hibernate-orm#interceptors

We need to propagate some user information to the database. For example, for Oracle:

session.doWork(new org.hibernate.jdbc.Work() {
    @Override
    public void execute(Connection conn) throws SQLException {
        conn.setClientInfo("E2E_CONTEXT.CLIENT_IDENTIFIER", clientIdentifier);
    }
});

We need this identifier to be propagated for em.persist, em.merge, em.createQuery("...").executeUpdate(), em.createStoredProcedureQuery("...").execute(), etc.

Thank you

Implementation ideas

No response

[quarkus-bot[bot]]

/cc @gsmet (hibernate-orm), @yrodiere (hibernate-orm)

[yrodiere]

Hello.

Please use GitHub discussion for questions that are not a bug report or a precise feature request.

Converting this to a discussion.

[quarkus-bot[bot]]

/cc @gsmet (hibernate-orm), @yrodiere (hibernate-orm)

[yrodiere]

I don't think Intereceptor is the right tool for the job, as it's primarily focused on entities, and you're focusing on the JDBC connection.

If you're using this to record entities as "owned" by a particular client, and filter entities down to those belonging to a particular client, I'd recommend using the built-in discriminator-based multitenancy feature instead. See https://quarkus.io/guides/hibernate-orm#discriminator-approach , https://docs.jboss.org/hibernate/orm/6.6/userguide/html_single/Hibernate_User_Guide.html#multitenacy-hibernate-TenantId

If your use case is different, and you really just need to execute some dynamic SQL every time a connection is retrieved, then I think you need to define an interceptor in Agroal:

    @ApplicationScoped
    static class AnotherInterceptor implements AgroalPoolInterceptor {
        // You can @Inject whatever context you need
        @Inject
        MyRequestScopedBean myBean;

        @Override
        public void onConnectionAcquire(Connection connection) {
            var clientIdentifier = myBean.getClientIdentifier();
            try {
                connection.setClientInfo("E2E_CONTEXT.CLIENT_IDENTIFIER", clientIdentifier);
            } catch (SQLException e) {
                throw new RuntimeException("Failed to set client identifier", e);
            }
        }
    }

[fabrii]

Hi @yrodiere. Thank you for your response.

Our requirement is audit oriented. We have been using Envers, but it is not sufficient for all our use cases as we sometimes have to execute stored procedures or native queries. In these cases, we must be able to audit what changed and which user is responsible.
We are also discussing the possibility of removing Envers and performing auditing purely with triggers. We know Envers handle multiple changes in one transaction better, but right now we are not auditing 100% of what is happening.