Configuring SSL for Client Authentication and Encryption With Self Signed Certificates On Both Ends Using orapki

Configuring SSL for Client Authentication and Encryption With Self Signed Certificates On Both Ends Using orapki

Applies to:
Advanced Networking Option - Version 10.2.0.5 to 11.2.0.2 [Release 10.2 to 11.2]
Information in this document applies to any platform.
Checked for relevance on 29-Apr-2013
Purpose

The note has been written using Oracle Enterprise Linux 4.0 and 5.0,  and Oracle  RDBMS versions 10.2.0.5, 11.1.0.7, and 11.2.0.2, although the steps are generic and should apply to all platforms.

In this note the client and server are separate machines to help clarify which configuration is server side and which is client side. This connection authenticates both the server and the client.


One can set up the SSL authentication as described in this note only when testing this authentication method or when the number of  the users authenticated via SSL is not large. The rationale is that in this note the client signs it's own certificate and we import the root certificate of the client into the wallet of the server. As such for a large number of different users we will end up with too many client root certificates being imported into the wallet of the server.



This note uses the Oracle command line tool orapki to generate self signed certificates and to manipulate the wallets. Some of the steps could be done using Oracle Wallet Manager but this note will focus on using orapki.

Oracle's primary reference for SSL is the Oracle Advanced Security Administrator's Guide. This guide describes Oracle's SSL solution and configuration in greater detail, see Configuring Secure Sockets Layer Authentication. The guide is available on the documentation CD.
Scope

This note is intended for use by Database Administrators.

Details

Configuring the server and client wallets


1) Create and configure the server wallet


All of the steps to create and configure the wallet are done from the UNIX shell (or cmd.exe on Windows).

The first step is optional and is to create a directory to put the wallet in if one does not already exist,


$> mkdir /u01/10.2/server_wallet



Then change directory into this directory,

$> cd /u01/10.2/server_wallet
$> cd /u01/10.2/server_wallet



Next we will use orapki to create the initial wallet,


$> orapki wallet create -wallet /u01/10.2/server_wallet -auto_login -pwd Welcome1



This will have created two new files in the directory,

$> ls -la /u01/10.2/server_wallet

-rw------- 1 oracle dba 7940 Nov 29 08:14 cwallet.sso
-rw------- 1 oracle dba 7912 Nov 29 08:14 ewallet.p12



The next step is to create a self-signed certificate. This will generate both a user certificate and the CA root certificate that is signing it,


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




If this wallet was opened using Oracle Wallet Manager (owm) it is possible to see these 2 new certificates.

The final step is to export the CA root certificate,


$> orapki wallet export -wallet /u01/10.2/server_wallet -dn "CN=server" -cert server_ca.cert


2) Create and configure the client wallet

All of the steps to create and configure the wallet are done from the UNIX shell (or cmd.exe on Windows).

The first step is optional and is to create a directory to put the wallet in if one does not already exist,


$> mkdir /u01/10.2/client_wallet



Then change directory into this directory,


$> cd /u01/10.2/client_wallet



Next we will use orapki to create the initial wallet,


$> orapki wallet create -wallet /u01/10.2/client_wallet -auto_login -pwd Welcome2



This will have created two new files in the directory,


$> ls -la /u01/10.2/client_wallet

-rw------- 1 adam adam 7940 2006-11-29 09:21 cwallet.sso
-rw------- 1 adam adam 7912 2006-11-29 09:21 ewallet.p12



The next step is to create a self-signed certificate. This will generate both a user certificate and the CA root certificate that is signing it,


$> orapki wallet add -wallet /u01/10.2/client_wallet -dn "CN=adam" -keysize 512 -self_signed -validity 365 -pwd Welcome2


If this wallet was opened using Oracle Wallet Manager (owm) it is possible to see these 2 new certificates.

The next step is to export the CA root certificate,


$> orapki wallet export -wallet /u01/10.2/client_wallet -dn "CN=adam" -cert client_ca.cert


The final step is to import the server's root certificate (server_ca.cert) into the client wallet,

and to import the client's root certificate (client_ca.cert) into the server wallet. It is assumed that the exported files are transferred via ftp or another mechanism between the machines.

So on the client the server's root certificate is imported with,


$> orapki wallet add -wallet /u01/10.2/client_wallet -trusted_cert -cert server_ca.cert -pwd Welcome2


and on the server the client's root certificate is import with,

$> orapki wallet add -wallet /u01/10.2/server_wallet -trusted_cert -cert client_ca.cert -pwd Welcome1



Configuring sqlnet for TCPS on the server and client


1) Server side Listener Configuration

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 = ukp12692.uk.oracle.com)(PORT = 1521))
     )
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCPS)(HOST = ukp12692.uk.oracle.com)(PORT = 1522))
     )
  )

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


2) Server side sqlnet.ora Configuration


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



3) Client side sqlnet.ora Configuration

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/10.2/client_wallet)
   )
  )




4) Client side tnsnames.ora Configuration


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


v10g =
  (DESCRIPTION =
    (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCPS)(HOST = ukp12692.uk.oracle.com)(PORT = 1522))
    )
    (CONNECT_DATA =
     (SERVICE_NAME = v10g)
    )
   )




Configuring the database

1) OS_AUTHENT_PREFIX and REMOTE_OS_AUTHENT

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

For example,


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



and


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



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

2) Create the user within the database

The user within the database has to be created specifying the distiguished name (DN) on their certificate. For example,


SQL> create user adam identified externally as 'CN=adam';



The user should have have create session granted so they are able to connect,


SQL> grant create session to adam;




Testing it

1) 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. For example,

$> tnsping v10g

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 = 138.3.128.29)(PORT = 1522))) (CONNECT_DATA = (SID = v10g)))
OK (100 msec)



2) The second test is to connect as the TCPS authenticated user. For example,


$> sqlplus /@v10g

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>



3) Then it is possible to confirm that the connection is for the correct user we created earlier,


SQL> select user from dual;

USER
------------------------------
ADAM



4) A final confirmation is to verify the network_protocol from the userenv, for example:


SQL> select sys_context('userenv','network_protocol') from dual;

SYS_CONTEXT('USERENV','NETWORK_PROTOCOL')
--------------------------------------------------------------------------------
tcps