Spring Boot + Hibernate + Oracle schema multitenancy

Question

I'm trying to get a schema-based multitenancy solution working, similar to this example but with Oracle instead of Postgres.

For example, I have three schemas: FOO, BAR, and BAZ. BAR and BAZ each have a table called MESSAGES. FOO has been granted SELECT on both BAR.MESSAGES and BAZ.MESSAGES. So if I connect as FOO and then execute

SELECT * FROM BAR.MESSAGES;

then I get a result as expected. But if I leave out the schema name (e.g. SELECT * FROM MESSAGES), then I get ORA-00942: table or view does not exist (the connection is using the wrong schema).

Here's my Dao / repository:

@Repository
public interface MessageDao extends CrudRepository<Foo, Long> {
}

The controller:

@GetMapping("/findAll")
public List<Message> findAll() {
    TenantContext.setCurrentTenant("BAR");
    var result = messageDao.findAll();
    return result;
}

The Config:

@Configuration
public class MessageConfig {
    @Bean
    public JpaVendorAdapter jpaVendorAdapter() {
        return new HibernateJpaVendorAdapter();
    }

    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(DataSource dataSource,
                                                                       MultiTenantConnectionProvider multiTenantConnectionProvider,
                                                                       CurrentTenantIdentifierResolver tenantIdentifierResolver) {
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(dataSource);
        em.setPackagesToScan(Message.class.getPackageName());

        em.setJpaVendorAdapter(this.jpaVendorAdapter());

        Map<String, Object> jpaProperties = new HashMap<>();
        jpaProperties.put(Environment.MULTI_TENANT, MultiTenancyStrategy.SCHEMA);
        jpaProperties.put(Environment.MULTI_TENANT_CONNECTION_PROVIDER, multiTenantConnectionProvider);
        jpaProperties.put(Environment.MULTI_TENANT_IDENTIFIER_RESOLVER, tenantIdentifierResolver);
        jpaProperties.put(Environment.FORMAT_SQL, true);

        em.setJpaPropertyMap(jpaProperties);
        return em;
    }

The MultitenantConnectionProvider:

@Component
public class MultiTenantConnectionProviderImpl implements MultiTenantConnectionProvider {

    @Autowired
    private DataSource dataSource;

    @Override
    public Connection getAnyConnection() throws SQLException {
        return dataSource.getConnection();
    }

    @Override
    public void releaseAnyConnection(Connection connection) throws SQLException {
        connection.close();
    }

    @Override
    public Connection getConnection(String currentTenantIdentifier) throws SQLException {
        String tenantIdentifier = TenantContext.getCurrentTenant();
        final Connection connection = getAnyConnection();
        try (Statement statement = connection.createStatement()) {
            statement.execute("ALTER SESSION SET CURRENT_SCHEMA = BAR");
        } catch (SQLException e) {
            throw new HibernateException("Problem setting schema to " + tenantIdentifier, e);
        }
        return connection;
    }

    @Override
    public void releaseConnection(String tenantIdentifier, Connection connection) throws SQLException {
        try (Statement statement = connection.createStatement()) {
            statement.execute("ALTER SESSION SET CURRENT_SCHEMA = FOO");
        } catch (SQLException e) {
            throw new HibernateException("Problem setting schema to " + tenantIdentifier, e);
        }
        connection.close();
    }

    @SuppressWarnings("rawtypes")
    @Override
    public boolean isUnwrappableAs(Class unwrapType) {
        return false;
    }

    @Override
    public <T> T unwrap(Class<T> unwrapType) {
        return null;
    }

    @Override
    public boolean supportsAggressiveRelease() {
        return true;
    }
}

And the TenantIdentifierResolver (though not really relevant because I'm hard-coding the tenants right now in the ConnectionProviderImpl above):

@Component
public class TenantIdentifierResolver implements CurrentTenantIdentifierResolver {

    @Override
    public String resolveCurrentTenantIdentifier() {
        String tenantId = TenantContext.getCurrentTenant();
        if (tenantId != null) {
            return tenantId;
        }
        return "BAR";
    }

    @Override
    public boolean validateExistingCurrentSessions() {
        return true;
    }
}

Any ideas as to why the underlying Connection isn't switching schemas as expected?

UPDATE 1

Maybe it's something to do with the underlying Oracle connection. There is a property on OracleConnection named CONNECTION_PROPERTY_CREATE_DESCRIPTOR_USE_CURRENT_SCHEMA_FOR_SCHEMA_NAME. The documentation says:

The user also has an option to append the CURRENT_USER value to the ADT name to obtain fully qualified name by setting this property to true. Note that it takes a network round trip to fetch the CURRENT_SCHEMA value.

But the problem remains even if I set this to true (-Doracle.jdbc.createDescriptorUseCurrentSchemaForSchemaName=true). This may be because the "username" on the Connection is still "FOO", even after altering the sesssion to set the schema to "BAR" (currentSchema on the Connection is "BAR"). But that would mean that the OracleConnection documentation is incorrect, wouldn't it?

UPDATE 2 I did not include the fact that we are using Spring Data JPA here as well. Maybe that has something to do with the problem? I have found that if I include the schema name hard-coded in the entity then it works (e.g. @Table(schema="BAR")), but having a hard-coded value there is not an acceptable solution.

It might also work if we rewrite the queries as native @Query and then include {h-schema} in the SQL, but in Hibernate this is the default schema, not the 'current' (dynamic) schema, so it's not quite right either.

Answer 1

It turns out that setting the current tenant on the first line of the Controller like that (TenantContext.setCurrentTenant("BAR")) is "too late" (Spring has already created a transaction?). I changed the implementation to use a servlet filter to set the tenant id from a header to a request attribute, and then fetch that attribute in the TenantIdentifierResolver, instead of using the TenantContext. Now it works as it should, without any of the stuff I mentioned in the updates.