oracledba.help
SpecialTopics

Wallet, Oracle

Overview

An Oracle Wallet is a password-protected container used to store authentication and signing credentials including: private keys, certificates and trusted certificates needed by SSL.

Configuring a full blown Oracle Wallet with a Master Key as done for TDE requires the Wallet to be open before the database opens. If just using the Wallet for certs it can be implemented in a safer more concise way as shown in these instructions.

Wallet Essential Info

A Wallet generally consists of two types of files:

  1. Encryption wallet file (ewallet.p12).
  2. Auto-open wallet file (cwallet.sso).

Default Location: Standard Database 

 $ORACLE_BASE/admin/<global_db_name>/wallet
 Example: /u01/app/oracle/admin/oradb/wallet

 If this directory does not exist or ORACLE_BASE is not set, the default location is: 
 $ORACLE_HOME/admin/<global_db_name>/wallet

Default Location: RAC Database (node) 

 <oracle_home>/<global_db_name>/wallet

 19c 
 Example: /u01/app/oracle/product/19.3.0.0.0/dbhome_1/admin/oradb/wallet
 12.1
 Example: /u01/app/oracle/product/12.1.0.2/dbhome_1/admin/oradb/wallet

12.1 path is completely unusable though! See RAC 12.1 Usage Session for practical example. Use an NFS path for 12.1 RAC nodes.

Wallet Directory and Files 

 oracle> pwd
 /u01/app/oracle/admin/oradb/wallet
 oracle> ll
 -rw------- 1 oracle oinstall 2885 Mar  9  2016 cwallet.sso
 -rw-rw-rw- 1 oracle oinstall    0 Mar  9  2016 cwallet.sso.lck
 -rw------- 1 oracle asmadmin 2840 Mar  9  2016 ewallet.p12
 -rw-rw-rw- 1 oracle oinstall    0 Mar  9  2016 ewallet.p12.lck

When specifying -auto_login the file "cwallet.sso" is generated.

Wallet Status: orapki 

  orapki wallet display -wallet /u01/app/oracle/admin/oradb/wallet
  Requested Certificates: 
  User Certificates: 
  Oracle Secret Store entries:  
  ORACLE.SECURITY.DB.ENCRYPTION.AduttmuRJk+Xv7d2xQc7xEIAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 
  ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY 
  ORACLE.SECURITY.KB.ENCRYPTION. 
  Trusted Certificates: 
  Subject: CN=ny.abc.local

Change oradb with your ORACLE_SID.

Wallet Status: SQL 

 COL wrl_type      FORMAT a12
 COL wrl_parameter FORMAT a35
 COL status        FORMAT a15
 COL wallet_type   FORMAT a15
 SELECT wrl_type,wrl_parameter,status,wallet_type FROM v$encryption_wallet;

For RAC: SELECT inst_id,wrl_type,wrl_parameter,status,wallet_type FROM gv$encryption_wallet; 

 Unconfigured Example
  WRL_TYPE  WRL_PARAMETER                       STATUS          WALLET_TYPE  WALLET_OR FULLY_BAC  CON_ID 
  --------  ----------------------------------  -------------   -----------  -------   ---------  ------ 
  FILE      /u01/app/oracle/admin/oradb/wallet  NOT_AVAILABLE   UNKNOWN      SINGLE    UNDEFINED  0      

 Configured Example: Master Key Not Created (but wallet and certs created)
  WRL_TYPE  WRL_PARAMETER                       STATUS              WALLET_TYPE  WALLET_OR FULLY_BAC  CON_ID 
  --------  ----------------------------------  ------------------  -----------  -------   ---------  ------ 
  FILE      /u01/app/oracle/admin/oradb/wallet  OPEN_NO_MASTER_KEY  UNKNOWN      SINGLE    UNDEFINED  0      

 Configured Example: Master Key Created
  WRL_TYPE  WRL_PARAMETER                       STATUS          WALLET_TYPE  WALLET_OR FULLY_BAC  CON_ID 
  --------  ----------------------------------  -------------   -----------  -------   ---------  ------ 
  FILE      /u01/app/oracle/admin/oradb/wallet  OPEN            PASSWORD     SINGLE    NO         0

