Using Transparent Database Encryption

Purpose

This tutorial describes how you can use Transparent Database Encrption to encrypt data stored on disk.

Time to Complete

30 minutes

Topics

This tutorial covers the following topics:

	Overview
	Scenario
	Prerequisites
	Preparing the Database for Encryption
	Creating a Table with an Encrypted Column
	Making Changes to Encrypted Data
	Adding and Applying a VPD Policy
	Testing the Policy
	Using LogMiner to View Redo Logs
	Recreating Your Table Without the Encrypted Column
	Reviewing the Redo Logs Again
	Cleanup
	Summary

Viewing Screenshots

Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over each individual icon in the following steps to load and view only the screenshot associated with that step.

Overview

Oracle Database 10g Release 2 Transparent Database Encryption simplifies encryption of sensitive personal information such as credit card numbers and social security numbers. Transparent Data Encryption eliminates the need to embed encryption routines in existing applications and dramatically lowers the cost and complexity of encryption. With a few simple commands, sensitive application data can be encrypted.

Automatic Encryption Of Sensitive Information

Most encryption solutions require specific calls to encryption functions within the application code. This is expensive because it typically requires extensive understanding of an application as well as the ability to write and maintain software. In general, most organizations don't have the time or expertise to modify existing applications to make calls to encryption routines. Oracle Transparent Data Encryption addresses the encryption problem by deeply embedding encryption in the Oracle database.

Application logic performed through SQL will continue to work without modification. In other words, applications can use the same syntax to insert data into an application table and the Oracle database will automatically encrypt the data before writing the information to disk. Subsequent select operations will have the data transparently decrypted so the application will continue to work normally. This is important because existing applications generally expect to see application data unencrypted. Displaying encrypted data may, at a minimum, confuse the application user and may even break an existing application.

Setting The Encryption Key

Oracle Transparent Data Encryption provides the key management infrastructure necessary for implementing encryption. Encryption works by passing clear text data along with a secret, known as the key, into an encryption program. The encryption program encrypts the clear text data using the supplied key and returns the data encrypted. Historically the burden of creating and maintaining the secret or key has been on the application. Oracle Transparent Data Encryption solves this problem by automatically generating a master key for the entire database. Upon starting up the Oracle database, an administrator must open an object known as an Oracle Wallet using password separate from the system or DBA password. The administrator then initializes the database master key. The master key is automatically generated.

Performance

Encryption typically creates problems for existing application indexes because the index data isn't encrypted. Oracle Transparent Data Encryption encrypts the index value associated with a given application table. This means that equality searches within an application will see little to no decrease in performance. For example, assume an index exists on application PERSON ID and the application executes the following statement:

SQL> Select rating from credit where person id = '23590';

Oracle database will use the existing application index even though the PERSON ID information is encrypted in the database.

Back to Topic List

Scenario

In this tutorial, you create a table that contains an encrypted column. You create an index on the encrypted column and grant access to the column for a particular user. This user then makes a change to the data. Then, you create a function which contains proper access control to the encrypted data and then apply the function through a VPD policy.

Transparent Data Encryption encrypts data right before it is written to disk and de-crypts it when it is read. It's transparent to all applications that use the SQL layer. Hence there is no easy way to verify that data has truly been encrypted. Since Oracle LogMiner records what is written to disk, you can access the information.

Back to Topic List

Prerequisites

Before starting this tutorial, you should:

1.	Perform the Installing Oracle Database 10g on Windows tutorial.
2.	Download and unzip tde.zip into your working directory(c:\wkdir)

Back to Topic List

Preparing the Database for Encryption

In this section, you update your sqlnet.ora, create an encrypted wallet (ewallet.p12), open the wallet, and create the master key for TDE. Perform the following steps:

Select Start > Programs > Oracle - OraDb10g_home1 > Application Development > SQL Plus. Enter /nolog for the User Name and click OK.

You need to update your sqlnet.ora file to include an entry for ENCRYPTED_WALLET_LOCATION. Enter the following command:

edit c:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora

In the notepad that pops up, add the following to the end of the file:

ENCRYPTION_WALLET_LOCATION= (SOURCE=(METHOD=FILE)(METHOD_DATA= (DIRECTORY=c:\oracle\product\10.2.0\db_1)))

Save your changes and close the file.

Note: Any directory can be picked for the encrypted wallet, but the path should not point to the standard obfuscated wallet (cwallet.sso) created during DB installation.

Next, you need to open the wallet and create the master encryption key. Execute the following script:

@c:\wkdir\tde00_dbsetup

connect / as sysdba
alter system set key identified by "welcome1";

The above alter command does the following:

	If no encrypted wallet is present in the directory specified, the encrypted wallet is created (ewallet.p12), the wallet is opened, and the master key for TDE is created/recreated.
	If the encrypted wallet is present in the directory specified, the walled is opened, and the master key for TDE is created/recreated.

