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.