当前位置:Oracle研究中心 > 开发DBA >
时间:2016-06-19 16:52 来源:Oracle研究中心 作者:惜分飞 点击: 次
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 Proracleplusction 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 Proracleplusction 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
SQL> conn oracleplus/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 oracleplus.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 oracleplus.cust_payment_info; select * from oracleplus.cust_payment_info * ERROR at line 1: ORA-28365: wallet is not open
--创建测试数据 SQL> create table oracleplus.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 oracleplus.cust_payment_info2 values ('Jon', 'Oldfield', 10001, '5446-9597-0881-2985'); insert into oracleplus.cust_payment_info2 values ('Chris', 'White', 10002, '5122-3580-4608-2560'); 1 row created. SQL> 1 row created. SQL> insert into oracleplus.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 oracleplus.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 Proracleplusction 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 oracleplus.CUST_PAYMENT_INFO2 MODIFY (CREDIT_CARD_NUMBER ENCRYPT NO SALT); Table altered. SQL> insert into oracleplus.cust_payment_info2 values ('oracleplus', 'XFF', 10004, 'WWW.oracleplus.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 oracleplus.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.oracleplus.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: oracleplus 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加密之后,数据无法通过exp导出 [oracle@localhost network]$ exp oracleplus/oracle file=/tmp/1.dmp tables=CUST_PAYMENT_INFO1 Export: Release 11.2.0.4.0 - Proracleplusction 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 Proracleplusction 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 oracleplus.CUST_PAYMENT_INFO1 is not supported. The table will not be exported. Export terminated successfully with warnings. --TDE加密之后,数据使用expdp导出需要使用 [oracle@localhost network]$ expdp oracleplus/oracle dumpfile=1.dmp tables=CUST_PAYMENT_INFO1 Export: Release 11.2.0.4.0 - Proracleplusction 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 Proracleplusction With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "oracleplus"."SYS_EXPORT_TABLE_01": oracleplus/******** 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 "oracleplus"."CUST_PAYMENT_INFO1" 6.406 KB 3 rows ORA-39173: Encrypted data has been stored unencrypted in dump file set. Master table "oracleplus"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for oracleplus.SYS_EXPORT_TABLE_01 is: /home/u01/admin/qsng/dpdump/1.dmp Job "oracleplus"."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 oracleplus/oracle dumpfile=2.dmp ENCRYPTION_MODE=TRANSPARENT > ENCRYPTION=ALL tables=CUST_PAYMENT_INFO2 reuse_dumpfiles=yes Export: Release 11.2.0.4.0 - Proracleplusction 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 Proracleplusction With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "oracleplus"."SYS_EXPORT_TABLE_01": oracleplus/******** 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 "oracleplus"."CUST_PAYMENT_INFO2" 6.453 KB 4 rows Master table "oracleplus"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for oracleplus.SYS_EXPORT_TABLE_01 is: /home/u01/admin/qsng/dpdump/2.dmp Job "oracleplus"."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 oracleplus/oracle dumpfile=2.dmp ENCRYPTION_MODE=PASSWORD >ENCRYPTION_PASSWORD=www.oracleplus.com ENCRYPTION=ALL tables=CUST_PAYMENT_INFO2 reuse_dumpfiles=yes Export: Release 11.2.0.4.0 - Proracleplusction 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 Proracleplusction With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "oracleplus"."SYS_EXPORT_TABLE_01": oracleplus/******** 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 "oracleplus"."CUST_PAYMENT_INFO2" 6.453 KB 4 rows Master table "oracleplus"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for oracleplus.SYS_EXPORT_TABLE_01 is: /home/u01/admin/qsng/dpdump/2.dmp Job "oracleplus"."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
--------------------------------------ORACLE-DBA----------------------------------------
最权威、专业的Oracle案例资源汇总之学习笔记:Oracle透明数据加密 TDE使用案例介绍
本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/436.html
Powered by Oracle研究中心 OraclePlus Inc.
BY DedeCMS 京ICP备14022373号-5
声明:Oracle研究中心部分文章源自互联网,转载均按原文作者要求转载,如有疑问联系QQ:1026002015,协商未果24小时删除。