Oracle Authentication with Certificates

Authentication with Certificates

Recently I had to implement some more advanced security in my shop and I got very frustated trying to collect information from internet.
Googling around didn't help me too mutch and the official oracle documentation was sometimes vague.

So I decided to write down what I have down and why.

This document is intended to help others not to repeat my errors and to receive some comment on some topic that I haven't fully understood yet.

Oracle permits different type of authentication. In particular, with the use of Oracle advanced security (a feature to be licensed), it is possible to authenticate a user using certificate.
This is useful for a multytier architecture where you wish strong authentication between the application server and the RDBMS.
It is even useful because you don't have to submitt a password anymore.

In the next document I'm going to elaborate.

For the use of the certificates you have three possibilities:

- Client and server import the repective certificates as trusted;
- server is used as CA and sign the client certificate.
- a CA is used to sign client and server PKI. The CA certificates is imported into the server (whatever certificates signed by the CA is granted access to server).

I'm going to perform the first configuration in this document and the third in the next one. I'm not going to implement 2 since 3 is more complex and educational.

The steps to perform to arrive to a working configuration are not commonplace so I'll try to simplify as much as I can.
The whole configuration is performed without GUI even because initially I couldn't get the "Oracle Advanced Security" tab from netmgr so I edited my configuration file with a vi.
To make it appear follow metalink note #156345.1:

Check the file 'NetProperties' in $ORACLE_HOME/network/tools:
The parameter INSTALLEDCOMPONENTS should include the value 'ANO' or 'ASO'.

Example:
INSTALLEDCOMPONENTS=CLIENT,NET8,JAVAVM,SERVER,ANO

If neither value 'ANO' nor 'ASO' is present, add it to the list.

Otherwise comment the parameter.

First of all you have to generate and store your certificates.

Oracle provide you with the Oracle Wallet Manager, which is the tool used to store your certificates, and orapki to generate, export, import, sign your certificates, requests and keys.
Orapki is provided for testing porpuse only.
You should rely on a Certification Authority for production systems.

Note: one of the most troblesome error I got with orapki was "No valid user certificate found in wallet ".
Follow the steps below to avoid it.

My architecture:

client: SuSE 9.1 with Oracle client 10.2.0.1
client name: linux

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

CLIENT SIDE:

Creation of the wallet. It is important to specify auto_login. Otherwise you are going to get the error: ORA-28759: Failed to open file.
This because the file cwallet.sso (generated when autologin is enabled) is not found by the client.

For the wallet creation you need to submitt a password which is going to be used for most of the operation on the wallet itself.

oracle@linux:~/wallet/WALLETS/oracle> orapki wallet create -wallet wallet-client -auto_login
Enter password:

Enter password again:

I generate a client self-signed certificate with an easy DN (my name: CN=Fabrizio).
The validity is 10 years.
As far as I know is not possible to specify a non-expiring date.
I tryed 100 years but the application crashed.

oracle@linux:~/wallet/WALLETS/oracle> orapki wallet add -wallet wallet-client -dn "CN=Fabrizio" -keysize 1024 -self_signed -validity 3650
Enter wallet password:

The certificate is exported into a file.

oracle@linux:~/wallet/WALLETS/oracle> orapki wallet export -wallet wallet-client -dn "CN=Fabrizio" -cert fabrizio.cer

SERVER SIDE:

A wallet is created even for the server.

oracle@breousdbls02:~/wallet/WALLETS/oracle> orapki wallet create -wallet wallet-ubankp -auto_login
Enter password:
Enter password again:

A certificate for the server is generated (I used the instance name as DN).

oracle@breousdbls02:~/wallet/WALLETS/oracle> orapki wallet add -wallet wallet-ubankp -dn "CN=UBANKP" -keysize 1024 -self_signed -validity 3650
Enter wallet password:       g

The certified is exported:

oracle@breousdbls02:~/wallet/WALLETS/oracle> orapki wallet export -wallet wallet-ubankp -dn "CN=UBANKP" -cert ubankp.cer

CLIENT SIDE:

The certificates need to be exchanged:

oracle@linux:~/wallet/WALLETS/oracle> scp fabrizio.cer oracle@breousdbls02.ras:~/wallet/WALLETS/oracle
Password:
fabrizio.cer                                  100% 605     0.6KB/s   00:00
oracle@linux:~/wallet/WALLETS/oracle> scp oracle@breousdbls02.ras:~/wallet/WALLETS/oracle/ubankp.cer .
Password:
ubankp.cer                                    100% 601     0.6KB/s   00:00

Now the certificate should be imported using orapki but the procedure always failed for me.

So I used the Oracle Wallet Manger to perform the operation (both: client and server).

CLIENT:

oracle@linux:~/wallet/WALLETS/oracle> owm &

SERVER:

Now on the database I create the user fabrizio:

SQL> create user Fabrizio identified globally as 'CN=Fabrizio';

SQL> grant create session to fabrizio;

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=Fabrizio';

To solve the error:

ORA-01017: invalid username/password; logon denied

instead of the global authentication!!!!

Now the network configuration.

SERVER:

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

listener.ora

oracle@breousdbls02:/u01/app/oracle/product/10.1/db_1/network/admin> cat listener.ora

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = /u01/app/oracle/product/10.1/db_1)
      (SID_NAME = UBANKP)
    )
)

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

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")
    )
)

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/wallet-client)
    )
)

Now, from the client:

oracle@linux:/app/oracle/product/10.2/client_1/network/admin> sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Dec 14 15:31:52 2005

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> CONNECT/@UBANKPSSL
Connected.
SQL> show user
USER is "FABRIZIO"

Or:

oracle@linux:/app/oracle/product/10.2/client_1/network/admin> sqlplus /@UBANKPSSL

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Dec 14 15:32:50 2005

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> show user
USER is "FABRIZIO"

Back to Index

Contact information:
fabrizio.magni _at_ gmail.com