sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】Oracle TDE探秘列、表空间加密技术笔记

时间:2016-11-22 22:05   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle TDE的研究笔记,通过案例步骤详细介绍TDE对列、表空间加密技术的详解。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: oracle TDE学习系列 (2) — 探秘列、表空间加密

Oracle 透明数据加密(TDE)功能是10.2 引入的,至于TDE的用途,顾名思义就是防止非认证用户或其它心怀叵测的人进行对敏感数据的偷窥。

TDE 加密跟必须依赖于oracle wallet,关于wallet的管理,请参考:第一篇

oracle TDE学习系列(1) - wallet 使用管理

在10.2版本中,仅仅限于列的加密,从11gR1开始又引入了表空间级别的加密,下面是简单的配置和相关测试。
++++++ 首先配置 wallet,创建密钥 ++++++

==== 在sqlnet.ora中加入如下信息:====

NAMES.DIRECTORY_PATH=(TNSNAMES,EZCONNECT)
ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/home/ora11g/admin/roger/wallet )))
==== 创建wallet目录 ====

[ora11g@11gr2test admin]$ mkdir -p  /home/ora11g/admin/roger/wallet
==== 生成加密密钥(master KEY)====

SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "roger007~!@";

System altered.
==== 进行加密列测试 ====

++++ 创建测试表 ++++

