月归档：一月 2016

从ORACLE 10.2开始提供了一个新的特性，让你只需要做如下动作：你可以不写一行代码，只需要声明你需要加密某列。当用户插入数据的时候，数据库透明的加密数据然后存储加密后的数据。同样的，当用户读取数据时，数据库自动进行节目。由于加解密操作对应用程序来说都是透明的，不需要应用程序修改代码，因此这个特性就叫做：透明数据加密（TDE）。
TDE实施
sqlnet.ora中增加

ENCRYPTION_WALLET_LOCATION=
   (SOURCE=(METHOD=FILE)(METHOD_DATA=
      (DIRECTORY=/home/u01/oracle/network/wallets)))   

重启监听

lsnrctl stop
lsnrctl start

配置钱包

[oracle@localhost wallets]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 PrXIFENFEIction on Tue Jan 5 14:43:18 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit PrXIFENFEIction
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "welcome1";

System altered.


SQL> select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/home/u01/oracle/network/wallets
OPEN

[oracle@localhost wallets]$ ls -ltr
total 8
-rw-r--r-- 1 oracle oinstall 2845 Jan  5 14:43 ewallet.p12

TDE加密测试

SQL> conn XIFENFEI/oracle
Connected.
SQL>  create table CUST_PAYMENT_INFO 
  2                     (first_name varchar2(11), 
  3                      last_name varchar2(10), 
                    order_number number(13), 
                    CREDIT_CARD_NUMBER varchar2(20) ENCRYPT NO SALT);  4    5  

Table created.

SQL>  insert into cust_payment_info values ('Jon', 'Oldfield', 10001, '5446-9597-0881-2985');

1 row created.

SQL> insert into cust_payment_info values ('Chris', 'White', 10002, '5122-3580-4608-2560');

1 row created.

SQL>  insert into cust_payment_info values ('Alan', 'Squire', 10003, '5595-9689-4375-7920'); 

1 row created.

SQL> commit;

Commit complete.

SQL> select * from USER_ENCRYPTED_COLUMNS;

TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------
ENCRYPTION_ALG                SAL INTEGRITY_AL
----------------------------- --- ------------
CUST_PAYMENT_INFO              CREDIT_CARD_NUMBER
AES 192 bits key              NO  SHA-1


SQL> conn / as sysdba
Connected.
SQL> select * from XIFENFEI.cust_payment_info;

FIRST_NAME  LAST_NAME  ORDER_NUMBER CREDIT_CARD_NUMBER
----------- ---------- ------------ --------------------
Jon         Oldfield          10001 5446-9597-0881-2985
Chris       White             10002 5122-3580-4608-2560
Alan        Squire            10003 5595-9689-4375-7920

SQL> ALTER SYSTEM SET WALLET close IDENTIFIED BY  "welcome1";

System altered.

SQL> select * from XIFENFEI.cust_payment_info;
select * from XIFENFEI.cust_payment_info
                  *
ERROR at line 1:
ORA-28365: wallet is not open

验证TDE加密数据

--创建测试数据
SQL> create table XIFENFEI.CUST_PAYMENT_INFO2 
  2                     (first_name varchar2(11), 
  3                      last_name varchar2(10), 
  4                      order_number number(13), 
                    CREDIT_CARD_NUMBER varchar2(20));  5  

Table created.

SQL> insert into XIFENFEI.cust_payment_info2 values ('Jon', 'Oldfield', 10001, '5446-9597-0881-2985');
insert into XIFENFEI.cust_payment_info2 values ('Chris', 'White', 10002, '5122-3580-4608-2560');

1 row created.

SQL> 
1 row created.

SQL> insert into XIFENFEI.cust_payment_info2 values ('Alan', 'Squire', 10003, '5595-9689-4375-7920'); 

1 row created.

SQL> commit;

Commit complete.

SQL> select CREDIT_CARD_NUMBER,rowid,
  2  dbms_rowid.rowid_relative_fno(rowid)rel_fno,
  3  dbms_rowid.rowid_block_number(rowid)blockno,
dbms_rowid.rowid_row_number(rowid) rowno
  4    5  from XIFENFEI.cust_payment_info2;

CREDIT_CARD_NUMBER   ROWID                 REL_FNO    BLOCKNO      ROWNO
-------------------- ------------------ ---------- ---------- ----------
5446-9597-0881-2985  AAAZXdAAEAAAmgUAAA          4     157716          0
5122-3580-4608-2560  AAAZXdAAEAAAmgUAAB          4     157716          1
5595-9689-4375-7920  AAAZXdAAEAAAmgUAAC          4     157716          2

SQL> select name from v$datafile where file#=4;

NAME
--------------------------------------------------------------------------------
/home/u01/oradata/qsng/users01.dbf

SQL> alter system checkpoint;

System altered.

--使用bbed直接查看数据文件中数据
[oracle@localhost oracle]$ bbed
Password: 

