Under normal conditions using an out of the box setup, Oracle database datafiles are stored in a proprietary format. Data such as strings of alphabetic characters can be viewed just as they are in the database by the naked eye when the physical datafile is opened with software as simple as a text editor.<\/p>\n
Since the physical files could potentially be accessed outside of the security of the database, a need to have greater security for the storage of these files became apparent with an increasing focus on data security. <\/p>\n
Transparent Data Encryption (\u201cTDE\u201d) is a technology originally released in the 10.2.0 version of Oracle Corp.\u2019s flagship database product, and aims to meet this need.<\/p>\n
Setting up TDE is fairly simple. All that is required is instructing the Oracle software where to create and find the wallet needed to encrypt and decrypt entries as they are made and requested from the database. <\/p>\n
To do this, an entry is made to the $ORACLE_HOME\/network\/admin\/sqlnet.ora file with the wallet location, such as shown below.<\/p>\n
\r\nENCRYPTION_WALLET_LOCATION=\r\n (SOURCE=(METHOD=FILE)\r\n (METHOD_DATA=(DIRECTORY=\/u01\/app\/oracle\/ggate\/encryption_wallet))\r\n )\r\n<\/pre>\nThis setting must be added to the $ORACLE_HOME\/network\/admin\/sqlnet.ora file on each node of an Oracle Real Application Clusters (\u201cRAC\u201d) database software home.<\/p>\n
After adding the entry above, we then open the wallet, initially creating it and enabling it for use.<\/p>\n
\r\nSQL> alter system set wallet open identified by \"89%fG$m@# \";\r\n\r\nSystem altered.\r\n\r\nSQL> exit\r\n<\/pre>\nWe proceed to create a tablespace using encryption.<\/p>\n
\r\nSQL> create tablespace enctest encryption default storage(encrypt);\r\n\r\nTablespace created.\r\n\r\nSQL>\r\n<\/pre>\nWe then create a table in our \u201cregular\u201d unecrypted USERS tablespace, as well as a table in our newly encrypted tablespace.<\/p>\n
\r\nSQL> conn cmh\/cmh\r\nConnected.\r\nSQL> create table unenc_table(c int, d varchar2(30)) tablespace users;\r\n\r\nTable created.\r\n\r\nSQL> insert into unenc_table values(1,'i can see this');\r\n\r\n1 row created.\r\n\r\nSQL> create table enc_table(c int primary key, d varchar2(30)) tablespace enctest;\r\n\r\nTable created.\r\n\r\nSQL> insert into enc_table values(1,'i can''t see this');\r\n\r\n1 row created.\r\n\r\nSQL> commit;\r\n\r\nCommit complete.\r\n\r\nSQL>\r\n<\/pre>\nNow, we proceed to test the ability of a standard utility to see the contents of each datafile.<\/p>\n
We first see that we can see our data in the unencrypted USERS tablespace.<\/p>\n
\r\nRMAN> copy datafile 4 to '\/home\/oracle\/users.dbf';\r\n\r\nStarting backup at 2010_06_24 15:09:16\r\nallocated channel: ORA_DISK_1\r\nchannel ORA_DISK_1: SID=149 instance=test2 device type=DISK\r\nchannel ORA_DISK_1: starting datafile copy\r\ninput datafile file number=00004 name=+DATA\/test\/datafile\/users.259.785876091\r\noutput file name=\/home\/oracle\/users.dbf tag=TAG20100624T150917 RECID=2 STAMP=786813152\r\nchannel ORA_DISK_1: datafile copy complete, elapsed time: 00:03:15\r\nFinished backup at 2010_06_24 15:12:32\r\n\r\nRMAN> exit\r\n\r\n\r\nRecovery Manager complete.\r\ntestdb2:oracle:test2:\/home\/oracle# strings -a users.dbf | grep \"see this\"\r\ni can see this\r\n<\/pre>\nHowever, when we search for the same string we inserted in our encrypted tablespace, we can\u2019t see it.<\/p>\n
\r\nRMAN> copy datafile 6 to '\/home\/oracle\/enc.dbf';\r\n\r\nStarting backup at 2010_06_24 15:35:41\r\nusing target database control file instead of recovery catalog\r\nallocated channel: ORA_DISK_1\r\nchannel ORA_DISK_1: SID=32 instance=test2 device type=DISK\r\nchannel ORA_DISK_1: starting datafile copy\r\ninput datafile file number=00006 name=+DATA\/test\/datafile\/enctest.408.786812769\r\noutput file name=\/home\/oracle\/enc.dbf tag=TAG20100624T153542 RECID=3 STAMP=786814560\r\nchannel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26\r\nFinished backup at 2010_06_24 15:36:08\r\n\r\nRMAN> exit\r\n\r\n\r\nRecovery Manager complete.\r\ntestdb2:oracle:test2:\/home\/oracle# strings -a enc.dbf | grep \"see this\"\r\ntestdb2:oracle:test2:\/home\/oracle#\r\n<\/pre>\nThis proves that the data on disk is encrypted.<\/p>\n
Network Security<\/b><\/p>\n
What happens when we attempt to sniff our data on the network? In this case, we see that as expected we can see it when we query the unencrypted data.<\/p>\n
We use the simple java class below for testing.<\/p>\n
\r\nimport java.sql.*;\r\n\r\nclass testEncrypt {\r\n\r\n public static void main(String args[]) {\r\n try {\r\n Class.forName(\"oracle.jdbc.driver.OracleDriver\");\r\n Connection conn = DriverManager.getConnection(\"jdbc:oracle:thin:cmh\/cmh@192.168.1.50:1521\/test.home\");\r\n ResultSet rst = conn.createStatement().executeQuery(\"select * from unenc_table\");\r\n while (rst.next()) {\r\n System.out.println(rst.getString(1) + rst.getString(2));\r\n }\r\n }\r\n catch (Exception e) {\r\n e.printStackTrace();\r\n }\r\n }\r\n}\r\n<\/pre>\nWe put our interface on our client machine into promiscuous mode and pipe the output to a search for the \u201csee this\u201d string\u2026<\/p>\n
\r\n[root@util01 ~]# tcpdump tcp port 1521 -A -s1500 | grep \u201csee this\u201d\r\ntcpdump: verbose output suppressed, use -v or -vv for full protocol decode\r\nlistening on eth0, link-type EN10MB (Ethernet), capture size 1500 bytes\r\nDupl@ication of this key is not allowed without permission\r\n........i can see this....B9..............AMERICAN......AMERICA. ....$.....AMERICA.......,......AL32UTF8.\r\n39 packets captured\r\n39 packets received by filter\r\n0 packets dropped by kernel\r\n<\/pre>\nUnfortunately, we see that we can also see our data when we query the encrypted tablespace.<\/p>\n
\r\n[root@util01 ~]# tcpdump tcp port 1521 -A -s1500 | grep this\r\ntcpdump: verbose output suppressed, use -v or -vv for full protocol decode\r\nlistening on eth0, link-type EN10MB (Ethernet), capture size 1500 bytes\r\nDupl@ication of this key is not allowed without permission\r\n........i can't see this....B:O.............AMERICAN......AMERICA. ....$.....AMERICA.......,......AL32UTF8.\r\nDupl@ication of this key is not allowed without permission\r\n39 packets captured\r\n39 packets received by filter\r\n0 packets dropped by kernel\r\n<\/pre>\nIt is at this point that we realize that TDE is not enough by itself. It protects the data at rest on disk (the actual file, or a backup copy to be sent off site). However, it does not protect our data in flight across the network.<\/p>\n
We need to configure Oracle Advanced Network Security, which also requires additions to our server side sqlnet.ora file.<\/p>\n
After adding these entries, our sqlnet.ora looks like the one below.<\/p>\n
\r\nENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=\/u01\/app\/oracle\/ggate\/encryption_wallet)))\r\n\r\nSQLNET.ENCRYPTION_SERVER = REQUIRED\r\nSQLNET.CRYPTO_CHECKSUM_SERVER = REQUIRED\r\nSQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (MD5)\r\n<\/pre>\nWe ensure these changes are made on each node of our RAC, and run our client test again. We now see that we can no longer sniff our data, whether it is encrypted or unencrypted in the database.<\/p>\n
After several runs of our test, no data is found in our packet dump.<\/p>\n
\r\n[root@util01 ~]# tcpdump tcp port 1521 -A -s1500 | grep \"see this\"\r\ntcpdump: verbose output suppressed, use -v or -vv for full protocol decode\r\nlistening on eth0, link-type EN10MB (Ethernet), capture size 1500 bytes\r\n<\/pre>\nPERFORMANCE IMPACT<\/p>\n
The performance impact of TDE is negligible on both user response time as well as database server CPU utilization.<\/p>\n
Below is a test with 40 threads concurrently inserting customers into an encrypted tablespace\u2026<\/p>\n
\r\ninsert into customers (customer_id, first_name,last_name,credit_limit,card_no)\r\nvalues (customer_id_seq.nextval, :b1, :b2, :b3, :b4)\r\n\r\n\r\ncall count cpu elapsed disk query current rows\r\n------- ------ -------- ---------- ---------- ---------- ---------- ----------\r\nParse 1 0.00 0.00 0 0 0 0\r\nExecute 608 8.55 196.20 90 44763 188120 30400\r\nFetch 0 0.00 0.00 0 0 0 0\r\n------- ------ -------- ---------- ---------- ---------- ---------- ----------\r\ntotal 609 8.55 196.20 90 44763 188120 30400\r\n<\/pre>\n\u2026as well as an unencrypted tablespace\u2026<\/p>\n
\r\ninsert into customers (customer_id, first_name,last_name,credit_limit,card_no)\r\nvalues (customer_id_seq.nextval, :b1, :b2, :b3, :b4)\r\n\r\n\r\ncall count cpu elapsed disk query current rows\r\n------- ------ -------- ---------- ---------- ---------- ---------- ----------\r\nParse 1 0.00 0.00 0 0 0 0\r\nExecute 621 7.37 227.83 0 39089 176521 31050\r\nFetch 0 0.00 0.00 0 0 0 0\r\n------- ------ -------- ---------- ---------- ---------- ---------- ----------\r\ntotal 622 7.37 227.83 0 39089 176521 31050\r\n<\/pre>\nThe low CPU utilization should be the focus of what is above, as the encryption occurs in user CPU space of the operating system.<\/p>\n
Also of note is the fact that the trace (Oracle 10046 event) that produced the results above showed our unencrypted credit card number in the trace file inserted by our test software. This is why you should protect the directory that houses your trace files.
\nCOST<\/p>\n
Both TDE and Advanced Network Security both come as part of the Oracle Advanced Security option of the database, which lists for $11,500 per licensed processor.<\/p>\n","protected":false},"excerpt":{"rendered":"
Under normal conditions using an out of the box setup, Oracle database datafiles are stored in a proprietary format. Data such as strings of alphabetic characters can be viewed just as they are in the database by the naked eye…<\/p>\n