Note: only users with the 'alter system' privilege can create a master key or open the wallet.

The master key should only be created once, unless you want to re-encrypt your data with a new encryption key !!!

For later sessions, you do not want to use the command given above; you need the wallet to be open (it has been closed when you shut down your database), but you don't want to create a new master key. Then the command is:

alter system set wallet open identified by "welcome1";

The master encryption key is necessary because each table has its own encryption key. These column keys are stored in the database. Since the wallet can only store a limited number of keys and is not very scalable, the column keys are encrypted with the master key. This way, you can have as many column keys as needed, with only a small number of master keys stored in the wallet (including retired keys, that you may need one day to decrypt data from an old backup-tape). By default, the command above generates a key using the Advanced Encryption Standard with 192 bits (AES192). 3DES could also be used, or a smaller or bigger number of bits for the AES encryption.

Back to Topic List

Creating a Table with an Encrypted Column

In this tutorial, you create some users, create a table with an encrypted column to store credit card information. You insert some data into the table, create an index on the encrypted column and grant access to the data. Perform the following steps:

1.	You first need to create some users. From your SQLPlus session, execute the following script: @c:\wkdir\tde01_crusers* connect system/oracle prompt Create users: JKING, LSMITH and LDORAN grant connect to JKING identified by welcome1; grant connect, DBA to LSMITH identified by welcome1; grant connect to LDORAN identified by welcome1; Note: You have granted LSMITH DBA role to demonstrate that TDE supports indices for equality searches. As a result, the output of dbms_xplan should include INDEX RANGE SCAN.
2.	Next you create a table that contains a column to store encrypted (default AES192) credit card information. NO SALT is specified since there will be an index on the credit_card_number column, which is not possible when the encrypted values are salted. From your SQLPlus session, execute the following script: @c:\wkdir\tde02_crtabl* connect oe/oe create table cust_payment_info (first_name varchar2(11), last_name varchar2(10), order_number number(5), credit_card_number varchar2(16) ENCRYPT NO SALT, active_card varchar2(3));
3.	Now you can add some data to the table you just created . From your SQLPlus session, execute the following script: @c:\wkdir\tde03_poptabl* insert into cust_payment_info values ('Jon', 'Oldfield', 10001, '5446959708812985','YES'); insert into cust_payment_info values ('Chris', 'White', 10002, '5122358046082560','YES'); insert into cust_payment_info values ('Alan', 'Squire', 10003, '5595968943757920','YES'); insert into cust_payment_info values ('Mike', 'Anderson', 10004, '4929889576357400','YES'); insert into cust_payment_info values ('Annie', 'Schmidt', 10005, '4556988708236902','YES'); insert into cust_payment_info values ('Elliott', 'Meyer', 10006, '374366599711820','YES'); insert into cust_payment_info values ('Celine', 'Smith', 10007, '4716898533036','YES'); insert into cust_payment_info values ('Steve', 'Haslam', 10008, '340975900376858','YES'); insert into cust_payment_info values ('Albert', 'Einstein', 10009, '310654305412389','YES');
4.	To improve performance, you create an index on the credit card number column. From your SQLPlus session, execute the following script: @c:\wkdir\tde04_cridx* create index cust_payment_info_idx on cust_payment_info (credit_card_number);
5.	You need to grant access to your users to the customer payment information table. In this case, LSMITH is the only user who can update the information. The other users can only view it. From your SQLPlus session, execute the following script: @c:\wkdir\tde05_grant_access* grant select on oe.CUST_PAYMENT_INFO to LDORAN; grant select, update on oe.CUST_PAYMENT_INFO to LSMITH; grant select on oe.CUST_PAYMENT_INFO to JKING;

Back to Topic List

Making Changes to Encrypted Data

In this section, you make a change to the table as LSMITH. Perform the following steps:

Since you gave LSMITH update access, you should be able to make a change. From your SQL*Plus session, execute the following script:

@c:\wkdir\tde06_make_update

prompt *** Connect as Lindsay Smith (Card_V)
conn LSMITH/welcome1;
update oe.CUST_PAYMENT_INFO set ACTIVE_CARD='NO' 
   where CREDIT_CARD_NUMBER='4556988708236902';

In order to see the execution plan, execute the following script:

@c:\wkdir\tde06a_review_xplan

select * from table (dbms_xplan.display_cursor);

The table user_encrypted_columns will tell you which column is encrypted and its algorithm. From your SQL*Plus session, execute the following script:

@c:\wkdir\tde07_select_encrypt_col

connect oe/oe
col TABLE_NAME format a18;
col COLUMN_NAME format a19;
col ENCRYPTION_ALG format a17;
select * from user_encrypted_columns;

Back to Topic List

Adding and Applying a VPD Policy

Since encryption does not replace proper access control, you will limit access to the rows with a very simple VPD policy: At first you will check if the person who logs into the database is an employee, and then you will limit access to oe.cust_payment_info by credit card number:

Card_A begins with '34' or '37'	Janette King
Card_V begins with '4'	Lindsay Smith
Card_M begins with '5'	Louise Doran

If you review the data you loaded into the table, you see that the credit card number associated with 'Albert Einstein' will never be selected by authorized users. It is only selected by intruders who circumvented the access control policies or administrative users who are exempt from these policies. This allows for highly focused auditing of this table.

Perform the following steps:

You first need to create the function that contains the logic you want for the credit card and employee validation. From your SQL*Plus session, execute the following script:

@c:\wkdir\tde08_crfunction

connect system/oracle;
prompt
prompt *** Create policy function to create the where-clause:
create or replace function f_policy_oe_cust_payment_info
-- Function must have the following parameters
   (schema in varchar2, tab in varchar2)
-- Function will return a string that is used as a WHERE clause                     
 return varchar2
as
 v_manager_id     number:=0;
 is_employee      number:=0;
 v_user           varchar2(20);
 out_string       varchar2(70) default '1=2 ';
begin
  -- get session user
  v_user := lower(sys_context('userenv','session_user'));
  -- Is the user an employee?
  begin
    select manager_id into v_manager_id
    from hr.employees
    where lower(email) = v_user;
    is_employee:=1;
  exception
   when no_data_found then 
    is_employee:=2;
  end;
  -- create where clause when user is authorized to see parts of the table
  if     is_employee=1 and lower(v_user)='jking' and v_manager_id=146 then
  out_string := out_string ||'or CREDIT_CARD_NUMBER like ''34%'' 
    or CREDIT_CARD_NUMBER like ''37%''';
  elsif  is_employee=1 and lower(v_user)='lsmith' and v_manager_id=146 then
  out_string := out_string ||'or CREDIT_CARD_NUMBER like ''4%''';
  elsif  is_employee=1 and lower(v_user)='ldoran' and v_manager_id=146 then
  out_string := out_string ||'or CREDIT_CARD_NUMBER like ''5%''';
  end if;
 return out_string;
end;
/

Now you can add the policy to the cust_payment_info table. From your SQL*Plus session, execute the following script:

@c:\wkdir\tde09_addpolicy

prompt
prompt *** Add policy to 'oe.cust_payment_info' table:
begin
dbms_rls.add_policy('oe','cust_payment_info','ac_cust_payment_info',
  'system','f_policy_oe_cust_payment_info', policy_type => dbms_rls.context_sensitive);
end;
/

Back to Topic List

Testing the Policy

In this section, you connect as each employee to see if the policy works. Three things happen to show the true transparency of TDE:

	An index has been used on the encrypted column
	Even though the credit card numbers are stored encrypted, the where clause in the VPD policy looks for numbers in clear text and retrieves the correct rows.
	The rows visible to the three employees contain the credit card numbers in clear text. Most likely, the employees wouldn't even know that the data was stored encrypted.

Perform the following steps:

You first connect as Janette King. She has access to Card_A that begins with '34' or '37'. From your SQL*Plus session, execute the following script:

@c:\wkdir\tde10_testpolicy_jking

prompt
prompt *** Connect as Janette King (Card_A)
conn JKING/welcome1;
col CREDIT_CARD_NUMBER heading Card_A format a18;
select * from oe.CUST_PAYMENT_INFO order by CREDIT_CARD_NUMBER;

Now you connect as Louise Doran. She has access to Card_M that begins with '5'. From your SQL*Plus session, execute the following script:

@c:\wkdir\tde11_testpolicy_ldoran

prompt
prompt *** Connect as Louise Doran (Card_M)
conn LDORAN/welcome1;
col CREDIT_CARD_NUMBER heading Card_M format a18;
select * from oe.CUST_PAYMENT_INFO order by CREDIT_CARD_NUMBER;

Then you connect as Lindsay Smith. She has access to Card_V that begins with '4'. From your SQL*Plus session, execute the following script:

@c:\wkdir\tde12_testpolicy_lsmith

prompt
prompt *** Connect as Lindsay Smith (Card_V)
conn LSMITH/welcome1;
col CREDIT_CARD_NUMBER heading Card_V format a18;
select * from oe.CUST_PAYMENT_INFO order by CREDIT_CARD_NUMBER;

Back to Topic List

Using LogMiner to View Redo Logs

Since TDE is done right before the data is written and transparent to all applications, there is no easy way to verify that the data has truly been encrypted. Since Oracle LogMiner records what has been written to disk, you can use it to view what is contained in the log files. Perform the following steps:

From your SQL*Plus session, execute the following script:

@c:\wkdir\tde13_logminer

