End to end identity propagation with jboss 4, hibernate and oracle. Part 3

Usual way to get the database connection - use data source. I saw a toons of applications, that use the DataSource#getConnection() method to get the data base connection via configured in application server data source. And usual situation, data source configured with priveledged user (root for mysql, schema owner in oracle case, sa for mssql, etc.) name and his password. LOL data source tell you - here the access key, and there the place where you can get everything and even more. To prevent unauthorized access need to use DataSource#getConnection(String userName, String userPassord) instead of DataSource#getConnection(). At this point need to solve following issues:

• how to configure hibernate to get the JDBC connection with user name and password ?
• how to configure jboss, application and data source ?
• passing the password ?
• is created connection will be in connection pool ?

Here the answers:

If you are look to hibernate configuration documentation you can find an amazing configuration parameter - hibernate.connection.provider_class - That has classname of a custom org.hibernate.connection.ConnectionProvider which provides JDBC connections to Hibernate WOW ! This in definitely that need to use !

The source code see below.

/**
* User: iazarny
* Date: 18.08.2008
* Time: 17:44:21
*
* This custom connection provider will work via JBoss datasource with
* application-managment-security configuration
* @see@ hibernate.connection.provider_class configation parameter for Hibernate JPA provider
*/
public class OracleConnectionProvider implements ConnectionProvider {

private static Logger logger = Logger.getLogger(OracleConnectionProvider.class);

private static InitialContext ctx;
private static DataSource ds;
static {
try {
    ctx = new InitialContext();
    ds = (DataSource) ctx.lookup("java:/CrTimeDS");
} catch (NamingException e) {
    logger.fatal("Error ",e);
}
}

private Properties properties;


public void configure(Properties properties) throws HibernateException {
this.properties = properties;
}

public Connection getConnection() throws SQLException {
String userName = null;
String password = null;
if(SecurityAssociation.getPrincipal()!=null) {
    userName = SecurityAssociation.getPrincipal().getName();
    password = SecurityAssociation.getCredential().toString();
    logger.info("SecurityAssociation.getPrincipal = " + userName);
    logger.info("SecurityAssociation.getCredential() = " + password!=null );
}
if (password==null) {
    userName = properties.getProperty(Environment.USER);
    password = properties.getProperty(Environment.PASS);
    logger.info("Environment.USER = " + userName);
    logger.info("Environment.PASS = " + password!=null );    
}


return ds.getConnection(userName,password);

}

public void closeConnection(Connection connection) throws SQLException {
logger.info("OracleConnectionProvider connection return to pool " + connection );
connection.close();
}

public void close() throws HibernateException {
logger.info("OracleConnectionProvider detach from DataSouce");
ds = null;
}

public boolean supportsAggressiveRelease() {
return false;
}
}

Guess you remember from End to end identity propagation with jboss 4, hibernate and oracle. Part 2 how to configure tamcat/jboss-web valves to get the current user password from SecurityAssociation.

As you can see from connection provider example need to configure CrTimeDS datasource.

<?xml version="1.0" encoding="UTF-8"?>
<datasources>
<local-tx-datasource>
   <jndi-name>CrTimeDS</jndi-name>
   <connection-url>jdbc:oracle:thin:@127.0.0.1:1521:az3</connection-url>
   <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
   <!-- username and password not necessary, because of application-managed-security -->
   <user-name></user-name>
   <password></password>
   <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter
   </exception-sorter-class-name>
   <min-pool-size>10</min-pool-size>
   <max-pool-size>200</max-pool-size>
   <blocking-timeout-millis>1000</blocking-timeout-millis>
   <idle-timeout-minutes>1</idle-timeout-minutes>
   <application-managed-security/>
   <metadata>
       <type-mapping>Oracle9i</type-mapping>
   </metadata>
</local-tx-datasource>
</datasources>

In the persistence.xml connection provider wired with hibernate. In case if hibernate will try to check data base schema against domain model on application start and you find a lot of stack traces you need to add copule of lines.
<property name="hibernate.connection.username" value="readSchemaOnlyUser"/>
<property name="hibernate.connection.password" value="readSchemaOnlyPassword"/>

The readSchemaOnlyUser user in oracle, that can read schema meta data, but not actuall data. It easy with oracle

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
          xsi:schemaLocation="http://java.sun.com/xml/ns/persistence
          http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd"  version="1.0">
 <persistence-unit name="crtimedbUnit" transaction-type="JTA">
     <provider>org.hibernate.ejb.HibernatePersistence</provider>
     <jta-data-source>java:/CrTimeDS</jta-data-source>
     <properties>
         <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle9Dialect"/>
         <property name="hibernate.connection.charset" value="UTF-8"/>
         <property name="hibernate.cache.use_second_level_cache" value="false"/>
         <property name="hibernate.cache.provider_class" value="org.hibernate.cache.NoCacheProvider"/>
         <property name="hibernate.archive.autodetection" value="class"/>
         <property name="hibernate.default_schema" value="CRTIME"/>
         <property name="hibernate.connection.provider_class" value="com.crtime.connection.provider.OracleConnectionProvider"/>
     </properties>
 </persistence-unit>
</persistence>

In web application configuration jboss-web.xml you have to configure security domain

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE jboss-web PUBLIC "-//JBoss//DTD Web Application 2.3V2//EN"
      "http://www.jboss.org/j2ee/dtd/jboss-web_3_2.dtd">
<jboss-web>
  <security-domain>java:/jaas/crtime-webapp</security-domain>
</jboss-web>

And last question is created connection for user willl be pooled ? Simple answer - yes it is. If you not sure - dig the jboss source code :)

Hmmm... 3 articles, guess nedd to provide overview how it works from end to end.

When not authorized user hit the page he will be promted by login to input his name and password. Provided login name and password will be checked via CrTimeAuthDS datasource by standard java login module (see part 1) in case if result is successful, user become authorized. Database connection will be obtained configured CrTimeDS and hibernate connection provider by using DataSource#getConnection(user, password) method. So user not impersonalized at data base level as well as web and middleware levels.