If changes made make sure to open a new SQLPLus session to see acccurate\current values.

orapki: Wallet Actions

Change as required for your environment. 

 -- Create Wallet
 orapki wallet create -wallet <FullPath> -pwd <password> -auto_login

 -- Delete Wallet
 orapki wallet delete -wallet <FullPath> -trusted_cert_all -pwd <password>

 You may need to use the Oracle Wallet Manager: 
   1. Run: owm &
   2. From menu choose: Wallet 🠊 Open.
   2. Select Yes to use your path: /u01/app/oracle/admin/oradb/wallet
   3. From menu choose: Wallet 🠊 Delete (enter password)
   4. Clean up any files as needed in /u01/app/oracle/admin/oradb/wallet.
 Note above is NOT using any environment vars.  Does not work with them!  

  After you bounce database v$encryption_wallet NOT_AVAILABLE will show.

 -- Change Wallet Password
  orapki wallet change_pwd -wallet <FullPath>

  If you are using a wallet with auto login enabled, you must regenerate the auto
  login wallet after changing the password.

  orapki wallet create -wallet <FullPath> -auto_login

Do NOT use environment vars for the <FullPath>.

orapki: Cert Actions

 -- Add Cert
 orapki wallet add -wallet <FullPath>/<MyCert.cer> -trusted_cert -cert "<MyCert.cer>" -pwd <password>

 -- Remove Cert
 orapki wallet remove -wallet <FullPath>/<MyCert.cer> -trusted_cert -cert "<MyCert.cer>" -pwd <password>

 -- Display Cert
 orapki cert display -cert <FullPath>/<MyCert.cer>
  • Do NOT use environment vars for the <FullPath>.
  • View complete cert via: orapki cert display -cert <Path>/redhat.cer -complete

Wallet Config Session: Standard DB

Create show_html_from_url()

 Script here.

Test Non-SSL URL

 SET SERVEROUTPUT ON
 EXEC show_html_from_url('http://oracledba.help/');

Note the HTML is displayed.

Test SSL URL

 SET SERVEROUTPUT ON
 EXEC show_html_from_url('https://www.redhat.com/');
 ORA-29273: HTTP request failed
 ORA-29024: Certificate validation failure
 ORA-06512: at "SYS.SHOW_HTML_FROM_URL", line 33

Note Oracle errors displayed (expected at this point).

New Database Baseline (No Wallet Configured Yet)

 COL wrl_type      FORMAT a12
 COL wrl_parameter FORMAT a35
 COL status        FORMAT a15
 COL wallet_type   FORMAT a15
 SELECT wrl_type,wrl_parameter,status,wallet_type FROM v$encryption_wallet;

 WRL_TYPE     WRL_PARAMETER			 STATUS              WALLET_TYPE
 ------------ -----------------------------------  ----------------    -----------
 FILE	    /u01/app/oracle/admin/oradb/wallet   NOT_AVAILABLE       UNKNOWN

Note: default wallet dir: /u01/app/oracle/admin/$ORACLE_SID/wallet

Create Wallet

 orapki wallet create -wallet /u01/app/oracle/admin/oradb/wallet -pwd MyStrongPw! -auto_login
 Using -auto_login allows you to use NULL when accessing URL via utl_http.request().

 -- Note Wallet Dir Created
 cd /u01/app/oracle/admin/$ORACLE_SID/wallet
 ls -l
 -rw------- 1 oracle oinstall 120 Feb 10 10:02 cwallet.sso
 -rw-rw-rw- 1 oracle oinstall   0 Feb 10 10:02 cwallet.sso.lck
 -rw------- 1 oracle oinstall  75 Feb 10 10:02 ewallet.p12
 -rw-rw-rw- 1 oracle oinstall   0 Feb 10 10:02 ewallet.p12.lck

 -- Note v$encryption_wallet Status
 SELECT wrl_type,wrl_parameter,status,wallet_type FROM v$encryption_wallet;

 WRL_TYPE     WRL_PARAMETER			   STATUS              WALLET_TYPE
 ------------ -----------------------------------  ----------------    -----------
 FILE	      /u01/app/oracle/admin/oradb/wallet   OPEN_NO_MASTER_KEY  UNKNOWN

