oracle TDE使用(mkstore方式)
环境:
Os:Centos 7
DB:11.2.0.4
说明：创建了wallet后一定要备份wallet目录下的文件,否则丢失了的话，使用该wallet加密的数据和表空间也会丢失.
1.先要创建一个"wallet钱包",这个钱包里面保存着密钥,Oracle就是通过这个密钥对列进行加密和解密的.
su - oracle
[oracle@rac01 ~]$ mkdir $ORACLE_BASE/wallet
[oracle@rac01 ~]$ cd $ORACLE_BASE/wallet
[oracle@rac01 wallet]$ pwd
/u01/oracle/app/wallet
2.生成wallet钱包之前先要设定wallet钱包的保存位置
设置wallet钱包位置的文件$ORACLE_HOME/network/admin/sqlnet.ora
ENCRYPTION_WALLET_LOCATION=(
SOURCE=(
(METHOD=FILE)
(METHOD_DATA=(DIRECTORY=/u01/oracle/app/wallet))
)
)
3.创建wallet(这种方式创建是自启动的)
[oracle@rac01 wallet]$ mkstore -wrl /u01/oracle/app/wallet -create
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
Enter password:
这里输入密码:oracle@123
4.打开wallet
alter system set encryption wallet open authenticated by "oracle@123";
5.关闭wallet
alter system set wallet close identified by "oracle@123";
6.设置密钥
SQL> alter system set encryption key authenticated by "oracle@123";
alter system set encryption key authenticated by "oracle@123"
*
ERROR at line 1:
ORA-28362: master key not found
该错误可以不用理会,查看如下视图看wallet的状态
select * from v$encryption_wallet;
7.建表
connect hxl/oracle
create table hxl.en_test (id int,name varchar(20),salary number(10,2) encrypt using 'AES256');
insert into hxl.en_test values(1,'hwb',50000.00);
insert into hxl.en_test values(2,'hwb2',30000.00);
commit;
7.删除sso(好像不需要重启)
su - oracle
mkstore -wrl /u01/oracle/app/wallet -deleteSSO
8.重启动数据库(好像不需要重启)
[grid@rac01 ~]$ srvctl stop database -d slnngk
[grid@rac01 ~]$ srvctl start database -d slnngk
9.加密字段修改成非加密的
SQL> alter table hxl.en_test modify (SALARY decrypt);
alter table hxl.en_test modify (SALARY decrypt)
*
ERROR at line 1:
ORA-28365: wallet is not open
SQL> alter system set encryption wallet open authenticated by "oracle@123";
System altered.
SQL> alter table hxl.en_test modify (SALARY decrypt);
Table altered.
SQL> alter system set wallet close identified by "oracle@123";
System altered.
SQL> select * from hxl.en_test;
ID NAME SALARY
---------- -------------------- ----------
1 hwb 50000
2 hwb2 30000
10.重新设置为加密字段
SQL> alter system set encryption wallet open authenticated by "oracle@123";
System altered.
SQL> alter table hxl.en_test modify (SALARY encrypt);
Table altered.
11.修改钱包密码
[oracle@rac01 wallet]$ orapki wallet change_pwd -wallet /u01/oracle/app/wallet
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
Enter wallet password: 旧密码
New password:
Enter wallet password: 新密码
我们这里新密码设置为:oracle@abc123
alter system set encryption wallet close authenticated by "oracle@123";
若原来是打开的,关闭的时候还是需要旧密码关闭
后面打开关闭就使用新密码了
alter system set encryption wallet open authenticated by "oracle@abc123";
alter system set encryption wallet close authenticated by "oracle@abc123";
12.如想开机启动钱包,需要单独添加sso
[oracle@rac01 wallet]$ mkstore -wrl /u01/oracle/app/wallet -createSSO
打开
alter system set encryption wallet open authenticated by "oracle@abc123";
执行关闭命令也不管用了的
alter system set encryption wallet close authenticated by "oracle@abc123";
set linesize 1000;
column WRL_TYPE format a16;
column WRL_PARAMETER format a64;
column STATUS format a16;
SQL> select WRL_TYPE,WRL_PARAMETER,STATUS from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS
---------------- ---------------------------------------------------------------- ----------------
file /u01/oracle/app/wallet OPEN
关闭所有钱包
SQL> alter system set encryption wallet close;
System altered.
13.删除wallet
su - oracle
mkstore -wrl /u01/oracle/app/wallet -delete
