Tag Archives: SQL

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.

Enable transactions on Informix DB

We need to have the following variables set:

INFORMIXDIR=/opt/IBM/informix
INFORMIXSQLHOSTS=/opt/IBM/Informix/etc/sqlhosts.ol_informix1170
INFORMIXSERVER=ol_informix1170
  1. Now, we can enable the transactions, in first we need to put the database in “quiescent mode” (???):

    >/opt/IBM/informix/bin/onmode –uy
    

    Note: doing this disable access to the database.

  2. enable transactions:

    >/opt/IBM/informix/bin/ontape –s –L 0 –B d9exp
    
  3. Re-enable the connections:

    >/opt/IBM/informix/bin/onmode -m
    
  4. Helpful information: Jdbc configuration

    jdbc:oracle:thin:@server:port:db
    

    More information here

Unlock Informix table

First, get the sessid using the following sql:

select unique
dbsname db,
tabname table,
case
when type="S" then "shared lock"
when type="IS" then "intent shared lock"
when type="SIX" then "shared intent excl lock"
when type="XS" then "shared key value by RR"
when type="IX" then "intent excl lock"
when type="X" then "exclusive lock"
when type="XR" then "excl key value by RR"
when type="U" then "update lock"
when type="B" then "byte lock"
else
"unknown lock type"
end lock_type,
lpad(owner,5) ses_id,
lpad(waiter,5) wait_id
from sysmaster:syslocks
where tabname != 'sysdatabases';

And check the table concerned by the lock. Using its session id, you can now unlock it with:

>onmode -z <sess_id>

Thanks to this thread

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! 😀