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