oracle TDE学习系列 (1) — wallet 使用管理

本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客

本文链接地址: oracle TDE学习系列 (1) — wallet 使用管理

关于oracle wallet，通常称为oracle钱夹，说的通俗一点，oracle wallet是一个用
口令加密的PKCS#12文件，PKCS#12是一个个人信息交换的语法标准，该公钥密码标准
由RSA security 涉及和发布。
我们知道11g的表空间加密依赖于oracle wallet以及wallet中的密钥，对于wallet分为
如下2种：
1. 手动打开的wallet （每次数据库启动以后，需要手动打开wallet）
2. 启动打开的wallet （每次数据库启动以后会启动打开）
如何设置数据库启动以后wallet启启动呢？很简单，通过owm去修改属性即可。
如果是DG环境，oracle推荐使自动的wallet（11g TDE支持dataguard）
创建wallet的方法分别有如下几种：
--> 手动调用OWM 进行GUI图形界面进行操作
--> 手工运行mkstore命令创建
--> alter system set encryption key identified by "xxxxx";
当然我这里图方便直接调用OWM进行创建了，如果是用mkstore命令，那么如下：
--> mkstore -wrl /home/ora11g/admin/roger/wallet -create
--> 输入密码（此时的密码是主密钥）
--> 确认密码
我们这里已经有一个wallet，所以我就直接打开即可，如下：

关于oracle wallet，通常称为oracle钱夹，说的通俗一点，oracle wallet是一个用

口令加密的PKCS#12文件，PKCS#12是一个个人信息交换的语法标准，该公钥密码标准

由RSA security 涉及和发布。

我们知道11g的表空间加密依赖于oracle wallet以及wallet中的密钥，对于wallet分为

如下2种：

1. 手动打开的wallet （每次数据库启动以后，需要手动打开wallet）

2. 启动打开的wallet （每次数据库启动以后会启动打开）

如何设置数据库启动以后wallet启启动呢？很简单，通过owm去修改属性即可。

如果是DG环境，oracle推荐使自动的wallet（11g TDE支持dataguard）

创建wallet的方法分别有如下几种：

--> 手动调用OWM 进行GUI图形界面进行操作

--> 手工运行mkstore命令创建

--> alter system set encryption key identified by "xxxxx";

当然我这里图方便直接调用OWM进行创建了，如果是用mkstore命令，那么如下：

--> mkstore -wrl /home/ora11g/admin/roger/wallet -create

--> 输入密码（此时的密码是主密钥）

--> 确认密码

我们这里已经有一个wallet，所以我就直接打开即可，如下：

SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "roger007~!@";
ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "roger007~!@"
*
ERROR at line 1:
ORA-28368: cannot auto-create wallet

SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "roger007~!@";

ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "roger007~!@"

ERROR at line 1:

ORA-28368: cannot auto-create wallet

如果出现上面错误，那么可以说手工指定wallet路径，添加如下信息到sqlnet.ora中：

1	如果出现上面错误，那么可以说手工指定wallet路径，添加如下信息到sqlnet.ora中：

NAMES.DIRECTORY_PATH=(TNSNAMES,EZCONNECT)
ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/home/ora11g/admin/roger/wallet )))

1 2	NAMES.DIRECTORY_PATH=(TNSNAMES,EZCONNECT) ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/home/ora11g/admin/roger/wallet )))

当然，这里的method_data路径必须存在，否则会报错：

1	当然，这里的method_data路径必须存在，否则会报错：

SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "roger007~!@";
ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "roger007~!@"
*
ERROR at line 1:
ORA-28368: cannot auto-create wallet

SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "roger007~!@";

ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "roger007~!@"

ERROR at line 1:

ORA-28368: cannot auto-create wallet

错误依旧，突然发现该目录是不存在的，手工mkdir创建以下，即可：

1	错误依旧，突然发现该目录是不存在的，手工mkdir创建以下，即可：

[ora11g@11gr2test admin]$ mkdir -p  /home/ora11g/admin/roger/wallet
SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "roger007~!@";
System altered.

[ora11g@11gr2test admin]$ mkdir -p /home/ora11g/admin/roger/wallet

SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "roger007~!@";

System altered.

下面我们手工通过mkstore命令来看看（如下操作我是10204环境中进行）：

1	下面我们手工通过mkstore命令来看看（如下操作我是10204环境中进行）：