connect / as sysdba;
alter database add supplemental log data;
REM select member as LOG_FILE_LOCATION from v$logfile;

EXECUTE DBMS_LOGMNR.ADD_LOGFILE
('c:\oracle\product\10.2.0\oradata\orcl\redo03.log', DBMS_LOGMNR.NEW);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE
('c:\oracle\product\10.2.0\oradata\orcl\redo02.log', DBMS_LOGMNR.ADDFILE);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE
('c:\oracle\product\10.2.0\oradata\orcl\redo01.log', DBMS_LOGMNR.ADDFILE)
prompt start LogMiner:
EXECUTE DBMS_LOGMNR.START_LOGMNR
  (options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY);
select sql_redo from v$logmnr_contents where
table_name = 'CUST_PAYMENT_INFO' and operation='INSERT';

LogMiner does not support encrypted data, so the encrypted values in the credit_card_number column are displayed as Unsupported Type.

Note: If you run this tutorial in your own environment, you need to run the following command to determine the logfile names and then modify the script accordingly:

select member as LOG_FILE_LOCATION from v$logfile;

Back to Topic List

Recreating Your Table Without the Encrypted Column

To show the difference between what you would see if the column was not encrypted, you drop the table and recreate it with no columns encrypted. Perform the following steps:

From your SQL*Plus session, execute the following script:

@c:\wkdir\tde14_crtabl2

connect oe/oe
drop table cust_payment_info;
create table cust_payment_info 
  (first_name varchar2(11), 
  last_name varchar2(10), 
  order_number number(5), 
  credit_card_number varchar2(20),
  active_card varchar2(3));
insert into cust_payment_info values
  ('Jon', 'Oldfield', 10001, 5446959708812985,'YES');
insert into cust_payment_info values
  ('Chris', 'White', 10002, 5122358046082560,'YES');
insert into cust_payment_info values
  ('Alan', 'Squire', 10003, 5595968943757920,'YES');
insert into cust_payment_info values
  ('Mike', 'Anderson', 10004, 4929889576357400,'YES');
insert into cust_payment_info values
  ('Annie', 'Schmidt', 10005, 4556988708236902,'YES');
insert into cust_payment_info values
  ('Elliott', 'Meyer', 10006, 374366599711820,'YES');
insert into cust_payment_info values
  ('Celine', 'Smith', 10007, 4716898533036,'YES');
insert into cust_payment_info values
  ('Steve', 'Haslam', 10008, 340975900376858,'YES');
insert into cust_payment_info values
  ('Albert', 'Einstein', 10009, 310654305412389,'YES');
create index cust_payment_info_idx on cust_payment_info (credit_card_number);
grant select on oe.CUST_PAYMENT_INFO to LDORAN;
grant select, update on oe.CUST_PAYMENT_INFO to LSMITH;
grant select on oe.CUST_PAYMENT_INFO to JKING;
prompt *** Connect as Lindsay Smith (Card_V)
conn LSMITH/welcome1;
update oe.CUST_PAYMENT_INFO set ACTIVE_CARD='NO' 
  where CREDIT_CARD_NUMBER=4556988708236902;

Back to Topic List

Reviewing the Redo Logs Again

You can now rerun the logminer script to see what it contains. Perform the following steps:

From your SQL*Plus session, execute the following script:

@c:\wkdir\tde15_logminer2

connect / as sysdba;
EXECUTE DBMS_LOGMNR.ADD_LOGFILE
('c:\oracle\product\10.2.0\oradata\orcl\redo03.log', DBMS_LOGMNR.NEW);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE
('c:\oracle\product\10.2.0\oradata\orcl\redo02.log', DBMS_LOGMNR.ADDFILE);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE
('c:\oracle\product\10.2.0\oradata\orcl\redo01.log', DBMS_LOGMNR.ADDFILE)
prompt start LogMiner:
EXECUTE DBMS_LOGMNR.START_LOGMNR
  (options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY);
select sql_redo from v$logmnr_contents where
table_name = 'CUST_PAYMENT_INFO' and operation='INSERT';

The column is not encrypted, and LogMiner shows the clear text data which has been written to disk.

Back to Topic List

Cleanup

To cleanup your environment, perform the following step:

From your SQL*Plus session, execute the following script:

@c:\wkdir\tde16_cleanup

connect system/oracle
drop user JKING cascade;
drop user LSMITH cascade;
drop user LDORAN cascade;
drop function f_policy_oe_cust_payment_info;
connect oe/oe
drop table cust_payment_info;

Back to Topic List

Summary

In this tutorial, you learned how to:

	Prepare the database for encryption
	Create a table with an encrypted column
	Make a change to your encrypted data
	Apply a VPD policy
	Use LogMiner to view redo logs
	Recreate your table without the encrypted column
	Review the redo logs again

Back to Topic List