Oracle 在10.2中引入了 transparent data encryption，也就是常说的TDE；下面来演示下TDE 的使用过程：
如果要使用tde，就首先要创建wallet，这个可以使用以下命令完成：
| SQL> alter system set encryption key identified by “huosi”; alter system set encryption key identified by “huosi” * ERROR at line 1: ORA-28368: cannot auto-create wallet |
这个错误是缺少相关的路径造成的更改 sqlnet.ora:
| ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA= (DIRECTORY=/install/admin/parameter/wallets/)) ) |
并在相关路径下创建 wallets路径；再次创建：
| [oracle@localhost admin]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.1.0 – Production on Mon Mar 12 17:16:27 2012 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production With the Partitioning, OLAP and Data Mining options SQL> alter system set encryption key identified by “huosi”; System altered. SQL> |
| SQL> create table tdetest(id int,name varchar2(10));Table created SQL>insert into tdetest(1,’huosi’); SQL> alter table tdetest modify name encrypt; Table altered. SQL> select * from tdetest; ID NAME ———- ———- 1 huosi SQL> alter system set wallet close; System altered. SQL> select * from tdetest; select * from tdetest ERROR at line 1: ORA-28365: wallet is not open |
通过以上的测试可以看出tde 加密列在关闭 wallet 后是无法访问的；那么再来看看DML操作
| SQL> insert into tdetest values(2,’huosi’);insert into tdetest values(2,’huosi’)* ERROR at line 1: ORA-28365: wallet is not open |
但是此时访问非加密列是可以的：
| SQL> select id from tdetest;ID———-1 |
在非加密列写入数据也是可以的：
| SQL> insert into tdetest (id) values(1);1 row created.SQL> commit;Commit complete. |
查询：
| SQL> select * from tdetest;select * from tdetest* ERROR at line 1: ORA-28365: wallet is not open SQL> select id from tdetest; ID ———- 1 1 SQL> alter system set encryption wallet open identified by “huosi”; System altered. |
下面再来看看相关索引的创建
| SQL> create index tde on tdetest(name) with salt;create index tde on tdetest(name) with salt* ERROR at line 1: ORA-02158: invalid CREATE INDEX option |
此时对于加密列是无法创建索引的，主要问题在于salt参数，下面进行下更改：然后创建：
| SQL> alter table tdetest modify name encrypt no salt;Table altered.SQL> create index tde on tdetest(name); Index created. SQL> |
此时如果使用 ctas 来将tdetest 表作为源，新表也会使用tde加密，同时启用 salt 参数；
默认情况下tde 不是随着数据库打开而打开的，所以如果使用了tde，请注意数据库重启后，
wallet是否开启，否则数据将无法查询；下面看看使用TDE后的产生的文件，假如此时删除wallet文件：
| [oracle@localhost wallets]$ lsewallet.p12[oracle@localhost wallets]$ cp ewallet.p12 ewallet.p12.bak [oracle@localhost wallets]$ ls ewallet.p12 ewallet.p12.bak [oracle@localhost wallets]$ rm ewallet.p12 [oracle@localhost wallets]$ ls ewallet.p12.bak [oracle@localhost wallets]$ |
启动数据库：
| [oracle@localhost wallets]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.1.0 – Production on Mon Mar 12 20:52:57 2012Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORA-32004: obsolete and/or deprecated parameter(s) specified ORACLE instance started. Total System Global Area 310378496 bytes Fixed Size 1219136 bytes Variable Size 100664768 bytes Database Buffers 205520896 bytes Redo Buffers 2973696 bytes Database mounted. Database opened. SQL> alter system set encryption wallet open identified by “huosi”; alter system set encryption wallet open identified by “huosi” * ERROR at line 1: ORA-28367: wallet does not exist |
如果此时访问wallet 加密的表是无法访问的：
| [oracle@localhost ~]$ sqlplus scott/tigerSQL*Plus: Release 10.2.0.1.0 – Production on Mon Mar 12 20:55:52 2012Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production With the Partitioning, OLAP and Data Mining options SQL> select * from tdetest; select * from tdetest * ERROR at line 1: ORA-28365: wallet is not open SQL> |
如果我们重新创建wallet会怎么样呢，下面来看看：
| SQL> show userUSER is “SYS”SQL> alter system set encryption key identified by “huosi”; System altered. SQL> |
我们再访问：
| SQL> conn scott/tigerConnected.SQL> select * from tdetest; select * from tdetest * ERROR at line 1: ORA-28362: master key not found |
如果重启数据库后会怎么样呢：
| SQL> startup forceORA-32004: obsolete and/or deprecated parameter(s) specifiedORACLE instance started. Total System Global Area 310378496 bytes Fixed Size 1219136 bytes Variable Size 104859072 bytes Database Buffers 201326592 bytes Redo Buffers 2973696 bytes Database mounted. Database opened. SQL> alter system set encryption wallet open identified by “huosi”; System altered. |
再次访问：
| SQL> conn scott/tigerConnected.SQL> select * from tdetest; select * from tdetest * ERROR at line 1: ORA-28362: master key not found |
从这里可以看出，一旦wallet文件丢失，那么tde加密将无法使用；那么如果我们将wallet文件还原回去呢；
先删除第二次创建的wallet文件，然后将备份的文件进行还原；
| [oracle@localhost wallets]$ lsewallet.p12 ewallet.p12.bak[oracle@localhost wallets]$ [oracle@localhost wallets]$ rm ewallet.p12 [oracle@localhost wallets]$ ls ewallet.p12.bak [oracle@localhost wallets]$ cp ewallet.p12.bak ewallet.p12 [oracle@localhost wallets]$ ls ewallet.p12 ewallet.p12.bak [oracle@localhost wallets]$ |
此时直接访问是没有办法访问的：
| SQL> show userUSER is “SCOTT”SQL> select * from tdetest; select * from tdetest * ERROR at line 1: ORA-28362: master key not found |
那么我们就先关闭wallet，然后再重新打开，然后再次访问：
| SQL> alter system set wallet close;System altered.SQL> alter system set encryption wallet open identified by “huosi”; System altered. SQL> |
查询：
| SQL> conn scott/tiger;Connected.SQL> select * from tdetest; ID NAME ———- ———- 1 huosi 1 SQL> |
从这里我们可以看到 数据又可以访问了，所以一旦使用了wallet备份后，请备份wallet产生的文件，
否则文件一旦丢失，将会出现不必要的麻烦；
那么如果出现wallet文件丢失后，数据需要怎么处理呢；我们来看看是否可以解密：
首先删除文件：然后再次以相同的秘密创建walllet：
| [oracle@localhost wallets]$ rm ewallet.p12[oracle@localhost wallets]$ lsewallet.p12.bak[oracle@localhost wallets]$ |
再次创建
| SQL> alter system set wallet close;System altered.SQL> alter system set encryption wallet open identified by “huosi”; alter system set encryption wallet open identified by “huosi” * ERROR at line 1: ORA-28367: wallet does not exist SQL> alter system set encryption key identified by “huosi”; System altered |
我们将前后两次使用相同秘密的文件进行对比：
| [oracle@localhost wallets]$ strings ewallet.p122Mm>#=3+b<9 yWXy eILw [j8zy }UEhI (i<@Q d37P gAx= jce9s hA4B XM^!2uEF )* ^?8O [oracle@localhost wallets]$ strings ewallet.p12.bak }/<C g.0~; -f~F 3lG* -’,K $OU^H ayG\fB )jK(‘ 5G([ 2{!b 92F< .hTO :Yu: hD9u(c1u dHP 6?K- [oracle@localhost wallets]$ |
从这里可以看出 即时使用了相同的 秘密创建wallet，生成是wallet 文件的内容也是不相同的；
这就是为什么不能够使用相同密码创建wallet后，仍旧不能读取tde加密列的原因；
那么此时我们看看block当中的实际情况：首先确定 table 的block的位置：
| SQL> select dbms_rowid.rowid_object(rowid) object_id, dbms_rowid.rowid_relative_fno(rowid) file_id,dbms_rowid.rowid_block_number(rowid) block_id ,dbms_rowid.rowid_row_number(rowid) num from tdetest where rownum<5;OBJECT_ID FILE_ID BLOCK_ID NUM———- ———- ———- ———-52874 4 484 052874 4 484 1 |
用bbed 打开；
| [oracle@localhost ~]$ bbed parfile=bbed.parPassword:BBED-00113: Invalid password. Please rerun utility with the correct password. [oracle@localhost ~]$ bbed parfile=bbed.par Password: BBED: Release 2.0.0.0.0 – Limited Production on Mon Mar 12 21:32:52 2012 Copyright (c) 1982, 2005, Oracle. All rights reserved. ************* !!! For Oracle Internal Use only !!! *************** BBED> set file 4 block 484 FILE# 4 BLOCK# 484 BBED> set offset 8000 OFFSET 8000 BBED> dump File: /data/paramete/users01.dbf (4) Block: 484 Offsets: 8000 to 8191 Dba:0x010001e4 ———————————————————————— 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 2c020202 c1022414 5a4ca5d9 5532c615 7fb3d3ac 57871090 47c3aa11 151d9274 7e2f4bc4 9ac62a99 d187062c 020102c1 022c0002 02c10234 5ab3d84e df9cf1e5 17b54c58 895fe0e8 6eafacce 5d779a10 6bf588f7 84464b33 dbc354af 87ececb4 96635be6 e2457e28 2b10023c 2c000202 c1020568 756f7369 0106f5eb <32 bytes per line> BBED> |
再dump：
| BBED> dump /vFile: /data/paramete/users01.dbf (4)Block: 484 Offsets: 8000 to 8191 Dba:0x010001e4 ——————————————————- 00000000 00000000 00000000 00000000 l ……………. 00000000 00000000 00000000 00000000 l ……………. 00000000 00000000 00000000 00000000 l ……………. 00000000 00000000 00000000 00000000 l ……………. 00000000 2c020202 c1022414 5a4ca5d9 l ….,…?$.ZLベ 5532c615 7fb3d3ac 57871090 47c3aa11 l U2?.秤琖…G锚. 151d9274 7e2f4bc4 9ac62a99 d187062c l …t~/K??.?., 020102c1 022c0002 02c10234 5ab3d84e l …?,…?4Z池N df9cf1e5 17b54c58 895fe0e8 6eafacce l ?皴.礚X._噼n? 5d779a10 6bf588f7 84464b33 dbc354af l ]w..k??FK3勖T? 87ececb4 96635be6 e2457e28 2b10023c l .祆?c[驸E~(+..< 2c000202 c1020568 756f7369 0106f5eb l ,…?.huosi..蹼 <16 bytes per line> |
那么此时可以看到 字符串： huosi，竟然明文显示来；这令我很惊讶；其实这个只是一种巧合；
如果删除了wallet文件后再次创建使用相同的密码；再重新创建表插入相同的值，
再次dump也是无法出现相同的结果的；看来目前是解密不了了；不过希望将来自己有能力破解 TDE；
336
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
