eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« 数据字典视图之:v$session 结构 | Blog首页 | Oracle Transparent Data Encryption - 性能测试 »

Oracle Transparent Data Encryption - 透明数据加密

Oracle的透明数据加密,是Oracle高级安全选项中的一个部分,需要额外支付软件费用。
这一选项,可以结合多种手段进行加密,包括使用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\)))

在SQL*Plus中创建Wallet密钥:
SQL> connect / as sysdba
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.

在创建数据表时可以指定加密:
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

加密和解密是自动进行的。

查询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

如果关闭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.

在加密列时,存在两个选项:Salt和No Salt。
Salt在加密前对数据增加随即字符串,增加破解的难度,使得同样的字符串加密结果不同;而对于NO Salt,则同样字符串可以获得同样的加密输出,其安全性相对略低。

在加密列上,如果使用Salt方式,则不能创建索引,Salt加密和索引两种属性互斥,不能同时设置:
SQL> create index idx01 on tde(data);
create index idx01 on tde(data)
                          *
ERROR at line 1:
ORA-28338: Column(s) cannot be both indexed and encrypted with salt
当使用缺省Salt方式加密时,此时允许对于加密列创建索引:
SQL> create table tde2 (id number(10) encrypt no salt,data varchar2(50) );

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.
当执行导出时,Oracle会给出提示:
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

提示表明,加密数据在转储文件中,以非加密方式存储,但是当执行导入时,需要存在加密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 |

1 Comment

VPD + TDE 就绝杀了


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com