1.更新 sqlnet.ora 文件以包含一个 ENCRYPTED_WALLET_LOCATION 条目。

打开$ORACLE_HOME/network/admin目录下的sqlnet.ora添加以下条目：

ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=D:\oracle\product\10.2.0\db_1\admin)))

指定万能加密密钥创建的目录。

 

2.创建万能加密密钥

sqlplus /nolog

connect / as sysdba

alter system set key identified by "welcome1";

关闭数据库后需要重新打开密钥

alter system set wallet open identified by "welcome1"; 
 
3.创建测试表并插入数据。

create table cust_payment_info
(first_name varchar2(11),
last_name varchar2(10),
order_number number(5),
credit_card_number varchar2(16) ENCRYPT NO SALT,
active_card varchar2(3));

 

insert into cust_payment_info values
('Jon', 'Oldfield', 10001, '5446959708812985','YES');
insert into cust_payment_info values
('Chris', 'White', 10002, '5122358046082560','YES');
insert into cust_payment_info values
('Alan', 'Squire', 10003, '5595968943757920','YES');
insert into cust_payment_info values
('Mike', 'Anderson', 10004, '4929889576357400','YES');
insert into cust_payment_info values
('Annie', 'Schmidt', 10005, '4556988708236902','YES');
insert into cust_payment_info values
('Elliott', 'Meyer', 10006, '374366599711820','YES');
insert into cust_payment_info values
('Celine', 'Smith', 10007, '4716898533036','YES');
insert into cust_payment_info values
('Steve', 'Haslam', 10008, '340975900376858','YES');
insert into cust_payment_info values
('Albert', 'Einstein', 10009, '310654305412389','YES');

 

4.可以再加密的列上创建索引。

create index cust_payment_info_idx on cust_payment_info (credit_card_number);

 

5.查询和更新

select * from CUST_PAYMENT_INFO where CREDIT_CARD_NUMBER  = '4556988708236902';

update oe.CUST_PAYMENT_INFO set ACTIVE_CARD='NO' where CREDIT_CARD_NUMBER='4556988708236902';

 

6.查看加密的列

select * from user_encrypted_columns;

 

总结：

TDE透明数据加密，因为对SQL来说是透明的，所以要验证是否加密，只能用LogMiner查看日志来验证。它意义在于，数据保存在磁盘上是加密的，如果有人复制了的数据文件，在里面是看不到加密数据的。