Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 2 posts ] 
Author Message
 Post subject: Oracle Proxy Authentication and Connection Pooling
PostPosted: Thu Dec 02, 2010 5:45 pm 
Newbie

Joined: Thu Sep 09, 2010 11:43 am
Posts: 3
Using some of the code from viewtopic.php?f=1&t=988458 I am trying to create an application that logs into an Oracle database using a gatekeeper account and then login with a Oracle Proxy Authentication user. This part seems to be working.

My problem is I am running out of connections quickly. It seems that once the connection is checked out they aren't reused.

applicationContext-hibernate.xml
Code:
<?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:aop="http://www.springframework.org/schema/aop"
    xmlns:tx="http://www.springframework.org/schema/tx"
    xmlns:p="http://www.springframework.org/schema/p"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
        http://www.springframework.org/schema/aop
        http://www.springframework.org/schema/aop/spring-aop-2.5.xsd
        http://www.springframework.org/schema/tx
        http://www.springframework.org/schema/tx/spring-tx-2.5.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context-2.5.xsd">


    <!-- Configurer replaces ${...} placeholders with the values from a properties file -->
    <context:property-placeholder location="classpath:connection.properties"/>
   
    <!-- Setup Session Factory -->
    <bean id="sessionFactory"
          class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
        <property name="mappingResources">
            <list>
                <value>tarts/hibernate/Requests.hbm.xml</value>
                <value>tarts/hibernate/WorkTimes.hbm.xml</value>
                <value>tarts/hibernate/RequestStepXref.hbm.xml</value>
                <value>tarts/hibernate/Attachments.hbm.xml</value>
                <value>tarts/hibernate/RequestSpeedchartXref.hbm.xml</value>
                <value>tarts/hibernate/RequestTpidXref.hbm.xml</value>
                <value>tarts/hibernate/Favorites.hbm.xml</value>
                <value>tarts/hibernate/AssignmentXref.hbm.xml</value>
                <value>tarts/hibernate/Estimates.hbm.xml</value>
                <value>tarts/hibernate/RequestNotes.hbm.xml</value>
                <value>tarts/hibernate/OrgEmpRoles.hbm.xml</value>
                <value>tarts/hibernate/EstimateTypes.hbm.xml</value>
                <value>tarts/hibernate/OrgHierarchies.hbm.xml</value>
                <value>tarts/hibernate/OrgRoleTypes.hbm.xml</value>
                <value>tarts/hibernate/WorkflowNotices.hbm.xml</value>
                <value>tarts/hibernate/Organizations.hbm.xml</value>
                <value>tarts/hibernate/RequestOrgXref.hbm.xml</value>
                <value>tarts/hibernate/OrgLevels.hbm.xml</value>
                <value>tarts/hibernate/PriorityCodes.hbm.xml</value>
                <value>tarts/hibernate/PriorityReqTypeXrefs.hbm.xml</value>
                <value>tarts/hibernate/RequestTypes.hbm.xml</value>
                <value>tarts/hibernate/TaskRequestXref.hbm.xml</value>
                <value>tarts/hibernate/RequestTypeStepXref.hbm.xml</value>
                <value>tarts/hibernate/WorkflowSequences.hbm.xml</value>
                <value>tarts/hibernate/Complexities.hbm.xml</value>
                <value>tarts/hibernate/Systems.hbm.xml</value>
                <value>tarts/hibernate/Projects.hbm.xml</value>
                <value>tarts/hibernate/RequirementsSets.hbm.xml</value>
                <value>tarts/hibernate/Requirements.hbm.xml</value>
                <value>tarts/hibernate/NoteTypes.hbm.xml</value>
                <value>tarts/hibernate/WorkflowStatuses.hbm.xml</value>
                <value>tarts/hibernate/StepCodes.hbm.xml</value>
                <value>tarts/hibernate/Importances.hbm.xml</value>
                <value>tarts/hibernate/TaskCategories.hbm.xml</value>
                <value>tarts/hibernate/TaskTypes.hbm.xml</value>
                <value>tarts/hibernate/TimeCertifiedBatches.hbm.xml</value>
                <value>tarts/hibernate/SysEmployees.hbm.xml</value>
            </list>
        </property>
        <property name="hibernateProperties" ref="hibernatePropertiesBean"/>
    </bean>
       
    <!-- Hibernate properties -->
    <bean id="hibernatePropertiesBean"
          class="org.springframework.beans.factory.config.PropertiesFactoryBean">
        <property name="properties">
            <props>
                <prop key="hibernate.connection.username">${jdbc.gateway.username}</prop>
                <prop key="hibernate.connection.password">${jdbc.gateway.password}</prop>
                <prop key="hibernate.connection.url">${jdbc.url}</prop>
                <prop key="hibernate.connection.driver_class">${jdbc.driverClassName}</prop>
                <prop key="hibernate.connection.provider_class">${connection.provider_class}</prop>
                <prop key="hibernate.dialect">${hibernate.dialect}</prop>
                <prop key="hibernate.show_sql">${hibernate.show_sql}</prop>
                <prop key="hibernate.c3p0.acquire_increment">${hibernate.c3p0.acquire_increment}</prop>
                <prop key="hibernate.c3p0.max_size">${hibernate.c3p0.max_size}</prop>
                <prop key="hibernate.c3p0.min_size">${hibernate.c3p0.min_size}</prop>
                <prop key="hibernate.c3p0.idle_test_period">${hibernate.c3p0.idle_test_period}</prop>
                <prop key="hibernate.c3p0.max_statements">${hibernate.c3p0.max_statements}</prop>
                <prop key="hibernate.c3p0.timeout">${hibernate.c3po.timeout}</prop>
               
            </props>
        </property>     
    </bean>
   
    <!-- Transaction Manager Config -->
    <bean id="transactionManager"
          class="org.springframework.orm.hibernate3.HibernateTransactionManager">
        <property name="sessionFactory" ref="sessionFactory" />
    </bean>
       
    <tx:advice id="txAdvice" transaction-manager="transactionManager">
        <tx:attributes>
            <tx:method name="*"/>
        </tx:attributes>
    </tx:advice>
   
    <aop:config>
        <aop:pointcut id="requestService"
              expression="execution(* dphhs.tarts.web.services.RequestServiceImpl.createNewRequest*(..))"/>
        <aop:advisor advice-ref="txAdvice" pointcut-ref="requestService"/>
     
    </aop:config>
   
    <!-- Activates various annotations -->
    <context:annotation-config/>
   
    <!-- DAO Config -->   
    <bean id="ComplexitiesDaoImpl"
          class="dphhs.tarts.dao.ComplexitiesDaoImpl">
        <property name="sessionFactory" ref="sessionFactory" />
    </bean>
    <bean id="ConnectionDaoImpl"
            class="dphhs.tarts.dao.ConnectionDaoImpl">
        <property name="sessionFactory" ref="sessionFactory" />       
    </bean>
    <bean id="PriorityCodesDaoImpl"
          class="dphhs.tarts.dao.PriorityCodesDaoImpl">
        <property name="sessionFactory" ref="sessionFactory" />
    </bean>
    <bean id="OrganizationsDaoImpl"
          class="dphhs.tarts.dao.OrganizationsDaoImpl">
        <property name="sessionFactory" ref="sessionFactory" />
    </bean>
    <bean id="OrgEmpRolesDaoImpl"
          class="dphhs.tarts.dao.OrgEmpRolesDaoImpl">
        <property name="sessionFactory" ref="sessionFactory" />
    </bean>
    <bean id="RequestsDaoImpl"
          class="dphhs.tarts.dao.RequestsDaoImpl">
        <property name="sessionFactory" ref="sessionFactory" />
    </bean>
    <bean id="RequestStepXrefDaoImpl"
          class="dphhs.tarts.dao.RequestStepXrefDaoImpl">
        <property name="sessionFactory" ref="sessionFactory" />
    </bean>
    <bean id="RequestTypesDaoImpl"
          class="dphhs.tarts.dao.RequestTypesDaoImpl">
        <property name="sessionFactory" ref="sessionFactory" />
    </bean>
    <bean id="StepCodesDaoImpl"
          class="dphhs.tarts.dao.StepCodesDaoImpl">
        <property name="sessionFactory" ref="sessionFactory" />
    </bean>
    <bean id="SysEmployeesDaoImpl"
          class="dphhs.tarts.dao.SysEmployeesDaoImpl">
        <property name="sessionFactory" ref="sessionFactory" />
    </bean>
    <bean id="SystemsDaoImpl"
          class="dphhs.tarts.dao.SystemsDaoImpl">
        <property name="sessionFactory" ref="sessionFactory" />
    </bean>
    <bean id="WorkflowStatusesDaoImpl"
          class="dphhs.tarts.dao.WorkflowStatusesDaoImpl">
        <property name="sessionFactory" ref="sessionFactory" />
    </bean>
