<?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>

<?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>

<?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>

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;
    }
}

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);
    }

}

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();
        }
    }
}