Tag Archives: Oracle

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.

Kill Oracle sessions

Some time the Oracle connections stay open, blocking the other transactions, there’s no other option than kill it.
We firstly need to identify it, as system user, execute the following query:

SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND';

Check the connection you need to kill, and:

ALTER SYSTEM KILL SESSION 'sid,serial#'

Now, you have your licence to kill! 😀