</beans>


applicationContext-security.xml
Code:
<?xml version="1.0" encoding="UTF-8"?>
<beans:beans xmlns="http://www.springframework.org/schema/security"
    xmlns:beans="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
        http://www.springframework.org/schema/security
        http://www.springframework.org/schema/security/spring-security-2.0.4.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context-2.5.xsd">
   
    <!-- Configurer replaces ${...} placeholders with the values from a properties file -->
    <context:property-placeholder location="classpath:connection.properties"/>
   
    <http auto-config="false">
        <intercept-url pattern="/login.do" filters="none" />
        <intercept-url pattern="/**" access="ROLE_ISERV_EMPLOYEE" />
        <form-login login-page="/login.do"
                default-target-url="/home.do"
                always-use-default-target="false"
                authentication-failure-url="/login.do?authfailed=true" />
        <logout invalidate-session="true"
                logout-url="/logout.do"
                logout-success-url="/login.do?loggedout=true" />
    </http>
   
    <beans:bean id="myAuthenticationProvider"
            class="dphhs.tarts.security.MyAuthenticationProvider">
        <beans:property name="dataSource" ref="dataSource"/>
        <custom-authentication-provider/>
    </beans:bean>
   
    <beans:bean id="dataSource"
            class="dphhs.tarts.security.MyAuthenticationConnectionProvider">
        <beans:property name="url" value="${jdbc.url}"/>
        <beans:property name="username" value="${jdbc.gateway.username}"/>
        <beans:property name="password" value="${jdbc.gateway.password}"/>
    </beans:bean>
   
