In this article we’ll see how to secure the connection between a client/application and an Oracle DB using SSL/TLS certs.
Automation is what we talk every single day and such security and password-less authentication is quite useful to keep our DB hardened and also help our scripts/batches to authenticate securely from a remote system. After you read and understand this article, also see here to know more on how to setup password-less authentication to an Oracle database System.
Prerequisites:
To make this work, consider 4 things that interact with each other. They are,
Setting up Wallet and jks
sqlnet.ora
listener.ora
tnsnames.ora (Optional to check SSL/TLS connection from with in the server)
So lets see the steps one by one.
1) Creating an auto-login Wallet
mkdir -p /app/oracle/local/bin/wallet/tls
orapki wallet create -wallet /app/oracle/local/bin/wallet/tls -auto_login -pwd "anypassword"2) Create a Self signed certificate
orapki wallet add -wallet /app/oracle/local/bin/wallet/tls -dn "CN=racdb-scan.dbgenre.com,OU=Technology,O=DBGENRE,L=Chennai,ST=Tamilnadu,C=IN" -keysize 2048 -sign_alg sha256 -pwd "anypassword”3) Generating CSR certificate from local
orapki wallet export -wallet /app/oracle/local/bin/wallet/tls -dn "CN=racdb-scan.dbgenre.com,OU=Technology,O=DBGENRE,L=Chennai,ST=Tamilnadu,C=IN" -request racdb-scan.req
4) Get your organisation signed certificates using CSR
Each organisation has its own method of generating signed certificates. Extract the CSR file algorithm and get a p12 certificate from your organisation. This can be any format like PKCS#7 (p7b), PKCS12 (p12), etc. I chose p12 here.
5) Import signed certs into Wallet
orapki wallet add -wallet /app/oracle/local/bin/wallet/tls -user_cert -cert racdb.apps.dbgenre.p12 -pwd "anypassword”6) Export the Oraganization's CA and ROOT CA certs to load into client wallet
orapki wallet export -wallet /app/oracle/local/bin/wallet/tls -dn "CN=ORG ROOT CA v3,DC=test,DC=dbgenre,DC=com" -cert DBGENRE_CA_Root.cert
orapki wallet export -wallet /app/oracle/local/bin/wallet/tls -dn "CN=ORG CA 02 v3,DC=test,DC=dbgenre,DC=com" -cert DBGENRE_CA.cert7) Generate JKS file for application use
orapki wallet pkcs12_to_jks -wallet /app/oracle/local/bin/wallet/tls –pwd "${WALLT_PWD}" -jksKeyStoreLoc /app/oracle/local/bin/wallet/tls/racdb-scan_v3.jks -jksKeyStorepwd "anyPassword"
8) Exchanging the certificate to the client
Each side of the connection needs to trust the other, so we must load the certificate from the server as a trusted certificate into the client wallet and vice versa.
Make sure client have the correct JDBC Thin driver. Modify the Java code to include the correct DB_URL, DB_USER, and DB_PASSWORD of the database that you have access to.
Make sure to use TCPS protocol with its corresponding port and configure the server's DN (Distinguished name) for mutual authentication.
jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(FAILOVER=ON)(LOAD_BALANCE=OFF)(ADDRESS=(PROTOCOL=TCPS)(HOST= racdb.apps.dbgenre)(PORT=2484))(ADDRESS=(PROTOCOL=TCPS)(HOST= racdb-dr.apps.dbgenre)(PORT=2484)))(CONNECT_DATA=(SERVICE_NAME= ORARAC01))(SECURITY=(SSL_SERVER_CERT_DN="CN=CN=racdb-scan.dbgenre.com,OU=Technology,O=DBGENRE,L=Chennai,ST=Tamilnadu,C=IN")))If you are using tnsnames.ora then it is sufficient to use TNS alias in the URL
ORARAC01_SSL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCPS)(HOST = racdb.apps.dbgenre)(PORT = 2484))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORARAC01)
)
(SECURITY = (SSL_SERVER_CERT_DN ="CN=CN=racdb-scan.dbgenre.com,OU=Technology,O=DBGENRE,L=Chennai,ST=Tamilnadu,C=IN")))SSL connection using TLSv1.2 (or more) with JKS
Java Key Store (JKS) is used as a container for the client's certificates exchanged between the server and the client. keyStore contains the client certificate which will be used for authentication and it also contains a set of private/public keys that will be used for encryption.
Applications should present the keyStore when the client needs to be authenticated on the server.
Application team has to make sure to have the files keyStore.jks and trustStore.jks at a location accessible to the application and use the connection properties to provide the JKS file location and password.
Refer point no. 7.
9) Server Network Configuration
On each node of RAC servers, do the following on sqlnet.ora, listener.ora and tnsnames.ora files. As mentioned in Oracle documentation, this article uses the terms SSL and TLS interchangeably. This depends on the cipher suites selected and placing the ciphers in the strongest-to-weakest order in the list.
sqlnet.ora:
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
ADR_BASE=/app/oracle
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_SERVER_DN_MATCH=TRUE
SSL_CIPHER_SUITES=(SSL_RSA_WITH_AES_128_CBC_SHA,SSL_RSA_WITH_AES_256_CBC_SHA)
SSL_VERSION=1.2
WALLET_LOCATION = (SOURCE=(METHOD = FILE) (METHOD_DATA = (DIRECTORY=/app/oracle/local/bin/wallet/tls)))listener.ora:
WALLET_LOCATION = (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/app/oracle/local/bin/wallet/tls)))
SSL_CIPHER_SUITES=(SSL_RSA_WITH_AES_256_CBC_SHA256,SSL_RSA_WITH_AES_256_CBC_SHA,SSL_RSA_WITH_AES_128_CBC_SHA)
SSL_CLIENT_AUTHENTICATION=FALSE
SSL_VERSION=1.2Remember to restart listeners as you make changes to sqlnet.ora and listener.ora files to take effect and the service should be registered to the listener service.
Secondly, if you see the service is not registered, then setting the local_listener and remote_listener parameters will help registering the service to listener. Refer point no. 11.
tnsnames.ora:
REMOTE_LISTENER= (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCPS)(HOST = 192.168.1.101)(PORT = 2484))
(ADDRESS = (PROTOCOL = TCPS)(HOST = 192.168.1.102)(PORT = 2484))
(ADDRESS = (PROTOCOL = TCPS)(HOST = 192.168.1.103)(PORT = 2484))
)
LOCAL_LISTENER1=(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraracdb1-vip.unix.dbgenre)(PORT = 4422))
(ADDRESS = (PROTOCOL = TCPS)(HOST = oraracdb1-vip.unix.dbgenre)(PORT = 2484))
)
LOCAL_LISTENER2=(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraracdb2-vip.unix.dbgenre)(PORT = 4422))
(ADDRESS = (PROTOCOL = TCPS)(HOST = oraracdb2-vip.unix.dbgenre)(PORT = 2484))
)
ORARAC01 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = racdb.apps.dbgenre)(PORT = 4422))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORARAC01)
)
)
ORARAC01_SSL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCPS)(HOST = racdb.apps.dbgenre)(PORT = 2484))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORARAC01)
)
(SECURITY = (SSL_SERVER_CERT_DN ="CN=CN=racdb-scan.dbgenre.com,OU=Technology,O=DBGENRE,L=Chennai,ST=Tamilnadu,C=IN"))
)10) Validating the connections to make sure it uses certificates for authentication.
openssl command will help us understand if the connections are going through certificates and which version of what we are using.
openssl pkcs12 -print_certs -in racdb.apps.dbgenre.p12 | openssl x509 -noout -text Refer: https://www.openssl.org/docs/man1.1.1/man1/openssl-pkcs12.html - For more information.
Summary command can also be used to check the version of SSL/TLS signers.
orapki wallet display -wallet /app/oracle/local/bin/wallet/tls -summary11) Registering REMOTE_LISTENER and LOCAL_LISTENER to the database
After all configuration, we have to register the ports to the database to make it understand which ports uses TCP and TCPS respectively.
sqlplus '/as sysdba'
alter system set local_listener='LOCAL_LISTENER1' sid='ORARAC01A';
Registers LOCAL_LISTENER on Node1 VIP
alter system set local_listener='LOCAL_LISTENER2' sid=ORARAC01B';
Registers LOCAL_LISTENER on Node2 VIP
alter system set remote_listener='REMOTE_LISTENER' sid='*';
Registers REMOTE_LISTENER on both the instances of RAC.
Incase of NON RAC system, just 1 LOCAL_LISTENER and 1 REMOTE_LISTENER with ports 4422 and 2484 respectively should work fine
After setting the local_listener and remote_listener parameters, perform a tnsping on ORARAC01 and ORARAC01_SSL to see if you are able to see the connection goes through.
Also perform a local connection using sqlplus using @ORARAC01 and @ORARAC01_SSL to see if you are able to authenticate with both the ports.
Hope this article helps you to understand the configuration of SSL/TLS certs on your database and securing its connection.
Cheers,
Balaaji Dhananjayan