The Oracle JDBC implementation provides access to Oracle's Proxy Authentication feature. The Proxy Authentication lets you configure a connection pool using a proxy user account with very limited rights. Then, during the connection process, you would specify the actual user name for the end user. This user name must be configured to allow a proxy connection through the user proxy ("grant connect through proxyuser").

This is valuable for web applications where you typically set up a data source with a shared database user. If this shared user is a proxy user account and you supply the actual end user name then the proxy authentication feature will make any database access this user performs to be performed with the end users actual database user account.

To use this feature you must provide an implementation of the ConnectionUsernameProvider interface. This interface has a single method named getUserName that should return the user name for the current end user to be connected via the proxy user. It's up to the application developer to provide the appropriate implementation. One type of implementation would be to retrieve the current principal or user name from the SecurityContextHolder provided when you use Spring Security.

An example of what this implementation could look like is:

public class CurrentUsernameProvider implements ConnectionUsernameProvider {

    public String getUserName() {
        Object principal = SecurityContextHolder.getContext().getAuthentication().getPrincipal(); 
        if (principal instanceof UserDetails) { 
            return ((UserDetails)principal).getUsername(); 
        } else { 
            return principal.toString(); 
        }
    }

}

See the Spring Security reference manual for more detail regarding the use of the SecurityContextHolder.

Connection proxy authentication is configured using the username-connection-proxy element. You also need to provide a reference to the user name provider that implements the ConnectionUsernameProvider interface mentioned above.

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       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-3.0.xsd
       http://www.springframework.org/schema/context
       http://www.springframework.org/schema/context/spring-context-3.0.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" 
        properties-location="classpath:orcl.properties">
        <orcl:username-connection-proxy connection-context-provider="usernameProvider"/> 
    </orcl:pooling-datasource>

    <bean id="usernameProvider" class="org.springframework.data.jdbc.test.CurrentUsernameProvider"/>

</beans>

To set up the database proxy user and to grant the user accounts to participate in the proxy authentication you could use this SQL:

-- create the new proxy user account
create user proxyuser identified by proxypasswd;
grant create session to proxyuser;
-- grant existing user to connect  through the proxy
alter user spring grant connect through proxyuser;

In your connection properties file (orcl.properties) you would need to provide the proxy user credentials:

url=jdbc:oracle:thin:@//localhost:1521/xe
username=proxyuser
password=proxypasswd

	Note
	We are currently only supporting proxy authentication using user name with no password authentication for the user connecting through the proxy. Support for other types of proxy connections will be provided in future releases.

There are times when you want to prepare the database connection in certain ways that aren't easily supported using standard connection properties. One example would be to set certain session properties in the SYS_CONTEXT like MODULE or CLIENT_IDENTIFIER. This chapter explains how to use a ConnectionPreparer to accomplish this. The example will set the CLIENT_IDENTIFIER.

We will need to add a ConnectionInterceptor using AOP and then configure the ConnectionInterceptor with a ConnectionPreparer implementation that performs the necessary preparations. Lets first look at our custom ClientIdentifierConnectionPreparer that implements the ConnectionPreparer interface. There is only a single method named prepare that needs to be implemented. The prepared connection is the return value which gives you an opportunity to wrap the connection with a proxy class if needed.

package org.springframework.data.jdbc.samples;

import org.springframework.data.jdbc.support.ConnectionPreparer;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;

public class ClientIdentifierConnectionPreparer implements ConnectionPreparer {

    String prepSql = "{ call DBMS_SESSION.SET_IDENTIFIER('SPRING') }"; 

    public Connection prepare(Connection conn) throws SQLException {
        CallableStatement cs = conn.prepareCall(prepSql); 
        cs.execute();
        cs.close();
        return conn;
    }

}

This example sets the CLIENT_IDENTIFIER to a fixed value, but you could implement a ConnectionPreparer that would use the current users login id. That way you can capture user login information even if your data source is configured with a shared user name.

The following application context entries show how this could be configured for your data source.

    <orcl:pooling-datasource id="dataSource" 
        connection-properties-prefix="conn"
        properties-location="classpath:orcl.properties"/>

    <aop:config> 
        <aop:advisor 
            pointcut="execution(java.sql.Connection javax.sql.DataSource.getConnection(..))" 
            advice-ref="testInterceptor"/>
    </aop:config>

    <bean id="testInterceptor" 
          class="org.springframework.data.jdbc.aop.ConnectionInterceptor">
        <property name="connectionPreparer" ref="connectionPreparer"/> 
    </bean>

    <bean id="connectionPreparer" 
        class="org.springframework.data.jdbc.samples.ClientIdentifierConnectionPreparer"/> 

Every time a new connection is obtained the connection preparer will set the CLIENT_IDENTIFIER. During database processing the value it was set to can be accessed using a call to a standard Oracle function - "sys_context('USERENV', 'CLIENT_IDENTIFIER')"