Friday, March 8, 2013

0 Oracle TDE on tablespace

Oralce 11g has new feature that allow to encrypt entire tablespace via TDE. The major advantage for use Tablespace TDE is that, in the TDE on the column level, if that TDE column happen to be the index, the index scan will not pick the index since it would not know its value . It affect the performance. In the tablespace TDE, the TDE is on the disk level, hence there is no such problem hence reduce the encryption performance impact.

For setting up the TDE, please refer here.

Setting up the test environment

alter system set encryption wallet open IDENTIFIED BY "XXXX";
 
CREATE TABLESPACE TEST_TDE
  DATAFILE '/u01/app/oracle/oradata/oradb0/TEST_TDE01.dbf' SIZE 256K REUSE 
  AUTOEXTEND ON NEXT 64K;
CREATE TABLESPACE TEST_TDE2
  DATAFILE '/u01/app/oracle/oradata/oradb0/TEST_TDE02.dbf' SIZE 256K REUSE
  AUTOEXTEND ON NEXT 64K encryption default storage (encrypt);
 
CREATE USER tde IDENTIFIED BY tde DEFAULT TABLESPACE TEST_TDE;
ALTER USER tde QUOTA UNLIMITED ON TEST_TDE;
ALTER USER tde QUOTA UNLIMITED ON TEST_TDE2;
GRANT CONNECT TO tde;
GRANT CREATE TABLE TO tde;

image

Create test table

When we encrypt entire tablespace, the column size no longer has charset length limitation for encryption. In my testing, the encryption column can have maxim 3932 char, otherwise, we would get -- ORA-28331: encrypted column size too long for its data type

CREATE TABLE test_employee (
     first_name VARCHAR2(10),
     nick_name VARCHAR2(4000) encrypt,
     salary NUMBER(6)
) TABLESPACE TEST_TDE;
 
-- ORA-28331: encrypted column size too long for its data type
 
CREATE TABLE test_employee (
     first_name VARCHAR2(10),
     nick_name VARCHAR2(3932) encrypt,
     salary NUMBER(6)
) TABLESPACE TEST_TDE;
 
CREATE TABLE test_employee2 (
     first_name VARCHAR2(10),
     nick_name VARCHAR2(4000),
     salary NUMBER(6)
) TABLESPACE TEST_TDE2;

 

image

Because it is TDE, so it make no difference from application perspective. the data all looks the same from query.

INSERT INTO test_employee  VALUES ('SUMMER', 'PANDA_SUMMER',1000);
select * from test_employee;
INSERT INTO test_employee2  VALUES ('SUMMER', 'PANDA_SUMMER',1000);
select * from test_employee;
image

The main difference is from non-TDE table, we can find non encryption data in the HEX mode as below screenshot.

image

For TDE tablespace, the entire data file is encrypted.

image

select tablespace_name,ENCRYPTED from dba_tablespaces

image

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates