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 188.8.131.52.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 184.108.40.206.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 220.127.116.11.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 18.104.22.168.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.
[Post Views: 131]
You can connect with me on LinkedIn.
Latest posts by Brijesh Gogia (see all)
- Performance Tuning Basics 16 : Using SQL Tuning Health-Check Script (SQLHC) - April 1, 2018
- Physical and Logical Block Corruption in Oracle Database - March 10, 2018
- Performance Tuning Basics 15 : AWR Report Analysis - February 6, 2018