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

10 月 13, 2011

—

由

关于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

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

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

当然，这里的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

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

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

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

System altered.

下面我们手工通过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 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中再添加一个即可，如下：

[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

我们可以发现，目前有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]$ 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

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

wallet密码跟我们这里要修改的master key不是一个东西，wallet密码仅仅是你登陆owm或进行wallet
时需要输入的密码，而master key是存在wallet中，用于加密解密数据库的加密列或加密表空间的。

另外还有一个表密钥，如果一个表有多个列需要进行加密，那么在该表上也只会生成一个表级密钥，
当前段用户从该表中取回数据时，会首先取出表密钥然后再取出存在wallet中的master key进行对
表密钥的解密，最后用解密后的表密钥去解密加密的列或表空间数据，最终返回明文数据给用户。

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

orapki wallet change_pwd -wallet 

这里主要测试下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> !

[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

wallet

stream

2011年10月13日

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

回复
roger

2011年10月13日

你的sqlnet.ora 贴出来看看

回复
love wife & love life ——Roger » Blog Archive » TDE学习（2） —探秘列、表空间加密

2011年10月13日

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

回复
wisdomone1

2011年10月14日

roger大帅牛加油，同时注重身体哈

回复
love wife & love life ——Roger » Blog Archive » oracle TDE学习系列 (3) — 如何备份？

2011年10月14日

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

回复
a 3

2012年11月21日

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

回复
1. admin
  
  2012年11月25日
  
  不能的。
  
  回复

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

评论

发表回复取消回复

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

评论

发表回复 取消回复

发表回复取消回复