28 | 03 | 2024
Latest Articles
Popular Articles

11G

Transparent Data Encryption TDE

User Rating:  / 0
PoorBest 

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> }