[ora11g@11gr2test ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.2.0 Production ON Sun Oct 9 20:54:20 2011
Copyright (c) 1982, 2010, Oracle.  ALL rights reserved.

Connected TO:
Oracle DATABASE 11g Enterprise Edition Release 11.2.0.2.0 - Production
WITH the Partitioning, Automatic Storage Management, OLAP, DATA Mining
AND REAL Application Testing options

SQL> conn roger/roger
Connected.

SQL> CREATE TABLE ht1(name varchar2(10),salary NUMBER);

TABLE created.

SQL> INSERT INTO ht1 VALUES('lizx','20000');

1 ROW created.

SQL> INSERT INTO ht1 VALUES('yu','30000');

1 ROW created.

SQL> INSERT INTO ht1 VALUES('hu','50000');

1 ROW created.

SQL> commit;

Commit complete.

++++++ MODIFY COLUMN 进行列加密 ++++++

SQL> conn roger/roger
Connected.

SQL> DESC ht1

Name                                      NULL     TYPE
----------------------------------------- -------- ----------------------------
NAME                                               VARCHAR2(10)
SALARY                                             NUMBER

SQL> ALTER TABLE  ht1 MODIFY (SALARY NUMBER ENCRYPT NO SALT);

TABLE altered.

SQL> SELECT * FROM ht1;

NAME           SALARY
---------- ----------
lizx            20000
yu              30000
hu              50000

SQL> ALTER system SET encryption wallet close IDENTIFIED BY "roger007~!@";

System altered.

SQL> SELECT * FROM roger.ht1;
SELECT * FROM roger.ht1
                    *
ERROR at line 1:
ORA-28365: wallet IS NOT OPEN

SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "roger007~!@";

System altered.

SQL> SELECT * FROM roger.ht1;

NAME           SALARY
---------- ----------
lizx            20000
yu              30000
hu              50000

++++++ 直接创建加密列的新表 ++++++

SQL> CREATE TABLE ht3 (id NUMBER ENCRYPT,name varchar2(10));               

TABLE created.                                                             

SQL> INSERT INTO ht3 VALUES(10,'lizx');                                    

1 ROW created.                                                             

SQL> INSERT INTO ht3 VALUES(100,'google');                                 

1 ROW created.                                                             

SQL> commit;                                                               

Commit complete.                                                           

SQL> ALTER system SET encryption wallet close IDENTIFIED BY "roger007~!@"; 

System altered.                                                            

SQL> SELECT * FROM ht3;                                                    
SELECT * FROM ht3                                                          
              *                                                            
ERROR at line 1:                                                           
ORA-28365: wallet IS NOT OPEN                                              

SQL> ALTER system SET encryption wallet OPEN IDENTIFIED BY "roger007~!@";  

System altered.                                                            

SQL> SELECT * FROM ht3;                                                    

        ID NAME                                                            
---------- ----------                                                      
        10 lizx                                                            
       100 google

表空间级别的加密

对于表空间加密,是11gR1 就引入,11gR2中支持的更为广泛,这里需要说明一下的是
表空间的加密只能是对于新创建的表空间而言,对于已经存在的表空间是无法进行的。

对于已经存在的表空间,如果要将其中的表迁移到加密表空间中,我们可以采取如下几种方式:

--> create table tab_name as select ... (CATS)
--> alter table move / alter index rebuild tablespace tbs;
--> exp/imp(expdp/impdp)
--> 在线重定义

我这里就挑最为简单的move操作进行测试:
SQL> conn /AS sysdba
Connected.

SQL> CREATE tablespace tbs_ht
  2  datafile '+DATA1/roger/ht01.dbf'
  3  SIZE 50m
  4  encryption
  5  DEFAULT storage(encrypt);

Tablespace created.

SQL> conn roger/roger
Connected.

SQL> DESC ht2 

Name               NULL     TYPE
------------------ -------- --------------------------------------------
NAME                        VARCHAR2(10)
SALARY                      NUMBER

SQL> ALTER TABLE ht2 move tablespace tbs_ht;

TABLE altered.

SQL> DESC ht2

Name               NULL     TYPE
------------------ -------- --------------------------------------------
NAME                        VARCHAR2(10)
SALARY                      NUMBER

SQL> SELECT * FROM DBA_ENCRYPTED_COLUMNS;                                                                             

OWNER     TABLE_NAME    COLUMN_NAME   ENCRYPTION_ALG                SAL INTEGRITY_AL
--------- ------------- ------------- ----------------------------- --- ------------
ROGER     HT1           SALARY        AES 192 bits KEY              NO  SHA-1      
ROGER     HT3           ID            AES 192 bits KEY              YES SHA-1
我们可以发现,对于普通表,如果直接move到加密表空间的话,那么其加密列都是允许salt的,
如果需要对加密列进行创建index,那么我们还得手工进行修改salt属性,否则会遇到如下错误。
SQL> CREATE INDEX id_ht3 ON ht3(id) tablespace tbs_ht;  
CREATE INDEX id_ht3 ON ht3(id) tablespace tbs_ht                  
                           *                                      
ERROR at line 1:                                                  
ORA-28338: COLUMN(s) cannot be BOTH indexed AND encrypted WITH salt

====== 因为对于salt列是不允许创建INDEX的 ======
另外,我们这里还需要注意一点的是,对于普通表move到加密表空间以后,
其信息在dba_encrypted_columns中是查不到的。
SQL> SELECT * FROM DBA_ENCRYPTED_COLUMNS;

OWNER      TABLE_NAME   COLUMN_NAME    ENCRYPTION_ALG                SAL INTEGRITY_AL
---------- ------------ -------------- ----------------------------- --- ------------
ROGER      HT1          SALARY         AES 192 bits KEY              NO  SHA-1      
ROGER      HT3          ID             AES 192 bits KEY              YES SHA-1      

SQL>  ALTER system SET encryption wallet close IDENTIFIED BY "roger007~!@";                                           

System altered.                                                                                                       

SQL>  SELECT * FROM HT3;                                                                                              
SELECT * FROM HT3                                                                                                    
               *                                                                                                      
ERROR at line 1:                                                                                                      
ORA-28365: wallet IS NOT OPEN                                                                                         

SQL> SELECT * FROM HT2;                                                                                               
SELECT * FROM HT2                                                                                                     
              *                                                                                                       
ERROR at line 1:                                                                                                      
ORA-28365: wallet IS NOT OPEN   

SQL> SELECT owner,TABLE_NAME,tablespace_name FROM dba_tables WHERE owner='ROGER';

OWNER                TABLE_NAME           TABLESPACE_NAME                       
-------------------- -------------------- ------------------------------        
ROGER                HT2                  TBS_HT                                
ROGER                HT3                  ROGER                                 
ROGER                HT1                  ROGER      

SQL> SELECT * FROM V$ENCRYPTED_TABLESPACES; 

       TS# ENCRYPT ENC ENCRYTPEDKEY                                                      MASTERKEYID                      BLOCKS_ENCRYPTED BLOCKS_DECRYPTED 
---------- ------- --- ----------------------------------------------------------------- -------------------------------  ---------------- ---------------- -
         7 AES128  YES 3330DB1B5FD56074D67AF7CC9061690D00000000000000000000000000000000 2F209A3E6D4F4F54BF35998B84DFA174  2                1

我们可以发现,对于表空间加密,其默认的算法跟是AES128,跟列加密是不同的。

对于表空间加密,我认为这点就非常好,非常的方便,最近要实施的一个项目就准备实施表空间加密。

通常的做法,我们是将普通表move到加密表空间中,当然需要注意一点的是,对于索引列,在rebuild index完成以后,我们需要手工去修改下索引列的salt属性,如下过程:

SQL> CREATE TABLE  ht4(id NUMBER,sex varchar2(6));

TABLE created.

SQL> INSERT INTO ht4 VALUES(1,'nan');                                                                                  

1 ROW created.                                                                                                         

SQL> INSERT INTO ht4 VALUES(2,'nv');                                                                                   

1 ROW created.                                                                                                         

SQL> commit;                                                                                                           

Commit complete.                                                                                                       

SQL> SELECT owner,TABLE_NAME,tablespace_name
  2  FROM dba_tables
  3  WHERE TABLE_NAME='HT4';                                                                                           

OWNER                TABLE_NAME           TABLESPACE_NAME                                                              
-------------------- -------------------- ------------------------------                                               
ROGER                HT4                  ROGER                                                                        

SQL> DESC ht4      

Name         NULL     TYPE                                       
------------ -------- --------------------------------------------
ID                    NUMBER                                     
SEX                   VARCHAR2(6)                                

SQL> CREATE INDEX idx_id_ht4 ON ht4(id);                                                                               

INDEX created.                                                                                                         

SQL> ALTER TABLE ht4 move tablespace  tbs_ht;                                                                          
ALTER TABLE ht4 move tablespace  tbs_ht                                                                                
            *                                                                                                          
ERROR at line 1:                                                                                                       
ORA-28365: wallet IS NOT OPEN   

SQL> ALTER system SET encryption wallet OPEN IDENTIFIED BY "roger007~!@";

System altered.

SQL> ALTER TABLE ht4 move tablespace  tbs_ht;

TABLE altered.

SQL> ALTER INDEX IDX_ID_HT4 rebuild tablespace  tbs_ht;

INDEX altered.

SQL> SELECT owner,TABLE_NAME,index_name,tablespace_name
  2  FROM dba_indexes
  3  WHERE TABLE_NAME='HT4';

OWNER                TABLE_NAME           INDEX_NAME                     TABLESPACE_NAME
-------------------- -------------------- ------------------------------ ------------------------------
ROGER                HT4                  IDX_ID_HT4                     TBS_HT

SQL> ALTER TABLE ht4 MODIFY (id encrypt no salt);

TABLE altered.

最后总结下表空间加密的限制,如下:

==> 加密表空间密钥不能重建
==> 不能使用no salt选项
==> bfile和外部表不能被加密
==> temp和undo表空间不能被加密
探秘oracle TDE 加密机制


下面我们来研究下 TDE 到底是如何实现的?是直接对数据block加密吗?
SQL> SELECT dump(SALARY),name FROM ht1;

DUMP(SALARY)                             NAME
---------------------------------------- ----------
Typ=2 Len=2: 195,3                       lizx
Typ=2 Len=2: 195,4                       yu
Typ=2 Len=2: 195,6                       hu

SQL> CREATE TABLE ht2 AS SELECT * FROM ht1;

TABLE created.

SQL> SELECT name,dump(SALARY) FROM ht2;

NAME       DUMP(SALARY)
---------- ----------------------------------------
lizx       Typ=2 Len=2: 195,3
yu         Typ=2 Len=2: 195,4
hu         Typ=2 Len=2: 195,6

====== 直接查询是完全一样的 ======

++++++ 查询block分布 ++++++

SQL> SELECT dbms_rowid.rowid_relative_fno(rowid) file#,
  2         dbms_rowid.rowid_block_number(rowid) blk#           
  3  FROM ht1;

     FILE#       BLK#
---------- ----------
         5        132
         5        132
         5        132

SQL> SELECT dbms_rowid.rowid_relative_fno(rowid) file#,
  2         dbms_rowid.rowid_block_number(rowid) blk# 
  3  FROM ht2;

     FILE#       BLK#
---------- ----------
         5        139
         5        139
         5        139

SQL> ALTER system dump datafile 5 block 132;

System altered.

SQL> ALTER system dump datafile 5 block 139;

System altered.
****** block dump ******

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0009.01a.000002b0  0x00c014c4.008f.2e  --U-    3  fsc 0x0000.000cbcfd
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x01400084
data_block_dump,data header at 0x14aa264
===============
tsiz: 0x1f98  ---->8088
hsiz: 0x18    ---->24
pbl: 0x014aa264
     76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x1f7b
avsp=0x1f63   ---->8035   #### block 中的可用空间大小 ####
tosp=0x1f63   ---->8035   #### 所有事务都commit以后, 该block中的可用空间大小 ####
0xe:pti[0]      nrow=3  offs=0
0x12:pri[0]     offs=0x1f8d
0x14:pri[1]     offs=0x1f84
0x16:pri[2]     offs=0x1f7b
block_row_dump:
tab 0, row 0, @0x1f8d
tl: 11 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 4]  6c 69 7a 78
col  1: [ 2]  c3 03
tab 0, row 1, @0x1f84
tl: 9 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  79 75
col  1: [ 2]  c3 04
tab 0, row 2, @0x1f7b
tl: 9 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  68 75
col  1: [ 2]  c3 06
end_of_block_dump
End dump data blocks tsn: 6 file#: 5 minblk 132 maxblk 132