[ora10g@killdb ~]$ mkstore -wrl $ORACLE_HOME/network/admin/wallet -create
Enter password: ******
Enter password again: ******
==== 这里我输入的wallet密码是www.killdb.com ==== 
==== 将数据库用户roger用户认证信息加入到wallet进行管理 ==== 
[ora10g@killdb admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /home/ora10g/product/10.2//network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
TEST_10G =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.110)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = roger)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

[ora10g@killdb ~]$ mkstore -wrl $ORACLE_HOME/network/admin/wallet -create

Enter password: ******

Enter password again: ******

==== 这里我输入的wallet密码是www.killdb.com ====

==== 将数据库用户roger用户认证信息加入到wallet进行管理 ====

[ora10g@killdb admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /home/ora10g/product/10.2//network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

TEST_10G =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.110)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = roger)

)

EXTPROC_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

(CONNECT_DATA =

(SID = PLSExtProc)

(PRESENTATION = RO)

)

[ora10g@killdb admin]$ mkstore -wrl $ORACLE_HOME/network/admin/wallet -createCredential  TEST_10G roger "roger"
Enter wallet password: ******
Create credential oracle.security.client.connect_string1
==== 这里输入的wallet同样是我们前面的www.killdb.com ==== 
[ora10g@killdb admin]$ mkstore -wrl $ORACLE_HOME/network/admin/wallet -createCredential  TEST_10G scott "tiger"
Enter wallet password:   
Create credential Secret Store error occured: oracle.security.pki.OracleSecretStoreException: Credential already exists
==== 报错了，提示存在？====

[ora10g@killdb admin]$ mkstore -wrl $ORACLE_HOME/network/admin/wallet -createCredential TEST_10G roger "roger"

Enter wallet password: ******

Create credential oracle.security.client.connect_string1

==== 这里输入的wallet同样是我们前面的www.killdb.com ====

[ora10g@killdb admin]$ mkstore -wrl $ORACLE_HOME/network/admin/wallet -createCredential TEST_10G scott "tiger"

Enter wallet password:

Create credential Secret Store error occured: oracle.security.pki.OracleSecretStoreException: Credential already exists

==== 报错了，提示存在？====

google了一下，原理是每个连接字符串只能对应一个数据库用户，那我们就收工在tnsnames.ora中再添加一个即可，如下：

1	google了一下，原理是每个连接字符串只能对应一个数据库用户，那我们就收工在tnsnames.ora中再添加一个即可，如下：

[ora10g@killdb admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /home/ora10g/product/10.2//network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
TEST_10G =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.110)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = roger)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
TEST_SCOTT =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.110)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = roger)
)
)

[ora10g@killdb admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /home/ora10g/product/10.2//network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

TEST_10G =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.110)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = roger)

)

EXTPROC_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

(CONNECT_DATA =

(SID = PLSExtProc)

(PRESENTATION = RO)

)

TEST_SCOTT =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.110)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = roger)

)

[ora10g@killdb admin]$ mkstore -wrl $ORACLE_HOME/network/admin/wallet -createCredential  TEST_SCOTT scott "tiger"
Enter wallet password: ******
Create credential oracle.security.client.connect_string2
==== 创建成功 ==== 
==== 到这里可能有人会问，如何知道哪些用户认证加到wallet了呢？oracle当然也提供了命令，如下：==== 
[ora10g@killdb admin]$ mkstore -wrl $ORACLE_HOME/network/admin/wallet -listCredential
Enter wallet password: ******   
List credential (index: connect_string username)
1: TEST_10G roger
2: TEST_SCOTT scott

[ora10g@killdb admin]$ mkstore -wrl $ORACLE_HOME/network/admin/wallet -createCredential TEST_SCOTT scott "tiger"

Enter wallet password: ******

Create credential oracle.security.client.connect_string2

==== 创建成功 ====

==== 到这里可能有人会问，如何知道哪些用户认证加到wallet了呢？oracle当然也提供了命令，如下：====

[ora10g@killdb admin]$ mkstore -wrl $ORACLE_HOME/network/admin/wallet -listCredential

Enter wallet password: ******

List credential (index: connect_string username)

1: TEST_10G roger

2: TEST_SCOTT scott

