Database Connection with a Certificate

Instead of connecting to the database with username and password it is also possible to connect to the database via username and certificate.

In this example we will use self signed certificates. A similar configuration is possible with trusted certificates.

To be able to login to the database with a certificate we need a wallet on the database server and a wallet on the client.

In this example we use a self signed certificate. The process for using a trusted certificate is similar.

Certificate creation on the server

First we need to create a wallet on the server:

mkdir/u01/app/oracle/admin/TDB01/wallet orapki wallet create -wallet /u01/app/oracle/admin/TDB01/wallet/-auto_login -pwdwelcome

Then we need to create a self signed server certificate for the server:

orapki wallet add -wallet /u01/app/oracle/admin/TDB01/wallet/ -dn "CN=oelvm02" -keysize 1024 -self_signed -validity 365 -pwd welcome1

To add this certificate to the client wallet we need to export this certificate:

orapki wallet export -wallet /u01/app/oracle/admin/TDB01/wallet/ -dn "CN=oelvm02" -cert /tmp/serv

Certificate creation on the client

First we need to create a wallet on the client:

mkdir-p /u01/app/oracle/admin/wallet
orapki wallet create -wallet /u01/app/oracle/admin/wallet/-auto_login -pwdwelcome1

Then we need to create a self signed server certificate for the client:

orapki wallet add -wallet /u01/app/oracle/admin/wallet/-dn "CN=oelvm01"-keysize 1024 -self_signed -validity 365 -pwdwelcome1

To add this certificate to the server wallet we need to export this certificate:

orapki wallet export -wallet /u01/app/oracle/admin/wallet/ -dn "CN=oelvm01" -cert /u01/app/oracle/admin/wallet/client.crt

Add Client certificate to Server Wallet

Copy the client.crt file to the server and add it to the wallet:

orapki  wallet add -wallet /u01/app/oracle/admin/TDB01/wallet-trusted_cert -cert /u01/app/oracle/admin/TDB01/wallet/client.crt -pwdwelcome1

Add Server certificate to Client Wallet

Copy the server.crt file to the server and add it to the wallet:

orapki wallet add -wallet /u01/app/oracle/admin/wallet-trusted_cert -cert /u01/app/oracle/admin/wallet/server.crt -pwdwelcome1

Network configuration on Server

To use encrypted communication with this certificate we need to create a listener endpoint. If you use Grid Infrastructure you have to use srvctl to add the endpoint.

Listener.ora

SSL_CLIENT_AUTHENTICATION = TRUE LISTENER =   (DESCRIPTION_LIST =     (DESCRIPTION =       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))       (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))     )     (DESCRIPTION = (ADDRESS = (PROTOCOL = TCPS)(HOST =  localhost)(PORT = 1523)))   ) WALLET_LOCATION =    (SOURCE =      (METHOD = FILE)      (METHOD_DATA = (DIRECTORY = /u01/app/oracle/admin/TDB01/wallet))

sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES=(TCPS,NTS) SSL_CLIENT_AUTHENTICATION = TRUE WALLET_LOCATION =    (SOURCE =      (METHOD = FILE)      (METHOD_DATA = (DIRECTORY = /home/oracle/Wallets/server_wallet))    )

tnsnames.ora

tdb01_tcps =    (DESCRIPTION =      (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCPS)(HOST = oelvm02)(PORT = 1523)))      (CONNECT_DATA = (SERVICE_NAME = TDB01_DB1.localdomain))    )

Network configuration on Client

sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES=(TCPS,NTS) SSL_CLIENT_AUTHENTICATION = TRUE WALLET_LOCATION =    (SOURCE =      (METHOD = FILE)      (METHOD_DATA = (DIRECTORY = /u01/app/oracle/admin/wallet))    )

tnsnames.ora

tdb01_tcps =    (DESCRIPTION =      (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCPS)(HOST = oelvm02)(PORT = 1523)))      (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = TDB01_DB1.localdomain))    )

Database User creation

To use the certificate at signing in we have to use external authentication and we use the CN of the certificate for the authentication.

SQL> createusertest identified externally as'CN=oelvm01'; SQL> grantconnect, createsession totest;

Test configuration

sqlplus test

sqlplus /@tdb01_tcps

In a additional post I will describe how to test the authentication with a java program.