Oracle Database 11g: SQLNet Authentication Using PKI

Secure connection between Oracle Server and Client

1. Create the server and client wallets

1.1 Server

• Create wallet location
  $ mkdir /app/oracle/product/11.2.0.3/owm/wallets/server
• Initiate wallet
  $ orapki wallet create -wallet /app/oracle/product/11.2.0.3/owm/wallets/server -auto_login -pwd think4me
• Create self-signed certificate
  $ orapki wallet add -wallet /app/oracle/product/11.2.0.3/owm/wallets/server -dn 'CN=server' -keysize 2048 -self_signed -validity 3650 -pwd think4me
• Export server root certificate
  $ orapki wallet export -wallet /app/oracle/product/11.2.0.3/owm/wallets/server -dn 'CN=server' -cert /app/oracle/product/11.2.0.3/owm/wallets/server/server_ca.cert

1.2 Client

• Create wallet location
  $ mkdir /app/oracle/product/11.2.0.3/owm/wallets/fei
• Initiate wallet
  $ orapki wallet create -wallet /app/oracle/product/11.2.0.3/owm/wallets/fei -auto_login -pwd think4me
• Create self-signed certificate
  $ orapki wallet add -wallet /app/oracle/product/11.2.0.3/owm/wallets/fei -dn 'CN=fei' -keysize 2048 -self_signed -validity 3650 -pwd think4me
• Export root certificate
  $ orapki wallet export -wallet /app/oracle/product/11.2.0.3/owm/wallets/fei -dn 'CN=fei' -cert /app/oracle/product/11.2.0.3/owm/wallets/fei/client_fei_ca.cert

2. Exchange the server and client root certificates

2.1 Import client root certificate into server wallet

$ orapki wallet add -wallet /app/oracle/product/11.2.0.3/owm/wallets/server -trusted_cert -cert /app/oracle/product/11.2.0.3/owm/wallets/fei/client_fei_ca.cert -pwd Welcome1

2.2 Import server root certificate into client wallet

$ orapki wallet add -wallet /app/oracle/product/11.2.0.3/owm/wallets/fei -cert /app/oracle/product/11.2.0.3/owm/wallets/server/server_ca.cert -pwd Welcome2

3. Configure TCPS on the server and client

3.1 Server

• listener.ora

SID_LIST_LISTENER =
  (SID_LIST =    
    (SID_DESC =  
      (GLOBAL_DBNAME = weekend.db.goweekend.ca)
      (ORACLE_HOME = /app/oracle/product/11.2.0.3)
      (SID_NAME = weekend)                           
    )                                                
  )                                                  
SSL_CLIENT_AUTHENTICATION = TRUE
WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /app/oracle/product/11.2.0.3/owm/wallets/server)
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = db.goweekend.ca)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCPS)(HOST = db.goweekend.ca)(PORT = 2484))
    )
  )

•  sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCPS)
SSL_VERSION = 0
NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME)
SSL_CLIENT_AUTHENTICATION = TRUE
WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /app/oracle/product/11.2.0.3/owm/wallets/server)
    )
  )

3.2 Client

• sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES= (TCPS)
SSL_VERSION = 0
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
SSL_SERVER_DN_MATCH = Yes
SSL_CLIENT_AUTHENTICATION = TRUE
WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /opt/app/oracle/product/11.2.0.2/owm/wallets/fei)
    )
  )

•  tnsnames.ora

WEEKEND =
  (DESCRIPTION =
   (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = tcps)(HOST = db.goweekend.ca)(PORT = 2484))
   )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = weekend)
    )
    (SECURITY =
      (SSL_SERVER_CERT_DN="CN=server"))
  )

4. Configure database and user

4.1 OS_AUTHENT_PREFIX and REMOTE_OS_AUTHENT

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

SQL> show parameter remote_os_authent
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_os_authent                    boolean     FALSE
SQL> show parameter os_authent_prefix
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix                    string      ops$
SQL>

if any of them doesn't meet the requirements, use below command to change it.
Login as sysdba,

    SQL> alter system set remote_os_authent=FALSE scope=spfile;
    and
    SQL> alter system set os_authent_prefix='' scope=spfile;
If any above changes made, please restart the instance.

4.2 Users

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

SQL> create user user2 identified externally as 'CN=fei';

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

SQL> grant create session to fei;

If the user exists, please run below command to change it.

SQL> alter user identified externally as 'CN=fei';

5. Test Connection

On client, run below command:

$ sqlplus /nolog
SQL> connect /@weekend
Connected.
SQL>