我们可以发现，目前有2个数据库用户认证加入到wallet了，那么有什么用处呢？请看：
首先我们还得修改sqlnet.ora，添加如下内容：

我们可以发现，目前有2个数据库用户认证加入到wallet了，那么有什么用处呢？请看：

首先我们还得修改sqlnet.ora，添加如下内容：

[ora10g@killdb admin]$ cat sqlnet.ora
SQLNET.WALLET_OVERRIDE=TRUE
NAMES.DIRECTORY_PATH=(TNSNAMES,EZCONNECT)
WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/home/ora10g/product/10.2/network/admin/wallet)))

[ora10g@killdb admin]$ cat sqlnet.ora

SQLNET.WALLET_OVERRIDE=TRUE

NAMES.DIRECTORY_PATH=(TNSNAMES,EZCONNECT)

WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/home/ora10g/product/10.2/network/admin/wallet)))

[ora10g@killdb admin]$ sqlplus /@test_scott
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Oct 12 08:26:27 2011
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show user
USER is "SCOTT"
==== 我们可以发现，实现无密码登陆 ==== 
到最后，我想大家会跟我一样还会有个疑问，那就是：如果scott用户更改密码了呢？
那还会有用吗？如果没用的话，是不是需要重建用户的认证呢？
答案是否，oracle还提供了modify功能，如下：
SQL> show user
USER is "SCOTT"
SQL> alter user scott identified by scott;
User altered.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[ora10g@killdb admin]$ sqlplus /@test_scott
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Oct 12 08:29:41 2011
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name: 
==== 下面进行修改wallet中的scott用户密码认证：==== 
[ora10g@killdb admin]$ mkstore -wrl $ORACLE_HOME/network/admin/wallet/ -modifyCredential TEST_SCOTT scott "scott"
Enter wallet password:   
Modify credential 
Modify 2
[ora10g@killdb admin]$ sqlplus /@test_scott
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Oct 12 08:31:51 2011
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
==== 既然有了modify，那是否有delete呢？当然有，如下：==== 
[ora10g@killdb admin]$ mkstore -wrl $ORACLE_HOME/network/admin/wallet/ -deleteCredential TEST_SCOTT
Enter wallet password: ******
Delete credential 
Delete 2
[ora10g@killdb admin]$ mkstore -wrl $ORACLE_HOME/network/admin/wallet -listCredential
Enter wallet password: ******  
List credential (index: connect_string username)
1: TEST_10G roger

[ora10g@killdb admin]$ sqlplus /@test_scott

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Oct 12 08:26:27 2011

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show user

USER is "SCOTT"

==== 我们可以发现，实现无密码登陆 ====

到最后，我想大家会跟我一样还会有个疑问，那就是：如果scott用户更改密码了呢？

那还会有用吗？如果没用的话，是不是需要重建用户的认证呢？

答案是否，oracle还提供了modify功能，如下：

SQL> show user

USER is "SCOTT"

SQL> alter user scott identified by scott;

User altered.

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[ora10g@killdb admin]$ sqlplus /@test_scott

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Oct 12 08:29:41 2011

ERROR:

ORA-01017: invalid username/password; logon denied

Enter user-name:

==== 下面进行修改wallet中的scott用户密码认证：====

[ora10g@killdb admin]$ mkstore -wrl $ORACLE_HOME/network/admin/wallet/ -modifyCredential TEST_SCOTT scott "scott"

Enter wallet password:

Modify credential

Modify 2

[ora10g@killdb admin]$ sqlplus /@test_scott

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Oct 12 08:31:51 2011

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

==== 既然有了modify，那是否有delete呢？当然有，如下：====

[ora10g@killdb admin]$ mkstore -wrl $ORACLE_HOME/network/admin/wallet/ -deleteCredential TEST_SCOTT

Enter wallet password: ******

Delete credential

Delete 2

[ora10g@killdb admin]$ mkstore -wrl $ORACLE_HOME/network/admin/wallet -listCredential

Enter wallet password: ******

List credential (index: connect_string username)

1: TEST_10G roger