</beans:beans>


web.xml
Code:
<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee   http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
     <context-param>
          <param-name>contextConfigLocation</param-name>
          <param-value>/WEB-INF/applicationContext-security.xml
                      /WEB-INF/applicationContext-hibernate.xml</param-value>
     </context-param>
     
     <filter>
          <filter-name>springSecurityFilterChain</filter-name>
          <filter-class>org.springframework.web.filter.DelegatingFilterProxy</filter-class>
     </filter>
     
     <filter-mapping>
          <filter-name>springSecurityFilterChain</filter-name>
          <url-pattern>/*</url-pattern>
     </filter-mapping>
     
     <listener>
          <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
     </listener>

     <servlet>
         <servlet-name>applicationContext</servlet-name>
         <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
     </servlet>
     
     <servlet-mapping>
         <servlet-name>applicationContext</servlet-name>
         <url-pattern>*.do</url-pattern>
     </servlet-mapping>

     <welcome-file-list>
          <welcome-file>index.jsp</welcome-file>
     </welcome-file-list>
     
     <login-config>
          <auth-method>BASIC</auth-method>
     </login-config>
</web-app>

MyAuthenticationConnectionProvider
Code:
package dphhs.tarts.security;

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

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import oracle.jdbc.pool.OracleDataSource;

public class MyAuthenticationConnectionProvider {

    private static final Logger log = LoggerFactory.getLogger(MyAuthenticationConnectionProvider.class);
   
    private String username;
    private String password;
    private String url;
   
    public Connection getConnection() throws SQLException {
       
        log.debug("Return New OracleDataSource()");
        log.debug("New OracleDataSource() [" + username + "/" +
                password + "]@" + url);
       
        OracleDataSource connection = new OracleDataSource();
       
        connection.setUser(username);
        connection.setPassword(password);
        connection.setURL(url);
       
        return (Connection) connection.getConnection();
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }
}


MyAuthenticationProvider
Code:
package dphhs.tarts.security;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import java.util.Vector;

import oracle.jdbc.driver.OracleConnection;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.security.Authentication;
import org.springframework.security.AuthenticationException;
import org.springframework.security.BadCredentialsException;
import org.springframework.security.GrantedAuthority;
import org.springframework.security.GrantedAuthorityImpl;
import org.springframework.security.providers.AuthenticationProvider;
import org.springframework.security.providers.UsernamePasswordAuthenticationToken;


/**
* Authentication Provider checks the validity of the user's credentials.
* <p>
* The process to check the user's credentials verifies that the username
* is currently enabled, not expired, and has permission to connect to the
* Oracle database.
*
*/
public class MyAuthenticationProvider implements AuthenticationProvider {

    private MyAuthenticationConnectionProvider dataSource;
    private static final Logger log = LoggerFactory.getLogger(MyAuthenticationProvider.class);
   
    public MyAuthenticationProvider() {
    }

