oracle 特殊功能,ORACLE 11Gr2 Transparent Data Encryption新功能

ORACLE 11Gr2 Transparent Data Encryption新功能

Transparent Data Encryption简称TDE，在11g里增加了基于tablespace级别的加密

1.配置wallet位置

Oracle strongly recommends that you use a separate wallet for storing master encryption keys used by TDE.

To designate a separate wallet, set the ENCRYPTION_WALLET_LOCATION parameter in the sqlnet.ora file to point to the wallet used exclusively by TDE.

[oracle@mgsrv ~]$ more /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora

ENCRYPTION_WALLET_LOCATION=

(SOURCE=(METHOD=FILE)(METHOD_DATA=

(DIRECTORY=/u01/app/oracle/trsen_wallet)))

2..创建encryption wallet密码

SQL> alter system set encryption key identified by “trsen”;

System altered.

生成的密钥文件

[oracle@mgsrv trsen_wallet]$ ls -lat

total 12

-rw-r–r– 1 oracle oinstall 1573 Oct 23 16:22 ewallet.p12

3.默认在创建的时候，已经open encryption wallet，下面是close和open encryption wallet方式

SQL> alter system set encryption wallet open identified by “trsen”;

alter system set encryption wallet open identified by “trsen”

*

ERROR at line 1:

ORA-28354: Encryption wallet, auto login wallet, or HSM is already open

SQL> alter system set encryption wallet close identified by “trsen”;

System altered.

SQL> alter system set encryption wallet open identified by “trsen”;

System altered.

4.创建表指定加密列

CREATE TABLE trsen

(first_name VARCHAR2(11),

last_name VARCHAR2(10),

order_number NUMBER(5) ENCRYPT,

credit_card_number VARCHAR2(16) ENCRYPT NO SALT,

active_card VARCHAR2(3));

INSERT INTO trsen VALUES (‘Jon’, ‘Oldfield’, 10001, ‘5446959708812985’,’YES’);

INSERT INTO trsen VALUES (‘Chris’, ‘White’, 10002, ‘5122358046082560’,’YES’);

INSERT INTO trsen VALUES (‘Alan’, ‘Squire’, 10003, ‘5595968943757920’,’YES’);

INSERT INTO trsen VALUES (‘Mike’, ‘Anderson’, 10004, ‘4929889576357400’,’YES’);

INSERT INTO trsen VALUES (‘Annie’, ‘Schmidt’, 10005, ‘4556988708236902’,’YES’);

INSERT INTO trsen VALUES (‘Elliott’, ‘Meyer’, 10006, ‘374366599711820’,’YES’);

INSERT INTO trsen VALUES (‘Celine’, ‘Smith’, 10007, ‘4716898533036’,’YES’);

INSERT INTO trsen VALUES (‘Steve’, ‘Haslam’, 10008, ‘340975900376858’,’YES’);

INSERT INTO trsen VALUES (‘Albert’, ‘Einstein’, 10009, ‘310654305412389’,’YES’);

By default, TDE adds salt to cleartext before encrypting it.

This makes it harder for attackers to steal data through a brute force attack.

However, if you plan to index the encrypted column, you must use NO SALT.

Salt在加密前对数据增加随机字符串，然后加密,这样做同样的记录就会得到不同的加密输出；

而对于NO Salt，则同样字符串可以获得同样的加密输出。

SQL> CREATE INDEX idx01_trsen ON trsen (credit_card_number);

Index created.

在salt方式的列上,无法创建索引

SQL> CREATE INDEX idx02_trsen ON trsen (order_number)

CREATE INDEX idx02_trsen ON trsen (order_number)

*

ERROR at line 1:

ORA-28338: Column(s) cannot be both indexed and encrypted with salt

没有关闭encryption wallet可正常查询,

SQL> select * from trsen;

FIRST_NAME LAST_NAME ORDER_NUMBER CREDIT_CARD_NUMB ACT

———– ———- ———— —————- —

Jon Oldfield 10001 5446959708812985 YES

Chris White 10002 5122358046082560 YES

Alan Squire 10003 5595968943757920 YES

Mike Anderson 10004 4929889576357400 YES

Annie Schmidt 10005 4556988708236902 YES

Elliott Meyer 10006 374366599711820 YES

Celine Smith 10007 4716898533036 YES

Steve Haslam 10008 340975900376858 YES

Albert Einstein 10009 310654305412389 YES

SQL> conn sys/oracle@prod4 as sysdba

Connected.

SQL> alter system set encryption wallet close identified by “trsen”;

alter system set encryption wallet close identified by “trsen”

*

ERROR at line 1:

ORA-28365: wallet is not open

SQL> conn trsen/trsen

Connected.

SQL> select * from trsen;=========>>>>发现encryption wallet close时，加密列无法查询，非加密列可以正常查询

select * from trsen

*

ERROR at line 1:

ORA-28365: wallet is not open

5.创建加密tablespace

SQL> CREATE TABLESPACE encrypt

2 DATAFILE ‘/u01/app/oracle/oradata/PROD4/encrypt.dbff’

3 SIZE 150M

4 ENCRYPTION USING ‘3DES168’

5 DEFAULT STORAGE(ENCRYPT);

Tablespace created.

将之前加密的表move到encryption wallet的表空间里,关闭wallet后发现没有加密的列也不能做查询操作

alter table trsen.trsen move tablespace encrypt;

alter system set encryption wallet close identified by “trsen”;

SQL> select first_name from trsen;

select first_name from trsen

*

ERROR at line 1:

ORA-28365: wallet is not open