Block header dump:  0x0140008b
Object id on Block  Y
seg/obj: 0x122d1  csc: 0x00.d62fd  itc: 3  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1400088 ver: 0x01 opc: 0
     inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.000d62fd
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x0140008b
data_block_dump,data header at 0x14aa27c
===============
tsiz: 0x1f80
hsiz: 0x18
pbl: 0x014aa27c
     76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x1f63
avsp=0x1f4b
tosp=0x1f4b
0xe:pti[0]      nrow=3  offs=0
0x12:pri[0]     offs=0x1f75
0x14:pri[1]     offs=0x1f6c
0x16:pri[2]     offs=0x1f63
block_row_dump:
tab 0, row 0, @0x1f75
tl: 11 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 4]  6c 69 7a 78
col  1: [ 2]  c3 03
tab 0, row 1, @0x1f6c
tl: 9 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  79 75
col  1: [ 2]  c3 04
tab 0, row 2, @0x1f63
tl: 9 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  68 75
col  1: [ 2]  c3 06
end_of_block_dump
End dump data blocks tsn: 6 file#: 5 minblk 139 maxblk 139
我们可以发现,block dump是有一些差异的,分析block ,我喜欢用bbed,如下:

首先, 把datafile从asm中复制到文件系统,通过rman进行
SQL> SELECT file_id,file_name,bytes FROM dba_data_files ORDER BY 1;

   FILE_ID FILE_NAME                                               BYTES