BBED: Release 2.0.0.0.0 - Limited PrXIFENFEIction on Tue Jan 5 22:06:59 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED> set filename '/home/u01/oradata/qsng/users01.dbf'
        FILENAME        /home/u01/oradata/qsng/users01.dbf

BBED> set blocksize 8192
        BLOCKSIZE       8192

BBED> set block 157716
        BLOCK#          157716

BBED> map
 File: /home/u01/oradata/qsng/users01.dbf (0)
 Block: 157716                                Dba:0x00000000
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0       

 struct ktbbh, 72 bytes                     @20      

 struct kdbh, 14 bytes                      @100     

 struct kdbt[1], 4 bytes                    @114     

 sb2 kdbr[3]                                @118     

 ub1 freespace[7943]                        @124     

 ub1 rowdata[121]                           @8067    

 ub4 tailchk                                @8188    


BBED> p *kdbr[0]
rowdata[80]
-----------
ub1 rowdata[80]                             @8147     0x2c

BBED> x /rccnc
rowdata[80]                                 @8147    
-----------
flag@8147: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8148: 0x01
cols@8149:    4

col    0[3] @8150: Jon
col    1[8] @8154: Oldfield
col    2[4] @8163: 10001 
col   3[19] @8168: 5446-9597-0881-2985

--证明没有加密数据文件中数据,可以直接查看

SQL> ALTER TABLE XIFENFEI.CUST_PAYMENT_INFO2 MODIFY (CREDIT_CARD_NUMBER ENCRYPT NO SALT);

Table altered.
SQL> insert into XIFENFEI.cust_payment_info2 values ('xifenfei', 'XFF', 10004, 'WWW.XIFENFEI.COM'); 

1 row created.

SQL> COMMIT;

Commit complete.

SQL>  alter system checkpoint;

System altered.

SQL> select CREDIT_CARD_NUMBER,rowid,
  2  dbms_rowid.rowid_relative_fno(rowid)rel_fno,
  3  dbms_rowid.rowid_block_number(rowid)blockno,
dbms_rowid.rowid_row_number(rowid) rowno
  4    5  from XIFENFEI.cust_payment_info2;

CREDIT_CARD_NUMBER   ROWID                 REL_FNO    BLOCKNO      ROWNO
-------------------- ------------------ ---------- ---------- ----------
5446-9597-0881-2985  AAAZXdAAEAAAmgUAAA          4     157716          0
5122-3580-4608-2560  AAAZXdAAEAAAmgUAAB          4     157716          1
5595-9689-4375-7920  AAAZXdAAEAAAmgUAAC          4     157716          2
WWW.XIFENFEI.COM     AAAZXdAAEAAAmgWAAA          4     157718          0

BBED> set filename '/home/u01/oradata/qsng/users01.dbf'
        FILENAME        /home/u01/oradata/qsng/users01.dbf

BBED> set blocksize 8192
        BLOCKSIZE       8192

BBED> set block 157716
        BLOCK#          157716

BBED> map
 File: /home/u01/oradata/qsng/users01.dbf (0)
 Block: 157716                                Dba:0x00000000
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0       

 struct ktbbh, 72 bytes                     @20      

 struct kdbh, 14 bytes                      @100     

 struct kdbt[1], 4 bytes                    @114     

 sb2 kdbr[3]                                @118     

 ub1 freespace[7723]                        @124     

 ub1 rowdata[341]                           @7847    

 ub4 tailchk                                @8188    


BBED> p *kdbr[0]
rowdata[146]
------------
ub1 rowdata[146]                            @7993     0x2c

BBED> x /rccnc
rowdata[146]                                @7993    
------------
flag@7993: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@7994: 0x02
cols@7995:    4

col    0[3] @7996: Jon
col    1[8] @8000: Oldfield
col    2[4] @8009: 10001 
col   3[52] @8014: g隐.1Y.>.焦右.l.0赌鉣X.^._K泅Dn&.蜥._sR^....


BBED> set block 157718
        BLOCK#          157718

BBED> map
 File: /home/u01/oradata/qsng/users01.dbf (0)
 Block: 157718                                Dba:0x00000000
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0       

 struct ktbbh, 72 bytes                     @20      

 struct kdbh, 14 bytes                      @100     

 struct kdbt[1], 4 bytes                    @114     

 sb2 kdbr[1]                                @118     

 ub1 freespace[7994]                        @120     

 ub1 rowdata[74]                            @8114    

 ub4 tailchk                                @8188    


BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0]                              @8114     0x2c

BBED> x /rccnc
rowdata[0]                                  @8114    
----------
flag@8114: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8115: 0x01
cols@8116:    4

