Lock up Your Data Warehouse – Part 2

Continuing on with this theme of securing your data warehouse…The next stage, having secured the transmission of all data be accessed via SQL net, is to make sure that all tools and applications, or as many as possible, use these encrypted network transmission processes. Lets look at some of these tools in more detail:

This requires the use of SQLDeveloper 1.5 or later. There are a number of ways to configure database connections in SQLDeveloper and SQLDeveloper 1.5 added the option to use the OCI/Thick driver rather than the standard JDBC connection. This option is located in the Database: Advanced Parameters section of the Preferences page.


Once this preference has been set it is then possible to define a connection based on an existing TNSNAMES.ora entry.


However, the trick is to make sure you have set your TNS_ADMIN directory prior to launching SQLDeveloper. Personally, I use a batch file to set my environment variables and launch SQLDeveloper so I know all my paths are correctly set. Without this information it is very difficult to determine which TNSNAMES.ora file SQLDeveloper is using to generate the list of TNS entries, especially if you have lots of Oracle homes as I do on my laptop.


Assuming you can find the correct the TNS entry then test the connection to make sure everything is working correctly.


If you have left the tracing parameters in your SQLNET.ora file as per the last blog post on this subject you should see a trace file created once the connection has been established. Executing a query will show that the network encryption is active and being used. As before you search for the “na_tns:” string in the trace file:

2009-07-01 14:35:59.236239 : na_tns:Secure Network Services is available.
2009-07-01 14:35:59.236298 : nau_adi:entry
2009-07-01 14:35:59.236354 : nau_adi:exit
2009-07-01 14:35:59.236411 : na_tns: authentication is not active
2009-07-01 14:35:59.236470 : na_tns: encryption is active, using RC4_256
2009-07-01 14:35:59.236530 : na_tns: crypto-checksumming is not active

This obviously shows that encryption is working but it would be nice if we could see what is happening on the network in real-time. This would allow us to check to see if the data is actually visible or not.

If we turn off all the security features and then execute a query from SQLDeveloper the sniffer will pick up the following which shows all the data that was returned from the database as a result of executing the query “SELECT * FROM products”


If we now turn on all the data encryption features described in the previous blog posting the trace looks very different:


In this case the data is encrypted and it is impossible to extract any meaning from the data being passed back to SQLDeveloper.

ODBC Clients
How do you control clients using ODBC? The temptation is to use the standard Microsoft but I find this provides too little control over the configuration process.



In my opinion it is better to install the Oracle ODBC driver that comes with the Database Client as this gives you more control and feedback over which TNSNAMES.ora file is being used.



Once you have defined the basic connection details and selected the correct TNS entry from the pull-down list it is always advisable to test the connection. If a successful connection is created a trace file will be generated that will show that the network encryption is active and being used. As before you search for the “na_tns:” string in the trace file.

JDBC Clients
To support data encryption for connections via JDBC Thin drivers there are additional libraries that need to be loaded as part of your project. All the security features have been built-in to the Java implementation. In the java environment the parameters that are usually managed by the SQLNet.ora file are instead defined within the oracle.jdbc.OracleConnection interface. However, there is a degree of caution needed here because Oracle only ships one version of its JDBC classes JAR file. This means the single JAR file has to conform to export regulations. Therefore, only settings permitted suitable for export markets are supported. Depending on where you are based and your specific security requirements this may or may not be an issue.

In terms of implementing data encryption and data integrity within a connection, you need to use the Java properties object, that is, an instance of java.util.Properties, to set the data encryption and integrity parameters supported by the JDBC Thin driver.

The following example instantiates a Java properties object and then uses the properties object in opening a connection to the database:

Properties prop = new Properties();
prop.setProperty(OracleConnection.CONNECTION_PROPERTY_THIN_NET_ENCRYPTION_TYPES, "( DES40C )");
prop.setProperty(OracleConnection.CONNECTION_PROPERTY_THIN_NET_CHECKSUM_TYPES, "( MD5 )");

OracleDataSource ods = new OracleDataSource();ods.setProperties(prop);ods.setURL("jdbc:oracle:thin:@localhost:1521:main");
Connection conn = ods.getConnection();

The parentheses around the values encryption type and checksum type allow for lists of values. When multiple values are supplied, the server and the client negotiate to determine which value is to be actually used.

There is a lot more information about this in the Oracle Database JDBC Developer's Guide and Reference 11g Release which you can access via OTN. I would recommend Chapter 9 as this deals specifically with JDBC client-side security features.

That is it. We have now successfully secured all data communications between our data warehouse and client applications using SQLNet, ODBC or JDBC. This means that anyone using a packet sniffer on your network is not going to be able to intercept data as it moves around the network. The next step is to directly encrypt the key sensitive data elements within the database. This provides the next level of “complete piece of mind” that only Oracle Security can provide.

If you have not started to look at data security within your data warehouse now is good time to start. In the next few postings I will look at some of the other security features you can add to your data warehouse to help you protect your most valuable asset.

For those customers who are currently evaluating solutions from the various data vendors (and hopefully you have included Oracle in your list!) make sure you look at security. Don’t just get blinded by performance make sure you can protect your data warehouse because you don’t want data leaking out to anyone and everyone. It is quite surprising the number of times we talk about security to data warehouse customers and the response comes back “we are not looking at security at the moment, this is just a POC”. That is not a smart move in my opinion because you do not want to be saddled with a data warehouse platform that just leaks data everywhere. Fortunately, Oracle Database Machine runs Oracle Database and comes ready to implement Oracle Maximum Security so you can be sure your data warehouse platform is both safe and secure.

More to follow…


Hi Keith, very interesting article. Do you know if it's possible to do encryption w/p using OCI with SQL Developer? TIA Ted

Posted by Ted on August 19, 2009 at 11:30 PM PDT #

Hi Ted, I am not sure how this would work with OCI and the documentation is very vague and focuses mainly on JDBC. All I can find is the following statement in the Oracle® Database Advanced Security Administrator's Guide 11g Release 1 (11.1): Transparent Data Encryption with OCI Row shipping cannot be used, because the key to make the row usable is not available at the receipt-point. see here: http://download.oracle.com/docs/cd/B28359_01/network.111/b28530/asotrans.htm#BABFEEAE May be this would be a good question to post on the OCI forum on OTN. Keith

Posted by Keith Laker on August 20, 2009 at 07:33 PM PDT #

Post a Comment:
Comments are closed for this entry.

The data warehouse insider is written by the Oracle product management team and sheds lights on all thing data warehousing and big data.


« July 2016