    /**
     * Assigns the dataSource to the Authentication Provider
     *
     * @param dataSource
     */
    public void setDataSource(MyAuthenticationConnectionProvider dataSource){
        this.dataSource = dataSource;
    }
   
    /**
     * Authenticates the username with password.
     * <p>
     * Using the gateway account a connection to the database is created. Then
     * if the username is determined to have the correct credentials to
     * attemp a connection to the database as proxy connection session is
     * started. If that is successful then the user authentiction object is
     * created.
     *
     * @param authentication
     * @return authentication
     * @throws AuthenticationException     unable to authenticate username
     */
    public Authentication authenticate(Authentication authentication)
            throws AuthenticationException {

        String username = (String) authentication.getPrincipal();
        String password = (String) authentication.getCredentials();
       
        log.debug("Set username and password to: [" + username + "/" + password + "]");
       
        Vector<GrantedAuthority> roles = new Vector<GrantedAuthority>();
       
        Connection conn = null;
       
        // Connect to the databse using the gateway keeper account and then
        // create a proxy connection session using the user's credentials
        // if that is successful then create the user authentication token
        try {
           
            conn = dataSource.getConnection();
           
            OracleConnection oConn = (OracleConnection) conn;
           
            Properties prop = new Properties();
            prop.setProperty(OracleConnection.PROXY_USER_NAME, username + "/" + password);

            // Creates a proxy session to the database       
            oConn.openProxySession(OracleConnection.PROXYTYPE_USER_NAME, prop);
           
            // Get all of the roles assigned to the user
            PreparedStatement statusStmt = conn.prepareStatement("SELECT GRANTED_ROLE FROM USER_ROLE_PRIVS");
//            PreparedStatement statusStmt = oConn.prepareStatement("SELECT GRANTED_ROLE, 'ROLE_USER' as AUTHORITY FROM USER_ROLE_PRIVS");
           
            ResultSet rs = statusStmt.executeQuery();
           
            // If the user is successful to this point then assign the default
            // user role to the user.
            roles.add(new GrantedAuthorityImpl("ROLE_USER"));
           
            while (rs.next()){
                String role = rs.getString("GRANTED_ROLE");
//                String role = rs.getString("AUTHORITY");
               
                if (!role.startsWith("ROLE_"))
                    role = "ROLE_" + role;
               
                roles.add(new GrantedAuthorityImpl(role));
//                System.out.println("role: " + role);
            }
            statusStmt.close();
           
            oConn.close(OracleConnection.PROXY_SESSION);
            oConn.close();
        }
        catch (SQLException e) {

//            e.printStackTrace();

            // throw oracle error message
            throw new BadCredentialsException(e.getMessage());
        }
        finally {
            try {
                if (conn != null)
                    conn.close();
            }
            catch(SQLException e){
               
            }
        }
       
        GrantedAuthority[] aRoles = new GrantedAuthority[roles.size()];
        roles.toArray(aRoles);
       
        UsernamePasswordAuthenticationToken auth =
            new UsernamePasswordAuthenticationToken(username, password, aRoles);
       
        return auth;
    }

    @SuppressWarnings("unchecked")
    public boolean supports(Class authentication) {
        return authentication.equals(UsernamePasswordAuthenticationToken.class);
    }

}


MyConnectionProvider
Code:
package dphhs.tarts.security;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

import oracle.jdbc.driver.OracleConnection;

import org.hibernate.connection.C3P0ConnectionProvider;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.support.nativejdbc.C3P0NativeJdbcExtractor;
import org.springframework.security.Authentication;
import org.springframework.security.context.SecurityContextHolder;

/**
* Connection Provider creates connection.
* <p>
* This performs Oracle Java Database Connectivity (JDBC) proxy
* authentication, also known as N-tier authentication. That allows one JDBC
* connection to act as a proxy for an other JDBC connection. The connection
* is established using a gateway keeper account that is authenticated to the
* database. Over that active connection the driver issues a command to create
* a session for the logged in user. The server returns a new session id and
* drive with send a switch command to switch to this new session. Allowing
* the session to use the current user credentials over the gateway keeper's
* connection. Allowing the connection only be established once.
* <p>
* Reference:
* <ul>
* <li>http://rocksolutions.wordpress.com/2010/03/23/proxy-authentication-in-oracle-jdbc/
* <li>http://download.oracle.com/docs/cd/B19306_01/java.102/b14355/proxya.htm#CHDHHAAD
* </ul>
*
*/
public class MyConnectionProvider extends C3P0ConnectionProvider {
   