Get Cert

🠊🠊🠊 This must be done from the Oracle database server itself. 🠈🠈🠈 

 Using FireFox:
 1. Go to: https://www.redhat.com
 2. Click Lock icon 🠊 Show connection Details 🠊 More Information
 3. [View Certificate] 🠊  Details 
 4. Select item at top of Certificate Hierachy (ex: DigiCert High Assurance EV Root CA).
 5. Select [Export]
 6. Save to your wallet dir: 
    /u01/app/oracle/admin/oradb/wallet/redhat.cer
    [X.509 Certificate (PEM)] should already be selected.
 7. [Save] 🠊 [Close]

Add Cert

 orapki wallet add -wallet /u01/app/oracle/admin/oradb/wallet -trusted_cert 
                   -cert "/u01/app/oracle/admin/oradb/wallet/redhat.cer" -pwd MyStrongPw!

 orapki cert display -cert /u01/app/oracle/admin/oradb/wallet/redhat.cer
 Subject:        CN=DigiCert High Assurance EV Root CA,OU=www.digicert.com...
 Issuer:         CN=DigiCert High Assurance EV Root CA,OU=www.digicert.com...
 Valid Until:    Sun Nov 09 19:00:00 EST 2031

Test Cert

 SET SERVEROUTPUT ON
 EXEC UTL_HTTP.set_wallet('file:/u01/app/oracle/admin/oradb/wallet', 'MyStrongPw!');
 EXEC show_html_from_url('https://www.redhat.com/');

 -- If -AutoLogin Used
 COL https FORMAT a150
 SELECT utl_http.request('https://www.redhat.com/', NULL, 
                         'file:/u01/app/oracle/admin/oradb/wallet',NULL
                        ) as "HTTPS" from dual;

 -- If -AutoLogin Not Used
 SELECT utl_http.request('https://www.redhat.com/', NULL, 
                         'file:/u01/app/oracle/admin/oradb/wallet','MyStrongPw!'
                        ) as "HTTPS" from dual;

Note the HTML from secure site (https) is now displayed.

Wallet Config Session: RAC 19c DB

19c RAC Wallet Default Path 

 /u01/app/oracle/product/19.3.0.0.0/dbhome_1/admin/oradb/wallet

Get Baseline gv$encryption_wallet (no wallet configured) 

 COL inst_id       FORMAT 999 HEAD 'Inst'
 COL wrl_type      FORMAT a12
 COL wrl_parameter FORMAT a35
 COL status        FORMAT a15
 COL wallet_type   FORMAT a15
 SELECT inst_id,wrl_type,wrl_parameter,status,wallet_type FROM gv$encryption_wallet;

Create Wallet (On All Nodes) 

 orapki wallet create -wallet /u01/app/oracle/product/19.3.0.0.0/dbhome_1/admin/oradb/wallet 
                      -pwd MyStrongPw! -auto_login
 Operation is successfully completed.

QC (via gv$encryption_wallet) 

 Inst WRL_TYPE	  WRL_PARAMETER 		      STATUS	      WALLET_TYPE
 ---- ------------ ----------------------------------- --------------- ---------------
    2 FILE	  /u01/app/oracle/product/19.3.0.0.0/ OPEN_NO_MASTER_ UNKNOWN
 		  dbhome_1/admin/oradb/wallet	      KEY

    1 FILE	  /u01/app/oracle/product/19.3.0.0.0/ OPEN_NO_MASTER_ UNKNOWN
 		  dbhome_1/admin/oradb/wallet	      KEY

Export or Copy Cert (On All Nodes) 

 Copy or Export To:
 /u01/app/oracle/product/19.3.0.0.0/dbhome_1/admin/oradb/wallet/redhat.cer

 ls -l /u01/app/oracle/product/19.3.0.0.0/dbhome_1/admin/oradb/wallet/redhat.cer

Add Cert (On All Nodes) 

 orapki wallet add -wallet /u01/app/oracle/product/19.3.0.0.0/dbhome_1/admin/oradb/wallet 
        -trusted_cert 
        -cert "/u01/app/oracle/product/19.3.0.0.0/dbhome_1/admin/oradb/wallet/redhat.cer" 
        -pwd MyStrongPw!
 Operation is successfully completed.

 orapki cert display 
    -cert /u01/app/oracle/product/19.3.0.0.0/dbhome_1/admin/oradb/wallet/redhat.cer

Test (From All Nodes and RAC SCAN) 

 SET SERVEROUTPUT ON
 EXEC 
   UTL_HTTP.set_wallet('file:/u01/app/oracle/product/19.3.0.0.0/dbhome_1/admin/oradb/wallet',
                       'MyStrongPw!');
 EXEC show_html_from_url('https://www.redhat.com/');

 COL https FORMAT a150
 SELECT utl_http.request
        ('https://www.redhat.com/', NULL, 
         'file:/u01/app/oracle/product/19.3.0.0.0/dbhome_1/admin/oradb/wallet',NULL)
         as "HTTPS" from dual;

Wallet Config Session: RAC 12.1 DB

Reguardless of what is on the Internet, I have only been able to get a 12.1 Wallet based cert working from an NFS mounted volume in RAC environments. Myriad attempts to create local and\or in ASM did not work for me.

As a result, the example here uses an NFS mounted volume (/u03) from all nodes. The Wallet path works great with correctly mounted NFS volumes.

In 19c you can simply create it on each node generally as you would non-RAC. See 19c RAC Config Session.

NFS Client Config (from Node 1) 

 su -
 mkdir -p /u03/admin/wallets/oradb
 cd /u03
 chown oracle:oinstall /u03/admin
 chown oracle:oinstall /u03/admin/wallets
 chown oracle:oinstall /u03/admin/wallets/oradb

 su - oracle
 touch /u03/admin/wallets/oradb/test.txt

sqlnet.ora (on all nodes) 

 Configure sqlnet.ora with your NFS path as shown here.

Configure Wallet (from Node1) 

 su - oracle
 cp /media/sf_sw/xfer/redhat.cer /u03/admin/wallets/oradb/

 orapki wallet create -wallet /u03/admin/wallets/oradb -pwd MyStrongPw! -auto_login
 orapki wallet add -wallet /u03/admin/wallets/oradb -trusted_cert 
                   -cert "/u03/admin/wallets/oradb/redhat.cer" -pwd MyStrongPw!
 orapki cert display -cert /u03/admin/wallets/oradb/redhat.cer

QC (on all nodes) 

 SET SERVEROUTPUT ON
 SELECT utl_http.request('https://www.redhat.com/',
 NULL,'file:/u03/admin/wallets/oradb','MyStrongPw!') as "HTTPS" from dual;

 -- Oracle Wallet Mgr
 owm & 
 Open
   /u03/admin/wallets/oradb

 -- orapki
 orapki wallet display -wallet /u03/admin/wallets/oradb

 -- gv$encryption_wallet
 COL inst_id       FORMAT 999 HEAD 'Inst'
 COL wrl_type      FORMAT a12
 COL wrl_parameter FORMAT a35
 COL status        FORMAT a15
 COL wallet_type   FORMAT a15
 SELECT inst_id,wrl_type,wrl_parameter,status,wallet_type FROM gv$encryption_wallet;

 Inst WRL_TYPE	  WRL_PARAMETER 		      STATUS	      WALLET_TYPE
 ---- ------------ ----------------------------------- --------------- ---------------
    2 FILE	  /u03/admin/wallets/oradb/	      CLOSED	      UNKNOWN
    1 FILE	  /u03/admin/wallets/oradb/	      CLOSED	      UNKNOWN

Error: If NFS Volume Not Mounted 

 ERROR at line 1:
 ORA-29273: HTTP request failed
 ORA-28759: failure to open file
 ORA-06512: at "SYS.UTL_HTTP", line 1491
 ORA-06512: at line 1

show_html_from_url()

CREATE OR REPLACE PROCEDURE show_html_from_url (
  p_url  IN  VARCHAR2,
  p_username IN VARCHAR2 DEFAULT NULL,
  p_password IN VARCHAR2 DEFAULT NULL
) AS
  l_http_request   UTL_HTTP.req;
  l_http_response  UTL_HTTP.resp;
  l_text           VARCHAR2(32767);
BEGIN
  -- Make a HTTP request and get the response.
  l_http_request  := UTL_HTTP.begin_request(p_url);

  -- Use basic authentication if required.
  IF p_username IS NOT NULL and p_password IS NOT NULL THEN
    UTL_HTTP.set_authentication(l_http_request, p_username, p_password);
  END IF;

  l_http_response := UTL_HTTP.get_response(l_http_request);

  -- Loop through the response.
  BEGIN
    LOOP
      UTL_HTTP.read_text(l_http_response, l_text, 32766);
      DBMS_OUTPUT.put_line (l_text);
    END LOOP;
  EXCEPTION
    WHEN UTL_HTTP.end_of_body THEN
      UTL_HTTP.end_response(l_http_response);
  END;
EXCEPTION
  WHEN OTHERS THEN
    UTL_HTTP.end_response(l_http_response);
    RAISE;
END show_html_from_url;
/

Create Master Key

 alter system set encryption key identified by "MyStrongPw!";

 Note files in dir updated: /u01/app/oracle/admin/oradb/wallet

 -- Note v$encryption_wallet Status
 SELECT wrl_type,wrl_parameter,status,wallet_type FROM v$encryption_wallet;

 WRL_TYPE     WRL_PARAMETER			   STATUS              WALLET_TYPE
 ------------ -----------------------------------  ----------------    ---------
 FILE	      /u01/app/oracle/admin/oradb/wallet   OPEN                PASSWORD

sqlnet.ora

If you do not use the default Wallet path you must make the following
sqlnet.ora entry that has the full path to your wallet directory. 

ENCRYPTION_WALLET_LOCATION=
  (SOURCE=
    (METHOD=FILE)
    (METHOD_DATA=
      (DIRECTORY=/u03/admin/wallets/oradb)
    )
  )
  • Change /u03/admin/wallets/oradb as required for your environment.
  • Do not include any environment variables, i.e. use the full path.
  • Ensure there is no forward slash at the end of the DIRECTORY value.

Neglecting to do this will reult in the folowing error: 

 ORA-28368: cannot auto-create wallet when setting the key.

Oracle Wallet Manager (OWM)

To run, logged in as the oracle user execute:
oracle> $ORACLE_HOME/bin/owm

You can use the Oracle Wallet Manager (OWM) to perform the following tasks:

  • Creating wallets.
  • Generating certificate requests.
  • Opening wallets to access PKI-based services.
  • Saving credentials to hardware security modules.
  • Upload\download wallets to an LDAP directory.
  • Importing third-party PKCS #12 -format wallets.

References

The encryption wallet is the one recommended for TDE. It needs to be opened manually after database startup and prior to TDE encrypted data being accessed. Because data is encrypted in REDO logs, UNDO and TEMP tablespaces, the TDE master encryption key needs to be available to the database before it is opened. For TDE Wallet requirements go here.