Using Oracle 11.2g Data Encryption and Integrity Connection Pool in OAS10g

Posted by Steve Racanovic | Posted in , | Posted on 12:03 PM

0

I haven't had the chance to write something for a while now. I was recently working on new feature with oracle advanced security data encryption and integrity using a JDBC connection pool. So I'll write my steps up of how I went about configuring this in OAS 10g.

This setup allows for data encryption and integrity without the overhead of SSL.

I used the following products to configure the steps detailed here:

* Oracle Application Server 10.1.3.2.0
* JDev 10.1.3.4.0
* JDBC 11.2.0.1.0
* Oracle Database 11.2


I am referencing these documentation for further details:

Oracle® Database Advanced Security Administrator's Guide 11g Release 2 (11.2)
Configuring Network Data Encryption and Integrity for Oracle Servers and Clients
http://download.oracle.com/docs/cd/E11882_01/network.112/e10746/asoconfg.htm#insertedID0

Oracle® Database JDBC Developer's Guide, 11g Release 2 (11.2)
Support for Data Encryption and Integrity
http://download.oracle.com/docs/cd/E11882_01/java.112/e10589/clntsec.htm#EHAFHEIG

Here are my complete steps:

1. First follow the steps to configure the database: - http://download.oracle.com/docs/cd/E11882_01/network.112/e10746/asoconfg.htm#i1006517

Depending on your selection, your 'sqlnet.ora' file should look something like this when finished:

# sqlnet.ora Network Configuration File: /home/u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER= (MD5)

SQLNET.ENCRYPTION_SERVER = required

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

SQLNET.CRYPTO_SEED = 'example123456'

SQLNET.ENCRYPTION_TYPES_SERVER= (RC4_256)

ADR_BASE = /home/u01/app/oracle

SQLNET.CRYPTO_CHECKSUM_SERVER = required
Once the steps have been completed, restart the listener and wait for the service to come back up.
[oracle@beast admin]$ lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 20-APR-2010 06:39:08

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1523)))
The command completed successfully
[oracle@beast admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 20-APR-2010 06:39:11

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Starting /home/u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /home/u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /home/u01/app/oracle/diag/tnslsnr/beast/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1523)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=beast.au.oracle.com)(PORT=1523)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1523)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 20-APR-2010 06:39:11
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /home/u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /home/u01/app/oracle/diag/tnslsnr/beast/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1523)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=beast.au.oracle.com)(PORT=1523)))
The listener supports no services
The command completed successfully
[oracle@beast admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 20-APR-2010 06:39:17

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1523)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 20-APR-2010 06:39:11
Uptime 0 days 0 hr. 0 min. 5 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /home/u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /home/u01/app/oracle/diag/tnslsnr/beast/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1523)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=beast.au.oracle.com)(PORT=1523)))
The listener supports no services
The command completed successfully
[oracle@beast admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 20-APR-2010 06:40:24

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1523)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 20-APR-2010 06:39:11
Uptime 0 days 0 hr. 1 min. 12 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /home/u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /home/u01/app/oracle/diag/tnslsnr/beast/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1523)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=beast.au.oracle.com)(PORT=1523)))
Services Summary...
Service "linux11gr2" has 1 instance(s).
Instance "linux11gr2", status READY, has 1 handler(s) for this service...
Service "linux11gr2XDB" has 1 instance(s).
Instance "linux11gr2", status READY, has 1 handler(s) for this service...
The command completed successfully

2. Download JDBC driver 11.2.0.1.0. You can get the driver from here. http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html
Since OAS comes with JDK 1.5_06 by default, I'll used the 'ojdbc5.jar'.

3. In My Oracle Support, review document 420303.1 - How to Use The Latest Thin JDBC Driver Across All Applications For a 10.1.3.x OAS Container

Based on that document, here are the steps how I upgraded the driver and verified it updated correctly:

4. I created a new instance called JDBC112010

> createinstance -instanceName JDBC112010

5. I started the new instance and checked to make sure it was up.

> opmnctl startproc process-type=JDBC112010
opmnctl: starting opmn managed processes...

> opmnctl status

Processes in Instance: web.sracanov-au.au.oracle.com
---------------------------------+--------------------+---------+---------
ias-component | process-type | pid | status
---------------------------------+--------------------+---------+---------
OC4JGroup:default_group | OC4J:JDBC112010 | 5124 | Alive
...

6. I then deploy the following application 'drvtest.ear'

7. I accessed and ran the application from URL - 'http://<server>:<port>/drvtest/dbdetails.jsp'

I completed my database details and clicked on the 'Submit' button.


The current JDBC details are displayed:

=============
JDBC Driver Name is ........ Oracle JDBC driver
JDBC Driver Version is ..... 10.1.0.5.0
JDBC Driver Major Version is 10
JDBC Driver Minor Version is 1
=============

8. I then continued to followed Doc ID 420303.1 to upgrade the JDBC Driver.

In point 7 of the document, I entered

Shared Library Name : oracle.jdbc
Shared Library Version : 11.2.0.1.0


NOTE: The name must be 'oracle.jdbc'

9. In point 15 of the document, I first created a backup of 'system-applications.xml'. I then entered



NOTE: The name must be 'oracle.jdbc'. The version details must match version enter in step 8.

10. After restarting the instance, I then accessed the test application again. 'http://<server>:<port>/drvtest/dbdetails.jsp'

I completed my database details and clicked on the 'Submit' button.

This is the results I received

=============
JDBC Driver Name is ........ Oracle JDBC driver
JDBC Driver Version is ..... 11.2.0.1.0
JDBC Driver Major Version is 11
JDBC Driver Minor Version is 2
=============



I can now see and confirm the JDBC Driver upgrade worked successfully.

11. Then created the datasource. You can do this following document 456270.1 - Creating a datasource in Application Server Control (ASC)/Enterprise Manager (EM) for 10.1.3.X

Here is how my connection pool looks like in 'data-sources.xml':











NOTE: You must use a managed datasource with oracle.jdbc.OracleDriver factory class. Can not use native data source. The pool properties there do not implement the encryption properties from oracle.jdbc.OracleConnection interface.

The XXX_TYPES should match the setting as configure in the database. Step 1


12. Download and deploy this application EAR file 'drvtest2.ear' which you will then connect to this connection pool.



Access the URL 'http://<server>:<port>/drvtest2/dbdetails.jsp' and enter the JNDI of the pool and click 'Submit'.



Your results should look something like:

System Information

JDK Details

=============
JDK Vendor is ... Sun Microsystems Inc.
JDK Version is ... 1.5.0_06
=============

JDBC Driver Details

=============
JDBC Driver Name is ........ Oracle JDBC driver
JDBC Driver Version is ..... 11.2.0.1.0
JDBC Driver Major Version is 11
JDBC Driver Minor Version is 2
=============

Database Details

=============
Database Product Name is ... Oracle
Database Product Version is Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Secure Algorithm Details

=============
Encryption algorithm is: RC4_256
Data Integrity algorithm is: MD5




Now I can see the encryption & data integrity algorithm used in this connection. You can further download a tool like wireshark - http://www.wireshark.org/ and run it on the application server to monitor this connection pool and confirm the message are encrypted and unreadable.



You can download the JDev workspace from here

Comments (0)