关于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
错误依旧,突然发现该目录是不存在的,手工mkdir创建以下,即可:
[ora11g@11gr2test admin]$ mkdir -p /home/ora11g/admin/roger/wallet SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "roger007~!@"; System altered.
[ora10g@killdb admin]$ sqlplus /@test_scott SQL*Plus: Release 10.2.0.5.0 - Production ON Wed Oct 12 08:26:27 2011Copyright (c) 1982, 2010, Oracle. ALL Rights Reserved. Connected TO:Oracle DATABASE 10g Enterprise Edition Release 10.2.0.5.0 - ProductionWITH the Partitioning, OLAP, DATA Mining AND REAL Application Testing options SQL> SHOW USERUSER IS "SCOTT" ==== 我们可以发现,实现无密码登陆 ==== 到最后,我想大家会跟我一样还会有个疑问,那就是:如果scott用户更改密码了呢?那还会有用吗?如果没用的话,是不是需要重建用户的认证呢?答案是否,oracle还提供了MODIFY功能,如下: SQL> SHOW USERUSER 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 - ProductionWITH 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 2011Copyright (c) 1982, 2010, Oracle. ALL Rights Reserved. Connected TO:Oracle DATABASE 10g Enterprise Edition Release 10.2.0.5.0 - ProductionWITH 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
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/rogerConnected. 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 USERUSER 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.lstdrwxr-x--- 2 ora10g oinstall 4096 Sep 24 05:11 samplesdrwx------ 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.lstdrwxr-x--- 2 ora10g oinstall 4096 Sep 24 05:11 samplesdrwx------ 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]$ exitexit
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]$ exitexit 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