Securing infrastructure remains critical aspects for everyone and for DBA that also means securing the database. This not just mean to secure your data written on disk (Encryption at rest) but also to secure your data which flows through network between client and network. DBSecurity Inflight Communication TLS security features becomes vital.
All leading Database providers comes up with TLS encryption for these in-flight transactions which secure the communication over the network. In this blog we will cover, how to enable TLS based encryption for Oracle Database.
There has been various blogs and sources of information to achieve this aspect but one may struggle to find a common place which list down all these activities at one place. I will try to consolidate them which will includes steps for RAC and Standby as well.
TLS is an incremental version of SSL version 3 and TLS1.2 is the latest one in the series. Using Oracle Database SSL provides:
1. Encrypt communication between client and server.
2. Authenticate client with server.
They way SSL works is client and server first performs the handshake between themselves which does following steps:
1. Client and Server finalize the cipher they will use to encrypt communication between client and server.
2. Server send it’s certificate to client and client verifies whether server certificate was signed with trusted CA.
3. Then clients send certificate to server and same validation occurs (optional).
4. Client and Server then generates a session key which is used to encrypt and decrypt further communication between them.
Next section will describe in details steps to perform this task. It includes both server and client side setting changes.
Server Side Settings
For SSL communication listeners should be configured to use TCPS protocol.
For RAC setup we need to first configure the SCAN listener.
Step A: (Configure SCAN Listener)
srvctl config scan_listener (This will list your scan configurtion on TCP Port)
srvctl stop scan_listener (Stop the scan listener)
srvctl stop scan (Stop the scan)
srvctl modify scan_listener -p TCP:<port>/TCPS:<ssl port>
srvctl start scan
srvctl start scan_listenerAll above steps will get your SCAN listeners ready for SSL communication on TCPS ports.
Step B: (Configure Local Listener)
$GRID_HOME/crsctl stat res -p | grep ENDPOINTS
srvctl modify listener -l <listener name> -p "<TCP:<port>/TCPS:<ssl port>"
srvctl stop listener -l <listener name>
srvctl start listener -l <listener name>
srvctl config listener -l <listener name>Above should list down both TCP and TCPS port for the local listener in question.
This concludes are setting for listener to enable the TCPS connections. Basically if you are running a standalone environment (without RAC), configure SCAN is not needed and only local listener needs to be configured.
One thing to notice here both TCP and TCPS connections works from same listener on different port, that will make sure your existing connection works fine. One may choose to run both in parallel or choose only one.
Step C: (Generate the SSL Wallet and Certificate)
One can imagine after listener configuration we would need a place where certificates will be stored which heart of this entire setup. In order to place these certificates Oracle provides SSL Wallet functionality through orapki utility. This is very easy and quick to setup, as described in this section.
Create a directory to hold the wallet, remember this location has to be present on all machines if it’s RAC and on Standby server as well. We will generate certificate one node and copy them to all the remaining nodes.
Create Wallet
mkdir -p <wallet location>/ssl
orapki wallet create -wallet <wallet location>/ssl -pwd **** -auto_login
orapki wallet display -wallet <wallet location>/sslNote: Above steps will create a autologin wallet and display it’s content.
orapki wallet add -wallet <wallet location>/ssl -dn "CN=<dbname>,OU=<values>,O=<values>,L=<values>,S=<values>,C=<values>" -keysize 2048 -pwd ****Note: Above will add the DN Name (Distinguish Name), this can be used based on your requirements, i would suggest to use db name so that same can be used on standby server. Next step to raise and CSR request which will based on your company to get the signed certificate. One may want to use self signed certificate (I don’t encourage this), those steps are easy and can be checked on oracle site.
Generate the CSR
orapki wallet export -wallet <wallet location>/ssl -dn "CN=<dbname>,OU=<values>,O=<values>,L=<values>,S=<values>,C=<values>" -request <location/filename.txt>Note: Above will generate an file named as filename.txt at the path specified, this file will needs to be send to procure the sign certificate. Once you receive sign certificate that needs to be imported along with root and intermediate (trusted) certificate.
Add Certificate to wallet
orapki wallet add -wallet <wallet location>/ssl -trusted_cert -cert <Root Certificate> -pwd ***
orapki wallet add -wallet <wallet location>/ssl -trusted_cert -cert <Intermediate Certificate> -pwd ***
orapki wallet add -wallet <wallet location>/ssl -user_cert -cert <Certificate from CSR> -pwd ***Note: In order to import an user certificate, complete chain of certificate needs to be imported to wallet. Different companies has their own structure like follow two layer root and user certificate only and some three layer approach root, intermediate and user certificate. Based on your company pattern import the intermediate certificate.
orapki wallet display -wallet <wallet location>/ssl
chmod 640 <wallet location>/sslAbove should now list all the trust and user certificate and your wallet is now ready to be used. One very important point to be noted here is if you are running your RAC services as separate user then orapki wallet display should work as that user as well along with oracle as listener runs from different user would need access to this wallet. Failing to this will lead to errors.
Now these wallet can also be copied to all RAC nodes (if any) and Standby nodes (if any) to same location. DBSecurity Inflight Communication TLS brings more secured setup.
Step D: (Configure Listener and sqlnet files to specify the wallet location)
Once wallet is created you would need to specify the wallet location at two places 1) Listener.ora where your scan and local listeners are runnign 2) sqlnet.ora file of your database.
Placing entries in GRID_HOME/network/admin/listener.ora
SSL_CLIENT_AUTHENTICATION = FALSE
WALLET_LOCATION =
(SOURCE =
(METHOD = FILE )
(METHOD_DATA =
(DIRECTORY = <wallet location>/ssl)
)
)Note : SSL_CLIENT_AUTHENTICATION values is set to FALSE as we not using SSL for authentication rather for encrypting the in-flight securing.
Place following in $ORACLE_HOME/network/admin/sqlnet.ora
SSL_CLIENT_AUTHENTICATION = FALSE
SQLNET.AUTHENTICATION_SERVICES= (BEQ,TCPS)
WALLET_LOCATION =
(SOURCE =
(METHOD = FILE )
(METHOD_DATA =
(DIRECTORY = <wallet location>/ssl)
)
)
SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA256)Note : There are many critical factors in above parameters, cipher suites should be advanced and at the time of writing AES256 with SHA256 provides good level of advanced encryption.
srvctl stop listener -l <listener name>
srvctl start listener -l <listener name>Step E: (Configure Database)
Init parameter local_listener should now also includes TCPS protocol, it can be be done in two ways either use alias and alias details in tnsnames.ora or put the entire string in the parameter itself. We will use the first one.
alter system set local_listener=<listener alias> scope=both sid='*';
Add <listener alias> entry in tnsnames.ora on all the nodes of RAC and Standby as well.
<listener name>
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = <listener name>))
(ADDRESS = (PROTOCOL = TCP)(HOST= <hostname or virtual name>)(PORT = <port>))
(ADDRESS = (PROTOCOL = TCPS)(HOST= <hostname or virtual name>)(PORT = <ssl port>))
)
)
srvctl stop database -d <database unique name>
srvctl start database -d <database unique name>Note : Above all concludes server level setting now, now client also needs to be configured to access the SSL enabled server on TCPS ports. Basically majority of client provides this functionality, we will discuss on example of sqlplus to get an idea how this can be done. Check your vendor documentations for more details on how this has to be enabled on client side.
Client Side Settings
mkdir -p <client wallet location>/ssl
orapki wallet create wallet <client wallet location>/ssl -pwd *** -auto_login
orapki wallet display -wallet <client wallet location>/sslNow import only trust certificate to complete the chain, many people get confused and import their user certificate. Client only needs trust certificate to communication and complete the chain with server. Also one more point to be noted here is we are not using two way SSL, in two way SSL client will also have it’s own certificate that will be validated on server. Based on your company policy you can configure same, in this document we are configured one way SSL where only Server has user certificate and client validates it.
orapki wallet add -wallet <client wallet location> -trusted_cert <certificate> -pwd ****Add the SSL based tns entry into client tnsnames.ora only thing to notice here is used TCPS in place of TCP and it’s port.
Last configuration is to put entries in sqlnet.ora about wallet location and cipher to be used like on server side.
SSL_CLIENT_AUTHENTICATION = FALSE
SQLNET.AUTHENTICATION_SERVICES= (BEQ,TCPS)
WALLET_LOCATION =
(SOURCE =
(METHOD = FILE )
(METHOD_DATA =
(DIRECTORY = <wallet location>/ssl)
)
)
SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA256)Now make a connection with sqlplus and that’s it 🙂 This is how DBSecurity Inflight Communication TLS can be achieved.
Some important books which can help to secure your database.
https://amzn.to/3jFC0oz (Oracle Security Handbook)
https://amzn.to/3jEtHsY (Oracle Database Security 12c Cookbook)
YOU MAY ALSO LIKE MY OTHER BLOGS:
ORACLE DATABASE INTERVIEW QUESTIONS
GENERATE 10046 TRACE (SQL_TRACE) FOR PERFORMANCE ANALYSIS
2 Comments