Oracle proxy user with Spring

The standard solution to manage the database connections in a web application is to let the server manage it, and use Spring to inject it wherever it is needed. By doing so, we can let also the transaction management to be done by a third party API, Spring fits perfectly this need.
But this option assumes that the user/password used to connect to the database are the server responsibility. But sometimes…
I had a project of migration of a heavy client written in Visual Basic into a web application, and this application uses a connection per user, meaning that the credentials are user specific.
This should not be an issue except if the Visual Basic application makes an heavy usage of Oracle variables, and, unfortunately, it was the case. It means that, for each HTTP request, we need to connect to the database by creating a new connection with the connected user credentials. This kind of behavior is resource consuming and this is something that we commonly address with a pool of connections.

To remedy to that situation, luckily, Oracle provides a mechanism to reflect this usage. The idea is to define a user proxy which will be used by the real user to connect through:

>CREATE USER proxy_user IDENTIFIED BY password;

>ALTER USER scott GRANT CONNECT THROUGH proxy_user;

Now, we can create a datasource with this proxy user and use the real user to access to the data.

But now, what about our application?
Fortunately, Spring provides classes to manage this particular database configuration but it does not allow us to use the server managed datasource.
The Maven dependency we need to add:

<dependency>
	<groupId>org.springframework.data</groupId>
	<artifactId>spring-data-oracle</artifactId>
	<version>1.0.0.RELEASE</version>
</dependency> 

And the Spring configuration:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:orcl="http://www.springframework.org/schema/data/orcl"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
                        http://www.springframework.org/schema/data/orcl http://www.springframework.org/schema/data/orcl/spring-data-orcl-1.0.xsd">

<orcl:pooling-datasource id="dataSource"
	url="jdbc:oracle:thin:@${net.classnotfound.jdbc.server}:${net.classnotfound.jdbc.port}:${net.classnotfound.jdbc.sid}"
	username="${net.classnotfound.jdbc.user}" 
	password="${net.classnotfound.jdbc.password}">
	<orcl:connection-properties>validationQuery=select 1 from dual</orcl:connection-properties>
	<orcl:connection-cache-properties>
		ValidateConnection=true
	</orcl:connection-cache-properties>
	<orcl:username-connection-proxy connection-context-provider="usernameProvider" />
</orcl:pooling-datasource>

<bean id="usernameProvider" class="net.classnotfound.core.jdbc.support.UserProvider" />

<bean id="transactionManager"
	class="org.springframework.jdbc.datasource.DataSourceTransactionManager"
	depends-on="dataSource">
	<property name="dataSource" ref="dataSource" />
</bean>

We see that we have to use a specific namespace to define the datasource and the attribute are enough explicit.

The user-name-connection-proxy is used to provide the username of the connected user. In my case, as I am using Spring-Security for that purpose, the user provider looks like:

package net.classnotfound.core.jdbc.support;

import org.springframework.data.jdbc.support.ConnectionUsernameProvider;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.security.core.userdetails.UserDetails;

public class UserProvider implements ConnectionUsernameProvider {
    /**
     * return the username of the current security context
     * 
     * @return the user provided by the current request
     */
    @Override
    public String getUserName() {
        Object principal = SecurityContextHolder.getContext().getAuthentication().getPrincipal();
        if (principal instanceof UserDetails) {
            return ((UserDetails) principal).getUsername();
        } else {
            return principal.toString();
        }
    }
}

Basically, it gets the information from the Spring Security context and returns the username.