transparent data encryption

TDE是用来加密保护数据库中的数据文件，为了防止不授权的解密，TDE将加密的key保存在
数据外。应用只需要很少的改动或者不需改动就可以使用TDE来处理敏感的数据。Database
的用户和应用不需要知道数据在存储时被加密了，因为对用户来说数据解密是透明的。
加密和解密都是由数据库完成的，应用不需要做任何改变。

TDE可以是column加密或者tablespace加密。

TDE的使用：

1. 定义路径
%ORACLE_HOME%/network/admin/sqlnet.ora:

ENCRYPTION_WALLET_LOCATION=
          (SOURCE=(METHOD=FILE)(METHOD_DATA=
                  (DIRECTORY=/u01/WALLET)))  
                 
2. 创建master encryption key
ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "welcome1";

drop table employee purge;
CREATE TABLE employee (
     first_name VARCHAR2(128),
     last_name VARCHAR2(128),
     empID NUMBER,
     salary NUMBER(6) ENCRYPT
);

insert into employee values ('AA', 'BB', 11, 103);
commit;

SQL> select file_id, block_id, blocks from dba_extents where segment_name = 'EMPLOYEE';
 
   FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
         4    2816649          8
 
SQL>

buffer tsn: 4 rdba: 0x012afa8d (4/2816653)
scn: 0x0418.13fd19b7 seq: 0x03 flg: 0x06 tail: 0x19b70603
frmt: 0x02 chkval: 0xa6af type: 0x06=trans data
Block header dump:  0x012afa8d
 Object id on Block? Y
 seg/obj: 0x2214f5  csc: 0x418.13fd1998  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x12afa89 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0037.01b.00010336  0x46800960.32ec.05  --U-    1  fsc 0x0000.13fd19b7
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

data_block_dump,data header at 0xde00264
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x0de00264
bdba: 0x012afa8d
     76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f57
avsp=0x1f43
tosp=0x1f43
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f57
block_row_dump:
tab 0, row 0, @0x1f57
tl: 65 fb: --H-FL-- lb: 0x1  cc: 4
col  0: [ 2]  41 41                  ---'AA'
col  1: [ 2]  42 42                  ---'BB'
col  2: [ 2]  c1 0c                  ---11
col  3: [52]
 55 43 c5 0a 08 8e a1 bf a5 23 10 75 ac 63 a9 d8 79 b0 49 44 53 88 30 13 95
 57 b2 19 ce 98 37 06 8a 98 6c 91 0e 63 7c ba 24 66 3a b2 77 68 36 af d2 b8
 00 f5                              ---100
end_of_block_dump

从dump文件中看到数据文件存储的是加密的数据。占用的字节数是52。

3. 导出加密数据
expdp test/test TABLES=employee DIRECTORY=dir DUMPFILE=employee.dmp ENCRYPTION=ENCRYPTED_COLUMNS_ONLY ENCRYPTION_PASSWORD=test

4. 导入加密数据
impdp test/test DIRECTORY=dir DUMPFILE=employee.dmp ENCRYPTION_PASSWORD=test remap_schema=test:test1

5. 查询哪些数据加密了
select * from DBA_ENCRYPTED_COLUMNS;
SQL> select * from DBA_ENCRYPTED_COLUMNS;
 
OWNER  TABLE_NAME  COLUMN_NAME   ENCRYPTION_ALG     SALT INTEGRITY_ALG
------ ----------- ------------- ------------------ ---- -------------
TEST   EMPLOYEE    SALARY        AES 192 bits key   YES  SHA-1
TEST1  EMPLOYEE    SALARY        AES 192 bits key   YES  SHA-1

select member as LOG_FILE_LOCATION from v$logfile;
 
EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/u01/oradata/dbs101/REDO01.LOG',DBMS_LOGMNR.NEW);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/u01/oradata/dbs101/REDO02.LOG', DBMS_LOGMNR.ADDFILE);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/u01/oradata/dbs101/REDO03.LOG', DBMS_LOGMNR.ADDFILE);

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 = 'EMPLOYEE'
                   and peration='INSERT';

insert into employee values ('AA', 'BB', 11, 101);
commit;

begin
DBMS_LOGMNR.ADD_LOGFILE('/u01/oradata/dbs101/REDO01.LOG',DBMS_LOGMNR.NEW);
end;
/

