I know TDE has been introduced in 10G R2, but I start with it from top to bottom and try to discover the 11G enhancements.
In a nutshell TDE is about a encryption master key stored in a wallet which is somewhere on a location. If there are table columns encrypted in the database then there is also a table encryption key. The combination of the encryption key + wallet master key provides security, mainly usefull for
off host database security. The wallet must be backed up and must be open in order to de-crypt the data. If the wallet is open then access to the encrypted data is transparant thus the name TDE.
ENCRYPTION_WALLET_LOCATION= (SOURCE=(METHOD=FILE)(METHOD_DATA= (DIRECTORY=C:\Oracle\Wallet)))
C:\Windows\system32>sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 - Production on Thu Aug 13 21:03:09 2009
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
PLATINUM_SYS >alter system set encryption key identified by "secretforyou";
System altered.
PLATINUM_SYS >alter system set encryption wallet close identified by "secret for you";
System altered.
PLATINUM_SYS >alter system set encryption wallet open identified by "secretforyou";
System altered.
PLATINUM_SYS >connect pm/pm
Connected.
PLATINUM_PM >create table t_employee (emp_no number,emp_name varchar2(100),emp_name_family varchar2(100),emp_salary number(6) encrypt,emp_time timestamp default systimestamp );
Table created.
PLATINUM_PM >alter table t_employee add constraint t_employee_pk primary key (emp_no);
Table altered.
PLATINUM_PM >select * from user_encrypted_columns;
TABLE_NAME COLUMN_NAME
ENCRYPTION_ALG SAL
T_EMPLOYEE EMP_SALARY
AES 192 bits key YES
Let us insert some data
SQL> desc t_employee;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMP_NO NUMBER
EMP_NAME VARCHAR2(100)
EMP_NAME_FAMILY VARCHAR2(100)
EMP_SALARY NUMBER(6) ENCRYPT
EMP_TIME TIMESTAMP(6)
SQL> insert into t_employee values (1,'Chantal','Walschaerts',10000,systimestamp);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t_employee;
Chantal
Walschaerts
10000
31-MAY-10 09.04.16.137723 PM
Let' s close the wallet, we obviously have to connect as sysdba. It seems the command we should use to close a wallet is different between 11GR1 and 11GR2. In 11G R2 we need to add the "identified by password", it does not seem this is needed for 11G R1 ( yet to be confirmed )
[oracle@pcguy dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon May 31 21:23:06 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter system set encryption wallet close identified by "secretforyou";
System altered.
We cannot view any encrypted data anymore as long as the wallet is closed
SQL> select * from pm.t_employee;
select * from pm.t_employee
*
ERROR at line 1:
ORA-28365: wallet is not open
We can select the non encrypted data though.( remember that only the salary field is encrypted )
SQL> select emp_no,emp_name,emp_name_family from pm.t_employee;
1
Chantal
Walschaerts
As long as the wallet is open there is no difference for the user accessing the data.
As soon as the wallet is closed the impact, security of encryption comes into play.
PLATINUM_PM >drop table t_employee purge;
Table dropped.
PLATINUM_PM >create table t_employee (emp_no number,emp_name varchar2(100),emp_name_family varchar2(100),emp_salary number(6) encrypt using '3DES168',emp_time timestamp default systimestamp );
Table created.
PLATINUM_PM >alter table t_employee add constraint t_employee_pk primary key (emp_no);
Table altered.
PLATINUM_PM >select * from user_encrypted_columns;
TABLE_NAME COLUMN_NAME
ENCRYPTION_ALG SAL
T_EMPLOYEE EMP_SALARY
3 Key Triple DES 168 bits key YES
We can choose between the below listed encryption algorithm
1. AES192
2. AES128
3. AES256
4. 3DES168
PLATINUM_PM >create index t_employee_idx on t_employee (emp_salary);
create index t_employee_idx on t_employee (emp_salary)
*
ERROR at line 1:
ORA-28338: cannot encrypt indexed column(s) with salt
PLATINUM_PM >alter table t_employee modify (emp_salary encrypt no salt);
Table altered.
PLATINUM_PM >create index t_employee_idx on t_employee (emp_salary);
Index created.
PLATINUM_PM >drop index t_employee_idx;
Index dropped.
PLATINUM_PM >alter table t_employee modify (emp_salary encrypt salt);
Table altered.
PLATINUM_PM >alter table t_employee rekey using 'AES128';
Table altered.
PLATINUM_PM >alter table t_employee rekey using 'AES256';
Table altered.
This is new in 11G.
One can encrypt at tablespace level. The decision encryption or not must be made at tablespace creation time.
Moving a segment from an encrypted tablespace to a tablespace without encryption causes decryption.
Moving a segment from a non encrypted tablespace to a tablespace with encryption causes encryption.
Tablespace created.
PLATINUM_SYS >select tablespace_name,encrypted from dba_tablespaces;
TABLESPACE_NAME ENC
SYSTEM NO
SYSAUX NO
UNDOTBS1 NO
TEMP NO
USERS_ARCHIVE_01 NO
USERS_ARCHIVE_02 NO
PM NO
PM1 NO
PM2 NO
PM3 NO
CATALOG NO
USERS NO
TS_ENCRYPT YES
13 rows selected.
PLATINUM_SYS >select * from v$encrypted_tablespaces;
TS# ENCRYPT ENC
14 3DES168 YES
TDE GYM 4 : Encryption using the Data Pump
Can be achieved using expdp 4 attributes comes into play
values are: ALL, DATA_ONLY, METADATA_ONLY,
ENCRYPTED_COLUMNS_ONLY, or NONE.
ENCRYPTION_ALGORITHM Specify how encryption should be done where valid
keyword values are: (AES128), AES192, and AES256.
ENCRYPTION_MODE Method of generating encryption key where valid keyword
values are: DUAL, PASSWORD, and (TRANSPARENT).
ENCRYPTION_PASSWORD Password key for creating encrypted column data.
Example
oracle@pcguy admin$ $ORACLE_HOME/bin/expdp system/palladium dumpfile=expdp_platinum.dmp logfile=expdp_platinum.log directory=BACKUP ENCRYPTION=ALL ENCRYPTION_ALGORITHM=AES128 ENCRYPTION_MODE=DUAL ENCRYPTION_PASSWORD=secretforyou full=Y
Export: Release 11.1.0.7.0 - 64bit Production on Monday, 24 August, 2009 21:06:23
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** dumpfile=expdp_platinum.dmp logfile=expdp_platinum.log directory=BACKUP ENCRYPTION=ALL ENCRYPTION_ALGORITHM=AES128 ENCRYPTION_MODE=DUAL ENCRYPTION_PASSWORD=******** full=Y