演示TDE的数据加密示例，并用logminer验证加密效果

TDE(Transparent Data Encryption)：

我们称之为“Oracle 透明数据加密技术”，它属于Oracle数据保护安全策略的一种。有两种数据加密技术，一种是基于列的数据加密，另一种是基于表空间的数据加密。

下面分别来说明一下这两种加密技术。

①  基于列的加密：对某一列进行加密，适用Oracle10GR2以上版本
②  基于表空间的加密：对整个表空间进行加密，适用Oracle11gR2以上版本

TDE(Transparent Data Encryption)优点：

①  对单列进行加密，并且可以创建索引
②  对用户透明，用户感知不到
③  管理简便，无需应用设置

TDE(Transparent Data Encryption)缺点：

①  加密列上只能创建B-tree索引，由于被加密算法编码过键值凌乱，无法支持范围扫描
②  外部对象不可加密
③  可传输表空间不可加密
④  Exp/Imp导出导入不可加密
TDE可支持的加密算法种类
①  AES192(default)
②  AES128
③  AES256
④  3DES168

TDE加密原理

①  先要创建一个“wallet钱包”，这个钱包里面保存着密钥，Oracle就是通过这个密钥对列进行加密和解密的。
②  生成wallet钱包之前先要设定wallet钱包的保存位置

开始实验：--需用用DBA用户做logmnr.

1.设置wallet钱包位置的文件$ORACLE_HOME/network/admin/sqlnet.ora并打开

[oracle@oel-01 admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ADR_BASE = /u01/app/oracle
encryption_wallet_location=(source=

                                  (method=file)

                                  (method_data=

                                  (directory=/u01/app/oracle/product/11.2.0/dbhome_1/network/admin)))
在wallet里创建密钥， testtest是打开或关闭wallet的密码
BYS@ bys001>alter system set encryption key authenticated by "testtest";
System altered.
从操作系统上查看，已经生成了wallet的文件。
[oracle@oel-01 admin]$ ll ewallet.p12
-rw-r--r-- 1 oracle oinstall 1573 Jul 18 20:33 ewallet.p12

2.创建一个使用加密列的表，使用“AES192”加密算法

BYS@ bys001>create table encry_test(a varchar2(9),b varchar2(9) encrypt using 'AEs192');
Table created.
BYS@ bys001>insert into encry_test values('a1','b1');
BYS@ bys001>insert into encry_test values('a2','b2');
BYS@ bys001>commit;

从数据字典中查询加密段的信息

BYS@ bys001>select * from dba_encrypted_columns;
OWNER                          TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------ ------------------------------
ENCRYPTION_ALG                SAL INTEGRITY_AL
----------------------------- --- ------------
BYS                            ENCRY_TEST                     B
AES 192 bits key              YES SHA-1

BYS@ bys001>select * from encry_test;

A         B
--------- ---------
a1        b1
a2        b2
加密字段是否可见完全依赖于wallet里面的密钥是否解密，把wallet关闭，密钥就无法还原加密信息

3.关闭wallet，查询加密表，返回错误 。

BYS@ bys001>alter system set wallet close identified by "testtest";
System altered.
BYS@ bys001>select * from encry_test;
select * from encry_test
              *
ERROR at line 1:
ORA-28365: wallet is not open
创建后wallet默认是打开状态。
BYS@ bys001>alter system set wallet open identified by "testtest";
System altered.
BYS@ bys001>select * from encry_test;
A         B
--------- ---------
a1        b1
a2        b2

4.对加密列进行logminer日志挖掘

BYS@ bys001>select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
                 2344451
BYS@ bys001>insert into encry_test values('a3','b3');

BYS@ bys001>commit;
BYS@ bys001>select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
                 2344469
记录开始和结束DML操作时的SCN。
BYS@ bys001>select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 INACTIVE
         3 CURRENT
BYS@ bys001>col member for a50
BYS@ bys001>select group#,member,type from v$logfile;
    GROUP# MEMBER                                             TYPE
---------- -------------------------------------------------- -------
         3 /u01/app/oracle/oradata/bys001/redo03.log          ONLINE
         2 /u01/app/oracle/oradata/bys001/redo02.log          ONLINE
         1 /u01/app/oracle/oradata/bys001/redo01.log          ONLINE
         1 /u01/app/oracle/oradata/bys001/redo01a.log         ONLINE
BYS@ bys001>execute dbms_logmnr.add_logfile(LogFileName => '/u01/app/oracle/oradata/bys001/redo03.log',Options => dbms_logmnr.new);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.25
BYS@ bys001>execute dbms_logmnr.start_logmnr(options =>dbms_logmnr.dict_from_online_catalog,startscn =>2344451,endscn =>2344469);
PL/SQL procedure successfully completed.

5.查看logmnr.挖掘出的的SQLREDO信息是被解密过的，是明文

BYS@ bys001>select operation,sql_redo,sql_undo from v$logmnr_contents where table_name='ENCRY_TEST';

OPERATION
--------------------------------
SQL_REDO
----------------------------------------------------------------------------------------------------
SQL_UNDO
----------------------------------------------------------------------------------------------------
INSERT
insert into "BYS"."ENCRY_TEST"("A","B") values ('a3','b3');
delete from "BYS"."ENCRY_TEST" where "A" = 'a3' and "B" = 'b3' and ROWID = 'AAASp8AAEAAAAI3AAC';
BYS@ bys001>alter system set wallet close identified by "testtest";
System altered.

6.关闭wallet钱包，wallet关闭密钥就无法还原加密记录。

此时sql_redo查询的信息已经是加密状态

BYS@ bys001>select operation,sql_redo,sql_undo from v$logmnr_contents where table_name='ENCRY_TEST';

OPERATION
--------------------------------
SQL_REDO
----------------------------------------------------------------------------------------------------
SQL_UNDO
----------------------------------------------------------------------------------------------------
INSERT
insert into "BYS"."ENCRY_TEST"("A","B") values ('a3',HEXTORAW('7b848dfd467dc93a559cfc918f635c2152d0e
2cfa6ad22a9d4562f893b3234ec12586403a2cb8dd346d658660aef6429bfef690f'));
delete from "BYS"."ENCRY_TEST" where "A" = 'a3' and "B" = HEXTORAW('7b848dfd467dc93a559cfc918f635c21
52d0e2cfa6ad22a9d4562f893b3234ec12586403a2cb8dd346d658660aef6429bfef690f') and ROWID = 'AAASp8AAEAAA
AI3AAC';