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/walletorapki 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 -pwdwelcome1
To add this certificate to the client wallet we need to export this certificate:
orapki walletexport-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 walletexport-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 = TRUELISTENER =(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 = TRUEWALLET_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 = TRUEWALLET_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 externallyas'CN=oelvm01';SQL>grantconnect,createsessiontotest;
Test configuration:
sqlplus test:
sqlplus /@tdb01_tcpsIn a additional post I will describe how to test the authentication with a java program.
