Oracle12C TDE wallet加密方法

在Oracle 12C中 TDE wallet加密使用了完全不同的管理wallet和master keys的接口，本文测试在RAC环境下如何设置TED加密以及数据库重启后如何自动开启Wallet
RAC环境在所有节点都执行步，第二步在一个节点生产wallet拷贝到其他节点即可。
1. 在文件$ORACLE_HOME/network/admin/sqlnet.or中设置 ENCRYPTION_WALLET_LOCATION
cat /oracle/db/base/product/12.2/network/admin/sqlnet.ora

ENCRYPTION_WALLET_LOCATION=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY=/home/oracle/tde12c)))

2. 创建keystore
SQL> select * from v$encryption_wallet


WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_ORDER FULLY_BACKED_UP CON_ID
-------------------- -------------------- -------------------- -------------------- ------------------ ------------------ ----------
FILE /home/oracle/tde12c/ NOT_AVAILABLE UNKNOWN SINGLE UNDE

SQL> administer key management create keystore '/home/oracle/tde12c/' identified by "oracle1234" ;

keystore altered.

SQL> host ls -lrt /home/oracle/tde12c
total 4
-rw------- 1 oracle dba 2400 Feb 7 09:08 ewallet.p12 <<<<<<wallet


SQL> select * from v$encryption_wallet;

WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_ORDER FULLY_BACKED_UP CON_ID
-------------------- -------------------- -------------------- -------------------- ------------------ ------------------ ----------
FILE /home/oracle/tde12c/ CLOSED UNKNOWN SINGLE UNDEFINED

创建keystore后STATUS 为CLOSED，如若使用加密需要打开

3. 打开keystore:

SQL> administer key management set keystore open identified by "oracle1234";

keystore altered.

SQL> select * from v$encryption_wallet;

WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_ORDER FULLY_BACKED_UP CON_ID
-------------------- -------------------- -------------------- -------------------- ------------------ ------------------ ----------
FILE /home/oracle/tde12c/ OPEN_NO_MASTER_KEY PASSWORD SINGLE UNDEFINED 0

这里提STATUS为OPEN_NO_MASTER_KEY ，且 WALLET_TYPE为PASSWORD

4. 创建 master key:

SQL> administer key management create key identified by "oracle1234" with backup;

keystore altered.

col WRL_TYPE for a20
col WRL_PARAMETER for a20
col WALLET_TYPE for a20
col STATUS for a20
set line 200

select key_id,activation_time from v$encryption_keys；

KEY_ID ACTIVATION_TIME
--------------------------------------------------------------------------------- ----------------------------------------
AbMTSPJoZE+8v+Yhk2+U5G8AAAAAAAAAAAAAAAAAAAAAAAAAAAAA


5激活master key(加密表或者表空间的必须操作）
SQL> select key_id,activation_time from v$encryption_keys;

KEY_ID ACTIVATION_TIME
------------------------------------------------------- ----------------------------------------
AbMTSPJoZE+8v+Yhk2+U5G8AAAAAAAAAAAAAAAAAAAAAAAAAAAAA 07-FEB-21 09.21.04.482110 AM +08:00

administer key management use key 'AbMTSPJoZE+8v+Yhk2+U5G8AAAAAAAAAAAAAAAAAAAAAAAAAAAAA' identified by "oracle1234" with backup;

master key激活后wallet状态为OPEN
col WRL_TYPE for a20
col WRL_PARAMETER for a20
col WALLET_TYPE for a20
col STATUS for a20
set line 200
select * from v$encryption_wallet;

WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_ORDER FULLY_BACKED_UP CON_ID
-------------------- -------------------- -------------------- -------------------- ------------------ ------------------ ----------
FILE /home/oracle/tde12c/ OPEN PASSWORD SINGLE NO 0

这里有个问题，就是当数据库重启时，需要手工打开Wallet，否则存储在加密表空间中的数据用户无法访问。
打开keystore
administer key management set keystore open identified by "oracle1234";


其他相关操作
修改keystore密码
SQL> administer key management alter keystore password identified by "<password>" set "<new password>" with backup using 'change';

keystore altered.
备份keystores
SQL> ADMINISTER KEY MANAGEMENT BACKUP KEYSTORE USING 'tde12c' IDENTIFIED BY "<new password>" to '/home/oracle';

keystore altered.

关闭keystore
SQL> administer key management set keystore close identified by "oracle1234";

keystore altered.

打开keystore
administer key management set keystore open identified by "oracle1234";

8.数据库重启后开启自动打开wallet，每个节点执行一次
connect / as sysdba
administer key management create auto_login keystore from keystore '/home/oracle/tde12c/' identified by "oracle1234";

col WRL_TYPE for a20
col WRL_PARAMETER for a20
col WALLET_TYPE for a20
col STATUS for a20
set line 200
select * from v$encryption_wallet;SQL>

WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_ORDER FULLY_BACKED_UP CON_ID
-------------------- --------------------------------- -------------------- -------------------- ------------------ ------------------ ----------
FILE /home/oracle/tde12c/ OPEN AUTOLOGIN SINGLE NO 0

如果要关auto-open wallet，需要删除cwallet.sso文件，在关闭，而后打开基于password的wallet