如何重建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=

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/53956/viewspace-1277633/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/53956/viewspace-1277633/