11G
Transparent Data Encryption TDE
User Rating: / 0
- Details
-
Parent Category: Articles
-
Created on Thursday, 25 March 2010 20:32
-
Last Updated on Monday, 12 March 2012 13:54
-
Published on Thursday, 25 March 2010 20:32
-
Written by Guy Lambregts
-
Hits: 6045
Transparent Data Encryption TDE
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.
TDE GYM 1 : I create a wallet and open and close it
I want a non default location for the wallet, as such I have to define the ENCRYPTION_WALLET_LOCATION sqlnet.ora parameter.
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.
TDE GYM 2 : Encrypted columns, encryption algorithm, salt and nosalt encryption and indexes
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.
TDE GYM 3 : Tablespace encryption
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.
PLATINUM_SYS >create tablespace TS_ENCRYPT encryption using '3DES168' default storage (ENCRYPT) extent management local uniform;
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
ENCRYPTION Encrypt part or all of the dump file where valid keyword
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
TDE GYM 5 : Encryption using RMAN
oracle@pcguy admin$ rman target /
Recovery Manager: Release 11.1.0.7.0 - Production on Mon Aug 24 21:18:18 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: PLATINUM (DBID=2439336951)
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name PLATINUM are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BZIP2'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/11.1.0/db_1/dbs/snapcf_PLATINUM.f'; # default
RMAN> CONFIGURE ENCRYPTION FOR DATABASE ON;
new RMAN configuration parameters:
CONFIGURE ENCRYPTION FOR DATABASE ON;
new RMAN configuration parameters are successfully stored
RMAN> CONFIGURE ENCRYPTION ALGORITHM 'AES256';
new RMAN configuration parameters:
CONFIGURE ENCRYPTION ALGORITHM 'AES256';
new RMAN configuration parameters are successfully stored
RMAN> CONFIGURE ENCRYPTION FOR TABLESPACE USERS OFF;
Tablespace USERS will not be encrypted in future backup sets
new RMAN configuration parameters are successfully stored
RMAN> show all;
RMAN configuration parameters for database with db_unique_name PLATINUM are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE ON;
CONFIGURE ENCRYPTION ALGORITHM 'AES256';
CONFIGURE COMPRESSION ALGORITHM 'BZIP2'; # default
CONFIGURE ENCRYPTION FOR TABLESPACE 'USERS' OFF;
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/11.1.0/db_1/dbs/snapcf_PLATINUM.f'; # default
RMAN> set encryption identified by "secretforyou";
executing command: SET encryption
RMAN> run {
2> allocate channel ch1 device type disk;
3> allocate channel ch2 device type disk;
4> backup as compressed backupset database;
5> backup current controlfile;
6> }