Authenticate yourself with certificates

Authentication via X.509 certificates

Starting from the previous document I’m going to describe a more complex configuration.
Basically I’m adding a certification authority (faked by a third oracle wallet) to sign the client and server X509 certificates.

I’m even introducing more complex DNs.

Still the configuration is textual only (I’m still stuck to command line and text configuration files).
If you prefer you can go with the GUI but I believe that the command line is still more educational (at least for myself).

Please, start from a simpler environment like the one described here.

Orapki is used to create and manage the wallet and certificates
If you are getting “No valid user certificate found in wallet ” follow the steps provided below.
.
Instead of orapki you should rely on a real Certification Authority for production systems.

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 auto-login is enabled) is not found by the client.

For the wallet creation you need to submit 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:

Add a certificate request..

oracle@linux:~/wallet/WALLETS/oracle> orapki wallet add -wallet wallet-client -dn “CN=Fabrizio Magni, OU=ICT, O=GESI, L=Milan, ST=Milan, C=IT” -keysize 1024
Enter wallet password:

Export the certificate request.

oracle@linux:~/wallet/WALLETS/oracle> orapki wallet export -wallet wallet-client -dn “CN=Fabrizio Magni, OU=ICT, O=GESI, L=Milan, ST=Milan, C=IT” -request fabrizio.req

SERVER SIDE:

I start by creating a fake certification authority with a wallet called wallet-ca:

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

Then I create a trusted certificate (auto_signed) valid for 10 years:

oracle@breousdbls02:~/wallet/WALLETS/oracle> orapki wallet add -wallet wallet-ca -dn “CN=root-cert, OU=RAS, O=RAS, L=Milan, ST=Milan, C=IT” -keysize 1024 -self_signed -validity 3650
Enter wallet password:

And export it:

oracle@breousdbls02:~/wallet/WALLETS/oracle> orapki wallet export -wallet wallet-ca -dn “CN=root-cert, OU=RAS, O=RAS, L=Milan, ST=Milan, C=IT” -cert root-ca.cer

Time for the real server wallet:

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

A certificate request for the server is generated.

oracle@breousdbls02:~/wallet/WALLETS/oracle> orapki wallet add -wallet wallet-ubankp -dn “CN=UBANKP, OU=RAS, O=RAS, L=Milan, ST=Milan, C=IT” -keysize 1024
Enter wallet password: g

The request is then exported.
Note: the DN is reverted. Otherwise you would get a “not found in wallet” error.

oracle@breousdbls02:~/wallet/WALLETS/oracle> orapki wallet export -wallet wallet-ubankp -dn “C=IT, ST=Milan, L=Milan, O=RAS, OU=RAS, CN=UBANKP” -request ubankp.req

Now you need to sign the certificate requests with your ca root certificate (exchange the files via scp):

oracle@breousdbls02:~/wallet/WALLETS/oracle> orapki cert create -wallet wallet-ca -request ubankp.req -cert ubankp.cer -validity 3650

oracle@breousdbls02:~/wallet/WALLETS/oracle> orapki cert create -wallet wallet-ca -request fabrizio.req -cert fabrizio.cer -validity 3650

Import the signed certificates into the client and server (exchange the files via scp):

oracle@breousdbls02:~/wallet/WALLETS/oracle> orapki wallet add -wallet wallet-ubankp -cert ubankp.cer
Enter wallet password:
oracle@breousdbls02:~/wallet/WALLETS/oracle> orapki wallet add -wallet wallet-client -cert fabrizio.cer
Enter wallet password:

oracle@linux:~/wallet/WALLETS/oracle> orapki wallet add -wallet wallet-client -cert ubankp.cer
Enter wallet password:
oracle@linux:~/wallet/WALLETS/oracle> orapki wallet add -wallet wallet-client -cert fabrizio.cer
Enter wallet password:

The above command seemed to succed but I couldn’t find any imported certificate inside the wallet so I used the GUI utility.
In this step you need to import the user certificate (the one which complete the request for certificate) and the CA certificates.

You don’t need to exchange the certificates since the CA root certificate is thrusted and anyone with a certificate signed by the root CA can authenticate (if the DN is correct).

The utility (Oracle Wallet Manager) is launched by command line issuing:

oracle@breousdbls02:~/wallet/WALLETS/oracle> owm

Now on the database I create the user fabrizio:

SQL> create user Fabrizio identified globally as ‘CN=Fabrizio Magni,OU=ICT,O=GESI,L=Milan,ST=Milan,C=IT’;

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 Magni,OU=ICT,O=GESI,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:

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

Now, from the client:

oracle@linux:~/wallet/WALLETS/oracle> tnsping UBANKPSSL

TNS Ping Utility for Linux: Version 10.2.0.1.0 – Production on 15-DEC-2005 12:56:32

Used parameter files:
/app/oracle/product/10.2/client_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 (180 msec)
oracle@linux:~/wallet/WALLETS/oracle> sqlplus /@UBANKPSSL

SQL*Plus: Release 10.2.0.1.0 – Production on Thu Dec 15 12:57:23 2005

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”

Common errors:

If you get:
TNS-12560: TNS:protocol adapter error

Maybe you need to reload/restart your listener.