【www.bbyears.com--Oracle教程】
从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