最后来看看如何设置新的密钥？首先这里我们要弄清楚2个概念：
wallet密码跟我们这里要修改的master key不是一个东西，wallet密码仅仅是你登陆owm或进行wallet
时需要输入的密码，而master key是存在wallet中，用于加密解密数据库的加密列或加密表空间的。
另外还有一个表密钥，如果一个表有多个列需要进行加密，那么在该表上也只会生成一个表级密钥，
当前段用户从该表中取回数据时，会首先取出表密钥然后再取出存在wallet中的master key进行对
表密钥的解密，最后用解密后的表密钥去解密加密的列或表空间数据，最终返回明文数据给用户。
更改wallet key很简单，可以通过owm gui界面进行或如下命令：
orapki wallet change_pwd -wallet <wallet_location>
这里主要测试下master key的更改。

最后来看看如何设置新的密钥？首先这里我们要弄清楚2个概念：

wallet密码跟我们这里要修改的master key不是一个东西，wallet密码仅仅是你登陆owm或进行wallet

时需要输入的密码，而master key是存在wallet中，用于加密解密数据库的加密列或加密表空间的。

另外还有一个表密钥，如果一个表有多个列需要进行加密，那么在该表上也只会生成一个表级密钥，

当前段用户从该表中取回数据时，会首先取出表密钥然后再取出存在wallet中的master key进行对

表密钥的解密，最后用解密后的表密钥去解密加密的列或表空间数据，最终返回明文数据给用户。

更改wallet key很简单，可以通过owm gui界面进行或如下命令：

orapki wallet change_pwd -wallet <wallet_location>

这里主要测试下master key的更改。

SQL> select * from V$ENCRYPTION_WALLET;
WRL_TYPE             WRL_PARAMETER                                                STATUS
-------------------- ------------------------------------------------------------ ---------
file                 /home/ora10g/product/10.2/network/admin/wallet               CLOSED
SQL> alter system set encryption wallet open identified by "www.killdb.com";
System altered.
SQL> select * from V$ENCRYPTION_WALLET;
WRL_TYPE             WRL_PARAMETER                                                STATUS
-------------------- ------------------------------------------------------------ ---------
file                 /home/ora10g/product/10.2/network/admin/wallet               OPEN
SQL> conn roger/roger
Connected.
SQL> create table ht01 (id number ENCRYPT,name varchar2(10));
create table ht01 (id number ENCRYPT,name varchar2(10))
*
ERROR at line 1:
ORA-28361: master key not yet set
SQL> alter system set encryption key identified by "www.killdb.com";
System altered.
SQL> create table ht01 (id number ENCRYPT,name varchar2(10));
Table created.
SQL> insert into ht01 values(1314,'killdb');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from DBA_ENCRYPTED_COLUMNS;
OWNER        TABLE_NAME       COLUMN_NAME      ENCRYPTION_ALG                SAL INTEGRITY_AL
------------ ---------------- ---------------- ----------------------------- --- ------------
ROGER        HT01             ID               AES 192 bits key              YES SHA-1
SQL> alter system set encryption key  identified by "oraclemaster";
alter system set encryption key  identified by "oraclemaster"
*
ERROR at line 1:
ORA-28353: failed to open wallet
SQL>  select * from V$ENCRYPTION_WALLET;
WRL_TYPE             WRL_PARAMETER                                                STATUS
-------------------- ------------------------------------------------------------ ---------
file                 /home/ora10g/product/10.2/network/admin/wallet               CLOSED
SQL> alter system set encryption wallet open identified by "www.killdb.com";
System altered.
SQL> select * from V$ENCRYPTION_WALLET;
WRL_TYPE             WRL_PARAMETER                                                STATUS
-------------------- ------------------------------------------------------------ ---------
file                 /home/ora10g/product/10.2/network/admin/wallet               OPEN
SQL>  select obj#, mkeyid from sys.enc$;
OBJ# MKEYID
---------- ----------------------------------------------------------------
51809 AcNbIDu9IE+6vzrrVp/L4qcAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
SQL> select owner,object_name from dba_objects where object_id=51809;
OWNER                          OBJECT_NAME
------------------------------ --------------------------------------------
ROGER                          HT01
SQL> select obj#, mkeyid from sys.enc$;
OBJ# MKEYID
---------- ----------------------------------------------------------------
51809 AcNbIDu9IE+6vzrrVp/L4qcAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
SQL> show user
USER is "ROGER"
SQL> alter system set encryption key identified by "www.killdb.com";
System altered.
SQL> select a.owner, object_name, b.mkeyid
2    from sys.dba_objects a, sys.enc$ b
3   where a.object_id = b.obj#;
OWNER     OBJECT_NAME     MKEYID
--------- --------------- -------------------------------------------------------
ROGER     HT01            AUkm6RyZ2084v/KP0PwFGUwAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
SQL> 
++++ 我们可以看到，master key已经更改。++++
++++ 当然，当我们更改了master key以后，我们最好也同时更改下表级密钥。++++
SQL> select * from ht01;
ID NAME
---------- ----------
1314 killdb
SQL> alter table ht01  rekey;
Table altered.
SQL> select * from ht01;
ID NAME
---------- ----------
1314 killdb
SQL> select a.owner, object_name, b.mkeyid
2    from sys.dba_objects a, sys.enc$ b
3   where a.object_id = b.obj#;
OWNER              OBJECT_NAME     MKEYID
------------------ --------------- -------------------------------------------------------
ROGER              HT01            AUkm6RyZ2084v/KP0PwFGUwAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
SQL> alter table ht01  rekey;
Table altered.
SQL> select a.owner, object_name, b.mkeyid
2    from sys.dba_objects a, sys.enc$ b
3   where a.object_id = b.obj#;
OWNER              OBJECT_NAME     MKEYID
------------------ --------------- -------------------------------------------------------
ROGER              HT01            AUkm6RyZ2084v/KP0PwFGUwAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
+++++ 既然wallet如此重要，那如何备份wallet？++++++
SQL> alter system set encryption wallet close identified by "www.killdb.com";
alter system set encryption wallet close identified by "www.killdb.com"
*
ERROR at line 1:
ORA-28364: invalid wallet operation
SQL> alter system set wallet close;
System altered.
SQL> alter system set wallet close;
System altered.
SQL> !

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

