Oracle ASO: TDE encryption wallet operation and key management

$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Mon Feb 14 04:31:18 2011

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> set echo on
SQL> @/home/oracle/aso_scripts/show_encryption_wallet_status.sql
SQL> desc v$encryption_wallet;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 WRL_TYPE                                           VARCHAR2(20)
 WRL_PARAMETER                                      VARCHAR2(4000)
 STATUS                                             VARCHAR2(9)

SQL> 
SQL> col wallet format a10
SQL> col status format a10
SQL> col wallet_location format a20
SQL> set echo on
SQL> select wrl_type wallet,status,wrl_parameter wallet_location
  2  from v$encryption_wallet;

WALLET     STATUS     WALLET_LOCATION
---------- ---------- --------------------
file       OPEN       /home/oracle/wallet

SQL> set echo off

$ $OH/bin/sqlplus system/oracle1

SQL*Plus: Release 11.1.0.7.0 - Production on Mon Feb 14 04:33:08 2011

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> alter system set encryption wallet close;

System altered.

$ sqlplus jbrinson/jbrinson

SQL*Plus: Release 11.1.0.7.0 - Production on Mon Feb 14 04:34:43 2011

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> select * from banking.customer;
select * from banking.customer
                      *
ERROR at line 1:
ORA-28365: wallet is not open


SQL> desc banking.customer
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CUSTOMER_ID                                        NUMBER
 CUSTOMER_NAME                                      VARCHAR2(30) ENCRYPT
 CUSTOMER_TYPE                                      VARCHAR2(3)
 CUSTOMER_CITY                                      VARCHAR2(20) ENCRYPT
 CUSTOMER_COUNTRY                                   VARCHAR2(2)

SQL> select CUSTOMER_ID, CUSTOMER_TYPE, CUSTOMER_COUNTRY from banking.customer;
select CUSTOMER_ID, CUSTOMER_TYPE, CUSTOMER_COUNTRY from banking.customer
                                                                 *
ERROR at line 1:
ORA-28365: wallet is not open


SQL> alter system set encryption wallet open identified by "abcdefg12#";
alter system set encryption wallet open identified by "abcdefg12#"
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> conn system/oracle1
Connected.
SQL> 
SQL> alter system set encryption wallet open identified by "abcdefg12#";

System altered.

SQL> conn jbrinson/jbrinson
Connected.
SQL> select * from banking.customer;

CUSTOMER_ID CUSTOMER_NAME                  CUS CUSTOMER_CITY        CU
----------- ------------------------------ --- -------------------- --
        101 HERTZ CORPORATION              LO  BERLIN               DE
        102 SUNGARD DATA SYSTEMS           GL  NEW YORK             US
        103 TEMASEK HOLDINGS               GL  SINGAPORE            SG
        104 NORDIC TELEPHONE               GL  STOCKHOLM            SE
        105 ORACLE CORPORATION             GL  REDWOOD SHORES       US
        106 QWEST COMMUNICATIONS           GL  DENVER               US
        107 OLD MUTUAL PRC                 GL  LONDON               UK
        108 FRESENIUS MED CARE             GL  LONDON               UK
        109 EMI GERMANY CORPORATION        LO  FRANKFURT            DE
        110 DAIMLER                        GL  STUTTGART            DE

10 rows selected.

SQL> alter system set encryption wallet close;
alter system set encryption wallet close
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> conn system/oracle1
Connected.
SQL> alter system set encryption wallet close;

System altered.

SQL> alter system set encryption wallet open identified by "abcdefg12#";

System altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

$ cp /home/oracle/wallet/ewallet.p12 /home/oracle/wallet/ewallet.p12.$(date +%y%m%d).old

$ ls -l /home/oracle/wallet/ewallet.p12*
-rw-rw-r-- 1 oracle dba    1573 Feb 14 03:04 /home/oracle/wallet/ewallet.p12
-rw-rw-r-- 1 oracle oracle 1573 Feb 14 04:43 /home/oracle/wallet/ewallet.p12.110214.old

$ sqlplus system/oracle1

SQL*Plus: Release 11.1.0.7.0 - Production on Mon Feb 14 04:43:29 2011

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> alter system set encryption key identified by "abcdefg12#";

System altered.

SQL> ! ls -l /home/oracle/wallet/ewallet.p12*
-rw-rw-r-- 1 oracle dba    1837 Feb 14 04:43 /home/oracle/wallet/ewallet.p12
-rw-rw-r-- 1 oracle oracle 1573 Feb 14 04:43 /home/oracle/wallet/ewallet.p12.110214.old

SQL> alter table banking.customer rekey;

Table altered.

SQL> exit


m set encryption key identified by "abcdefg12#";

System altered.

SQL> ! ls -l /home/oracle/wallet/ewallet.p12*
-rw-rw-r-- 1 oracle dba    1837 Feb 14 04:43 /home/oracle/wallet/ewallet.p12
-rw-rw-r-- 1 oracle oracle 1573 Feb 14 04:43 /home/oracle/wallet/ewallet.p12.110214.old

SQL> alter table banking.customer rekey;

Table altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options


$ /u01/oracle/product/11.1.0/db_1/bin/owm &
[1] 7934
$ Done.

$ /u01/oracle/product/11.1.0/sqlplus system/oracle1

SQL*Plus: Release 11.1.0.7.0 - Production on Mon Feb 14 04:52:21 2011

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> ! ls -l /home/oracle/wallet/ewallet.p12*
-rw------- 1 oracle dba    1838 Feb 14 04:52 /home/oracle/wallet/ewallet.p12
-rw-rw-r-- 1 oracle oracle 1573 Feb 14 04:43 /home/oracle/wallet/ewallet.p12.110214.old

SQL> alter system set encryption wallet open identified by "hijklmnop12#";
alter system set encryption wallet open identified by "hijklmnop12#"
*
ERROR at line 1:
ORA-28354: wallet already open


SQL> alter system set encryption wallet close;

System altered.


SQL> alter system set encryption wallet open identified by "hijklmno1#";
alter system set encryption wallet open identified by "hijklmno1#"
*
ERROR at line 1:
ORA-28353: failed to open wallet


SQL> alter system set encryption wallet open identified by "hijklmnop12#";

System altered.

SQL> alter system set encryption wallet close;

System altered.

SQL> exit

$ orapki wallet change_pwd -wallet /home/oracle/wallet
Enter wallet password: <- old password here
   
New password:
Enter wallet password: <- new password here