Need help configuring Grails with Oracle JDBC thin client Encryption

Question

I need to set up our Grails app with AES encryption going to Oracle. In all the Java examples I have seen you create a Properties object, create an OracleDataSource and call setProperties, like so:

OracleDriver dr = new OracleDriver();
Properties prop = new Properties();
prop.setProperty( OracleConnection.CONNECTION_PROPERTY_THIN_NET_ENCRYPTION_LEVEL,AnoServices.ANO_REQUIRED);
// set more
(OracleConnection)dr.connect(url,prop);

In Grails, connections are handled by DataSource.groovy, which is a BasicDataSource, and so although you can configure properties easily with a closure like the following:

myDatasource {
pooled = false
driverClassName = "oracle.jdbc.OracleDriver"
dbCreate = "validate" // one of 'create', 'create-drop', 'update', 'validate', ''
dialect = "org.hibernate.dialect.Oracle10gDialect"
username = username
password = password
url =      url
logSql = true
format_sql = true
pooled = true
properties {
    maxActive = 8
    maxIdle = 4
    minIdle = 1
    initialSize = 1
    minEvictableIdleTimeMillis = 60000
    timeBetweenEvictionRunsMillis = 60000
    maxWait = 10000
    validationQuery = "select 1 from dual"
    testOnBorrow=true
    testWhileIdle=true
    testOnReturn=true
}

All of the properties in that closure correspond to setters for BasicDataSource, so you can't just shove the OracleConnection properties in there.

Has anyone found a way to set up Oracle encryption for Grails? I would appreciate any help you can give

Brian

Burt Beckwith Burt Beckwith · Accepted Answer · 2016-05-07T02:01:57

The datasource is configured from settings in DataSource.groovy but only if there isn't already a Spring bean configured, and it's easy to configure one yourself in resources.groovy.

One thing to be aware of is that although we use the dataSource bean, that's a proxy of the real bean (actually a proxy of a proxy - one to ensure that calls to getConnection() return the current connection used by an active transaction if one exists and another that avoids database initialization calls if a retrieved pooled connection doesn't end up being used for a query before being returned) so you should override the dataSourceUnproxied bean to retain the benefits of the proxies.

I don't have access to an Oracle instance to test this but it should be close to what you need:

import oracle.jdbc.OracleConnection
import oracle.jdbc.pool.OracleDataSource
import oracle.net.ano.AnoServices

beans = {

   def props = [
      (OracleConnection.CONNECTION_PROPERTY_THIN_NET_ENCRYPTION_LEVEL): AnoServices.ANO_REQUIRED,
      (OracleConnection.CONNECTION_PROPERTY_THIN_NET_ENCRYPTION_TYPES): '(' + AnoServices.ENCRYPTION_AES256 + ')',
      (OracleConnection.CONNECTION_PROPERTY_THIN_NET_CHECKSUM_LEVEL): AnoServices.ANO_REQUESTED,
      (OracleConnection.CONNECTION_PROPERTY_THIN_NET_CHECKSUM_TYPES): '(' + AnoServices.CHECKSUM_SHA1 + ')']

   dataSourceUnproxied(OracleDataSource) {
      connectionProperties = props as Properties
      implicitCachingEnabled = true
      password = '...'
      delegate.URL = '...' // can't use URL = '...' because of Groovy's default import of java.net.URL
      user = '...'
   }
}

Depending on the version of the driver the property names might be different (e.g. properties instead of connectionProperties, cachingEnabled instead of implicitCachingEnabled, etc.) and you can set additional properties as needed corresponding to the various setter methods available, e.g. fastConnectionFailoverEnabled = true for setFastConnectionFailoverEnabled(true).

Depending on the version of Grails the configured dataSource is probably a Tomcat JDBC org.apache.tomcat.jdbc.pool.DataSource and not a commons-dbcp BasicDataSource; the change was made for Grails 2.3 and earlier versions can (and should because of its significantly better performance) use the jdbc-pool plugin.

I looked at the source of the Tomcat JDBC driver wondering if it could/should be used as the real DataSource impl and letting it call getConnection() on the Oracle driver and saw that it supports a connectionProperties property that might make this simpler. It might be possible to concatenate the encryption properties in the supported format and add that to

dataSource {
   driverClassName = 'oracle.jdbc.OracleDriver'
   username = '...'
   ...
   connectionProperties = 'oracle.net.encryption_types_client=(AES256);' +
                          'oracle.net.encryption_client=REQUIRED;' +
                          'oracle.net.crypto_checksum_client=REQUESTED;' +
                          'oracle.net.crypto_checksum_types_client=(SHA1)'
}

Check out the Tomcat JDBC Pool docs for more info.

Need help configuring Grails with Oracle JDBC thin client Encryption

1 Answers