SQL> select * from V$ENCRYPTION_WALLET;

WRL_TYPE WRL_PARAMETER STATUS

-------------------- ------------------------------------------------------------ ---------

file /home/ora10g/product/10.2/network/admin/wallet CLOSED

SQL> alter system set encryption wallet open identified by "www.killdb.com";

System altered.

SQL> select * from V$ENCRYPTION_WALLET;

WRL_TYPE WRL_PARAMETER STATUS

-------------------- ------------------------------------------------------------ ---------

file /home/ora10g/product/10.2/network/admin/wallet OPEN

SQL> conn roger/roger

Connected.

SQL> create table ht01 (id number ENCRYPT,name varchar2(10));

create table ht01 (id number ENCRYPT,name varchar2(10))

ERROR at line 1:

ORA-28361: master key not yet set

SQL> alter system set encryption key identified by "www.killdb.com";

System altered.

SQL> create table ht01 (id number ENCRYPT,name varchar2(10));

Table created.

SQL> insert into ht01 values(1314,'killdb');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from DBA_ENCRYPTED_COLUMNS;

OWNER TABLE_NAME COLUMN_NAME ENCRYPTION_ALG SAL INTEGRITY_AL

------------ ---------------- ---------------- ----------------------------- --- ------------

ROGER HT01 ID AES 192 bits key YES SHA-1

SQL> alter system set encryption key identified by "oraclemaster";

alter system set encryption key identified by "oraclemaster"

ERROR at line 1:

ORA-28353: failed to open wallet

SQL> select * from V$ENCRYPTION_WALLET;

WRL_TYPE WRL_PARAMETER STATUS

-------------------- ------------------------------------------------------------ ---------

file /home/ora10g/product/10.2/network/admin/wallet CLOSED

SQL> alter system set encryption wallet open identified by "www.killdb.com";

System altered.

SQL> select * from V$ENCRYPTION_WALLET;

WRL_TYPE WRL_PARAMETER STATUS

-------------------- ------------------------------------------------------------ ---------

file /home/ora10g/product/10.2/network/admin/wallet OPEN

SQL> select obj#, mkeyid from sys.enc$;

OBJ# MKEYID

---------- ----------------------------------------------------------------

51809 AcNbIDu9IE+6vzrrVp/L4qcAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

SQL> select owner,object_name from dba_objects where object_id=51809;

OWNER OBJECT_NAME

------------------------------ --------------------------------------------

ROGER HT01

SQL> select obj#, mkeyid from sys.enc$;

OBJ# MKEYID

---------- ----------------------------------------------------------------

51809 AcNbIDu9IE+6vzrrVp/L4qcAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

SQL> show user

USER is "ROGER"

SQL> alter system set encryption key identified by "www.killdb.com";

System altered.

SQL> select a.owner, object_name, b.mkeyid

2 from sys.dba_objects a, sys.enc$ b

3 where a.object_id = b.obj#;

OWNER OBJECT_NAME MKEYID

--------- --------------- -------------------------------------------------------

ROGER HT01 AUkm6RyZ2084v/KP0PwFGUwAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

SQL>

++++ 我们可以看到，master key已经更改。++++

++++ 当然，当我们更改了master key以后，我们最好也同时更改下表级密钥。++++

SQL> select * from ht01;

ID NAME

---------- ----------

1314 killdb

SQL> alter table ht01 rekey;

Table altered.

SQL> select * from ht01;

ID NAME

---------- ----------

1314 killdb

SQL> select a.owner, object_name, b.mkeyid

2 from sys.dba_objects a, sys.enc$ b

3 where a.object_id = b.obj#;

OWNER OBJECT_NAME MKEYID

------------------ --------------- -------------------------------------------------------

ROGER HT01 AUkm6RyZ2084v/KP0PwFGUwAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

SQL> alter table ht01 rekey;

Table altered.

SQL> select a.owner, object_name, b.mkeyid

2 from sys.dba_objects a, sys.enc$ b

3 where a.object_id = b.obj#;

OWNER OBJECT_NAME MKEYID

------------------ --------------- -------------------------------------------------------

ROGER HT01 AUkm6RyZ2084v/KP0PwFGUwAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

+++++ 既然wallet如此重要，那如何备份wallet？++++++

SQL> alter system set encryption wallet close identified by "www.killdb.com";

alter system set encryption wallet close identified by "www.killdb.com"

ERROR at line 1:

ORA-28364: invalid wallet operation

SQL> alter system set wallet close;

System altered.

SQL> alter system set wallet close;

System altered.

SQL> !

[ora10g@killdb ~]$ cd $ORACLE_HOME/net*/admin
[ora10g@killdb admin]$ ls -ltr
total 48
-rw-r--r--  1 ora10g oinstall  172 Dec 26  2003 shrept.lst
drwxr-x---  2 ora10g oinstall 4096 Sep 24 05:11 samples
drwx------  2 ora10g oinstall 4096 Oct 12 08:06 wallet
-rw-r--r--  1 ora10g oinstall  488 Oct 12 08:08 listener.ora
-rw-r--r--  1 ora10g oinstall  712 Oct 12 08:20 tnsnames.ora
-rw-r--r--  1 ora10g oinstall  181 Oct 12 08:25 sqlnet.ora
[ora10g@killdb admin]$ mv wallet wallet_old
[ora10g@killdb admin]$ ls -ltr
total 48
-rw-r--r--  1 ora10g oinstall  172 Dec 26  2003 shrept.lst
drwxr-x---  2 ora10g oinstall 4096 Sep 24 05:11 samples
drwx------  2 ora10g oinstall 4096 Oct 12 08:06 wallet_old
-rw-r--r--  1 ora10g oinstall  488 Oct 12 08:08 listener.ora
-rw-r--r--  1 ora10g oinstall  712 Oct 12 08:20 tnsnames.ora
-rw-r--r--  1 ora10g oinstall  181 Oct 12 08:25 sqlnet.ora
[ora10g@killdb admin]$ exit
exit

[ora10g@killdb ~]$ cd $ORACLE_HOME/net*/admin

[ora10g@killdb admin]$ ls -ltr

total 48

-rw-r--r-- 1 ora10g oinstall 172 Dec 26 2003 shrept.lst

drwxr-x--- 2 ora10g oinstall 4096 Sep 24 05:11 samples

drwx------ 2 ora10g oinstall 4096 Oct 12 08:06 wallet

-rw-r--r-- 1 ora10g oinstall 488 Oct 12 08:08 listener.ora

-rw-r--r-- 1 ora10g oinstall 712 Oct 12 08:20 tnsnames.ora

-rw-r--r-- 1 ora10g oinstall 181 Oct 12 08:25 sqlnet.ora

[ora10g@killdb admin]$ mv wallet wallet_old

[ora10g@killdb admin]$ ls -ltr

total 48

-rw-r--r-- 1 ora10g oinstall 172 Dec 26 2003 shrept.lst

