Select Page

Exploring Oracle 11g Tablespace Encryption

Author: Mark Hammingh | | December 2, 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)

$ 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

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

$ 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

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.

SQL>alter system set encryption wallet open authenticated by “somereallyobscurestringofcharactersandd1g1t5”; System altered.

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

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.

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.

$ strings /opt/oracle/nocrypttest.

dbf | grep -i SENSITIVEPASSWORD SENSITIVEPASSWORD SENSITIVEPASSWORD

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!

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

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

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

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.

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

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.

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

How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified

The “ORA-12154: TNS Oracle error message is very common for database administrators. Learn how to diagnose & resolve this common issue here today.

Vijay Muthu | February 4, 2021

How to Recover a Table from an Oracle 12c RMAN Backup

Our database experts explain how to recover and restore a table from an Oracle 12c RMAN Backup with this step-by-step blog. Read more.

Megan Elphingstone | February 2, 2017

Data Types: The Importance of Choosing the Correct Data Type

Most DBAs have struggled with the pros and cons of choosing one data type over another. This blog post discusses different situations.

Craig Mullins | October 11, 2017

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS