External Password Store for Oracle Databases

Published on: 2024-09-18 - Permalink Oracle

Sometimes we have the need to arrange a set of instructions or scripts on automated processes, E.g when we automate a backup process, or when we prepare a batch process to load data in a database or even practical maintenance processes, like purging an audit table.

In the case of Oracle scripts, it's very common to use an externally authenticated user, this is, a user which is authenticated using an external identity, like the OS or a Kerberos service or an LDAP, however, besides the OS authentication, those are complex setups that includes more licensing costs and infrastructure.

If we're not running those process on the database box, a common practice is to use a designed user for those processes, however, those credentials are usually enclosed on the job managers or parent scripts, even worse, the execution line for those scripts are recorded on log entries (which they might also include the user credentials)

Oracle has a feature implemented on the OCI client named Secure External Password Store, it allows us to store credentials on an encrypted wallet and those can be accessed only by the client itself during the login operation.

Basic Syntax

We can create a new EPS wallet by using the "mkstore" command

mkstore -wrl <wallet_location> -create

Create Entry

Once created, we can manage its entries using the same command with the -createCredential parameter, just like this

mkstore -wrl <wallet_location> -createCredential <db_connect_string> <username> <password>

This way, each credential will be recorded using the connection string as its identifier, we'll be using this identifier for maintaining the credential information and for connecting into the database

Modify Entry

In order to modify an existing record, we can use the ´-modifyCredential´ command

mkstore -wrl <wallet_location> -createCredential <db_connect_string> <new_username> <new_password>

SQLNet Profile setup

After creating and setting up our wallet, we need to setup our sqlnet.ora file with the following entries

• WALLET_LOCATION
• SQLNET.WALLET_OVERRIDE

WALLET_LOCATION = 
  ( SOURCE = 
    (METHOD = FILE)
	(METHOD_DATA = 
	  (DIRECTORY = /path/to/eps)
	)
  )
SQLNET.WALLET_OVERRIDE = TRUE

Once everything is set, we can use the following connection string /@<db_connect_string> instead of <user>/<password>@<db_connect_string>

Troubeshooting

TOAD Always asks for username/password when using an EPS Wallet or external authentication

when we setup an EPS connection using TOAD from Quest we may find that the tool is always asking us for a username and a password, even if we setup the SQLNet profile properly as described above.

It's not intuitive, but if we input ´EXTERNAL´ as username, it will not request us a password and will try to use the external authentication defined on the SQLNet Profile, please note that we need to use the Oracle Client instead of a JDBC connection in order to make it work.