oracle 12C CDB下开启wallet

环境:

OS:Centos 7

DB:12.2.0.1 开启了cdb

 

1.Setup a Keystore（密钥库） Location:
# mkdir -pv /etc/ORACLE/hxl/encryption_keystore
# cd /etc
# chown -R oracle:oinstall ORACLE
# chmod -R 700 ORACLE
编辑"$ORACLE_HOME/network/admin/sqlnet.ora"文件, 增加下面的记录：

ENCRYPTION_WALLET_LOCATION =
(SOURCE =(METHOD = FILE)
(METHOD_DATA =(DIRECTORY = /etc/ORACLE/hxl/encryption_keystore/))
)

2.Create a Keystore(密钥库):
sqlplus /nolog
connect / as sysdba
sql> administer key management create keystore '/etc/oracle/hxl/encryption_keystore/' identified by "oracle";

查看生成的文件
SQL> host ls -l /etc/ORACLE/hxl/encryption_keystore


3.Open the Keystore(密钥库)
需要在root container打开密钥库，如果没有使用CONTAINER=ALL 只影响当前的container.状态发生改变
sql>alter system set "_db_discard_lost_masterkey"=true;
sql>administer key management set keystore open identified by "oracle" container=all;


如果CLOSE 使用
administer key management set keystore close identified by "oracle" container=all;

4.Create TDE Master Encryption Key:
打开密钥库后还必须要在root container和每个PDB创建一个主密钥，可以使用CONTAINER=ALL一条命令创建.
如果没带还需要在每个PDB中创建，创建后可以在 V$ENCRYPTION_KEYS view查询，同时密钥库状态改变，密钥一定要保管好，每次修改记的备份和异地保存.

SQL> administer key management set key identified by "oracle" with backup container=all;


5.创建加密的表空间
SQL> alter session set container=ORA12CPDB4;
SQL> create tablespace cdb_secure
  2  datafile '/u01/app/oracle/oradata/ora12c/ora12cpdb4/cdb_secure01.dbf'
  3  size 150m
  4  encryption using '3des168'
  5  default storage(encrypt);

Tablespace created.


这个时候是可以查看 Dba_Data_Files视图的

6.尝试关闭秘钥
cdb模式下关闭
SQL> administer key management set keystore close identified by "oracle" container=all;

SQL> alter session set container=ORA12CPDB4;
SQL> select * from Dba_Data_Files;
select * from Dba_Data_Files
              *
ERROR at line 1:
ORA-28365: wallet is not open

发现无法访问了,需要再次打开.

 

7.创建加密表测试

CONN hxl/oracle@pdb4

CREATE TABLE tde_ts_test (
  id    NUMBER(10),
  data  VARCHAR2(50)
) TABLESPACE CDB_SECURE;

INSERT INTO tde_ts_test VALUES (1, 'This is also a secret!');
COMMIT;

 

尝试打开和关闭wallet,分别查询测试表

cdb root模式:

administer key management set keystore close identified by "oracle" container=all;
administer key management set keystore open identified by "oracle" container=all;

 

8.数据库重启后开启自动打开wallet
connect / as sysdba
administer key management create local auto_login keystore from keystore '/etc/ORACLE/hxl/encryption_keystore/' identified by oracle;

可以查看到wallet目录会生成cwallet.sso文件
[oracle@localhost ~]$ ls -al /etc/ORACLE/hxl/encryption_keystore/
total 328
drwx------ 2 oracle oinstall  4096 Mar 20 09:27 .
drwx------ 3 oracle oinstall    33 Mar  6 14:35 ..
-rw------- 1 oracle oinstall 41435 Mar 20 09:27 cwallet.sso
-rw------- 1 oracle oinstall 41392 Mar 19 16:43 ewallet.p12

 

相关查询视图:

SQL> set linesize 200
SQL> COLUMN wrl_parameter FORMAT A50
SQL> SELECT * FROM v$encryption_wallet;



SQL> SET LINESIZE 100
SQL> SELECT con_id, key_id FROM v$encryption_keys;

 

-- The End --