Oracle TDE 简单测试
惜分飞发表于 2016-01-05 15:02:31
从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