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.
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.
Transparent Data Encryption (“TDE”) is a technology originally released in the 10.2.0 version of Oracle Corp.’s flagship database product, and aims to meet this need.
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.
To do this, an entry is made to the $ORACLE_HOME/network/admin/sqlnet.ora file with the wallet location, such as shown below.
ENCRYPTION_WALLET_LOCATION=
(SOURCE=(METHOD=FILE)
(METHOD_DATA=(DIRECTORY=/u01/app/oracle/ggate/encryption_wallet))
)
This setting must be added to the $ORACLE_HOME/network/admin/sqlnet.ora file on each node of an Oracle Real Application Clusters (“RAC”) database software home.
After adding the entry above, we then open the wallet, initially creating it and enabling it for use.
SQL> alter system set wallet open identified by "89%fG$m@# ";
System altered.
SQL> exit
We proceed to create a tablespace using encryption.
SQL> create tablespace enctest encryption default storage(encrypt);
Tablespace created.
SQL>
We then create a table in our “regular” unecrypted USERS tablespace, as well as a table in our newly encrypted tablespace.
SQL> conn cmh/cmh
Connected.
SQL> create table unenc_table(c int, d varchar2(30)) tablespace users;
Table created.
SQL> insert into unenc_table values(1,'i can see this');
1 row created.
SQL> create table enc_table(c int primary key, d varchar2(30)) tablespace enctest;
Table created.
SQL> insert into enc_table values(1,'i can''t see this');
1 row created.
SQL> commit;
Commit complete.
SQL>
Now, we proceed to test the ability of a standard utility to see the contents of each datafile.
We first see that we can see our data in the unencrypted USERS tablespace.
RMAN> copy datafile 4 to '/home/oracle/users.dbf';
Starting backup at 2010_06_24 15:09:16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=149 instance=test2 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/test/datafile/users.259.785876091
output file name=/home/oracle/users.dbf tag=TAG20100624T150917 RECID=2 STAMP=786813152
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:03:15
Finished backup at 2010_06_24 15:12:32
RMAN> exit
Recovery Manager complete.
testdb2:oracle:test2:/home/oracle# strings -a users.dbf | grep "see this"
i can see this
However, when we search for the same string we inserted in our encrypted tablespace, we can’t see it.
RMAN> copy datafile 6 to '/home/oracle/enc.dbf';
Starting backup at 2010_06_24 15:35:41
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=32 instance=test2 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+DATA/test/datafile/enctest.408.786812769
output file name=/home/oracle/enc.dbf tag=TAG20100624T153542 RECID=3 STAMP=786814560
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26
Finished backup at 2010_06_24 15:36:08
RMAN> exit
Recovery Manager complete.
testdb2:oracle:test2:/home/oracle# strings -a enc.dbf | grep "see this"
testdb2:oracle:test2:/home/oracle#
This proves that the data on disk is encrypted.
Network Security
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.
We use the simple java class below for testing.
import java.sql.*;
class testEncrypt {
public static void main(String args[]) {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:cmh/[email protected]:1521/test.home");
ResultSet rst = conn.createStatement().executeQuery("select * from unenc_table");
while (rst.next()) {
System.out.println(rst.getString(1) + rst.getString(2));
}
}
catch (Exception e) {
e.printStackTrace();
}
}
}
We put our interface on our client machine into promiscuous mode and pipe the output to a search for the “see this” string…
[root@util01 ~]# tcpdump tcp port 1521 -A -s1500 | grep “see this”
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on eth0, link-type EN10MB (Ethernet), capture size 1500 bytes
Dupl@ication of this key is not allowed without permission
........i can see this....B9..............AMERICAN......AMERICA. ....$.....AMERICA.......,......AL32UTF8.
39 packets captured
39 packets received by filter
0 packets dropped by kernel
Unfortunately, we see that we can also see our data when we query the encrypted tablespace.
[root@util01 ~]# tcpdump tcp port 1521 -A -s1500 | grep this
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on eth0, link-type EN10MB (Ethernet), capture size 1500 bytes
Dupl@ication of this key is not allowed without permission
........i can't see this....B:O.............AMERICAN......AMERICA. ....$.....AMERICA.......,......AL32UTF8.
Dupl@ication of this key is not allowed without permission
39 packets captured
39 packets received by filter
0 packets dropped by kernel
It 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.
We need to configure Oracle Advanced Network Security, which also requires additions to our server side sqlnet.ora file.
After adding these entries, our sqlnet.ora looks like the one below.
ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/app/oracle/ggate/encryption_wallet)))
SQLNET.ENCRYPTION_SERVER = REQUIRED
SQLNET.CRYPTO_CHECKSUM_SERVER = REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (MD5)
We 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.
After several runs of our test, no data is found in our packet dump.
[root@util01 ~]# tcpdump tcp port 1521 -A -s1500 | grep "see this"
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on eth0, link-type EN10MB (Ethernet), capture size 1500 bytes
PERFORMANCE IMPACT
The performance impact of TDE is negligible on both user response time as well as database server CPU utilization.
Below is a test with 40 threads concurrently inserting customers into an encrypted tablespace…
insert into customers (customer_id, first_name,last_name,credit_limit,card_no)
values (customer_id_seq.nextval, :b1, :b2, :b3, :b4)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 608 8.55 196.20 90 44763 188120 30400
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 609 8.55 196.20 90 44763 188120 30400
…as well as an unencrypted tablespace…
insert into customers (customer_id, first_name,last_name,credit_limit,card_no)
values (customer_id_seq.nextval, :b1, :b2, :b3, :b4)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 621 7.37 227.83 0 39089 176521 31050
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 622 7.37 227.83 0 39089 176521 31050
The low CPU utilization should be the focus of what is above, as the encryption occurs in user CPU space of the operating system.
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.
COST
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.