Using Transparent Database Encryption
This module describes how you can use Transparent Database Encrption to encrypt data stored on disk..
30 minutes
This module will discuss the following topics:
 Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: Because this action loads all screenshots simultaneously, 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.
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 abity 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';
The Oracle database will use the existing application index even though the PERSON ID information is encrypted in the database.
Back to Topic List
In this tutorial, you will create a table that contains an encrypted column. You will create an index on the encrypted column and grant access to the column for a particular user. This user will then make a change to the data. You will then 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
In this section, you will update your sqlnet.ora, create an encrypted wallet (ewallet.p12), open the wallet, and create the master key for TDE. Perform the following:
| 1. |
You need to update your sqlnet.ora file to include an entry for ENCRYPTED_WALLET_LOCATION. Open a terminal window and enter the following commands:
cd $ORACLE_HOME/network/admin
gedit sqlnet.ora
Add the following entry to the end of the file:
ENCRYPTION_WALLET_LOCATION=
(SOURCE=(METHOD=FILE)(METHOD_DATA=
(DIRECTORY=/u01/app/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.
|
| 2. |
Next, you need to open the wallet and create the master encryption key. From your terminal window, enter the following commands:
cd /home/oracle/wkdir
sqlplus /nolog
@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
In this tutorial, you will create some users, create a table with an encrypted column to store credit card information. You will insert some data into the table, create an index on the encrypted column and grant access to the data. Perform the following:
| 1. |
You first need to create some users. From your SQL*Plus session, execute the following script:
@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 will 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 SQL*Plus session, execute the following script:
@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 SQL*Plus session, execute the following script:
@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 will create an index on the credit card number column. From your SQL*Plus session, execute the following script:
@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 SQL*Plus session, execute the following script:
@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
In this section, you will make a change to the table as LSMITH. Perform the following:
| 1. |
Since you gave LSMITH update access, you should be able to make a change. From your SQL*Plus session, execute the following script:
@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';
|
| 2. |
In order to see the execution plan, execute the following script:
@tde06a_review_xplan
select * from table (dbms_xplan.display_cursor);
|
| 3. |
The table user_encrypted_columns will tell you which column is encrypted and its algorithm. From your SQL*Plus session, execute the following script:
@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
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:
| 1. |
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:
@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;
/
|
| 2. |
Now you can add the policy to the cust_payment_info table. From your SQL*Plus session, execute the following script:
@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
In this section, you will 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:
| 1. |
You will 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:
@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;
|
| 2. |
You will now connect as Louise Doran. She has access to Card_M that begins with '5'. From your SQL*Plus session, execute the following script:
@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;
|
| 3. |
You will then connect as Lindsay Smith. She has access to Card_V that begins with '4'. From your SQL*Plus session, execute the following script:
@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
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:
| 1. |
From your SQL*Plus session, execute the following script:
@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
('+MY_DG2/racdb/onlinelog/group_3.263.562151437', DBMS_LOGMNR.NEW);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE
('+MY_DG2/racdb/onlinelog/group_2.262.562151433', DBMS_LOGMNR.ADDFILE);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE
('+MY_DG2/racdb/onlinelog/group_1.261.562151431', 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
To show the difference between what you would see if the column was not encrypted, you will drop the table and recreate it with no columns encrypted. Perform the following:
| 1. |
From your SQL*Plus session, execute the following script:
@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
You can now rerun the logminer script to see what it contains. Perform the following:
| 1. |
From your SQL*Plus session, execute the following script:
@tde15_logminer2
connect / as sysdba;
EXECUTE DBMS_LOGMNR.ADD_LOGFILE
('+MY_DG2/racdb/onlinelog/group_3.263.562151437', DBMS_LOGMNR.NEW);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE
('+MY_DG2/racdb/onlinelog/group_2.262.562151433', DBMS_LOGMNR.ADDFILE);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE
('+MY_DG2/racdb/onlinelog/group_1.261.562151431', 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
To cleanup your environment, perform the following:
| 1. |
From your SQL*Plus session, execute the following script:
@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;
exit;
|
Back to Topic List
In this lesson, 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
Move
your mouse over this icon to hide all screenshot
|
