如何重建encrytion wallet
宰父志新
当使用TDE(Transparent Data Encryption)
对数据库进行加密时，使用的密钥是随机生成的，这一随机生成的密钥，又会被masterkey进行加密，而masterkey会存放在encryption wallet或者HSM里，
以下内容是
TDE用作加密表列与加密表空间两种场景下重建encryption
wallet的过程（重建一般用于wallet文件以外丢失而又不能恢复的情况
，这种情况是我们最不希望看到的，因为这
必然会导致加密数据的丢失，
定期备份wallet的重要性可想而知）
##新建wallet，将masterkey存放于wallet里
mkdir -p /oradata06/wallet
vi sqlnet.ora
ENCRYPTION_WALLET_LOCATION=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY= /oradata06/wallet)
)
alter system set encryption key identified by abcd_1234;
ls -l /oradata06/wallet
total 16
-rw-r--r-- 1 oracle oinstall 1573 Sep 22 15:43 ewallet.p12
oracle@qc570709b:/home/oracle>mkstore -wrl /oradata06/wallet -list
Oracle Secret Store Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:
Oracle Secret Store entries:
ORACLE.SECURITY.DB.ENCRYPTION.ASS+rtwdxk9Gv/Hs6TIV7pgAAAAAAAAAAAAAAAAAAAAAAAAAAAAA --masterkey
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
ORACLE.SECURITY.TS.ENCRYPTION.BS8N9QmwrZrPOcpY6aJPnZYCAwAAAAAAAAAAAAAAAAAAAAAAAAAA --这行不知道是什么即使重建完masterkey后这行始终不变
create table t1 (id number,col1 varchar2(1) encrypt);
insert into t1 values(1,'A');
commit;
select * from v$encrypted_tablespaces;
no rows selected
select * from enc$;
OBJ# OWNER#
---------- ----------
MKEYID ENCALG
---------------------------------------------------------------- ----------
INTALG
----------
COLKLC
--------------------------------------------------------------------------------
KLCLEN FLAG
---------- ----------
5532091 1
ASS+rtwdxk9Gv/Hs6TIV7pgAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 3
1
417741414141414141414141414141414141414141414454386D533354745A6A726F66365A425A43
2B56456D2F61307266754434474136716331576F456D7146437533516B63396B614D37347A78696C
426361645673773D
88
mkstore -wrl /oradata06/wallet -list
Oracle Secret Store Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:
Oracle Secret Store entries:
ORACLE.SECURITY.DB.ENCRYPTION.ASS+rtwdxk9Gv/Hs6TIV7pgAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
ORACLE.SECURITY.TS.ENCRYPTION.BS8N9QmwrZrPOcpY6aJPnZYCAwAAAAAAAAAAAAAAAAAAAAAAAAAA
mkstore -wrl /oradata06/wallet -viewEntry ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
Oracle Secret Store Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY = ASS+rtwdxk9Gv/Hs6TIV7pgAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
//
//在masterkey用于column encrypt的情况下重建wallet
//
--先备份原先加密的表
create table t1_bak as select * from t1;
--备份完之后的表是不加密的
SQL> desc t1_bak;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
COL1 VARCHAR2(1)
--重命名ewallet.p12文件
cd /oradata06/wallet
mv ewallet.p12 ewallet.p12.old
--重新select t1表发现还能select出来，创建新的加密表也能成功，wallet还处于open状态，因为wallet open后整个内容会被加载到SGA里，所以文件本身的删除暂不会影响到TDE
select * from t1;
select * from v$encryption_wallet
WRL_TYPE WRL_PARAMETER STATUS
-------------------- ---------------------------------------- ------------------
file /oradata06/wallet OPEN
create table t2 (id number,col2 varchar2(1) encrypt);
Table created.
--close Wallet
alter system set wallet close identified by ABCD_1234;
System altered.
SQL> select * from poweruser.t1;
select * from poweruser.t1
*
ERROR at line 1:
ORA-28365: wallet is not open
--重新打开ewallet，终于报错了，直到将wallet从ewallet.p12.old命名回原来的名称ewallet.p12后才能正常open wallet
alter system set wallet open identified by ABCD_1234;
System altered.
--知道了上述特性后按照删除wallet文件->重置masterkey的方式来重建wallet
**删除步骤略去。。。
**下面重置masterkey
alter system set encryption key identified by ABCD_5678;
ERROR at line 1:
ORA-28362: master key not found
select * from v$encryption_wallet
WRL_TYPE WRL_PARAMETER STATUS
-------------------- ------------------------------ ------------------
file /oradata06/wallet OPEN
--虽然报错，但新的wallet文件还是生成了，上面的ORA-28362意指数据库中还存在有使用老的masterkey加密的encryption key，但这个老的masterkey没有包含在当前新建的wallet文件里，
SQL> alter system set encryption key identified by ABCD_5678;
alter system set encryption key identified by ABCD_5678
*
ERROR at line 1:
ORA-28362: master key not found
SQL> alter system set wallet close identified by ABCD_5678;
System altered.
select * from v$encryption_wallet
WRL_TYPE WRL_PARAMETER STATUS
-------------------- ------------------------------ ------------------
file /oradata06/wallet CLOSED
--t1表的masterkeyid还是和原来一样
OBJ# OWNER# MKEYID ENCALG INTALG
---------- ---------- ---------------------------------------------------------------- ---------- ----------
COLKLC
------------------------------------------------------------------------------------------------------------------------
KLCLEN FLAG
---------- ----------
5532091 1 ASS+rtwdxk9Gv/Hs6TIV7pgAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 3 1
417741414141414141414141414141414141414141414454386D533354745A6A726F66365A425A432B56456D2F61307266754434474136716331576F
456D7146437533516B63396B614D37347A78696C426361645673773D
88
5532115 1 ASS+rtwdxk9Gv/Hs6TIV7pgAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 3 1
4177414141414141414141414141414141414141414143496C4B734A38665A4269746C3354444F307154716355434151433233597342474E63416F54
727368394C72597054304D784179696473475372616B36465631303D
88
--重建wallet，该错误表示用来加密column encrypted key的masterkey没有存在于新建的wallet文件中
alter system set encryption key identified by "ABCD_5678";
*
ERROR at line 1:
ORA-28396: rekey of enc$ dictionary table failed
oracle@qc570709b:/oradata06/wallet>ls -rlt
total 16
-rw-r--r-- 1 oracle oinstall 1573 Sep 22 16:44 ewallet.p12
--新的masterkey
oracle@qc570709b:/oradata06/wallet>mkstore -wrl /oradata06/wallet -list
Oracle Secret Store Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:
Oracle Secret Store entries:
ORACLE.SECURITY.DB.ENCRYPTION.AS00Ujj7+U8RvxZt4qnX594AAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.ATctymEWWk/fv60qAOI6VB0AAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
mkstore -wrl /oradata06/wallet -viewEntry ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
Enter wallet password:
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY = AS00Ujj7+U8RvxZt4qnX594AAAAAAAAAAAAAAAAAAAAAAAAAAAAA
--drop掉t1、t2表
drop table t1;
drop table t2;
--再次重置encryption key成功，因为t1、t2表都已经drop掉了，没有masterkey游离于wallet之外了
alter system set encryption key identified by "ABCD_5678";
System altered.
oracle@qc570709b:/oradata06/wallet>mkstore -wrl /oradata06/wallet -list
Oracle Secret Store Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:
Oracle Secret Store entries:
ORACLE.SECURITY.DB.ENCRYPTION.AS00Ujj7+U8RvxZt4qnX594AAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.ATctymEWWk/fv60qAOI6VB0AAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.AW2PpYY4208ivx+gbbg1f+AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.AX+sAfvKQE9Cv0kEE2bOLogAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
//
/masterkey用于表空间加密的情况下重建wallet
//
--创建加密表空间ts_ecrypt03
SQL> alter system set encryption key identified by "ABCD_5678";
System altered.
SQL> create tablespace ts_ecrypt03 datafile '/oradata06/datafile/encrypt03.dbf' size 128m encryption using 'AES128' default storage(encrypt);
Tablespace created.
--创建加密表空间中的表
create table bb (username encrypt using 'AES192',userid,created) tablespace ts_ecrypt03 as select * from all_users;
Table created.
SQL> desc bb;
Name Null? Type
----------------------------------------- -------- ----------------------------
USERNAME NOT NULL VARCHAR2(30) ENCRYPT
USERID NOT NULL NUMBER
CREATED NOT NULL DATE
SQL> select * from enc$;
no rows selected
SQL> select * from v$encrypted_tablespaces;
TS# ENCRYPT ENC
---------- ------- ---
ENCRYTPEDKEY
----------------------------------------------------------------
MASTERKEYID BLOCKS_ENCRYPTED BLOCKS_DECRYPTED
-------------------------------- ---------------- ----------------
59 AES128 YES
2ED20D971328D51A29EA193597DF99BC00000000000000000000000000000000
0906B7C73B864F9DBF120BB2112359D0 2 1
--用来加密TS_ECRYPT03表空间encrypted key所用的masterkey
select name,utl_raw.cast_to_varchar2( utl_encode.base64_encode('01'||substr(mkeyid,1,4))) || utl_raw.cast_to_varchar2( utl_encode.base64_encode(substr(mkeyid,5,length(mkeyid)))) masterkeyid_base64 FROM (select t.name, RAWTOHEX(x.mkid) mkeyid from v$tablespace t, x$kcbtek x where t.ts#=x.ts# and t.name like 'TS_ECRYPT%');
NAME
------------------------------
MASTERKEYID_BASE64
--------------------------------------------------------------------------------
TS_ECRYPT03
AQkGt8c7hk+dvxILshEjWdA=
--加密bb表encrypted key的masterkey和加密TS_ECRYPT03表空间encrypted key所用的masterkey相同
SQL> select mkeyid from enc$;
MKEYID
----------------------------------------------------------------
AQkGt8c7hk+dvxILshEjWdAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
--删除wallet文件
mv ewallet.p12 ewallet.p12.0922_5
--直接重置masterkey，生成新的wallet文件，新的wallet文件里没有包含老的masterkey，所以ORA-28362
alter system set encryption key identified by ABCD_7890;
*
ERROR at line 1:
ORA-28362: master key not found
--上述命令还是生成了新的masterkey
oracle@qc570709b:/oradata06/wallet>mkstore -wrl /oradata06/wallet -viewEntry ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
Oracle Secret Store Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.
Enter wallet password: 90
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY = AWUTpq98gU9Mv9aqMy8ssiAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
--由于新的wallet文件里没有包含ts_ecrypt03创建时使用的masterkey所以下列语句报错
SQL> select * from bb;
select * from bb
*
ERROR at line 1:
ORA-28362: master key not found
--此时创建新的加密表空间也失败
create tablespace ts_ecrypt04 datafile '/oradata06/ecrypt04' size 128m encryption using 'AES128' default storage(encrypt);
*
ERROR at line 1:
ORA-28374: typed master key not found in wallet
--再次重置masterkey后又遇到了ORA-28396，这次是因为encrypted tablespace里的那张加密的表bb
alter system set encryption key identified by ABCD_7890;
*
ERROR at line 1:
ORA-28396: rekey of enc$ dictionary table failed
--把bb表drop，reset masterkey终于恢复正常
SQL> drop table poweruser.bb;
Table dropped.
alter system set encryption key identified by ABCD_7890;
--新的加密表空间也创建成功
create tablespace ts_ecrypt04 datafile '/oradata06/ecrypt04.dbf' size 128m encryption using 'AES128' default storage(encrypt);
Tablespace created.
--查看wallet文件内容
mkstore -wrl /oradata06/wallet -list
Oracle Secret Store Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:
Oracle Secret Store entries:
ORACLE.SECURITY.DB.ENCRYPTION.AUzti1Pbdk8/v2jYUGfEtgcAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.AWUTpq98gU9Mv9aqMy8ssiAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.AZrgeKbxDU9Gv8SedTZpX0UAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
mkstore -wrl /oradata06/wallet -viewEntry ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
Oracle Secret Store Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY = AZrgeKbxDU9Gv8SedTZpX0UAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
--验证两个表空间均已经用上了最新的masterkey
select name,utl_raw.cast_to_varchar2( utl_encode.base64_encode('01'||substr(mkeyid,1,4))) || utl_raw.cast_to_varchar2( utl_encode.base64_encode(substr(mkeyid,5,length(mkeyid)))) masterkeyid_base64 FROM (select t.name, RAWTOHEX(x.mkid) mkeyid from v$tablespace t, x$kcbtek x where t.ts#=x.ts# and t.name like 'TS_ECRYPT%');
NAME
------------------------------
MASTERKEYID_BASE64
--------------------------------------------------------------------------------
TS_ECRYPT03
AZrgeKbxDU9Gv8SedTZpX0U=
TS_ECRYPT04
AZrgeKbxDU9Gv8SedTZpX0U=
##新建wallet，将masterkey存放于wallet里
mkdir -p /oradata06/wallet
vi sqlnet.ora
ENCRYPTION_WALLET_LOCATION=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY= /oradata06/wallet)
)
alter system set encryption key identified by abcd_1234;
ls -l /oradata06/wallet
total 16
-rw-r--r-- 1 oracle oinstall 1573 Sep 22 15:43 ewallet.p12
oracle@qc570709b:/home/oracle>mkstore -wrl /oradata06/wallet -list
Oracle Secret Store Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:
Oracle Secret Store entries:
ORACLE.SECURITY.DB.ENCRYPTION.ASS+rtwdxk9Gv/Hs6TIV7pgAAAAAAAAAAAAAAAAAAAAAAAAAAAAA --masterkey
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
ORACLE.SECURITY.TS.ENCRYPTION.BS8N9QmwrZrPOcpY6aJPnZYCAwAAAAAAAAAAAAAAAAAAAAAAAAAA --这行不知道是什么即使重建完masterkey后这行始终不变
create table t1 (id number,col1 varchar2(1) encrypt);
insert into t1 values(1,'A');
commit;
select * from v$encrypted_tablespaces;
no rows selected
select * from enc$;
OBJ# OWNER#
---------- ----------
MKEYID ENCALG
---------------------------------------------------------------- ----------
INTALG
----------
COLKLC
--------------------------------------------------------------------------------
KLCLEN FLAG
---------- ----------
5532091 1
ASS+rtwdxk9Gv/Hs6TIV7pgAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 3
1
417741414141414141414141414141414141414141414454386D533354745A6A726F66365A425A43
2B56456D2F61307266754434474136716331576F456D7146437533516B63396B614D37347A78696C
426361645673773D
88
mkstore -wrl /oradata06/wallet -list
Oracle Secret Store Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:
Oracle Secret Store entries:
ORACLE.SECURITY.DB.ENCRYPTION.ASS+rtwdxk9Gv/Hs6TIV7pgAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
ORACLE.SECURITY.TS.ENCRYPTION.BS8N9QmwrZrPOcpY6aJPnZYCAwAAAAAAAAAAAAAAAAAAAAAAAAAA
mkstore -wrl /oradata06/wallet -viewEntry ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
Oracle Secret Store Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY = ASS+rtwdxk9Gv/Hs6TIV7pgAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
//
//在masterkey用于column encrypt的情况下重建wallet
//
--先备份原先加密的表
create table t1_bak as select * from t1;
--备份完之后的表是不加密的
SQL> desc t1_bak;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
COL1 VARCHAR2(1)
--重命名ewallet.p12文件
cd /oradata06/wallet
mv ewallet.p12 ewallet.p12.old
--重新select t1表发现还能select出来，创建新的加密表也能成功，wallet还处于open状态，因为wallet open后整个内容会被加载到SGA里，所以文件本身的删除暂不会影响到TDE
select * from t1;
select * from v$encryption_wallet
WRL_TYPE WRL_PARAMETER STATUS
-------------------- ---------------------------------------- ------------------
file /oradata06/wallet OPEN
create table t2 (id number,col2 varchar2(1) encrypt);
Table created.
--close Wallet
alter system set wallet close identified by ABCD_1234;
System altered.
SQL> select * from poweruser.t1;
select * from poweruser.t1
*
ERROR at line 1:
ORA-28365: wallet is not open
--重新打开ewallet，终于报错了，直到将wallet从ewallet.p12.old命名回原来的名称ewallet.p12后才能正常open wallet
alter system set wallet open identified by ABCD_1234;
System altered.
--知道了上述特性后按照删除wallet文件->重置masterkey的方式来重建wallet
**删除步骤略去。。。
**下面重置masterkey
alter system set encryption key identified by ABCD_5678;
ERROR at line 1:
ORA-28362: master key not found
select * from v$encryption_wallet
WRL_TYPE WRL_PARAMETER STATUS
-------------------- ------------------------------ ------------------
file /oradata06/wallet OPEN
--虽然报错，但新的wallet文件还是生成了，上面的ORA-28362意指数据库中还存在有使用老的masterkey加密的encryption key，但这个老的masterkey没有包含在当前新建的wallet文件里，
SQL> alter system set encryption key identified by ABCD_5678;
alter system set encryption key identified by ABCD_5678
*
ERROR at line 1:
ORA-28362: master key not found
SQL> alter system set wallet close identified by ABCD_5678;
System altered.
select * from v$encryption_wallet
WRL_TYPE WRL_PARAMETER STATUS
-------------------- ------------------------------ ------------------
file /oradata06/wallet CLOSED
--t1表的masterkeyid还是和原来一样
OBJ# OWNER# MKEYID ENCALG INTALG
---------- ---------- ---------------------------------------------------------------- ---------- ----------
COLKLC
------------------------------------------------------------------------------------------------------------------------
KLCLEN FLAG
---------- ----------
5532091 1 ASS+rtwdxk9Gv/Hs6TIV7pgAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 3 1
417741414141414141414141414141414141414141414454386D533354745A6A726F66365A425A432B56456D2F61307266754434474136716331576F
456D7146437533516B63396B614D37347A78696C426361645673773D
88
5532115 1 ASS+rtwdxk9Gv/Hs6TIV7pgAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 3 1
4177414141414141414141414141414141414141414143496C4B734A38665A4269746C3354444F307154716355434151433233597342474E63416F54
727368394C72597054304D784179696473475372616B36465631303D
88
--重建wallet，该错误表示用来加密column encrypted key的masterkey没有存在于新建的wallet文件中
alter system set encryption key identified by "ABCD_5678";
*
ERROR at line 1:
ORA-28396: rekey of enc$ dictionary table failed
oracle@qc570709b:/oradata06/wallet>ls -rlt
total 16
-rw-r--r-- 1 oracle oinstall 1573 Sep 22 16:44 ewallet.p12
--新的masterkey
oracle@qc570709b:/oradata06/wallet>mkstore -wrl /oradata06/wallet -list
Oracle Secret Store Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:
Oracle Secret Store entries:
ORACLE.SECURITY.DB.ENCRYPTION.AS00Ujj7+U8RvxZt4qnX594AAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.ATctymEWWk/fv60qAOI6VB0AAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
mkstore -wrl /oradata06/wallet -viewEntry ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
Enter wallet password:
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY = AS00Ujj7+U8RvxZt4qnX594AAAAAAAAAAAAAAAAAAAAAAAAAAAAA
--drop掉t1、t2表
drop table t1;
drop table t2;
--再次重置encryption key成功，因为t1、t2表都已经drop掉了，没有masterkey游离于wallet之外了
alter system set encryption key identified by "ABCD_5678";
System altered.
oracle@qc570709b:/oradata06/wallet>mkstore -wrl /oradata06/wallet -list
Oracle Secret Store Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:
Oracle Secret Store entries:
ORACLE.SECURITY.DB.ENCRYPTION.AS00Ujj7+U8RvxZt4qnX594AAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.ATctymEWWk/fv60qAOI6VB0AAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.AW2PpYY4208ivx+gbbg1f+AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.AX+sAfvKQE9Cv0kEE2bOLogAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
//
/masterkey用于表空间加密的情况下重建wallet
//
--创建加密表空间ts_ecrypt03
SQL> alter system set encryption key identified by "ABCD_5678";
System altered.
SQL> create tablespace ts_ecrypt03 datafile '/oradata06/datafile/encrypt03.dbf' size 128m encryption using 'AES128' default storage(encrypt);
Tablespace created.
--创建加密表空间中的表
create table bb (username encrypt using 'AES192',userid,created) tablespace ts_ecrypt03 as select * from all_users;
Table created.
SQL> desc bb;
Name Null? Type
----------------------------------------- -------- ----------------------------
USERNAME NOT NULL VARCHAR2(30) ENCRYPT
USERID NOT NULL NUMBER
CREATED NOT NULL DATE
SQL> select * from enc$;
no rows selected
SQL> select * from v$encrypted_tablespaces;
TS# ENCRYPT ENC
---------- ------- ---
ENCRYTPEDKEY
----------------------------------------------------------------
MASTERKEYID BLOCKS_ENCRYPTED BLOCKS_DECRYPTED
-------------------------------- ---------------- ----------------
59 AES128 YES
2ED20D971328D51A29EA193597DF99BC00000000000000000000000000000000
0906B7C73B864F9DBF120BB2112359D0 2 1
--用来加密TS_ECRYPT03表空间encrypted key所用的masterkey
select name,utl_raw.cast_to_varchar2( utl_encode.base64_encode('01'||substr(mkeyid,1,4))) || utl_raw.cast_to_varchar2( utl_encode.base64_encode(substr(mkeyid,5,length(mkeyid)))) masterkeyid_base64 FROM (select t.name, RAWTOHEX(x.mkid) mkeyid from v$tablespace t, x$kcbtek x where t.ts#=x.ts# and t.name like 'TS_ECRYPT%');
NAME
------------------------------
MASTERKEYID_BASE64
--------------------------------------------------------------------------------
TS_ECRYPT03
AQkGt8c7hk+dvxILshEjWdA=
--加密bb表encrypted key的masterkey和加密TS_ECRYPT03表空间encrypted key所用的masterkey相同
SQL> select mkeyid from enc$;
MKEYID
----------------------------------------------------------------
AQkGt8c7hk+dvxILshEjWdAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
--删除wallet文件
mv ewallet.p12 ewallet.p12.0922_5
--直接重置masterkey，生成新的wallet文件，新的wallet文件里没有包含老的masterkey，所以ORA-28362
alter system set encryption key identified by ABCD_7890;
*
ERROR at line 1:
ORA-28362: master key not found
--上述命令还是生成了新的masterkey
oracle@qc570709b:/oradata06/wallet>mkstore -wrl /oradata06/wallet -viewEntry ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
Oracle Secret Store Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.
Enter wallet password: 90
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY = AWUTpq98gU9Mv9aqMy8ssiAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
--由于新的wallet文件里没有包含ts_ecrypt03创建时使用的masterkey所以下列语句报错
SQL> select * from bb;
select * from bb
*
ERROR at line 1:
ORA-28362: master key not found
--此时创建新的加密表空间也失败
create tablespace ts_ecrypt04 datafile '/oradata06/ecrypt04' size 128m encryption using 'AES128' default storage(encrypt);
*
ERROR at line 1:
ORA-28374: typed master key not found in wallet
--再次重置masterkey后又遇到了ORA-28396，这次是因为encrypted tablespace里的那张加密的表bb
alter system set encryption key identified by ABCD_7890;
*
ERROR at line 1:
ORA-28396: rekey of enc$ dictionary table failed
--把bb表drop，reset masterkey终于恢复正常
SQL> drop table poweruser.bb;
Table dropped.
alter system set encryption key identified by ABCD_7890;
--新的加密表空间也创建成功
create tablespace ts_ecrypt04 datafile '/oradata06/ecrypt04.dbf' size 128m encryption using 'AES128' default storage(encrypt);
Tablespace created.
--查看wallet文件内容
mkstore -wrl /oradata06/wallet -list
Oracle Secret Store Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:
Oracle Secret Store entries:
ORACLE.SECURITY.DB.ENCRYPTION.AUzti1Pbdk8/v2jYUGfEtgcAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.AWUTpq98gU9Mv9aqMy8ssiAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.AZrgeKbxDU9Gv8SedTZpX0UAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
mkstore -wrl /oradata06/wallet -viewEntry ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
Oracle Secret Store Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY = AZrgeKbxDU9Gv8SedTZpX0UAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
--验证两个表空间均已经用上了最新的masterkey
select name,utl_raw.cast_to_varchar2( utl_encode.base64_encode('01'||substr(mkeyid,1,4))) || utl_raw.cast_to_varchar2( utl_encode.base64_encode(substr(mkeyid,5,length(mkeyid)))) masterkeyid_base64 FROM (select t.name, RAWTOHEX(x.mkid) mkeyid from v$tablespace t, x$kcbtek x where t.ts#=x.ts# and t.name like 'TS_ECRYPT%');
NAME
------------------------------
MASTERKEYID_BASE64
--------------------------------------------------------------------------------
TS_ECRYPT03
AZrgeKbxDU9Gv8SedTZpX0U=
TS_ECRYPT04
AZrgeKbxDU9Gv8SedTZpX0U=
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/53956/viewspace-1277633/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/53956/viewspace-1277633/
类似资料：