Database Security - Inflight Communication through TLS

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. 

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.


HOW TLS Works?

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_listener

All 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.


mkdir -p <wallet location>/ssl

orapki wallet create -wallet <wallet location>/ssl -pwd **** -auto_login

orapki wallet display -wallet <wallet location>/ssl

Note: 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. 

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.

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>/ssl

Above 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.

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>/ssl

Now 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 :)



Comments

Post a Comment

Popular posts from this blog

How to Solve - "WAIT FOR EMON PROCESS NTFNS"

Event Monitor Process in short called as EMON is generally used by oracle to send notification. Often applications uses Database Change Notification process also called as DBCN to be aware of changes in the database, based on these notifications they take further actions. Often clients subscribe to the database and notifications are send as callback to them. In some cases you will notice database sessions are showing wait event "WAIT FOR EMON PROCESS NTFNS" and it can even make your database un-responsive. One observation can be these processes are taking more CPU (may not in be in all cases). As stated EMON process deals with database notification and it's spawn and gets down itself and Administrators have no control on theses processes. Anyone can go through oracle documents to read more EMON functioning process.  EMON uses oracle advanced queues and database queue notifications into them and EMON process these notifications to clients based on their subscriptions. In s
Read more

Query Regression - "OR" Transformation Oracle 19c

Recently while upgrading Oracle Database from version 12.1 to 19.10 found there were couple of queries which was running slow post upgrade. Usually these type of behavior is seen while upgrading to newer version and oracle provide various ways to fix them. The difference between good and bad plan was an introduction of  "MERGE JOIN CARTESIAN" (aka MJC) and that's in where regressed query in 19c taking most of the time. MJC sometimes works for queries but in this case it was clearly not helping.  We had following options here:     1. Copy the profile from 12c to 19c database and it worked, but the problem was not just to specific query but with many similar query. This means profile option is not very attractive in this case.     2. We also tried running query with OFE 12.1 to use the same optimizer features as previous version and query behaves well. Though this option can't be placed as we need to use 19c OFE and can be placed only for testing purpose.           alte
Read more

ORA - 12537: TNS: connection closed

Often when active clone to be done to create a new environment, we land up into couple of issues. I will discuss about one issues during this process. Action : Create a new database on different machine from a source. Error : ORA - 12537: TNS: connection closed In order to duplicate the environment was using below rman log /tmp/duplicate.log connect target sys/<password>@<source tns alias> connect auxiliary sys/<password>@<target tns alias> run{ allocate channel ch1 device type disk; allocate channel ch2 device type disk; allocate channel ch3 device type disk; allocate channel ch4 device type disk; allocate auxiliary channel ax1 device type disk; allocate auxiliary channel ax2 device type disk; allocate auxiliary channel ax3 device type disk; allocate auxiliary channel ax4 device type disk; DUPLICATE TARGET DATABASE TO <TARGET_DBNAME> FROM ACTIVE DATABASE NOFILENAMECHECK; } While making connection to Auxiliary database received mentioned error ORA - 12537:
Read more