Monday, February 14, 2011

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