Spring Boot with Oracle Proxy Datasource

I recently started working with a talented team delivering a spring boot/angular application. The application implements an internal business need of a large client. It is heavily integrated with many of the clients’ internal systems and 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 centralized 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 web apps in the client’s enterprise IT environment. But they are irrelevant to this story. Our local environment is somewhat different — the machines are Windows-based. It is called LOCAL to contrast the DEV environment, which is the first shared environment where the app gets deployed. We use the spring-boot embedded Jetty container for running our app.

Client’s enterprise IT, database management policy, requires all connections to the database to 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 in the Oracle docs.

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 needed. 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 (the app will use this one) 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 considered). 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, let’s 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 want to provide a concrete, in this case, logged-in user, to the DB infrastructure with JNDI datasource provisioning in all but local environments. Read on if you need more tinkering with the DB connections (specific connection preparation).

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 advice that will be used to prepare the connection. We 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 than the XML config. In essence, we defined a pointcut at the execution of oracle.jdbc.pool.OracleDataSource.getConnection() method. It 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 critical thing to note is that the OracleDataSource MUST be declared a Spring bean for the point cut 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 we needed to figure out to get this setup to work. I wrote them down as a reference for anybody who has to go proxying and preparing Oracle DB connections.