Tag Archives: datasource

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.

Define configuration file outside of the classpath

Here I describe the way I use to configure my applications. The main idea is to define a property file outside of the application, doing this, it is possible to deploy the same application in several environments (dev/test/prod) without any changes.

First of all, the property file:

net.classnotfound.app.jdbc.server=db-server
net.classnotfound.app.jdbc.port=1521
net.classnotfound.app.jdbc.sid=xe
net.classnotfound.app.jdbc.user=scott
net.classnotfound.app.jdbc.password=koala

The tips is to define a parameter in the Tomcat context file which points to the file location:

<Parameter name="directoryConfig" override="false" value="FILE_PATH"/>

And now, to get the value in the application, I use Spring this way:

<bean id="placeholderConfig"
	class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
	<property name="location"
		value="file:#{contextParameters.directoryConfig}/app-config.properties" />
</bean>

This is an example of its usage to set up an Oracle datasource:

<orcl:pooling-datasource id="dataSource"
	url="jdbc:oracle:thin:@${net.classnotfound.app.jdbc.server}:${net.classnotfound.app.jdbc.port}:${net.classnotfound.app.jdbc.sid}"
	username="${net.classnotfound.app.jdbc.user}" password="${net.classnotfound.app.jdbc.password}">
</orcl:pooling-datasource>

Now, we can just change the path of the configuration file, depending on each environment, to automatically have the related parameters.

Add datasource access in tests

In my web applications, the datasource is often defined in Tomcat (see here), when I execute my integration tests using JUnit, I don’t need Tomcat, but I need an access to the database, and to the Spring context too.
My solution is to create an abstract test which provides me these features, I use:

Doing this, there’s nothing to change in the class I test, I can check the database access, the transaction management or the bean definitions:

package net.classnotfound;
import java.sql.SQLException;
import javax.naming.NamingException;
import oracle.jdbc.pool.OracleDataSource;

import org.junit.BeforeClass;
import org.junit.runner.RunWith;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.mock.jndi.SimpleNamingContextBuilder;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations={"/integrationTestContext.xml"})
public abstract class AbstractTest {
    
    private static final String PWD = "password";
    private static final String USER = "user";
    private static final String JDBC_URL = "jdbc:oracle:thin:@localhost:1521:classnotfound";
    private static final Logger LOG = LoggerFactory.getLogger(AbstractTest.class); 

    @BeforeClass
    public static void setUpClass() throws Exception {

        OracleDataSource ods = null;
        try {
            ods = new OracleDataSource();
        } catch (SQLException e) {
            LOG.error(null,e);
        }
        ods.setURL(JDBC_URL);
        ods.setUser(USER);
        ods.setPassword(PWD);
        LOG.info("DB connection, URL: {}; user: {}; pwd {}", new String[] {JDBC_URL, USER, PWD});

        SimpleNamingContextBuilder builder = null;
        try {
            builder = SimpleNamingContextBuilder.emptyActivatedContextBuilder();
            builder.bind("java:comp/env/jdbc/applicationDatasource",ods);
        } catch (NamingException e) {
            LOG.error(null,e);
        }
    }
}

And to use it in my test, I just need to extend this class:

public class TestService extends AbstractTest{

    @Autowired
    private MyService myService;
...
}

Tomcat Datasource configuration

I describe here my favorite solution to define the datasource of my web applications.

To do that in Tomcat :

  1. Copy the JDBC driver jar into the Tomcat lib directory
  2. define the datasource in the file server.xml:
     <GlobalNamingResources>
    
        <Resource 
            auth="Container" 
            driverClassName="oracle.jdbc.OracleDriver" 
            maxActive="20" 
            maxIdle="10" 
            maxWait="-1" 
            name="jdbc/myGlobalDatasource" 
            password="password" 
            type="javax.sql.DataSource" 
            url="jdbc:oracle:thin:@[server]:[port]:[sid]" 
            username="user"/>
    
    </GlobalNamingResources>
  3. map this datasource to your application using its context.xml file:
    <ResourceLink 
        global="jdbc/globalDatasource" 
        name="jdbc/applicationDatasource" 
        type="javax.sql.DataSource"/>
  4. And now, when you want to use it (using Spring):
    <bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
        <property name="jndiName" value="jdbc/applicationDatasource" />
        <property name="resourceRef" value="true" />
    </bean>

Done!

This example uses an Oracle connection but it can be easily adapted for another database.