Oracle’s secure external password store

This post has been published in 2009 and a lot has happened since that day. It appears as if it was still quite popular, which is why I decided to update it to Oracle 19c. What hasn’t changed though is the lack of adoption of this feature. I can’t quite understand why there are still so many scripts out there somehow trying to hide a clear text password from plain sight, with varying degree of success. Not storing a password in the backup script at all is probably the most secure way of doing things.

This article was based on Oracle 19.5.0, running on Oracle Linux 7. I used a single instance, non-CDB database. Instructions for other configurations may differ, refer to the official documentation (see below) for details.

Creating the Secure External Password Store

The procedure to create the password store hasn’t changed much since the original post was written. My intention in this example is to call RMAN to create a database backup without having to provide the password for the database. The password store is persisted on the database server for convenience.

First, I need a directory where to store the wallet. I went with $ORACLE_BASE/admin/$ORACLE_SID/wallet. I should probably add that my database is a single instance, non-CDB database. The wallet is created within said directory:

[oracle@server1 wallet]$ mkstore -wrl $(pwd) -create
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Enter password:   
Enter password again:   
[oracle@server1 wallet]$ 

Make sure to use a Strong Password when prompted. The next step is to add the credential to the wallet, as shown in this example:

[oracle@server1 wallet]$ mkstore -wrl $(pwd) -createCredential SEPTEST sys
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Your secret/Password is missing in the command line 
Enter your secret/Password:   
Re-enter your secret/Password:   
Enter wallet password:   
[oracle@server1 wallet]$  

The -createCredential flag takes the TNSname and the username as arguments. Theoretically it allows you to enter the corresponding password at the command line, but that’s a Very Bad Idea as it’s too easy to get hold of the password. Please don’t do that. In the above example I added the SYS account (for backups) to the password store. Note the first prompt is the password for the SYS account, the next prompt is for the wallet as per the first step.

Using the Secure External Password Store

Now that the credential is stored in the wallet, the next steps are to make them available to clients. This is done in 2 files: sqlnet.ora and tnsnames.ora. Since I don’t like messing around with these in $ORACLE_HOME/network/admin I put them in the directory containing my backup script. The first file to create is sqlnet.ora:

$ cat sqlnet.ora 
WALLET_LOCATION =
  (SOURCE =(METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /u01/app/oracle/admin/ORA19NCDB/wallet)
    )
  )

SQLNET.WALLET_OVERRIDE = TRUE 

This is the bare minimum, refer to the official documentation (Chapter 3 of the 19c Security Guide) for any potential additional parameters you must add.

The next file to create is tnsnames.ora. Just remember that your entry has to match the first argument in the call to -createCredential. In this example it’s SEPTEST.

$ cat tnsnames.ora 
SEPTEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORA19NCDB)
    )
  ) 

The only remaining task is to set TNS_ADMIN to the directory containing the 2 files, and I’m good to go:

$ export TNS_ADMIN=$(pwd)
$ rman target /@septest

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Jan 16 18:06:02 2020
Version 19.5.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORA19NCD (DBID=49197792)

RMAN>  

Note the connection syntax is different: when connecting via the Secure External Password Store you prefix the @tnsname with a forward slash as shown in the example.

More Information

If you are a fan of sqlcl, you might find an earlier post about using the Secure External Password Store with sqlcl useful. Even more information can be found in the Oracle Database 19c Security Guide, chapter 3 “Configuring Authentication”.