col    0[8] @8117: xifenfei
col    1[3] @8126: XFF
col    2[4] @8130: 10004 
col   3[52] @8135: 籕G蠖璆]Qu]..o._証?.湮`.C..)S....&...Z

通过测试可以发现两点:
1. TDE加密之后,数据无法通过数据文件获取,也就是说在没有钱包的情况下,就算有数据文件,也无法直接获取数据
2. 当对表进行alter语句设置加密之后,原表中数据已经进行加密,而且后续插入数据也加密

TDE加密后数据导出问题

--TDE加密之后,数据无法通过exp导出
[oracle@localhost network]$ exp XIFENFEI/oracle file=/tmp/1.dmp tables=CUST_PAYMENT_INFO1

Export: Release 11.2.0.4.0 - PrXIFENFEIction on Tue Jan 5 16:09:54 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit PrXIFENFEIction
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)

About to export specified tables via Conventional Path ...
EXP-00107: Feature (COLUMN ENCRYPTION) of column CREDIT_CARD_NUMBER in table 
  XIFENFEI.CUST_PAYMENT_INFO1 is not supported. The table will not be exported.
Export terminated successfully with warnings.

--TDE加密之后,数据使用expdp导出需要使用
[oracle@localhost network]$ expdp XIFENFEI/oracle dumpfile=1.dmp tables=CUST_PAYMENT_INFO1

Export: Release 11.2.0.4.0 - PrXIFENFEIction on Tue Jan 5 16:10:29 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit PrXIFENFEIction
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "XIFENFEI"."SYS_EXPORT_TABLE_01":  XIFENFEI/******** dumpfile=1.dmp tables=CUST_PAYMENT_INFO1 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "XIFENFEI"."CUST_PAYMENT_INFO1"                  6.406 KB       3 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "XIFENFEI"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for XIFENFEI.SYS_EXPORT_TABLE_01 is:
  /home/u01/admin/qsng/dpdump/1.dmp
Job "XIFENFEI"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at Tue Jan 5 16:10:43 2016 elapsed 0 00:00:11

--指定ENCRYPTION_MODE=TRANSPARENT使用钱包加密方式
[oracle@localhost oracle]$ expdp XIFENFEI/oracle dumpfile=2.dmp ENCRYPTION_MODE=TRANSPARENT 
> ENCRYPTION=ALL tables=CUST_PAYMENT_INFO2 reuse_dumpfiles=yes

Export: Release 11.2.0.4.0 - PrXIFENFEIction on Tue Jan 5 22:45:02 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit PrXIFENFEIction
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "XIFENFEI"."SYS_EXPORT_TABLE_01":  XIFENFEI/******** dumpfile=2.dmp ENCRYPTION_MODE=TRANSPARENT
  ENCRYPTION=ALL tables=CUST_PAYMENT_INFO2 reuse_dumpfiles=yes 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "XIFENFEI"."CUST_PAYMENT_INFO2"                  6.453 KB       4 rows
Master table "XIFENFEI"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for XIFENFEI.SYS_EXPORT_TABLE_01 is:
  /home/u01/admin/qsng/dpdump/2.dmp
Job "XIFENFEI"."SYS_EXPORT_TABLE_01" successfully completed at Tue Jan 5 22:45:06 2016 elapsed 0 00:00:03

--使用ENCRYPTION_MODE=PASSWORD方式导出
[oracle@localhost oracle]$ expdp XIFENFEI/oracle dumpfile=2.dmp ENCRYPTION_MODE=PASSWORD 
>ENCRYPTION_PASSWORD=www.xifenfei.com  ENCRYPTION=ALL tables=CUST_PAYMENT_INFO2 reuse_dumpfiles=yes

Export: Release 11.2.0.4.0 - PrXIFENFEIction on Tue Jan 5 22:46:17 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit PrXIFENFEIction
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "XIFENFEI"."SYS_EXPORT_TABLE_01":  XIFENFEI/******** dumpfile=2.dmp ENCRYPTION_MODE=PASSWORD 
  ENCRYPTION_PASSWORD=******** ENCRYPTION=ALL tables=CUST_PAYMENT_INFO2 reuse_dumpfiles=yes 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "XIFENFEI"."CUST_PAYMENT_INFO2"                  6.453 KB       4 rows
Master table "XIFENFEI"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for XIFENFEI.SYS_EXPORT_TABLE_01 is:
  /home/u01/admin/qsng/dpdump/2.dmp
Job "XIFENFEI"."SYS_EXPORT_TABLE_01" successfully completed at Tue Jan 5 22:46:21 2016 elapsed 0 00:00:03

钱包随库一起open

[oracle@localhost wallets]$ orapki wallet create -pwd welcome1 -wallet /home/u01/oracle/network/wallets  -auto_login
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

--注意随库open之后钱包无法关闭
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 5044088832 bytes
Fixed Size                  2261928 bytes
Variable Size            1040190552 bytes
Database Buffers         3992977408 bytes
Redo Buffers                8658944 bytes
Database mounted.
Database opened.
SQL> select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/home/u01/oracle/network/wallets
OPEN

SQL> alter system set wallet close identified by "welcome1";
alter system set wallet close identified by "welcome1"
*
ERROR at line 1:
ORA-28365: wallet is not open

SQL>  alter system set wallet close;

System altered.

SQL> select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/home/u01/oracle/network/wallets
OPEN