Database Connection to an oracle Database authenticated with a Certificate

Database Connection to an oracle Database authenticated 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/server.crt

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.

Leave a Reply

Your email address will not be published. Required fields are marked *