Spring Boot with Oracle Proxy Datasource

I recently started working with a talented team of people delivering a spring boot/angular application. The application implements an internal business need of a large client and is heavily integrated with a lot of the clients internal systems as well as a couple of external services (SOAP/HTTP). The environment where the application will run consists of the following components (in stages DEV, UAT, PROD):

• Oracle Linux as the server OS
• Oracle 12g database server (local development uses a centralised non-clustered instance)
• Apache Tomcat 8.5 as the run-time container

There are other components (such as Microsoft AD with Kerberos based SSO for all of the webapps in the clients enterprise IT environment) but they are not relevant to this story. Our local environment (called LOCAL to contrast the DEV environment which is the first shared environment where the app gets deployed) is somewhat different — the machines are Windows based, and we use the spring-boot embedded Jetty container for running our app.

Clients enterprise IT database management policy requires all connections to the database use a proxy user for the initial connection and then subsequently use the application logged-in user for actual database work. The why and how is nicely described here.

Contact

Looking for software development experts?

Get in touch

Google led me to this nice piece of Spring JDBC documentation — a quick read (chapter 8.1.) hinted that this could be the solution to implementing the proxy-user policy.

However, we are building a Spring Boot application with Java config and wanted to modify the solution found in the docs to our needs. Additionally, we wanted to use a Tomcat managed datasource in DEV, UAT and PROD environments but in our LOCAL environment we wanted to instantiate a datasource programatically. Easy, right?

Spring Boot with Oracle proxy datasource

Turns out it wasn’t hard to do, it took the team a couple of hours of focused time to achieve what we need. I’m sure it can be done in more ways than one, but here is ours in 4 easy steps:

Step 1:

As the Spring JDBC docs say, create an implementation of a ConnectionUsernameProvider interface — used by the proxy datasource to retrieve the actual application user that will be used to execute database operations

@Component
class SecurityContextHolderUserProvider implements ConnectionUsernameProvider {
     @Override
     String getUserName() {
          Object principal = SecurityContextHolder.context.authentication.principal
          if (principal instanceof UserDetails) {
               return (principal as UserDetails).username
          } else {
               return principal.toString()
          }
     }
}

We use spring-security to handle our authentication/authorisation needs — the code we use is just groovy-flavoured code directly from the spring-jdbc documentation.

Step 2:

Create Oracle datasource specific configuration file, holding the proxy datasource (this one will be used by the app) and a locally instantiated Oracle specific datasource.

@Configuration
@ConfigurationProperties("oracle")
class OracleConfiguration {
    @Autowired
    private ConnectionUsernameProvider contextProvider
    private OracleDataSource dataSourceInternal
    String username
    String password
    String url
    @Bean
    @Profile(["local"])
    OracleDataSource oracleDataSource() throws SQLException {
        dataSourceInternal.user = username
        dataSourceInternal.password = password
        dataSourceInternal.URL = url
        dataSourceInternal.implicitCachingEnabled = true
        dataSourceInternal.fastConnectionFailoverEnabled = true
        return dataSourceInternal
    }
    @Bean
    @Primary
    DataSource dataSource() throws SQLException {
        ProxyDataSource dataSource = new ProxyDataSource(dataSourceInternal, contextProvider)
        return dataSource
    }
}

This config reads the properties from application.properties (the “oracle” prefixed properties are taken into account). The proxy datasource bean is the key here — specifically its type is org.springframework.data.jdbc.support.oracle.ProxyDataSource and it is responsible for creating connections based on the underlying datasource (of type oracle.jdbc.pool.OracleDataSource) and making sure those connections rely on the username provided by the SecurityContextHolderUserProviderfrom STEP 1.

Step 3:

So far, we have a locally viable configuration. But, as I said, we want to have datasources as Tomcat JNDI resources in all other environments — we want our application to be unaware of (at least) different proxy DB users and connection strings.

So, lets define a JNDI datasource in Tomcat! We can do it server wide (put it in server.xml) or context wide (for our application). Our datasource is context wide, with only the basic parameters set (user and password):

<Resource name="jdbc/datasource" auth="Container" 
          type="oracle.jdbc.pool.OracleDataSource" 
          factory="oracle.jdbc.pool.OracleDataSourceFactory" 
          url="DB_URL" user="proxyuser" password="proxypass"/>

Our database configuration gets an additional bean which is populated by JNDI lookup, but only in DEV, UAT and PROD environments.

@Configuration
@ConfigurationProperties("oracle")
class OracleConfiguration {
    @Autowired
    private ConnectionUsernameProvider contextProvider
    private OracleDataSource dataSourceInternal
    String username
    String password
    String url
    @Bean
    @Profile(["local"])
    OracleDataSource oracleDataSource() throws SQLException {
        dataSourceInternal.user = username
        dataSourceInternal.password = password
        dataSourceInternal.URL = url
        dataSourceInternal.implicitCachingEnabled = true
        dataSourceInternal.fastConnectionFailoverEnabled = true
        return dataSourceInternal
    }
    @Bean
    @Profile(["dev", "uat", "prod"])
    OracleDataSource jndiDataSource() {
        JndiDataSourceLookup dataSourceLookup = new JndiDataSourceLookup()
        dataSourceInternal =
            dataSourceLookup.getDataSource("java:comp/env/jdbc/datasource") as OracleDataSource
        return dataSourceInternal
    }
    @Bean
    @Primary
    DataSource dataSource() throws SQLException {
        ProxyDataSource dataSource = new ProxyDataSource(dataSourceInternal, contextProvider)
        return dataSource
    }
}

The configuration and code in the first three steps will be enough if you just want to provide a concrete, in this case logged in user, to the DB infrastructure with JNDI datasource provisioning in all but local environments. If you need some more tinkering with the DB connections (specific connection preparation), read on.

Step 4:

The last thing we wanted to do is get access to the DB connection before it gets used for DB operations — we want to do some specific preparation work. Again, the Spring JDBC docs state that the solution (chapter 8.2.) is to create an advice which will be used to prepare the connection. We just need to convert the XML based config to JAVA config:

@Aspect
@Component
class OracleConnectionPrepareAdvice {
    @Autowired
    private ConnectionUsernameProvider contextProvider
    @Pointcut("execution(* oracle.jdbc.pool.OracleDataSource.getConnection(..))")
    void prepareConnectionPointcut() {}
    @AfterReturning(pointcut="prepareConnectionPointcut()", returning = "connection")
    void afterPrepareConnection(Connection connection) {
        String prepString =
            String.format("{ call DBMS_SESSION.SET_IDENTIFIER('%s') }", contextProvider.userName)
        CallableStatement cs = connection.prepareCall(prepString)
        cs.execute()
        cs.close()
    }
}

IMHO, this is much more readable then the XML config — in essence, we defined a pointcut at the execution of oracle.jdbc.pool.OracleDataSource.getConnection() method which is responsible for providing connections from the connection pool. Our advice then uses the pointcut and executes after the oracle.jdbc.pool.OracleDataSource.getConnection() method returns a connection — the only thing we are doing here right now is setting the identifier parameter on our DBMS_SESSION object.

The important thing to note here is that the OracleDataSource MUST be declared as a Spring bean for the pointcut to work — Spring AOP works only on Spring beans.

I know some details might be missing from this configuration, but these are the important bits that we needed to figure out to get this kind of setup to work. I wrote them down as a reference for anybody that has to go down the path of proxying and preparing Oracle DB connections.