SQL Net Encryption – Oracle Database Cloud

TDE that we discussed in previous post is encryption mechanism for the data in rest. In this post, we are going to discuss the security of data in motion and will check that the network encryption is enabled by default in the Database Cloud Services and the risk for your information if it’s not enabled. If you are in an On-Premise environment, you can have access to this feature with Oracle Database Enterprise Edition as part of the main core capabilities.

Also from version 12c onwards, there is no need to have an additional option license to use it.

Note that SQL Net Encryption is not a new feature of Oracle Cloud Database. It is an old feature but its importance has been augmented because of trend of Oracle databases being moved into Cloud servers.

Below are the steps that we will follow to show the capabilities of SQL Net Encryption:

STEP 1: Create a SSH connection as opc user

We will be using Putty to connect to the IP address for our Cloud Database with service name ‘brijesh’.

STEP 2: Check network encryption configuration in sqlnet.ora

Below is the content of our sqlnet.ora in the Oracle cloud database:

[oracle@brijesh admin]$ cat /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/sqlnet.ora

SQLNET.ENCRYPTION_SERVER = required
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA1)
SQLNET.CRYPTO_CHECKSUM_SERVER = required
ENCRYPTION_WALLET_LOCATION = (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/app/oracle/admin/brijesh/tde_wallet)))
SQLNET.ENCRYPTION_TYPES_SERVER = (AES256, AES192, AES128)
NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT)
SQLNET.WALLET_OVERRIDE = FALSE
SQLNET.EXPIRE_TIME = 10
SSL_VERSION = 1.0
WALLET_LOCATION = (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/app/oracle/admin/brijesh/db_wallet)))

Currently we have encryption enabled in our database.

STEP 3: Execute tcpdump to monitor network traffic

Now Switch to root so that you can execute the tcpdump command.

[opc@brijesh ~]$ sudo -s
[root@brijesh opc]# id
uid=0(root) gid=0(root) groups=0(root)
[root@brijesh opc]# /usr/sbin/tcpdump -Xs 1518 -i lo port 1521
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on lo, link-type EN10MB (Ethernet), capture size 1518 bytes

Keep this window open so that tcpdump data monitoring can be visible.

STEP 4: Query some data and check traffic in tcpdump

Connect to your pluggable database and query some data.

[oracle@brijesh ~]$ sqlplus system/<password>@PDB1
SQL*Plus: Release 12.2.0.1.0 Production on Sat Dec 31 19:40:51 2016
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Sat Dec 31 2016 03:44:53 +00:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select * from SCOTT.ACCT;

ACCTNO       ANAME          CITY
---------- -------------- -------------
 11          ACCOUNT1     CHICAGO
 22          ACCOUNT2     INDIANAPOLIS
 33          ACCOUNT3     CALIFORNIA
 44          ACCOUNT4     BOSTON

Check the session where you leave the tcpdump running and you will notice as shown below that the network traffic is encrypted and nothing is readable in the right most column:

STEP 5: Disable network encryption

sqlnet.ora file controls the database encryption settings. To disable the network encryption, temporarily we will rename the sqlnet.ora file so that oracle database processes can’t access it.

[oracle@brijesh ~]$ cd $ORACLE_HOME/network/admin
[oracle@brijesh admin]$ mv sqlnet.ora sqlnet.ora_temp
[oracle@brijesh admin]$ ls -tlr sqlnet*
-rw-r--r-- 1 oracle oinstall 532 Dec 30 21:24 sqlnet.ora_temp

STEP 6: Query some data again and check the tcpdump output

[oracle@brijesh admin]$ sqlplus system/Welcome_123@PDB1
SQL*Plus: Release 12.2.0.1.0 Production on Sat Dec 31 19:49:43 2016
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Sat Dec 31 2016 19:40:51 +00:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select * from SCOTT.ACCT;

ACCTNO        ANAME        CITY
---------- -------------- -------------
 11          ACCOUNT1      CHICAGO
 22          ACCOUNT2      INDIANAPOLIS
 33          ACCOUNT3      CALIFORNIA
 44          ACCOUNT4      BOSTON

Check the tcpdump output and now you can see the table data is clearly visible in readable format in the last column of tcpdump output, confirming that the data is unprotected with the Network Encryption disabled:

STEP 7: Enable back the network encryption

We will enable back the Network Encryption again by simply restoring the sqlnet.ora file that we renamed earlier.

All the new sessions will be protected again without the need of restart services on the database.

[oracle@brijesh ~]$ cd $ORACLE_HOME/network/admin
[oracle@brijesh admin]$ mv sqlnet.ora_temp sqlnet.ora
[oracle@brijesh admin]$ ls -tlr sqlnet*
-rw-r--r-- 1 oracle oinstall 532 Dec 30 21:24 sqlnet.ora

This small example shows how important is to keep your sqlnet.ora in shape with all required encryption parameters in place.

Author
Recent Posts

Brijesh Gogia

I’m an experienced Cloud/Oracle Applications/DBA Architect with more than 15 years of full-time DBA/Architect experience. I have gained wide knowledge on Oracle and Non-Oracle software stack running on-prem and on Cloud and have worked on several big projects for multi-national companies. I enjoy working with leading-edge technology and have a passion for Cloud architecture, automation, database performance, and stability. Thankfully my work allows me time for researching new technologies (and to write about them).