begin
DBMS_LOGMNR.ADD_LOGFILE('/u01/oradata/dbs101/REDO02.LOG', DBMS_LOGMNR.ADDFILE);
end;
/

begin
DBMS_LOGMNR.ADD_LOGFILE('/u01/oradata/dbs101/REDO03.LOG', DBMS_LOGMNR.ADDFILE);
end;
/

begin
DBMS_LOGMNR.START_LOGMNR (options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY);
end;
/

select sql_redo from v$logmnr_contents    
                   where table_name = 'EMPLOYEE'
                   and peration='INSERT';

SQL> select file_id, block_id, blocks from dba_extents where segment_name = 'EMPLOYEE';
 
   FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
         4     261752          8

alter system dump logfile '/u01/oradata/dbs101/REDO02.LOG' dba min 4 261752 dba max 4 261760;

ALTER SYSTEM SET wallet close IDENTIFIED BY "welcome1";

alter system dump datafile 4 block min 261752 block max 261760;

redo log file:

col  0: [ 2]  41 41
col  1: [ 2]  42 42
col  2: [ 2]  c1 0c
col  3: [52]
 55 43 c5 0a 08 8e a1 bf a5 23 10 75 ac 63 a9 d8 79 b0 49 44 53 88 30 13 95
 57 b2 19 ce 98 37 06 8a 98 6c 91 0e 63 7c ba 24 66 3a b2 77 68 36 af d2 b8
 00 f5

5. 在加密字段上创建index，必须用no salt声明：
drop table employee purge;
CREATE TABLE employee (
     first_name VARCHAR2(128),
     last_name VARCHAR2(128),
     empID NUMBER,
     salary NUMBER(6) ENCRYPT NO SALT
);

create index employee_ix on employee(salary);

insert into employee values ('AA', 'BB', 11, 103);
commit;

查看数据文件中的数据存储，index中加密的数据和data block中的是一样的：
select file_id, block_id from dba_extents where segment_name ='EMPLOYEE_IX';

SQL> select file_id, block_id,blocks from dba_extents where segment_name ='EMPLOYEE_IX';
 
   FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
        88       6240          8
 
ALTER SYSTEM DUMP DATAFILE 88 BLOCK MIN 6240 BLOCK MAX 6248;

Block header dump:  0x16001863
 Object id on Block? Y
 seg/obj: 0x8fa95  csc: 0x418.14a7625e  itc: 2  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x16001860 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0x0004.001.0002b503  0x00c02b4e.676e.05  --U-    1  fsc 0x0000.14a76269
Leaf block dump
===============
header address 365829732=0x15ce1e64
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: pcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 1
kdxcofbo 38=0x26
kdxcofeo 7990=0x1f36
kdxcoavs 7952
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[7990] flag: ------, lock: 2, len=46
col 0; len 36; (36):
 fd 10 0a cd 92 db dd 2a 08 73 9e 72 57 cd 0c 6e 10 8d 12 97 47 d4 95 a9 40
 45 8f 28 22 a8 b5 5d b7 7a c1 dd
col 1; len 6; (6):  16 00 18 5b 00 00

SQL> select file_id, block_id,blocks from dba_extents where segment_name ='EMPLOYEE';
 
   FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
        88       6232          8
 
SQL>

ALTER SYSTEM DUMP DATAFILE 88 BLOCK MIN 6232 BLOCK MAX 6240;

Block header dump:  0x1600185b
 Object id on Block? Y
 seg/obj: 0x8fa94  csc: 0x418.14a76267  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x16001858 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0004.001.0002b503  0x00c02b4e.676e.04  --U-    1  fsc 0x0000.14a76269
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x1600185b
data_block_dump,data header at 0x15ce1e64
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x15ce1e64
     76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f67
avsp=0x1f53
tosp=0x1f53
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f67
block_row_dump:
tab 0, row 0, @0x1f67
tl: 49 fb: --H-FL-- lb: 0x1  cc: 4
col  0: [ 2]  41 41
col  1: [ 2]  42 42
col  2: [ 2]  c1 0c
col  3: [36]
 fd 10 0a cd 92 db dd 2a 08 73 9e 72 57 cd 0c 6e 10 8d 12 97 47 d4 95 a9 40
 45 8f 28 22 a8 b5 5d b7 7a c1 dd
end_of_block_dump

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25105315/viewspace-704381/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25105315/viewspace-704381/