Database encryption with TDE Part 1

Does your organization have the need to be PCI compliant? Does your organization handle sensitive patient data? Are you worried about losing sensitive data with lost backup tapes?

If the answer to some of these questions is yes, then you probably have the need to encrypt your data. This is a multi-blog entry covering Oracle database feature called Transparent Data Encryption or TDE. I will show some examples of TDE implementation in single instance Oracle databases as well as multi-instance RAC databases.

The innovative idea behind TDE is that it makes database encryption a very easy task, not involving any application changes to existing applications.

So here are the main steps in implementation of TDE:

1. Edit your sqlnet.ora to include entry for encryption wallet like this:

ENCRYPTION_WALLET_LOCATION=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY= /oracle/product/11.1.0.6/db_1/network/admin/wallet)
)
)

2. Bounce the listener for sqlnet.ora change to take effect. 

3. Issue a command like: alter system set encryption key identified by "password"; Behind the scenes this command creates the encryption wallet and adds a TDE master key into the wallet. 

4. That's basically it. Now it's possible to start defining encryption for your table columns. If we look at the wallet location we can see that there is a new file generated for our wallet: 

$ pwd

/oracle/product/11.1.0.6/db_1/network/admin/wallet
$ ls -ltr
total 4
-rw-r--r-- 1 oracle oinstall 1573 Apr 24 20:14 ewallet.p12

5. Create table that holds encrypted data (or modify existing tables):

SQL> create table sample (x int, y varchar(2) encrypt);

Table created.

That's it. All the data that will enter column Y is to be encrypted using the default AES192 algorithm.

Inspecting the dictionary we can find out which tables have encrypted data:

SQL> select * from dba_encrypted_columns;

OWNER TABLE_ COLUMN_NAME ENCRYPTION_ALG SAL
----- ------ ------------------------------ ----------------------------- ---
ANSSI SAMPLE Y AES 192 bits key YES

How easy is this?

Ever found database recovery challenging? Well that's not the case anymore with 11g if you have proper backups. Oracle 11g makes it easier for DBA's to do the most important task that cannot be allowed to go wrong: RECOVERY!

Let's see an example of 11g Data Recovery Advisor in action:

First let's simulate a disk break:

[oracle@11g orcl]$ mv taulualue1.dbf taulualue1.bad

Starting database gives the error:

SQL> startup
ORACLE instance started

Database mounted.
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: '/opt/app/oracle/oradata/orcl/taulualue1.dbf'

This was where the DBA would get nervous, how do I proceed from here?

Well, 11g to the rescue :)

First let's see what wrong:

RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
222 HIGH OPEN 26-FEB-08 One or more non-system datafiles are missing

Let's drill down to the problem in little more detail:

RMAN> list failure 222 detail;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
222 HIGH OPEN 26-FEB-08 One or more non-system datafiles are missing
Impact: See impact for individual child failures
List of child failures for parent failure ID 222
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
5410 HIGH OPEN 26-FEB-08 Datafile 8: '/opt/app/oracle/oradata/orcl/taulualue1.dbf' is missing
Impact: Some objects in tablespace TAULUALUE1 might be unavailable

That looks nice. Now let's ask RMAN to advise the failure:

RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
222 HIGH OPEN 26-FEB-08 One or more non-system datafiles are missing
Impact: See impact for individual child failures
List of child failures for parent failure ID 222
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
5410 HIGH OPEN 26-FEB-08 Datafile 8: '/opt/app/oracle/oradata/orcl/taulualue1.dbf' is missing
Impact: Some objects in tablespace TAULUALUE1 might be unavailable
analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=649 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
1. If file /opt/app/oracle/oradata/orcl/taulualue1.dbf was unintentionally renamed or moved, restore it
Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Restore and recover datafile 8
Strategy: The repair includes complete media recovery with no data loss
Repair script: /opt/app/oracle/diag/rdbms/orcl/orcl/hm/reco_2754197347.hm

Let's look what kind of script RMAN has created for us:

cat /opt/app/oracle/diag/rdbms/orcl/orcl/hm/reco_2754197347.hm

# restore and recover datafile

restore datafile 8;

recover datafile 8;

That looks perfect! Let's do what RMAN suggests us:

RMAN> repair failure;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /opt/app/oracle/diag/rdbms/orcl/orcl/hm/reco_2754197347.hm
contents of repair script:
# restore and recover datafile
restore datafile 8;
recover datafile 8;
Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script
Starting restore at 26-FEB-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00008 to /opt/app/oracle/oradata/orcl/taulualue1.dbf
channel ORA_DISK_1: reading from backup piece /rman_backups/ORCL_backups/0dj9mte5_1_1
channel ORA_DISK_1: piece handle=/rman_backups/ORCL_backups/0dj9mte5_1_1 tag=TAG20080226T185357
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 26-FEB-08
Starting recover at 26-FEB-08
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 26-FEB-08
repair failure complete
Do you want to open the database (enter YES or NO)? yes
database opened

I think recovery cannot be made a lot easier than this!

Total Recall

One of the most interesting new features from my point of view in 11g is the new Total Recall option with it's Flashback Data Archive. Now it's possible to track data changes in the past in a very efficient manner both in terms of performance and disk consumption. Here's a quick example of setting up Flashback Data Archive:

CREATE FLASHBACK ARCHIVE DEFAULT FDA_1_YEAR TABLESPACE FDA_1_YEAR
QUOTA 100M RETENTION 1 YEAR;

alter table scott.emp flashback archive fda_1_year;

In the example above we created a flashback data archive (FDA)and then set the emp table to be included in the FDA and we wished to retain the flashback information for one year.

Having done this it's now possible to look backwards in time for the values of emp table using the 'as of' flashback sql-structure. An example of this would be:

select ename, sal from scott.emp as of timestamp to_timestamp('2007-14-08 15:30:00','YYYY-DD-MM HH24:MI:SS');

We can see from dba_flashback_archive_tables that Oracle stores the flashback information in a separate table which is actually range partioned and compressed to maximize performance and minimize space comsumption:

select table_name, archive_table_name from dba_flashback_archive_tables;

TABLENAME ARCHIVE_TABLE_NAME
------------------------------ -----------------------------------------------------
EMP SYS_FBA_HIST_69515

SQL> select compression, compress_for from dba_tab_partitions where table_name='SYS_FBA_HIST_69515';

COMPRESS COMPRESS_FOR
-------- ------------------
ENABLED FOR ALL OPERATIONS <-- This is new in Oracle 11g and means that compression will be continuous for this table.

FDA will be the way to go if you have the need to retain historical data changes for your database.

Oracle 11g is here!!

The latest release of Oracle database for Linux x86 can be downloaded here:

http://www.oracle.com/technology/software/products/database/index.html

The installation was once again a smooth experience and I will be discussing some really cool new features soon, including Real Application Testing, security enhancements, new partitioning schemes etc.

"su functionality" with sqlplus

Now this sounds a bit unbelievable at first but with 10g R2 "su functionality as we know it from Unixes" can be accomplished.

SQL> create user tester identified by tester;
User created.

SQL> grant connect, resource to tester;
Grant succeeded.

SQL> create user datauser identified by datauser;
User created.

SQL> grant connect, resource to datauser;
Grant succeeded.

SQL> connect datauser/datauser
Connected.

SQL> create table sample(a number);
Table created.

SQL> insert into sample values (99999);
1 row created.

SQL> commit;
Commit complete.

SQL> connect system
Enter password: ******
Connected.

SQL> alter user datauser grant connect through tester;
User altered.

SQL> connect tester[datauser]/tester
Connected.

SQL> show user
USER is "DATAUSER"

SQL> select user from dual;

USER
------------------------------
DATAUSER

SQL> desc sample
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER

SQL> select sys_context('userenv','session_user') "Sess. User",
2 sys_context('userenv','proxy_user') "Prox.User",
3 sys_context('userenv','authentication_type') "Auth.type" from dual;

Sess. User Prox.User Auth.type
-------------------- -------------------- ----------
DATAUSER TESTER PROXY