Addalab

X.509 certificates with Oracle Application Server

Starting from the first and second document I’m going to test the connection via certificates from an oracle application server to an oracle database.

The steps will be:

– application server security configuration (certificate generation),
– connection pool and data-source definition.
– test.

My architecture:

client: SLES9 with Oracle Application Server 10.1.3
client name: breoraasls02

server: SLES 9 SP2 with Oracle RDBMS 10.1.0.4
server name: breousdbls02
Instance name: UBANKP

APPLICATION SERVER SIDE:

Usually I generate the certificates and certificates requests with orapki.
this time I wished to test the owm and the GUI so:

oracle@breoraasls02:~> mkdir -p /opt/oracle/wallet/WALLETS/oracle

oracle@breoraasls02:~> owm &

From the GUI I create the wallet and the certificate request. Then I export this request.

When I have my certificate request I transport it on the server where my CA wallet is located:

oracle@breoraasls02:~/wallet/WALLETS/oracle> scp test_conn.req oracle@breousdbls02.ras:~/wallet/WALLETS/oracle
The authenticity of host ‘breousdbls02.ras (192.168.24.145)’ can’t be established.
RSA key fingerprint is 05:49:21:34:e6:77:8e:34:3a:89:12:98:4f:20:28:c7.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added ‘breousdbls02.ras,192.168.24.145’ (RSA) to the list of known hosts.
Password:
test_conn.req                                 100%  642     0.6KB/s   00:00

And I sign it creating a certificate.

orapki cert create -wallet wallet-ca -request test_conn.req -cert test_conn.cer -validity 3650

I transport the certificate back on the application server and with it my ca certificate.

oracle@breoraasls02:~/wallet/WALLETS/oracle> scp oracle@breousdbls02.ras:~/wallet/WALLETS/oracle/test_conn.cer .
Password:
test_conn.cer                                 100%  807     0.8KB/s   00:00

oracle@breoraasls02:~/wallet/WALLETS/oracle> scp oracle@breousdbls02.ras:~/wallet/WALLETS/oracle/root-ca.cer .
Password:
root-ca.cer                                   100%  807     0.8KB/s   00:00

With the oracle wallet manager I import the user certificate and the trusted certificate (the CA one).

Now on the database I create the user test_conn:

SQL> create user test_conn identified globally as ‘CN=test_conn,OU=AS,O=RAS,L=Milan,ST=Milan,C=IT’;

SQL> grant create session to test_conn;

and a dummy user pippo:

SQL> create user dummyuser identified by dummyuser;

SQL> grant create session to dummyuser;

This user is going to be authenticated without password.
The DN is checked instead and compared to the certificates’ DNs stored in the wallet.

From 10gR2 on you need to use:



create user Fabrizio identified externally as ‘CN=test_conn,OU=AS,O=RAS,L=Milan,ST=Milan,C=IT’;



To solve the error:



ORA-01017: invalid username/password; logon denied



instead of the global authentication!!!!

Now the network configuration.

SERVER (same as document two of the series):

In your listener.ora specify the port where to listen for the TPCS protocol and the position of your wallet.

listener.ora


LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = breousdbls02.ras)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCPS)(HOST = breousdbls02.ras)(PORT = 2484))
    )
  )

The sqlnet.ora also contains the wallet position and the crypto parameters:

SQLNET.AUTHENTICATION_SERVICES= (TCPS, BEQ)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
SSL_CLIENT_AUTHENTICATION = TRUE
SSL_CIPHER_SUITES= (SSL_RSA_EXPORT_WITH_RC4_40_MD5)
SSL_VERSION = 0

wallet_location =
 (SOURCE=
  (METHOD=File)
  (METHOD_DATA=
   (DIRECTORY=/opt/oracle/wallet/WALLETS/oracle/wallet-ubankp)))

CLIENT:

The tnsnames.ora contains the entry for the SERVICE_NAME for the TPCS protocol:

UBANKPSSL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCPS)(HOST = breousdbls02.ras)(PORT = 2484))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = UBANKP)
    )
    (SECURITY=
      (SSL_SERVER_CERT_DN="CN=UBANKP, OU=RAS, O=RAS, L=Milan, ST=Milan, C=IT")
    )
  )

SSL_SERVER_CERT_DN specify the DN used by the server certificate.

The sqlnet.ora contains the wallet location and the crypto parameters according to the server:

SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCPS)

SSL_VERSION = 0

TRACE_LEVEL_CLIENT = SUPPORT

SSL_CLIENT_AUTHENTICATION = TRUE

SSL_SERVER_DN_MATCH = TRUE

SSL_CIPHER_SUITES= (SSL_RSA_EXPORT_WITH_RC4_40_MD5)
WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /home/oracle/wallet/WALLETS/oracle)
    )
  )

Now, from the client:

oracle@breoraasls02:/u01/app/oracle/product/10.1/ias_1/network/admin> tnsping UBANKPSSL

TNS Ping Utility for Linux: Version 10.1.0.4.0 - Production on 23-DEC-2005 11:35:11

Copyright (c) 1997, 2003, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/10.1/ias_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCPS)(HOST = breousdbls02.ras)(PORT = 2484))) (CONNECT_DATA = (SERVICE_NAME = UBANKP)) (SECURITY= (SSL_SERVER_CERT_DN=CN=UBANKP, OU=RAS, O=RAS, L=Milan, ST=Milan, C=IT)))
OK (150 msec)

From here we need two configure the application server defining a data source and a connection pool.

Import note: for the configuration I’m using OCI (jdbc thick client) since I want to rely on my configuration files in $ORACLE_HOME/network/admin.

A later document is going to issue the configuration with thin client.

The configuration file is for the default OC4J called “home” is: $ORACLE_HOME/j2ee/home/config/data-sources.xml
If your OC4J is called, for example MYOC4J, the file would be: $ORACLE_HOME/j2ee/MYOC4J/config/data-sources.xml

The list of the OC4J can be obtained from the AS web console or from the command line:

$ORACLE_HOME/opmn/bin/opmnctl status

Where I define a new connection pool with:

</connection-pool>
   <connection-pool name='Certificate connection pool' max-connections='50' min-connections='5' >
   <connection-factory factory-class='oracle.jdbc.pool.OracleDataSource' user='' password='' url='jdbc:oracle:oci:@UBANKPSSL'>
</connection-factory>

and the data source:

<managed-data-source user='' password='' connection-pool-name='Certificate connection pool' jndi-name='jdbc/OracleTESTSSL' name='test_conn' />

At the end my file will look like this:

<?xml version="1.0"?>

<data-sources xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xsi:noNamespaceSchemaLocation='http://xmlns.oracle.com/oracleas/schema/data-sources-10_1.xsd' schema-major-version='10' schema-minor-version='1'>
   <managed-data-source connection-pool-name='Example Connection Pool' jndi-name='jdbc/OracleDS' name='OracleDS' />
   <managed-data-source user='' password='' connection-pool-name='Certificate connection pool' jndi-name='jdbc/OracleTESTSSL' name='test_conn' />
   <connection-pool name='Example Connection Pool' >
   <connection-factory factory-class='oracle.jdbc.pool.OracleDataSource' user='scott' password='tiger' url='jdbc:oracle:thin:@//localhost:1521/oracle.regress.rdbms.dev.us.oracle.com'>
</connection-factory>
</connection-pool>
   <connection-pool name='Certificate connection pool' max-connections='50' min-connections='5' >
   <connection-factory factory-class='oracle.jdbc.pool.OracleDataSource' user='' password='' url='jdbc:oracle:oci:@UBANKPSSL'>
</connection-factory>
</connection-pool>
</data-sources>

It can be done manually but because of my laziness and the fear of errors I relied on the Application server Administration console for then editing the above file manually.

Start defining the connection pool.
I use the OCI driver so my URL is going to be jdbc:oracle:oci:@UBANKSSL while my users will be the dummyuser.

This last user is used to configure the connection pool.
Then go into data-sources.xml and leave username and passwords blank.

Now define the data-source starting from the connection pool.
The username is just “/”. No password is defined.

This is the result:

Now test the connection from the data source. Click on Test Connection.

The connection was a success.
No password has been submitted by the data source.

Now the real connection by certificates:

oracle@breoraasls02:/u01/app/oracle/product/10.1/ias_1/j2ee/home/config> vi data-sources.xml

<?xml version="1.0"?>
<data-sources xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xsi:noNamespaceSchemaLocation='http://xmlns.oracle.com/oracleas/schema/data-sources-10_1.xsd' schema-major-version='10' schema-minor-version='1'>
   <managed-data-source connection-pool-name='Example Connection Pool' jndi-name='jdbc/OracleDS' name='OracleDS' />
   <managed-data-source user='' password='' connection-pool-name='Certificate connection pool' jndi-name='jdbc/OracleTESTSSL' name='test_conn' />
   <connection-pool name='Example Connection Pool' >
   <connection-factory factory-class='oracle.jdbc.pool.OracleDataSource' user='scott' password='tiger' url='jdbc:oracle:thin:@//localhost:1521/oracle.regress.rdbms.dev.us.oracle.com'>
</connection-factory>
</connection-pool>
   <connection-pool name='Certificate connection pool' >
   <connection-factory factory-class='oracle.jdbc.pool.OracleDataSource' user='' password='' url='jdbc:oracle:oci:@UBANKPSSL'>
</connection-factory>
</connection-pool>
</data-sources>

As you can see I deleted the username and passwords leaving them blank.

I test the connection and look into the database:

SQL> select username from V$session where username is not NULL;
USERNAME
------------------------------
TEST_CONN
DBSNMP
DBSNMP
SYS

My connection pool is authenticating with the certificate and the user is the one associated with the certificate DN: TEST_CONN.

Another test:

SQL> alter user test_conn quota unlimited on users;
User altered.

SQL> create table test_conn .dummytable (dummycol1 varchar2(20));

Table created.

SQL> insert into test_conn .dummytable (dummycol1) values ('This is a test');

1 row created.

SQL> commit;

Commit complete.

Now change the test performed by the data source from

select * from dual

to

select * from dummytable

addalabadmin

, , ,