ITPub博客

首页 > 数据库 > Oracle > 如何重建encrytion wallet

如何重建encrytion wallet

原创 Oracle 作者:oliseh 时间:2014-09-22 23:25:40 0 删除 编辑
当使用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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
不仅仅专注Oracle database技术, member of SHOUG

注册时间:2014-04-06

  • 博文量
    128
  • 访问量
    1642295