Exploring Oracle 11g Tablespace Encryption

By | In Oracle | December 02nd, 2008

Tablespace encryption encrypts data at the datafile level to keep people from being able to peek at the oracle datafiles directly. Oracle 10 allowed us to encrypt certain columns, and starting in Oracle 11, tablespace encryption is made available. First we’ll need to setup oracle wallet. Oracle will need to store a key to encrypt and decrypt the data in our tablespace. The key is kept in a file external to the database using oracle wallet. The first step to setup the wallet is to setup the sqlnet.ora file to a location where the wallet will be stored. Oracle documentation suggests using $ORACLEnetworkadmin, but since I’m testing this using an Oracle 11 RAC database I’ll have to put the file in a location available to all nodes in the cluster, so I’ve chosen my voting disk on /ocfs. Here is what my sqlnet.ora looks like (also – be sure to copy the sqlnet.ora file to each node in the RAC cluster) [code language=”plain”][oracle@oratest1 ~]$ cat $ORACLE_HOME/network/admin/sqlnet.ora ENCRYPTION_WALLET_LOCATION= (SOURCE=(METHOD=FILE)(METHOD_DATA= (DIRECTORY=/ocfs/oracle/rac11g/wallet/))) [oracle@oratest1 ~]$ scp /opt/oracle/product/11.1.0/db_1/network/admin/sqlnet.ora oracle@oratest2:/opt/oracle/product/11.1.0/db_1/network/admin/sqlnet.ora [/code] Next, we create the wallet using the mkstore utility on the command line. The mstore command will prompt for a password, be sure to write this one down as we’ll be using it later [code language=”plain”] [oracle@oratest1 ~]$ mkdir -p /ocfs/oracle/rac11g/wallet [oracle@oratest1 ~]$ chmod 700 /ocfs/oracle/rac11g/wallet [oracle@oratest1 ~]$ cd /ocfs/oracle/rac11g/wallet [oracle@oratest1 walet]$ mkstore -wrl . -create [oracle@oratest1 walet]$ ls -la total 19 drwx—— 2 oracle dba 2048 Nov 28 11:30 . drwxr-xr-x 3 oracle dba 2048 Nov 28 11:13 .. -rw——- 1 oracle dba 7340 Nov 28 11:30 cwallet.sso -rw——- 1 oracle dba 7312 Nov 28 11:30 ewallet.p12 [/code] Finally, you have to open the wallet, the following command will do just that. Make sure you run this command from a sqlplus session that was started using the sqlnet.ora created earlier. [code language=”plain”] SQL>alter system set encryption wallet open authenticated by “somereallyobscurestringofcharactersandd1g1t5″; System altered. [/code] Test ability to encrypt sensitive data Lets suppose we have an application which stores passwords in a database table (beleive me, this happens; I’ve seen this in production systems – more often than I’d like to admit). First lets create a simple test table and see if we can find the password by looking at the data file directly. Create two tablespaces, one will be encrypted, and one will be a normal unencrypted tablespace. We’ll also create a simple table to test out the encryption [code language=”plain”] SQL>create tablespace crypttest datafile ‘/opt/oracle/crypttest.dbf’ size 256m ENCRYPTION default storage( encrypt ); Tablespace created. SQL>create tablespace nocrypttest datafile ‘/opt/oracle/nocrypttest.dbf’ size 256m; Tablespace created. SQL>create table sensitive tablespace nocrypttest as select username, password from dba_users where rownum = 0; Table created. SQL>insert into sensitive values (‘TEST_USER’,’SENSITIVEPASSWORD’); 1 row created. SQL>commit; Commit complete. SQL>create index i_usrpass on sensitive ( password ) tablespace nocrypttest; Index created. SQL>alter system checkpoint; System altered. [/code] Now that we have a normal unencrypted table, we drop back out to the operating system to see if the sensitive data is visible by looking at the datafile directly. You’ll notice its quite easy to find what we are looking for; The first “SENSITIVEPASSWORD” is found in the table segment, the second is from the index segment. Uugh. [code language=”plain”] [oracle@oratest1 ~]$ strings /opt/oracle/nocrypttest.dbf | grep -i SENSITIVEPASSWORD SENSITIVEPASSWORD SENSITIVEPASSWORD [/code] Now that we’ve verified that our sensitive data is not protected very well, lets throw it into the encrypted tablespace and give it another go. The password is no longer plainly visible in the datafile. Yeah! [code language=”plain”] SQL>alter table sensitive move tablespace crypttest; Table altered. SQL>alter index i_usrpass rebuild tablespace crypttest; Index altered. SQL>alter system checkpoint; System altered. SQL>exit [oracle@oratest1 ~]$ strings /opt/oracle/crypttest.dbf | grep -i SENSITIVEPASSWORD | wc -l 0 [/code] Of course, there are always trade offs; encryption is no different. Oracle is decrypting the data in the background when it has to fetch from disk into the db cache, and decrypt it again when it writes back to disk. This en/decrypting will naturally require some extra cpu cycles Lets create a couple of tables for comparisons between encrypted and unencrypted full table and index scans. Create a couple of tables to play around with [code language=”plain”] SQL> create table cryptperf tablespace crypttest 2 as select * from ( 3 select * from dba_objects union all 4 select * from dba_objects union all 5 select * from dba_objects union all 6 select * from dba_objects union all 7 select * from dba_objects union all 8 select * from dba_objects 9 ); Table created. Elapsed: 00:00:17.97 SQL> create table nocryptperf tablespace nocrypttest 2 as select * from ( 3 select * from dba_objects union all 4 select * from dba_objects union all 5 select * from dba_objects union all 6 select * from dba_objects union all 7 select * from dba_objects 8 ); Table created. Elapsed: 00:00:33.50 [/code] Full table scan test: it took around 10 seconds longer to scan the encrypted table as compared to the normal table. This is actually a bigger difference that I’d expect to see. Lets not jump the gun here are start saying that tablespace encryption is slow and refuse to apply it in a production system – this test is probably not extensive enough. However, it does warrent caution and of course rigorous testing should be done before any implementation. [code language=”plain”] SQL> select count(1) from ( select * from cryptperf ) ; COUNT(1) ———- 422622 Elapsed: 00:00:16.35 Execution Plan ———————————————————- Plan hash value: 470027687 ———————————————————————— | Id | Operation | Name | Rows | Cost (%CPU)| Time | ———————————————————————— | 0 | SELECT STATEMENT | | 1 | 1723 (1)| 00:00:21 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| CRYPTPERF | 411K| 1723 (1)| 00:00:21 | ———————————————————————— Note —– – dynamic sampling used for this statement Statistics ———————————————————- 28 recursive calls 0 db block gets 6298 consistent gets 6448 physical reads 0 redo size 420 bytes sent via SQL*Net to client 420 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> select count(1) from ( select * from nocryptperf ); COUNT(1) ———- 352190 Elapsed: 00:00:04.87 Execution Plan ———————————————————- Plan hash value: 949999261 ————————————————————————– | Id | Operation | Name | Rows | Cost (%CPU)| Time | ————————————————————————– | 0 | SELECT STATEMENT | | 1 | 1437 (1)| 00:00:18 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| NOCRYPTPERF | 380K| 1437 (1)| 00:00:18 | ————————————————————————– Note —– – dynamic sampling used for this statement Statistics ———————————————————- 28 recursive calls 0 db block gets 5266 consistent gets 5445 physical reads 0 redo size 420 bytes sent via SQL*Net to client 420 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed [/code] Index scan test In oracle 10gR2 index range scans were reportedly not supported on indexed columns that were encrypted. In the test below we add an index on the object_id column and show that the optimizer was able to perform an index range scan on the index. [code language=”plain”] SQL> create index i_cryptperf on cryptperf(object_id) tablespace crypttest; Index created. Elapsed: 00:00:04.29 SQL> create index i_nocryptperf on nocryptperf(object_id) tablespace nocrypttest; Index created. SQL> begin dbms_stats.gather_table_stats(‘SYS’,’CRYPTPERF’,cascade => TRUE); end; 2 / PL/SQL procedure successfully completed. Elapsed: 00:00:13.14 SQL> begin dbms_stats.gather_table_stats(‘SYS’,’NOCRYPTPERF’,cascade => TRUE); end; 2 / PL/SQL procedure successfully completed. Elapsed: 00:00:03.00 SQL> SQL> set autotrace on; SQL> select object_id from nocryptperf where object_id = 20; OBJECT_ID ———- 20 20 20 20 20 Elapsed: 00:00:00.02 Execution Plan ———————————————————- Plan hash value: 4180234382 ——————————————————————————– — | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ——————————————————————————– — | 0 | SELECT STATEMENT | | 5 | 25 | 3 (0)| 00:00:01 | |* 1 | INDEX RANGE SCAN| I_NOCRYPTPERF | 5 | 25 | 3 (0)| 00:00:01 | ——————————————————————————– — Predicate Information (identified by operation id): ————————————————— 1 – access(“OBJECT_ID”=20) Statistics ———————————————————- 141 recursive calls 0 db block gets 20 consistent gets 0 physical reads 0 redo size 470 bytes sent via SQL*Net to client 420 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 4 sorts (memory) 0 sorts (disk) 5 rows processed SQL> select object_id from cryptperf where object_id = 20; OBJECT_ID ———- 20 20 20 20 20 20 6 rows selected. Elapsed: 00:00:00.01 Execution Plan ———————————————————- Plan hash value: 2919947406 ——————————————————————————– | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ——————————————————————————– | 0 | SELECT STATEMENT | | 6 | 30 | 3 (0)| 00:00:01 | |* 1 | INDEX RANGE SCAN| I_CRYPTPERF | 6 | 30 | 3 (0)| 00:00:01 | ——————————————————————————– Predicate Information (identified by operation id): ————————————————— 1 – access(“OBJECT_ID”=20) Statistics ———————————————————- 164 recursive calls 0 db block gets 24 consistent gets 0 physical reads 0 redo size 475 bytes sent via SQL*Net to client 420 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 4 sorts (memory) 0 sorts (disk) 6 rows processed [/code]

Contact Us

Leave a Reply

Your email address will not be published.
Required fields are marked (*).