« 数据字典视图之：v$session 结构 | Blog首页 | Oracle Transparent Data Encryption - 性能测试 »
Oracle Transparent Data Encryption - 透明数据加密
作者：eygle | 【转载请注出处】|【云和恩墨 领先的zData数据库一体机 | zCloud PaaS云管平台 | SQM SQL审核平台 | ZDBM 数据库备份一体机】
链接：https://www.eygle.com/archives/2011/09/oracle_transparent_data_encryption.html
Oracle的透明数据加密，是Oracle高级安全选项中的一个部分，需要额外支付软件费用。链接：https://www.eygle.com/archives/2011/09/oracle_transparent_data_encryption.html
这一选项，可以结合多种手段进行加密，包括使用Wallet（PKCS#12标准）以及支持PKCS#11 RAS硬件设备。
在10g中，透明加密支持基于列级的加密，而在Oracle 11gR2中，增加了基于表空间的透明加密。
以下是官方文档中关于加密解密的流程图：
以下是一个简单测试。
首先在SQLNET.ora文件中增加如下一段：
ENCRYPTION_WALLET_LOCATION=
(SOURCE=(METHOD=FILE)(METHOD_DATA=
(DIRECTORY=D:\Oracle\11.2.0\NETWORK\ADMIN\encryption_wallet\)))
(SOURCE=(METHOD=FILE)(METHOD_DATA=
(DIRECTORY=D:\Oracle\11.2.0\NETWORK\ADMIN\encryption_wallet\)))
在SQL*Plus中创建Wallet密钥：
SQL> connect / as sysdba
Connected.
SQL> alter system set encryption key authenticated by "eygle";
System altered.
Connected.
SQL> alter system set encryption key authenticated by "eygle";
System altered.
关闭和打开Wallet：
SQL> alter system set encryption wallet close;
alter system set encryption wallet close
*
ERROR at line 1:
ORA-28390: auto login wallet not open
SQL> alter system set encryption wallet close identified by "eygle";
System altered.
SQL> alter system set wallet open identified by "eygle";
System altered.
alter system set encryption wallet close
*
ERROR at line 1:
ORA-28390: auto login wallet not open
SQL> alter system set encryption wallet close identified by "eygle";
System altered.
SQL> alter system set wallet open identified by "eygle";
System altered.
在创建数据表时可以指定加密：
SQL> connect eygle/eygle
Connected.
SQL> create table tde (id number(10),data varchar2(50) encrypt);
Table created.
SQL> insert into tde select user_id,username from dba_users;
9 rows created.
SQL> commit;
Commit complete.
SQL> connect / as sysdba
Connected.
SQL> select * from eygle.tde;
ID DATA
---------- --------------------------------------------------
0 SYS
5 SYSTEM
34 EYGLE
9 OUTLN
31 APPQOSSYS
30 DBSNMP
32 WMSYS
14 DIP
21 ORACLE_OCM
Connected.
SQL> create table tde (id number(10),data varchar2(50) encrypt);
Table created.
SQL> insert into tde select user_id,username from dba_users;
9 rows created.
SQL> commit;
Commit complete.
SQL> connect / as sysdba
Connected.
SQL> select * from eygle.tde;
ID DATA
---------- --------------------------------------------------
0 SYS
5 SYSTEM
34 EYGLE
9 OUTLN
31 APPQOSSYS
30 DBSNMP
32 WMSYS
14 DIP
21 ORACLE_OCM
加密和解密是自动进行的。
查询dba_encrypted_columns视图可以找到加密列：
SQL> select * from dba_encrypted_columns;
OWNER TABLE_NAME COLUMN_NAME ENCRYPTION_ALG SAL INTEGRITY_AL
---------- --------------- -------------------- ----------------------------- --- ------------
EYGLE TDE DATA AES 192 bits key YES SHA-1
OWNER TABLE_NAME COLUMN_NAME ENCRYPTION_ALG SAL INTEGRITY_AL
---------- --------------- -------------------- ----------------------------- --- ------------
EYGLE TDE DATA AES 192 bits key YES SHA-1
如果关闭Wallet，则加密列不可访问：
SQL> select * from eygle.tde;
select * from eygle.tde
*
ERROR at line 1:
ORA-28365: wallet is not open
SQL> alter system set encryption wallet close identified by "eygle";
System altered.
SQL> select * from eygle.tde;
select * from eygle.tde
*
ERROR at line 1:
ORA-28365: wallet is not open
SQL> desc eygle.tde
Name Null? Type
----------------------------------------------------------------- -------- -------------------------------
ID NUMBER(10)
DATA VARCHAR2(50) ENCRYPT
SQL> select id from eygle.tde;
ID
----------
0
5
34
9
31
30
32
14
21
9 rows selected.
select * from eygle.tde
*
ERROR at line 1:
ORA-28365: wallet is not open
SQL> alter system set encryption wallet close identified by "eygle";
System altered.
SQL> select * from eygle.tde;
select * from eygle.tde
*
ERROR at line 1:
ORA-28365: wallet is not open
SQL> desc eygle.tde
Name Null? Type
----------------------------------------------------------------- -------- -------------------------------
ID NUMBER(10)
DATA VARCHAR2(50) ENCRYPT
SQL> select id from eygle.tde;
ID
----------
0
5
34
9
31
30
32
14
21
9 rows selected.
在加密列时，存在两个选项：Salt和No Salt。
Salt在加密前对数据增加随即字符串，增加破解的难度，使得同样的字符串加密结果不同；而对于NO Salt，则同样字符串可以获得同样的加密输出，其安全性相对略低。
在加密列上，如果使用Salt方式，则不能创建索引,Salt加密和索引两种属性互斥，不能同时设置：
SQL> create index idx01 on tde(data);当使用缺省Salt方式加密时，此时允许对于加密列创建索引：
create index idx01 on tde(data)
*
ERROR at line 1:
ORA-28338: Column(s) cannot be both indexed and encrypted with salt
SQL> create table tde2 (id number(10) encrypt no salt,data varchar2(50) );当执行导出时，Oracle会给出提示：
Table created.
SQL> insert into tde2 select user_id,username from dba_users;
9 rows created.
SQL> select * from tde2;
ID DATA
---------- --------------------------------------------------
0 SYS
5 SYSTEM
34 EYGLE
9 OUTLN
31 APPQOSSYS
30 DBSNMP
32 WMSYS
14 DIP
21 ORACLE_OCM
9 rows selected.
SQL> commit;
Commit complete.
SQL> create index idx1 on tde2(id);
Index created.
D:\>expdp eygle/eygle directory=temp dumpfile=tde2.dmp tables=TDE
Export: Release 11.2.0.2.0 - Production on Thu Sep 8 15:35:19 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "EYGLE"."SYS_EXPORT_TABLE_01": eygle/******** directory=temp dumpfile=tde2.dmp tables=TDE
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 "EYGLE"."TDE" 5.562 KB 9 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "EYGLE"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for EYGLE.SYS_EXPORT_TABLE_01 is:
D:\TEMP\TDE2.DMP
Job "EYGLE"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 15:35:23
Export: Release 11.2.0.2.0 - Production on Thu Sep 8 15:35:19 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "EYGLE"."SYS_EXPORT_TABLE_01": eygle/******** directory=temp dumpfile=tde2.dmp tables=TDE
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 "EYGLE"."TDE" 5.562 KB 9 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "EYGLE"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for EYGLE.SYS_EXPORT_TABLE_01 is:
D:\TEMP\TDE2.DMP
Job "EYGLE"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 15:35:23
提示表明，加密数据在转储文件中，以非加密方式存储，但是当执行导入时，需要存在加密Wallet才能够执行导入，否则将会出现ORA-28362的异常。
历史上的今天...
>> 2009-09-08文章:
>> 2008-09-08文章:
>> 2007-09-08文章:
>> 2005-09-08文章:
By eygle on 2011-09-08 15:50 | Comments (1) | Advanced | 2863 |
VPD + TDE 就绝杀了