    private static final Logger log = LoggerFactory.getLogger(MyConnectionProvider.class);
   
    public MyConnectionProvider() {
    }

    /**
     * Connection to database is established and returned to calling application.
     * This method determines if the user is authenticated via spring security
     * and if they are then a proxy connection is established otherwise the
     * gateway connection is then established.
     *
     * @return        java.sql.Connection
     * @exception    SQLException         If connection could not be established.
     */
    @Override
    public Connection getConnection() throws SQLException {
       
        Authentication auth = null;
       
        try {
            // Get SecurityContextHolder context authentication from Spring
            // Security. This will throw exception if user has not been
            // authenticated by Spring Security.
           
            auth = SecurityContextHolder.getContext().getAuthentication();
            log.debug("SecurityContextHolder.getContext().getAuthentication()[" + auth.getName() + "/" + auth.getCredentials() + "]" );
        } catch (Exception e) {
           
            log.debug("No SecurityContextHolder.getContext().getAuthentication() found.", e);
//            e.printStackTrace();
        }
       
        if(auth != null){
           
            log.debug("Connection will be setup as ORACLE_PROXY");
            return setUserForConnection(super.getConnection(), auth);
        }
        else {
           
            log.debug("Connection is not a proxy");
            return super.getConnection();
        }
    }
   
    /**
     * Establishes the proxy session over the current database connection.
     *
     * @param     pConn            a currently authenticated connection
     * @param     auth            a Spring Security authenticated object
     * @return    Connection
     * @throws     SQLException    If connection or proxy session can not be established
     */
    private Connection setUserForConnection(Connection pConn, Authentication auth)
            throws SQLException {
       
        try {
           
            log.debug("Creating proxy session with username/password: [" +    auth.getName() + "/" + auth.getCredentials() + "]");
           
            // Get the current authenticated connection from the Jdbc driver
            C3P0NativeJdbcExtractor c3p0NativeJdbcExtractor = new C3P0NativeJdbcExtractor();
            OracleConnection conn = (OracleConnection) c3p0NativeJdbcExtractor.getNativeConnection(pConn);
           
            // Set properties as proxy users with username and password
            Properties prop = new Properties();
            prop.setProperty(OracleConnection.PROXY_USER_NAME, auth.getName() + "/" + auth.getCredentials());
           
            // Open proxy session and authenticate username and password
            // If user is not authenticated then SQLExcepiton with be thrown
            conn.openProxySession(OracleConnection.PROXYTYPE_USER_NAME, prop);
           
            return conn;
       
        } catch (SQLException e){
            e.printStackTrace();
            throw e;
        }
    }
   
    /**
     * Close open session and if proxy session close that first.
     *
     * @param    conn Connection
     * @throws    SQLException    if unable to close connection
     */
    @Override
    public void closeConnection(Connection conn) throws SQLException {
       
        log.debug("Close Connection");
        this.closeProxyConnection(conn);
        super.closeConnection(conn);
    }
   
    /**
     * When the proxy connection is established close the proxy session first.
     * 
     * @param pConn    Current Connection
     */
    private void closeProxyConnection(Connection pConn){
       
        try {
           
            // get the current connection to the database
            C3P0NativeJdbcExtractor c3p0NativeJdbcExtractor = new C3P0NativeJdbcExtractor();
            OracleConnection conn = (OracleConnection)c3p0NativeJdbcExtractor.getNativeConnection(pConn);
           
            log.debug("Before closing connection: Hibernate session is closed: " + pConn.isClosed());
            log.debug("Before closing connection: Oracle session is closed: " + conn.isClosed());
           
            // close connection
            if(conn.isProxySession()){
                log.debug("Closing OracleConnection.PROXY_SESSION");
                conn.close(OracleConnection.PROXY_SESSION);
                conn.close();
            }
            else {
                log.debug("Connection is not PROXY_SESSION");
            }
       
            log.debug("After closing connection: Hibernate session is closed: " + pConn.isClosed());
            log.debug("After closing connection: Oracle session is closed: " + conn.isClosed());
           
        } catch (Exception e){
           
            log.debug("Error closing PROXY_SESSION");
            e.printStackTrace();
        }
    }
}


Top
 Profile  
 
 Post subject: Re: Oracle Proxy Authentication and Connection Pooling
PostPosted: Mon Aug 06, 2012 8:17 am 
Newbie

Joined: Sat Aug 04, 2012 1:06 pm
Posts: 1
Did You solve this issue?
I have the same problem.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 2 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.