---------- -------------------------------------------------- ----------
         1 +DATA1/roger/system01.dbf                           723517440
         2 +DATA1/roger/sysaux01.dbf                           534773760
         3 +DATA1/roger/undotbs01.dbf                           47185920
         4 +DATA1/roger/users01.dbf                              5242880
         5 +DATA1/roger/roger01.dbf                            209715200
[ora11g@11gr2test ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Wed Oct 12 00:33:36 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ROGER (DBID=2468313792)

RMAN> copy datafile 5 to '/home/ora11g/cheshi_bbed/roger01.dbf';

Starting backup at 12-OCT-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=43 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATA1/roger/roger01.dbf
output file name=/home/ora11g/cheshi_bbed/roger01.dbf tag=TAG20111012T003345 RECID=1 STAMP=764296437
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:16
Finished backup at 12-OCT-11
++++++ 配置bbed,如下:++++++

[ora11g@11gr2test cheshi_bbed]$ ls -ltr

total 205044
-rw-r--r-- 1 ora11g oinstall        54 Oct 12 00:34 par.txt
-rw-r--r-- 1 ora11g oinstall        76 Oct 12 00:35 a.txt
-rw-r----- 1 ora11g oinstall 209723392 Oct 12 00:40 roger01.dbf
-rw-r--r-- 1 ora11g oinstall      5824 Oct 12 00:40 log.bbd

[ora11g@11gr2test cheshi_bbed]$ cat a.txt

5 /home/ora11g/cheshi_bbed/roger01.dbf                            209715200

SQL> select 209723392 - 209715200 from dual;

209723392-209715200
-------------------
               8192

++++++ 多余的一个block是 datafile header ++++++
下面用bbed分别来比较下这几个block的差异:

1. 加密列的block
BBED> set file 5 block 132

        FILE#           5
        BLOCK#          132

BBED> map /v

File: /home/ora11g/cheshi_bbed/roger01.dbf (5)
Block: 132                                   Dba:0x01400084
------------------------------------------------------------
KTB Data Block (Table/Cluster)

struct kcbh, 20 bytes                      @0      
    ub1 type_kcbh                           @0      
    ub1 frmt_kcbh                           @1      
    ub1 spare1_kcbh                         @2      
    ub1 spare2_kcbh                         @3      
    ub4 rdba_kcbh                           @4      
    ub4 bas_kcbh                            @8      
    ub2 wrp_kcbh                            @12     
    ub1 seq_kcbh                            @14     
    ub1 flg_kcbh                            @15     
    ub2 chkval_kcbh                         @16     
    ub2 spare3_kcbh                         @18     

struct ktbbh, 72 bytes                     @20  #### ktbbh占据72 byte ####
    ub1 ktbbhtyp                            @20     
    union ktbbhsid, 4 bytes                 @24     
    struct ktbbhcsc, 8 bytes                @28     
    sb2 ktbbhict                            @36     
    ub1 ktbbhflg                            @38     
    ub1 ktbbhfsl                            @39     
    ub4 ktbbhfnx                            @40     
    struct ktbbhitl[2], 48 bytes            @44     

struct kdbh, 14 bytes                      @100  
    ub1 kdbhflag                            @100    
    sb1 kdbhntab                            @101    
    sb2 kdbhnrow                            @102    
    sb2 kdbhfrre                            @104    
    sb2 kdbhfsbo                            @106    
    sb2 kdbhfseo                            @108    
    sb2 kdbhavsp                            @110    
    sb2 kdbhtosp                            @112    

struct kdbt[1], 4 bytes                    @114    
    sb2 kdbtoffs                            @114    
    sb2 kdbtnrow                            @116    

sb2 kdbr[3]                                @118    

ub1 freespace[7904]                        @124    

ub1 rowdata[160]                           @8028   

ub4 tailchk                                @8188   

BBED> p ktbbh

struct ktbbh, 72 bytes                      @20     
   ub1 ktbbhtyp                             @20       0x01 (KDDBTDATA)
   union ktbbhsid, 4 bytes                  @24     
      ub4 ktbbhsg1                          @24       0x000122c5
      ub4 ktbbhod1                          @24       0x000122c5
   struct ktbbhcsc, 8 bytes                 @28     
      ub4 kscnbas                           @28       0x000d62cc
      ub2 kscnwrp                           @32       0x0000
   sb2 ktbbhict                             @36       2
   ub1 ktbbhflg                             @38       0x32 (NONE)
   ub1 ktbbhfsl                             @39       0x00
   ub4 ktbbhfnx                             @40       0x01400080
   struct ktbbhitl[0], 24 bytes             @44     
      struct ktbitxid, 8 bytes              @44     
         ub2 kxidusn                        @44       0x0009
         ub2 kxidslt                        @46       0x001a
         ub4 kxidsqn                        @48       0x000002b0
      struct ktbituba, 8 bytes              @52     
         ub4 kubadba                        @52       0x00c014c4
         ub2 kubaseq                        @56       0x008f
         ub1 kubarec                        @58      Oracleoracleplus.net 0x2e
      ub2 ktbitflg                          @60       0x8000 (KTBFCOM)
      union _ktbitun, 2 bytes               @62     
         sb2 _ktbitfsc                      @62       0
         ub2 _ktbitwrp                      @62       0x0000
      ub4 ktbitbas                          @64       0x000cbcfd
   struct ktbbhitl[1], 24 bytes             @68     
      struct ktbitxid, 8 bytes              @68     
         ub2 kxidusn                        @68       0x0009
         ub2 kxidslt                        @70       0x0013
         ub4 kxidsqn                        @72       0x000002cb
      struct ktbituba, 8 bytes              @76     
         ub4 kubadba                        @76       0x00c014a2
         ub2 kubaseq                        @80       0x00a2
         ub1 kubarec                        @82       0x07
      ub2 ktbitflg                          @84       0x2003 (KTBFUPB)
      union _ktbitun, 2 bytes               @86     
         sb2 _ktbitfsc                      @86       0
         ub2 _ktbitwrp                      @86       0x0000
      ub4 ktbitbas                          @88       0x000d62d1
-- 2个itl

BBED> p kdbr

sb2 kdbr[0]                                 @118      8014
sb2 kdbr[1]                                 @120      7971
sb2 kdbr[2]                                 @122      7928

BBED> p *kdbr[0]

rowdata[86]
-----------
ub1 rowdata[86]                             @8114     0x2c

BBED> x /1rnnnnnnnn

rowdata[86]                                 @8114   
-----------
flag@8114: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8115: 0x02
cols@8116:    2

col    0[4] @8117: #########################################
col   1[36] @8122:  0xc1  0xfa  0xcf  0x10  0xcb  0x61  0x10  0xc4  0x26  0x00
0xec  0x18  0x6c  0x65  0x69  0x3b  0xf2  0xff  0x4f  0x60  0x1e  0xaf  0x16
0xcd  0xb3  0x96  0xcc  0x2c  0xf4  0xbe  0x1a  0x81  0xfc  0xb0  0xce  0x96

我们可用看到,对于加密block,该列数据显示的是这么多的十六进制,从上看应该是AES192算法。
上面的这一长串的16进制看不懂是如何弄出来的,总之,我们知道该值是oracle根据AES192算法,
集合我们的密钥进行计算出来的值。

#################################################################################
2. 普通的block
BBED> set file 5 block 139

        FILE#           5
        BLOCK#          139

BBED> map /v

File: /home/ora11g/cheshi_bbed/roger01.dbf (5)
Block: 139                                   Dba:0x0140008b
------------------------------------------------------------
KTB Data Block (Table/Cluster)

struct kcbh, 20 bytes                      @0      
    ub1 type_kcbh                           @0      
    ub1 frmt_kcbh                           @1      
    ub1 spare1_kcbh                         @2      
    ub1 spare2_kcbh                         @3      
    ub4 rdba_kcbh                           @4      
    ub4 bas_kcbh                            @8      
    ub2 wrp_kcbh                            @12     
    ub1 seq_kcbh                            @14     
    ub1 flg_kcbh                            @15     
    ub2 chkval_kcbh                         @16     
    ub2 spare3_kcbh                         @18     

struct ktbbh, 96 bytes                     @20  #### ktbbh占据96 byte ####
    ub1 ktbbhtyp                            @20     
    union ktbbhsid, 4 bytes                 @24     
    struct ktbbhcsc, 8 bytes                @28     
    sb2 ktbbhict                            @36     
    ub1 ktbbhflg                            @38     
    ub1 ktbbhfsl                            @39     
    ub4 ktbbhfnx                            @40     
    struct ktbbhitl[3], 72 bytes            @44     

struct kdbh, 14 bytes                      @124    
    ub1 kdbhflag                            @124    
    sb1 kdbhntab                            @125    
    sb2 kdbhnrow                            @126    
    sb2 kdbhfrre                            @128    
    sb2 kdbhfsbo                            @130    
    sb2 kdbhfseo                            @132    
    sb2 kdbhavsp                            @134    
    sb2 kdbhtosp                            @136    

struct kdbt[1], 4 bytes                    @138    
    sb2 kdbtoffs                            @138    
    sb2 kdbtnrow                            @140    

sb2 kdbr[3]                                @142    

ub1 freespace[8011]                        @148    

ub1 rowdata[29]                            @8159   

ub4 tailchk                                @8188   

BBED> p ktbbh

struct ktbbh, 96 bytes                      @20     
   ub1 ktbbhtyp                             @20       0x01 (KDDBTDATA)
   union ktbbhsid, 4 bytes                  @24     
      ub4 ktbbhsg1                          @24       0x000122d1
      ub4 ktbbhod1                          @24       0x000122d1
   struct ktbbhcsc, 8 bytes                 @28     
      ub4 kscnbas                           @28       0x000d62fd
      ub2 kscnwrp                           @32       0x0000
   sb2 ktbbhict                             @36       3
   ub1 ktbbhflg                             @38       0x32 (NONE)
   ub1 ktbbhfsl                             @39       0x00
   ub4 ktbbhfnx                             @40       0x01400088
   struct ktbbhitl[0], 24 bytes             @44     
      struct ktbitxid, 8 bytes              @44     
         ub2 kxidusn                        @44       0xffff
         ub2 kxidslt                        @46       0x0000
         ub4 kxidsqn                        @48       0x00000000
      struct ktbituba, 8 bytes              @52     
         ub4 kubadba                        @52       0x00000000
         ub2 kubaseq                        @56       0x0000
         ub1 kubarec                        @58       0x00
      ub2 ktbitflg                          @60       0x8000 (KTBFCOM)
      union _ktbitun, 2 bytes               @62     
         sb2 _ktbitfsc                      @62       0
         ub2 _ktbitwrp                      @62       0x0000
      ub4 ktbitbas                          @64       0x000d62fd
   struct ktbbhitl[1], 24 bytes             @68     
      struct ktbitxid, 8 bytes              @68     
         ub2 kxidusn                        @68       0x0000
         ub2 kxidslt                        @70       0x0000
         ub4 kxidsqn                        @72       0x00000000
      struct ktbituba, 8 bytes              @76     
         ub4 kubadba                        @76       0x00000000
         ub2 kubaseq                        @80       0x0000
         ub1 kubarec                        @82       0x00
      ub2 ktbitflg                          @84       0x0000 (NONE)
      union _ktbitun, 2 bytes               @86     
         sb2 _ktbitfsc                      @86       0
         ub2 _ktbitwrp                      @86       0x0000
      ub4 ktbitbas                          @88       0x00000000
   struct ktbbhitl[2], 24 bytes             @92     
      struct ktbitxid, 8 bytes              @92     
         ub2 kxidusn                        @92       0x0000
         ub2 kxidslt                        @94       0x0000
         ub4 kxidsqn                        @96       0x00000000
      struct ktbituba, 8 bytes              @100    
         ub4 kubadba                        @100      0x00000000
         ub2 kubaseq                        @104      0x0000
         ub1 kubarec                        @106      0x00
      ub2 ktbitflg                          @108      0x0000 (NONE)
      union _ktbitun, 2 bytes               @110    
         sb2 _ktbitfsc                      @110      0
         ub2 _ktbitwrp                      @110      0x0000
      ub4 ktbitbas                          @112      0x00000000

-- 3个itl(每个itl 占据24个byte,所以加密block相对正常block这里就少了24个byte)

BBED> p kdbr

sb2 kdbr[0]                                 @142      8053
sb2 kdbr[1]                                 @144      8044
sb2 kdbr[2]                                 @146      8035

BBED> p *kdbr[0] 

rowdata[18]
-----------
ub1 rowdata[18]                             @8177     0x2c

BBED> x /1rnnnnnnnnnnn

rowdata[18]                                 @8177   
-----------
flag@8177: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8178: 0x00
cols@8179:    2

col    0[4] @8180: #########################################
col    1[2] @8185: 20000  --正常block这里显示的是明文20000的数值。
从上面的分析可用看出,TDE其实是加密的数据block,换句话说,如果我不给你密钥,
那么即使数据文件给你,你也看不到具体的数据。

最后,我们需要来补充下oracle引入的几个相关的试图:
++++++ 查询walet的状态以及路径 ++++++

SQL> SELECT * FROM V$ENCRYPTION_WALLET;

WRL_TYPE             WRL_PARAMETER                            STATUS
-------------------- ---------------------------------------- ------------------
file                 /home/ora11g/admin/roger/wallet          OPEN

++++++ 查看加密的表列 ++++++

SQL> SELECT * FROM DBA_ENCRYPTED_COLUMNS;

OWNER      TABLE_NAME           COLUMN_NAME    ENCRYPTION_ALG      SAL INTEGRITY_AL
---------- -------------------- -------------- ------------------- --- ------------
ROGER      HT1                  SALARY         AES 192 bits KEY    NO  SHA-1
从上面我们可用看到,加密的列为 SALARY,其中使用的加密算法是AES192,
这也印证了前面我们bbed的分析是的对的。

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle TDE探秘列、表空间加密技术笔记

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1311.html

Oracle研究中心

关键词:

Oracle TDE

Oracle TDE对列和表空间加密的方法