演示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';

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29196873/viewspace-1067295/，如需轉載，請註明出處，否則將追究法律責任。