-
Oracle 透明数据加密（TDE）的常见任务
环境准备
一个容器数据库，带一个PDB：orclpdb1。
目前没有进行任何加密设置。SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 ORCLPDB1 READ WRITE NO SQL> show parameter wallet_root; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ wallet_root string SQL> show parameter tde_configuration NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ tde_configuration string- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
配置Key Store
connect / as sysdba ALTER SYSTEM SET wallet_root='$ORACLE_BASE/wallet' SCOPE=SPFILE; shutdown immediate; startup !mkdir $ORACLE_BASE/wallet ALTER SYSTEM SET tde_configuration="keystore_configuration=file" SCOPE=BOTH;- 1
- 2
- 3
- 4
- 5
- 6
目前位置，$ORACLE_BASE/wallet中还没有任何文件。
创建Key Store
connect / as sysdba ADMINISTER KEY MANAGEMENT CREATE KEYSTORE IDENTIFIED BY keypwd;- 1
- 2
此时，key store文件有了：
SQL> !ls $ORACLE_BASE/wallet tde SQL> !ls $ORACLE_BASE/wallet/tde ewallet.p12- 1
- 2
- 3
- 4
- 5
但key store的状态是关闭的：
SQL> select con_id, status from V$ENCRYPTION_WALLET; CON_ID STATUS ---------- ------------------------------ 1 CLOSED 2 CLOSED 3 CLOSED- 1
- 2
- 3
- 4
- 5
- 6
- 7
打开key store：
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY keypwd;- 1
此时，根数据库的key store状态变成OPEN_NO_MASTER_KEY：
SQL> select con_id, status from V$ENCRYPTION_WALLET; CON_ID STATUS ---------- ------------------------------ 1 OPEN_NO_MASTER_KEY 2 CLOSED 3 CLOSED- 1
- 2
- 3
- 4
- 5
- 6
- 7
创建Master Key：
ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE IDENTIFIED BY keypwd WITH BACKUP USING 'mekbkp';- 1
- 2
- 3
- 4
我们可以看到备份，而且key store的状态变为：
SQL> !ls $ORACLE_BASE/wallet/tde ewallet_2023091407054383_mekbkp.p12 ewallet.p12 SQL> select con_id, status from V$ENCRYPTION_WALLET; CON_ID STATUS ---------- ------------------------------ 1 OPEN 2 CLOSED 3 CLOSED- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
当前系统表状态
Oracle 19c Advanced Security Guide 文档列出了TDE相关的系统表。我们大致看一下。
V$ENCRYPTION_WALLET和key store相关：
SQL> set lines 120 col status for a10 select CON_ID, WRL_TYPE, STATUS, WALLET_TYPE, WALLET_ORDER, KEYSTORE_MODE from V$ENCRYPTION_WALLET; CON_ID WRL_TYPE STATUS WALLET_TYPE WALLET_OR KEYSTORE ---------- -------------------- ---------- -------------------- --------- -------- 1 FILE OPEN PASSWORD SINGLE NONE 2 FILE CLOSED UNKNOWN SINGLE UNITED 3 FILE CLOSED UNKNOWN SINGLE UNITED- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
V$DATABASE_KEY_INFO与系统表空间加密有关：
SQL> select * from V$DATABASE_KEY_INFO; ENCRYPT ENCRYPTEDKEY ------- ------------------------------------------------------------------------------------------------ MASTERKEYID MAS CON_ID -------------------------------- --- ---------- AES128 77B4410C25AFD59E983669101DE55EB20000000000000000000000000000000000000000000000000000000000000000 24F4F8FE12434F18BF88049E85E70C82 YES 1 NONE 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 00000000000000000000000000000000 NO 2 NONE 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 00000000000000000000000000000000 NO 3- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
V$ENCRYPTION_KEYS和master key有关：
col creation_time for a20 col activation_time for a20 col creator_pdbname for a12 col origin for a10 alter session set nls_date_format = 'MM/DD/YYYY HH24:MI'; select cast(creation_time as date) as creation_time, cast(activation_time as date) as activation_time , key_use, keystore_type, origin, backed_up, creator_pdbname from V$ENCRYPTION_KEYS; CREATION_TIME ACTIVATION_TIME KEY_USE KEYSTORE_TYPE ORIGIN BACKED_UP CREATOR_PDBN -------------------- -------------------- ---------- ----------------- ---------- --------- ------------ 09/14/2023 07:05 09/14/2023 07:05 TDE IN PDB SOFTWARE KEYSTORE LOCAL NO CDB$ROOT- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
加解密PDB中的表空间
连接到PDB。
SQL> connect sys@orclpdb1 as sysdba Enter password: Connected.- 1
- 2
- 3
目前PDB中还没有master key，因此无法加密：
SQL> alter tablespace users encryption online encrypt; alter tablespace users encryption online encrypt * ERROR at line 1: ORA-28361: master key not yet set SQL> select status from V$ENCRYPTION_WALLET; STATUS ---------- CLOSED- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
设置PDB中的master key：
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY keypwd; ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY keypwd WITH BACKUP USING 'pdbmekbkp';- 1
- 2
现在PDB的master key已经设置好：
SQL> select status from V$ENCRYPTION_WALLET; STATUS ------------------------------ OPEN SQL> !ls -l1 $ORACLE_BASE/wallet/tde total 16 -rw-------. 1 oracle oinstall 2555 Sep 14 07:05 ewallet_2023091407054383_mekbkp.p12 -rw-------. 1 oracle oinstall 3995 Sep 14 08:42 ewallet_2023091408425331_pdbmekbkp.p12 -rw-------. 1 oracle oinstall 5467 Sep 14 08:42 ewallet.p12- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
现在加解密都没有问题了：
-- 在线加密 SQL> alter tablespace users encryption online encrypt; Tablespace altered. SQL> alter tablespace users encryption online decrypt; Tablespace altered. -- 离线加密 SQL> alter tablespace users offline; Tablespace altered. SQL> alter tablespace users encryption offline encrypt; Tablespace altered. SQL> alter tablespace users online; Tablespace altered.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
当前系统表状态
以下SQL语句均在CDB$ROOT中执行。
V$ENCRYPTION_WALLET和key store相关，其中PDB相关的行变化了：
SQL> set lines 120 col status for a10 select CON_ID, WRL_TYPE, STATUS, WALLET_TYPE, WALLET_ORDER, KEYSTORE_MODE from V$ENCRYPTION_WALLET; CON_ID WRL_TYPE STATUS WALLET_TYPE WALLET_OR KEYSTORE ---------- -------------------- ---------- -------------------- --------- -------- 1 FILE OPEN PASSWORD SINGLE NONE 2 FILE CLOSED UNKNOWN SINGLE UNITED 3 FILE OPEN PASSWORD SINGLE UNITED- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
V$DATABASE_KEY_INFO与系统表空间加密有关，也是和PDB相关的行变化了：
SQL> select * from V$DATABASE_KEY_INFO; ENCRYPT ENCRYPTEDKEY ------- ------------------------------------------------------------------------------------------------ MASTERKEYID MAS CON_ID -------------------------------- --- ---------- AES128 77B4410C25AFD59E983669101DE55EB20000000000000000000000000000000000000000000000000000000000000000 24F4F8FE12434F18BF88049E85E70C82 YES 1 NONE 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 00000000000000000000000000000000 NO 2 AES128 69A8A389784AFCD06F84FE3EB12F3E8A0000000000000000000000000000000000000000000000000000000000000000 64D9F54F8A354F36BFECB3955CDD77DA YES 3- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
V$ENCRYPTION_KEYS和master key有关：
col creation_time for a20 col activation_time for a20 col creator_pdbname for a12 col origin for a10 alter session set nls_date_format = 'MM/DD/YYYY HH24:MI'; select cast(creation_time as date) as creation_time, cast(activation_time as date) as activation_time , key_use, keystore_type, origin, backed_up, creator_pdbname from V$ENCRYPTION_KEYS; CREATION_TIME ACTIVATION_TIME KEY_USE KEYSTORE_TYPE ORIGIN BACKED_UP CREATOR_PDBN -------------------- -------------------- ---------- ----------------- ---------- --------- ------------ 09/14/2023 07:05 09/14/2023 07:05 TDE IN PDB SOFTWARE KEYSTORE LOCAL YES CDB$ROOT 09/14/2023 08:42 09/14/2023 08:42 TDE IN PDB SOFTWARE KEYSTORE LOCAL NO ORCLPDB1- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
以下SQL语句均在PDB中执行：
SQL> select encrypted from user_tablespaces where tablespace_name = 'USERS'; ENC --- YES SQL> select TS#, ENCRYPTIONALG, ENCRYPTEDTS, STATUS, CON_ID from V$ENCRYPTED_TABLESPACES; TS# ENCRYPT ENC STATUS CON_ID ---------- ------- --- ---------- ---------- 5 AES128 YES NORMAL 3- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
查询Key Store的类型
自动登录为AUTOLOGIN。需要口令打开则为PASSWORD。
SQL> select CON_ID, STATUS, WALLET_TYPE from V$ENCRYPTION_WALLET; CON_ID STATUS WALLET_TYPE ---------- -------------------- -------------------- 1 OPEN AUTOLOGIN 2 OPEN AUTOLOGIN 3 OPEN AUTOLOGIN 4 OPEN_NO_MASTER_KEY AUTOLOGIN- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
Key Store改为自动登录
目前表空间可以加解密，但有一个问题。即如果数据库重启，我们还需要手工打开Key Store。
connect / as sysdba ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE IDENTIFIED BY keypwd;- 1
- 2
- 3
- 4
现在Key Store的WALLET_TYPE由PASSWORD变为AUTOLOGIN：
set lines 120 col status for a10 select CON_ID, WRL_TYPE, STATUS, WALLET_TYPE, WALLET_ORDER, KEYSTORE_MODE from V$ENCRYPTION_WALLET; CON_ID WRL_TYPE STATUS WALLET_TYPE WALLET_OR KEYSTORE ---------- -------------------- ---------- -------------------- --------- -------- 1 FILE OPEN AUTOLOGIN SINGLE NONE 2 FILE OPEN AUTOLOGIN SINGLE UNITED 3 FILE OPEN AUTOLOGIN SINGLE UNITED- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
重启数据库后，Key Store状态自动变为打开：
SQL> select status from V$ENCRYPTION_WALLET; STATUS ---------- OPEN OPEN OPEN SQL> !ls -l1 $ORACLE_BASE/wallet/tde total 24 -rw-------. 1 oracle oinstall 5512 Sep 14 08:59 cwallet.sso -rw-------. 1 oracle oinstall 2555 Sep 14 07:05 ewallet_2023091407054383_mekbkp.p12 -rw-------. 1 oracle oinstall 3995 Sep 14 08:42 ewallet_2023091408425331_pdbmekbkp.p12 -rw-------. 1 oracle oinstall 5467 Sep 14 08:42 ewallet.p12- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
自动登录的Key Store文件为cwallet.sso。
备份Key Store
此操作需在CDB$ROOT中进行，否则报错：
SQL> ADMINISTER KEY MANAGEMENT BACKUP KEYSTORE USING 'bkp230914' IDENTIFIED BY keypwd; ADMINISTER KEY MANAGEMENT BACKUP KEYSTORE USING 'bkp230914' IDENTIFIED BY keypwd * ERROR at line 1: ORA-65040: operation not allowed from within a pluggable database- 1
- 2
- 3
- 4
- 5
必须用FORCE KEYSTORE子句：
SQL> ADMINISTER KEY MANAGEMENT BACKUP KEYSTORE USING 'bkp230914' IDENTIFIED BY keypwd; ADMINISTER KEY MANAGEMENT BACKUP KEYSTORE USING 'bkp230914' IDENTIFIED BY keypwd * ERROR at line 1: ORA-28417: password-based keystore is not open SQL> ADMINISTER KEY MANAGEMENT BACKUP KEYSTORE USING 'bkp230914' FORCE KEYSTORE IDENTIFIED BY keypwd; keystore altered.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
查看生成的备份文件，看来在tag中加入日期是多此一举：
SQL> !ls -l1 $ORACLE_BASE/wallet/tde total 32 -rw-------. 1 oracle oinstall 5512 Sep 14 09:04 cwallet.sso -rw-------. 1 oracle oinstall 2555 Sep 14 07:05 ewallet_2023091407054383_mekbkp.p12 -rw-------. 1 oracle oinstall 3995 Sep 14 08:42 ewallet_2023091408425331_pdbmekbkp.p12 -rw-------. 1 oracle oinstall 5467 Sep 14 09:04 ewallet_2023091409040585_bkp230914.p12 -rw-------. 1 oracle oinstall 5467 Sep 14 09:04 ewallet.p12- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
也可以备份到指定位置：
SQL> connect / as sysdba; Connected. SQL> ADMINISTER KEY MANAGEMENT BACKUP KEYSTORE USING 'bkp230914' FORCE KEYSTORE IDENTIFIED BY keypwd to '/tmp'; keystore altered. SQL> !ls /tmp/*bkp* /tmp/ewallet_2023091402173955_bkp230914.p12- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
关闭Key Store
ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY keypwd;- 1
打开Key Store
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY keypwd;- 1
只有先打开root container的key store后，才能打开PDB中的key store。
修改Wallet类型为Local Auto Login
当前类型为Auto Login，但Local Auto Login更安全，因为只能在本机使用。
SQL> select WALLET_TYPE from V$ENCRYPTION_WALLET; WALLET_TYPE -------------------- AUTOLOGIN- 1
- 2
- 3
- 4
- 5
过程：
-- 关闭key store SQL> administer key management set keystore close; keystore altered. SQL> show parameter wallet_root NAME TYPE VALUE ------------------------------------ ----------- ---------------------------------- wallet_root string /u01/app/oracle/admin/ORCL/wallet -- 必须将原有的auto login key store移走，否则后续建立时报错 -- ORA-46630: keystore cannot be created at the specified location SQL> !cd /u01/app/oracle/admin/ORCL/wallet SQL> !mv cwallet.sso cwallet.sso.bak SQL> administer key management set keystore open force keystore identified by keypwd; keystore altered. SQL> administer key management create local auto_login keystore from keystore identified by keypwd; keystore altered.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
修改Key Store的口令
修改口令可以不备份，但必须使用FORCE KEYSTORE打开Key Store：
connect / as sysdba ADMINISTER KEY MANAGEMENT ALTER KEYSTORE PASSWORD FORCE KEYSTORE IDENTIFIED BY keypwd SET newkeypwd;- 1
- 2
- 3
- 4
- 5
- 6
- 7
修改口令不影响加解密操作。
不知为何，修改口令时，第一次不要求备份，而第二次要求：
ADMINISTER KEY MANAGEMENT ALTER KEYSTORE PASSWORD FORCE KEYSTORE IDENTIFIED BY newkeypwd 5 SET keypwd; ADMINISTER KEY MANAGEMENT ALTER KEYSTORE PASSWORD * ERROR at line 1: ORA-46631: keystore needs to be backed up SQL> ADMINISTER KEY MANAGEMENT ALTER KEYSTORE PASSWORD FORCE KEYSTORE IDENTIFIED BY newkeypwd SET keypwd WITH BACKUP USING 'chgpwd'; SQL> !ls -l1 $ORACLE_BASE/wallet/tde total 40 -rw-------. 1 oracle oinstall 5512 Sep 14 09:20 cwallet.sso -rw-------. 1 oracle oinstall 2555 Sep 14 07:05 ewallet_2023091407054383_mekbkp.p12 -rw-------. 1 oracle oinstall 3995 Sep 14 08:42 ewallet_2023091408425331_pdbmekbkp.p12 -rw-------. 1 oracle oinstall 5467 Sep 14 09:04 ewallet_2023091409040585_bkp230914.p12 -rw-------. 1 oracle oinstall 5467 Sep 14 09:20 ewallet_2023091409204262_chgpwd.p12 -rw-------. 1 oracle oinstall 5467 Sep 14 09:20 ewallet.p12- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
列出所有的Master Key
查询视图，文档见这里：
select count(*) from V$ENCRYPTION_KEYS;- 1
orapki也可以显示wallet的内容，不过暂时看不懂：
$ orapki wallet display -wallet ewallet.p12 Oracle PKI Tool Release 19.0.0.0.0 - Production Version 19.4.0.0.0 Copyright (c) 2004, 2023, Oracle and/or its affiliates. All rights reserved. Enter wallet password: Requested Certificates: Subject: CN=oracle User Certificates: Oracle Secret Store entries: ORACLE.SECURITY.DB.ENCRYPTION.AUZsXbDy0E9Kv89JLoHrIhUAAAAAAAAAAAAAAAAAAAAAAAAAAAAA ORACLE.SECURITY.DB.ENCRYPTION.AWnUPXcte0+Mvx/GHKD7w30AAAAAAAAAAAAAAAAAAAAAAAAAAAAA ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY.079124B6FE41560CE06500001703C3BC ORACLE.SECURITY.ID.ENCRYPTION. ORACLE.SECURITY.KB.ENCRYPTION. ORACLE.SECURITY.KM.ENCRYPTION.AUZsXbDy0E9Kv89JLoHrIhUAAAAAAAAAAAAAAAAAAAAAAAAAAAAA ORACLE.SECURITY.KM.ENCRYPTION.AWnUPXcte0+Mvx/GHKD7w30AAAAAAAAAAAAAAAAAAAAAAAAAAAAA ORACLE.SECURITY.KT.ENCRYPTION.AWnUPXcte0+Mvx/GHKD7w30AAAAAAAAAAAAAAAAAAAAAAAAAAAAA Trusted Certificates:- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
后续再看帮助吧。
Master Key的 Rekey（轮换）
Rekey操作会新建一个master key，并激活他。
CDB$ROOT和PDB有各自的Master Key，本例仅针对PDB。
SQL> alter session set container=orclpdb1; Session altered. col creation_time for a20 col activation_time for a20 col creator_pdbname for a12 col origin for a10 alter session set nls_date_format = 'MM/DD/YYYY HH24:MI'; select cast(creation_time as date) as creation_time, cast(activation_time as date) as activation_time , key_use, keystore_type, origin, backed_up, creator_pdbname from V$ENCRYPTION_KEYS; CREATION_TIME ACTIVATION_TIME KEY_USE KEYSTORE_TYPE ORIGIN BACKED_UP CREATOR_PDBN -------------------- -------------------- ---------- ----------------- ---------- --------- ------------ 09/14/2023 08:42 09/14/2023 08:42 TDE IN PDB SOFTWARE KEYSTORE LOCAL YES ORCLPDB1- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
执行rekey操作：
ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY FORCE KEYSTORE IDENTIFIED BY keypwd WITH BACKUP USING 'mekrekey'; keystore altered. SQL> !ls -l1 $ORACLE_BASE/wallet/tde total 48 -rw-------. 1 oracle oinstall 6776 Sep 14 09:28 cwallet.sso -rw-------. 1 oracle oinstall 2555 Sep 14 07:05 ewallet_2023091407054383_mekbkp.p12 -rw-------. 1 oracle oinstall 3995 Sep 14 08:42 ewallet_2023091408425331_pdbmekbkp.p12 -rw-------. 1 oracle oinstall 5467 Sep 14 09:04 ewallet_2023091409040585_bkp230914.p12 -rw-------. 1 oracle oinstall 5467 Sep 14 09:20 ewallet_2023091409204262_chgpwd.p12 -rw-------. 1 oracle oinstall 5467 Sep 14 09:28 ewallet_2023091409281769_mekrekey.p12 -rw-------. 1 oracle oinstall 6731 Sep 14 09:28 ewallet.p12- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
此时，V$ENCRYPTION_KEYS表新增了一条记录：
CREATION_TIME ACTIVATION_TIME KEY_USE KEYSTORE_TYPE ORIGIN BACKED_UP CREATOR_PDBN -------------------- -------------------- ---------- ----------------- ---------- --------- ------------ 09/14/2023 08:42 09/14/2023 08:42 TDE IN PDB SOFTWARE KEYSTORE LOCAL YES ORCLPDB1 09/14/2023 09:28 09/14/2023 09:28 TDE IN PDB SOFTWARE KEYSTORE LOCAL NO ORCLPDB1- 1
- 2
- 3
- 4
很容易猜到，ACTIVATION_TIME最新，BACKED_UP为NO的应该就是当前的Master Key。
加密Key的Rekey
本例也是针对PDB。
Master Key的rekey不会重新解密和加密数据，但DEK（Data Encryption Key）的rekey会。
表空间加密使用alter tablespace，表加密则使用alter table。本例为前者，并使用在线的rekey：
SQL> alter tablespace users encryption rekey; Tablespace altered. Elapsed: 00:01:23.42- 1
- 2
- 3
- 4
- 5
创建和激活Master Key
这个分两步进行的操作实际就是Rekey的过程。
虽然也可以针对CDB$ROOT，但本例针对PDB。
SQL> ADMINISTER KEY MANAGEMENT CREATE KEY USING TAG 'newmek' FORCE KEYSTORE IDENTIFIED BY keypwd WITH BACKUP USING 'newmek'; SQL> !ls -l1 $ORACLE_BASE/wallet/tde total 60 -rw-------. 1 oracle oinstall 8216 Sep 14 09:58 cwallet.sso -rw-------. 1 oracle oinstall 2555 Sep 14 07:05 ewallet_2023091407054383_mekbkp.p12 -rw-------. 1 oracle oinstall 3995 Sep 14 08:42 ewallet_2023091408425331_pdbmekbkp.p12 -rw-------. 1 oracle oinstall 5467 Sep 14 09:04 ewallet_2023091409040585_bkp230914.p12 -rw-------. 1 oracle oinstall 5467 Sep 14 09:20 ewallet_2023091409204262_chgpwd.p12 -rw-------. 1 oracle oinstall 5467 Sep 14 09:28 ewallet_2023091409281769_mekrekey.p12 -rw-------. 1 oracle oinstall 6731 Sep 14 09:58 ewallet_2023091409580241_newmek.p12 -rw-------. 1 oracle oinstall 8171 Sep 14 09:58 ewallet.p12- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
可以，看到此Master Key并没有激活（ACTIVATION_TIME为空）：
CREATION_TIME ACTIVATION_TIME KEY_USE KEYSTORE_TYPE ORIGIN BACKED_UP CREATOR_PDBN -------------------- -------------------- ---------- ----------------- ---------- --------- ------------ 09/14/2023 09:28 09/14/2023 09:28 TDE IN PDB SOFTWARE KEYSTORE LOCAL YES ORCLPDB1 09/14/2023 08:42 09/14/2023 08:42 TDE IN PDB SOFTWARE KEYSTORE LOCAL YES ORCLPDB1 09/14/2023 09:58 TDE IN PDB SOFTWARE KEYSTORE LOCAL NO ORCLPDB1- 1
- 2
- 3
- 4
- 5
激活Master Key：
SQL> select key_id from V$ENCRYPTION_KEYS where ACTIVATION_TIME is null; KEY_ID ------------------------------------------------------------------------------ AYgYXF7JY08WvylfJIZ44LUAAAAAAAAAAAAAAAAAAAAAAAAAAAAA SQL> ADMINISTER KEY MANAGEMENT USE KEY 'AYgYXF7JY08WvylfJIZ44LUAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' FORCE KEYSTORE IDENTIFIED BY keypwd WITH BACKUP USING 'newmek'; keystore altered. SQL> !ls -l1 $ORACLE_BASE/wallet/tde total 68 -rw-------. 1 oracle oinstall 8216 Sep 14 10:11 cwallet.sso -rw-------. 1 oracle oinstall 2555 Sep 14 07:05 ewallet_2023091407054383_mekbkp.p12 -rw-------. 1 oracle oinstall 3995 Sep 14 08:42 ewallet_2023091408425331_pdbmekbkp.p12 -rw-------. 1 oracle oinstall 5467 Sep 14 09:04 ewallet_2023091409040585_bkp230914.p12 -rw-------. 1 oracle oinstall 5467 Sep 14 09:20 ewallet_2023091409204262_chgpwd.p12 -rw-------. 1 oracle oinstall 5467 Sep 14 09:28 ewallet_2023091409281769_mekrekey.p12 -rw-------. 1 oracle oinstall 6731 Sep 14 09:58 ewallet_2023091409580241_newmek.p12 -rw-------. 1 oracle oinstall 8171 Sep 14 10:11 ewallet_2023091410112509_newmek.p12 -rw-------. 1 oracle oinstall 8171 Sep 14 10:11 ewallet.p12- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
系统表中的状态变了。
CREATION_TIME ACTIVATION_TIME KEY_USE KEYSTORE_TYPE ORIGIN BACKED_UP CREATOR_PDBN -------------------- -------------------- ---------- ----------------- ---------- --------- ------------ 09/14/2023 09:28 09/14/2023 09:28 TDE IN PDB SOFTWARE KEYSTORE LOCAL YES ORCLPDB1 09/14/2023 08:42 09/14/2023 08:42 TDE IN PDB SOFTWARE KEYSTORE LOCAL YES ORCLPDB1 09/14/2023 09:58 09/14/2023 10:11 TDE IN PDB SOFTWARE KEYSTORE LOCAL NO ORCLPDB1- 1
- 2
- 3
- 4
- 5
为Master Key设置标签
ADMINISTER KEY MANAGEMENT SET TAG 'expdpkey' FOR 'AWnUPXcte0+Mvx/GHKD7w30AAAAAAAAAAAAAAAAAAAAAAAAAAAAA' FORCE KEYSTORE IDENTIFIED BY keypwd WITH BACKUP;- 1
- 2
- 3
- 4
- 5
导出Master Key
ADMINISTER KEY MANAGEMENT EXPORT KEYS WITH SECRET "my_secret" TO '/tmp/export.exp' IDENTIFIED BY keypwd- 1
- 2
- 3
- 4
- 5
以上是导出所有的master key，其中的secret是用来加密master key的。也可以通过tag或key id选择master key。
在SQL尾部加入以下即可：
WITH IDENTIFIER IN (SELECT KEY_ID FROM V$ENCRYPTION_KEYS WHERE TAG IN ('expdpkey'));- 1
- 2
带选择条件的导出只能在cdb$root中执行。
示例可参考这里
导入Master Key
ADMINISTER KEY MANAGEMENT IMPORT KEYS WITH SECRET "my_secret" FROM '/tmp/export.exp' IDENTIFIED BY keypwd WITH BACKUP;- 1
- 2
- 3
- 4
- 5
目前导入CDB$ROOT成功了，在PDB中执行则报错，原因不明
参考
-
相关阅读:
一篇解决Elasticsearch全部问题。
Qt第三方库QicsTable简单实例（1）
教育教学论坛杂志教育教学论坛杂志社教育教学论坛编辑部2022年第39期目录
Double Q-learning
java计算机毕业设计家校沟通程序MyBatis+系统+LW文档+源码+调试部署
【黑马-SpringCloud技术栈】【02】服务拆分及远程调用_服务提供者与消费者
Redis的介绍及如何在Linux上安装redis、常用命令、Java连接操作Redis
IT这个岗位，人才缺口百万，薪资水涨船高，上不封顶
vue脚手架vue-cli的卸载与安装方式
Flink集群部署
- 原文地址：https://blog.csdn.net/stevensxiao/article/details/132868513