drwxr-x--- 2 ora10g oinstall 4096 Sep 24 05:11 samples

drwx------ 2 ora10g oinstall 4096 Oct 12 08:06 wallet_old

-rw-r--r-- 1 ora10g oinstall 488 Oct 12 08:08 listener.ora

-rw-r--r-- 1 ora10g oinstall 712 Oct 12 08:20 tnsnames.ora

-rw-r--r-- 1 ora10g oinstall 181 Oct 12 08:25 sqlnet.ora

[ora10g@killdb admin]$ exit

exit

SQL> select * from roger.ht01;
select * from roger.ht01
*
ERROR at line 1:
ORA-28365: wallet is not open
SQL> alter system set encryption wallet open identified by "www.killdb.com";
alter system set encryption wallet open identified by "www.killdb.com"
*
ERROR at line 1:
ORA-28367: wallet does not exist
SQL> !
[ora10g@killdb ~]$ cd $ORACLE_HOME/net*/admin
[ora10g@killdb admin]$ mv wallet_old wallet
[ora10g@killdb admin]$ exit
exit
SQL> alter system set encryption wallet open identified by "www.killdb.com";
System altered.
SQL> select * from roger.ht01;
ID NAME
---------- ----------
1314 killdb
==== 对于wallet的备份，我们可以直接拷贝即可如下，当然我这里直接mv测试：==== 
[ora10g@killdb admin]$ cp -r wallet wallet_20111113

SQL> select * from roger.ht01;

select * from roger.ht01

ERROR at line 1:

ORA-28365: wallet is not open

SQL> alter system set encryption wallet open identified by "www.killdb.com";

alter system set encryption wallet open identified by "www.killdb.com"

ERROR at line 1:

ORA-28367: wallet does not exist

SQL> !

[ora10g@killdb ~]$ cd $ORACLE_HOME/net*/admin

[ora10g@killdb admin]$ mv wallet_old wallet

[ora10g@killdb admin]$ exit

exit

SQL> alter system set encryption wallet open identified by "www.killdb.com";

System altered.

SQL> select * from roger.ht01;

ID NAME

---------- ----------

1314 killdb

==== 对于wallet的备份，我们可以直接拷贝即可如下，当然我这里直接mv测试：====

[ora10g@killdb admin]$ cp -r wallet wallet_20111113

Tags: wallet
Posted in Oracle rdbms on 10月 13, 2011

7 Responses to “oracle TDE学习系列 (1) — wallet 使用管理”

stream Says:

10月 13, 2011 at 上午 5:56

您好，想请教下ORA-28368错误，我已经在sqlnet.ora文件中指定了路径，也在这个路径下建了目录，可是还是报ORA-28368错误，在默认地址也建了目录，问题还是没解决，您对ORA-28368有深入研究吗？
roger Says:

10月 13, 2011 at 上午 6:30

你的sqlnet.ora 贴出来看看
love wife & love life ——Roger » Blog Archive » TDE学习（2） —探秘列、表空间加密 Says:

10月 13, 2011 at 上午 6:32

[…] TDE 加密跟必须依赖于oracle wallet，关于wallet的管理，请参考：第一篇 oracle TDE学习系列(1) – wallet 使用管理在10.2版本中，仅仅限于列的加密，从11gR1开始又引入了表空间级别的加密， […]
wisdomone1 Says:

10月 14, 2011 at 上午 3:56

roger大帅牛加油，同时注重身体哈
love wife & love life ——Roger » Blog Archive » oracle TDE学习系列 (3) — 如何备份？ Says:

10月 14, 2011 at 下午 3:32

[…] TDE 数据库的备份，这是TDE学习系列的第3篇，前两篇请参看如下链接： oracle TDE学习系列(1) — wallet 使用管理 oracle TDE学习系列(2) — 探秘列、表空间加密 […]
a 3 Says:

11月 21, 2012 at 下午 2:34

TDE能防止dba通过sql，或者所谓的数据owner看到其中的内容吗？
admin Says:

11月 25, 2012 at 下午 2:34

不能的。

You must be logged in to post a comment.

love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客

Categories

Archives

最新评论

国内圈子

oracle security

oracle TDE学习系列 (1) — wallet 使用管理

7 Responses to “oracle TDE学习系列 (1) — wallet 使用管理”

Leave a Reply