Oracle Database Strong Authentication (certificate)

1) Create and configure the server wallet

Create an empty server wallet:

$> mkdir /u01/app/oracle/server_wallet
$> cd /u01/app/oracle/server_wallet
$> orapki wallet create -wallet /u01/app/oracle/server_wallet -auto_login -pwd welcome1

After running the above command the following two files will be created in the directory:

$> ls -la /u01/app/oracle/server_wallet

-rw------- 1 oracle oinstall 3589 May  4 11:53 cwallet.sso
-rw------- 1 oracle oinstall 3512 May  4 11:53 ewallet.p12

Create server's self signed certificate:

$> orapki wallet add -wallet /u01/app/oracle/server_wallet -dn "CN=server" -keysize 512 -self_signed -validity 365 -pwd welcome1

2) Create and configure the client wallet

Create an empty wallet for the client(into a new directory) :

$> mkdir /u01/app/oracle/client_wallet
$> cd /u01/app/oracle/client_wallet
$> orapki wallet create -wallet /u01/app/oracle/client_wallet -auto_login -pwd welcome2

Note:  We used the -auto_login option to make sure that we are able to move the cwallet.sso file to other machines. If the client is running on the same machine as the server then you can use the -auto_login_local

The next step is to create a certificate request :

orapki wallet add -wallet . -dn "CN=client,O=oracle,C=ro" -keysize 1024 -pwd "Welcome2"
orapki wallet export -wallet . -dn "CN=client,O=oracle,C=ro" -request ./certificate_request.txt -pwd "welcome1"

Copy the certificate request from the "client" directory to the "server" directory:

scp certificate_request.txt oracle@192.168.1.101:/u01/app/oracle/server_wallet

Sign the certificate of the client and also export server's CA certificate:

$> cd /u01/app/oracle/server_wallet
$> orapki cert create -wallet . -request ./certificate_request.txt -cert ./signed_certificate.cert -validity 365 -pwd "welcome1"
$> orapki wallet export -wallet /u01/app/oracle/server_wallet -dn "CN=server" -cert server_ca.cert

Note: The server's auto login wallet is used as a certificate authority. By using this method you will avoid using a real CA to sign the certificates of the clients.

Copy the signed certificate(signed_certificate.cert) and the server's root certificate ( server_ca.cert) to the client's wallet directory and import them into client's wallet:

$> cd  /u01/app/oracle/server_wallet
$> cp signed_certificate.cert /u01/app/oracle/client_wallet/
$> cp server_ca.cert /u01/app/oracle/client_wallet/

$> orapki wallet add -wallet . -trusted_cert -cert ./server_ca.cert -pwd "welcome2"
$> orapki wallet add -wallet . -user_cert -cert ./signed_certificate.cert -pwd "welcome2"

3) Create the user within the database

SQL> create user client identified externally as 'CN=client,O=oracle,C=ro';
SQL> grant create session to client;

Note: The user within the database has to be created specifying the distiguished name (DN) on their certificate. The user certificate created earlier had the DN 'CN=client,O=oracle,C=ro' so we have to use the same string while creating the user in the database

Note: Steps 2) & 3) can be repeated for all the users that will be authenticated using SSL. In the example we configured the database user and the client wallet and the certificates for a generic user called "client" but the same steps apply to any user.

After performing all the above steps we have all the needed certificates for both the server and the client in the server_wallet and client_wallet directories on the database server machine. Furthermore we do have an user in the database that is associated with the DN of the certificate stored in the client's wallet. We executed all these steps on the same machine(on the DB Server) just to emphasize the idea that we can centralize the configuration of the SSL authentication.  For a large user population we can repeat steps 2) and 3) until we get the wallets with the right certificates for all the users and have all the users created in the database.

4) Configuring server side listener

Configure a TCPS listener address

Use Net Manager to create an new TCPS listener or add new TCPS address to an existing listener. You will also need to add the wallet location. For example,

LISTENER =
   (DESCRIPTION_LIST =
     (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = seclin4.ro.oracle.com)(PORT = 1521))
     )
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCPS)(HOST = seclin4.ro.oracle.com)(PORT = 1522))
     )
  )

WALLET_LOCATION =
  (SOURCE=
   (METHOD=File)
    (METHOD_DATA=
     (DIRECTORY=/u01/app/oracle/server_wallet)))

5) Configuring server side sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCPS)
SSL_VERSION = 0
SSL_CLIENT_AUTHENTICATION = TRUE
WALLET_LOCATION =
  (SOURCE =
   (METHOD = FILE)
    (METHOD_DATA =
     (DIRECTORY = /u01/app/oracle/server_wallet)
    )
   )

6) Configuring client side sqlnet.ora

In the client sqlnet.ora set TCPS as an authentication method and set the wallet location,

SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCPS)
SSL_VERSION = 0
SSL_CLIENT_AUTHENTICATION = TRUE

WALLET_LOCATION =
  (SOURCE =
   (METHOD = FILE)
   (METHOD_DATA =
   (DIRECTORY = /u01/app/oracle/client_wallet)
   )
  )

7) Configuring client side tnsnames.ora

Use Net Manager or Net Configuration Assistant to create a service name using TCPS, e.g.

ORCLSSL =
  (DESCRIPTION =
    (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCPS)(HOST = seclin4.ro.oracle.com)(PORT = 1522))
    )
    (CONNECT_DATA =
     (SERVICE_NAME = ORCL)
    )
   )

8) Configuring the database

The database parameter OS_AUTHENT_PREFIX must be null and REMOTE_OS_AUTHENT must be FALSE:

SQL> alter system set remote_os_authent=FALSE scope=spfile;
SQL> alter system set os_authent_prefix='' scope=spfile;

The instance will need to be restarted for these changes to take effect.

9) Testing the configuration

The first test is to confirm the client is able to tnsping the alias for the TCPS listener.This will confirm that the client wallet is properly accessible by the client:

$> tnsping ORCLSSL

TNS Ping Utility for Linux: Version 10.2.0.2.0 - Production on 29-NOV-2006 10:16:32

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

Used parameter files:
/u01/10.2/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCPS)(HOST = 10.171.112.16)(PORT = 1522))) (CONNECT_DATA = (SID = v10g)))
OK (100 msec)

The second test is to connect as the user that is associated with the certificate stored in the wallet:

$> sqlplus /@ORCLSSL

SQL*Plus: Release 10.2.0.2.0 - Production on Wed Nov 29 10:17:27 2006

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

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

SQL>SQL> select user from dual;

USER
------------------------------
CLIENT