Re: Getting Oracle TDE Wallet State

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Thu, 17 Nov 2005 23:13:41 +0100
Message-ID: <dlivaj$v8s$03$1@news.t-online.com>

Eddy C schrieb:
> I just tried it and nothing comes back, most weird.
>
> If you haven't used TDE try it out and see what happens.
>
> alter system set wallet open identified by "welcome1";
>
> // flush everything.
>
> select * from v$wallet;
>
> I get no rows returned.
>

If you create your wallet with
ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY 'password' then you create practically empty wallet with only a certification request, certificate requests are not shown in v$wallet , only signed certificates. To get a valid ( signed ) certificate into this wallet you have to export this certificate request, then sign it and import together with trusted certificate. After that certificate is shown in v$wallet.

Here is ( very simplified ) shown, how you can do it. I've used for this example ( to deal only with commandline ) OpenSSL, but you can equally use also the Oracle Certificate Authority or any other supported by Oracle PKI.

*Ensure, that in sqlnet.ora path to oracle wallet is given* cat $ORACLE_HOME/network/admin/sqlnet.ora NAMES.DIRECTORY_PATH= (TNSNAMES, LDAP, EZCONNECT) WALLET_LOCATION =
(SOURCE =

     (METHOD = FILE)
     (METHOD_DATA =
        (DIRECTORY = /etc/oracle/wallets/ORA102)
     )

)

*startup instance*
sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Nov 17 17:03:39 2005

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size                  1218316 bytes
Variable Size              88082676 bytes
Database Buffers           75497472 bytes
Redo Buffers                2973696 bytes

Database mounted.
Database opened.
SQL> *create wallet with only certificate request* SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY oracle;

System altered.
SQL> select dn,status from v$wallet;

no rows selected
SQL> alter system set encryption wallet close;

System altered.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

*ensure , oracle wallet is created*
cd /etc/oracle/wallets/ORA102
ls ewallet.p12
ewallet.p12

*export certificate request*
orapki wallet export -wallet . -dn "CN=oracle" -request oracle.req -pwd ORACLE *create OpenSSL CA*
cp /usr/share/ssl/openssl.cnf .

/usr/share/ssl/misc/CA -newca
CA certificate filename (or enter to create)

Making CA certificate ...
Generating a 1024 bit RSA private key
........++++++
........++++++
writing new private key to './demoCA/private/./cakey.pem' Enter PEM pass phrase:
Verifying - Enter PEM pass phrase:

You are about to be asked to enter information that will be incorporated into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank.

Country Name (2 letter code) [GB]:DE
State or Province Name (full name) [Berkshire]:Bayern Locality Name (eg, city) [Newbury]:Munich Organization Name (eg, company) [My Company Ltd]:. Organizational Unit Name (eg, section) []:. Common Name (eg, your name or your server's hostname) []:Oracle CA Email Address []:.

*Sign Oracle certificate request*
openssl ca -in ./oracle.req -out ./oracle.pem -config ./openssl.cnf -policy policy_anything
Using configuration from ./openssl.cnf
Enter pass phrase for ./demoCA/private/cakey.pem: Check that the request matches the signature Signature ok
Certificate Details:

         Serial Number: 1 (0x1)
         Validity
             Not Before: Nov 17 21:32:55 2005 GMT
             Not After : Nov 17 21:32:55 2006 GMT
         Subject:
             commonName                = oracle
         X509v3 extensions:
             X509v3 Basic Constraints:
             CA:FALSE
             Netscape Comment:
             OpenSSL Generated Certificate
             X509v3 Subject Key Identifier:
             12:25:AE:98:D2:00:0F:80:FE:12:F0:17:A5:83:38:63:57:03:00:89
             X509v3 Authority Key Identifier:
 
keyid:B6:E0:90:02:51:F1:D7:5D:6D:59:F6:33:E1:6D:E7:C6:AA:CC:B9:27
             DirName:/C=DE/ST=Bayern/L=Munich/CN=Oracle CA
             serial:00

Certificate is to be certified until Nov 17 21:32:55 2006 GMT (365 days) Sign the certificate? [y/n]:y

1 out of 1 certificate requests certified, commit? [y/n]y Write out database with 1 new entries
Data Base Updated

*Check the v$wallet*
sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Nov 17 16:55:15 2005

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY oracle;

System altered.

SQL> col CERT_ID for a10
SQL> col DN for a10
SQL> col SERIAL_NUM for a10
SQL> col ISSUER for a50
SQL> col KEYSIZE for 9999
SQL> col STATUS for a10
SQL> select * from v$wallet;
SQL> set line 200
SQL> select * from v$wallet;

CERT_ID    DN         SERIAL_NUM ISSUER 
             KEYSIZE STATUS

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

-------------------------------------------------- ------- ----------
AumTC2Tjk2 CN=oracle  0x01       C=DE,ST=Bayern,L=Munich,CN=Oracle CA 
                 512 AVAILABLE

K/DBON8Kyw
wnMBAAAAAA
AAAAAAAAAA
AAAAAAAAAA
AA

Best regards

Maxim Received on Thu Nov 17 2005 - 16:13:41 CST