Create a Wallet to Store Secure User Credentials for RMAN Connection

In this blog, I’ll be giving a tutorial on an Oracle hidden gem using an Oracle tool to securely allow you to run RMAN without passing the password in cleartext. The Oracle Database Secure External Password Store feature, which does not require any additional license to use, will allow you to set up a wallet to enable user credentials to be stored within it. Therefore, allowing the Oracle OS user to use an alias to connect to the database without the need to enter the username or password.

Scenario: You want to connect to RMAN to run backups as the Oracle OS user. You are using an RMAN recovery catalog, so you must connect to both the CATALOG and the TARGET database. You are currently passing in the user and password for both. You need a solution where users and passwords are not stored in plain text files on the OS or passed on the Linux command line where it could be stored in .bash_history.

Here is the test environment used for this tutorial:

Linux 6

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit

The present connection you use looks somewhat like the following below (note: this is just a test system). You are currently passing the following syntax manually on the command line, or you have a shell script with the user/password hardcoded in a text file. CDB is the database as defined in the tnsnames.ora file:

rman catalog rmansys/welcome1@rmancat target sys/welcome1@cdb

The first step in using a secure wallet is to create the wallet and directory where the wallet files reside:

Create a new directory for the ‘wallet’ in the ORACLE_HOME/network directory (or any directory you choose):

echo $ORACLE_HOME /u01/app/oracle/product/12.1.0.2/dbhome_1 cd $ORACLE_HOME cd network mkdir wallet

You have now created the wallet directory, and now need to edit the sqlnet.ora to tell the Oracle NET connections where to look for the wallet information.

cd $ORACLE_HOME/network/admin

Modify the sqlnet.ora and add the WALLET_LOCATION and SQLNET.WALLET_OVERRIDE parameter to start using the Secure External Password Store:

vi sqlnet.ora
WALLET_LOCATION =
   (SOURCE =
  	(METHOD = FILE)
  	(METHOD_DATA = (DIRECTORY=/u01/app/oracle/product/12.1.0.2/dbhome_1/network/wallet))
    )
SQLNET.WALLET_OVERRIDE = TRUE

The sqlnet.ora parameter, SQLNET.WALLET_OVERRIDE=TRUE, is how we tell the Oracle client to use the wallet manager opposed to the standard OS Authentication.

Your sqlnet.ora may now look similar to the following. If you didn’t already have a sqlnet.ora file, then you must create it and add the wallet location information.

Next, you will add a new entry in the tnsnames.ora file for the RMAN catalog connection. Note: only one user/password credential can be stored per connect string. In this TNS entry, we are only storing the RMAN recovery catalog user credential.

Note: only one password may be stored in the wallet per TNS alias. This works fine in our example because we only need one user to connect to the RMAN catalog and one user to connect to the target database (sys) that we will use to backup or restore the database.

vi tnsnames.ora
 
catalog  =
  (DESCRIPTION =
	(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
	(CONNECT_DATA =
  	(SERVER = DEDICATED)
  	(SERVICE_NAME = rmancat)
  	(SID = rmancat)
	)
  )

Verify the new connection string works

tnsping catalog

TNS Ping Utility for Linux: Version 12.1.0.2.0 – Production on 14-JUL-2020 12:40:12

Used parameter files:

/u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = rmancat) (SID = rmancat)))

OK (0 msec)

Now, you are ready to create the secure wallet for the RMAN recovery catalog user and password.

The first step is to create the wallet, using the “-create command.” This command will prompt you for the “wallet password,” which is only for the wallet and not the user password in the database.

mkstore -wrl /u01/app/oracle/product/12.1.0.2/dbhome_1/network/wallet -create

Oracle Secret Store Tool : Version 12.1.0.2

Enter password:

Enter password again:

(My test wallet password in this tutorial was “welcome1.” On your production system, you will want a much more secure password. You will then need to remember this password for future maintenance.)

Next, you will create the RMAN recovery catalog user “rmansys,” which is my example credentials in the wallet, using the “-createCredential” command. You’ll need to include three parameters: 1) connection string (catalog), 2) user (rmansys), and 3) password (welcome1). Again, you will be prompted to enter the main wallet password, which you created in the previous step.

mkstore -wrl /u01/app/oracle/product/12.1.0.2/dbhome_1/network/wallet -createCredential catalog rmansys welcome1

Oracle Secret Store Tool : Version 12.1.0.2

Enter wallet password:

Create credential oracle.security.client.connect_string1

Now, we can connect to the RMAN recovery catalog without passing in the catalog credentials. However, we also want to hide the target database credentials.

[oracle@bigdatalite admin]$ rman catalog /@catalog target sys/welcome1@cdb

Recovery Manager: Release 12.1.0.2.0 – Production on Wed Jun 19 17:49:34 2019

connected to target database: CDB (DBID=2127236908)

connected to recovery catalog database

RMAN> exit

Once again, we will add a connect string to the tnsnames.ora file for the “target” database user and password. As you see in this example, I am naming the tns alias “target.” This is because that is how I am referring to the alias in the RMAN connection string. In the tnsnames.ora, the sid and service_name is defined as the actual database name for the target database that will be backed up by RMAN. Along with the appropriate host and port for the actual target database.

vi tnsnames.ora
 
target  =
  (DESCRIPTION =
	(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
	(CONNECT_DATA =
  	(SERVER = DEDICATED)
  	(SERVICE_NAME = cdb)
  	(SID = cdb)
	)
  )

Verify the new connection string works

tnsping target

TNS Ping Utility for Linux: Version 12.1.0.2.0 – Production on 14-JUL-2020 12:57:14

Used parameter files:

/u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cdb) (SID = cdb)))

OK (0 msec)

Then, we will add another credential to the wallet that already exists. This time we will include “target” as the connect string, “sys” as the sys user in the target database, and the appropriate sys password.

mkstore -wrl /u01/app/oracle/product/12.1.0.2/dbhome_1/network/wallet -createCredential target sys welcome1

Oracle Secret Store Tool : Version 12.1.0.2

Enter wallet password:

Create credential oracle.security.client.connect_string2

There you have it! You can now securely log into the RMAN catalog and connect to the target database as sys without displaying the passwords.

rman catalog /@catalog target /@target

Recovery Manager: Release 12.1.0.2.0 – Production on Wed Jun 19 17:53:46 2019

connected to target database: CDB (DBID=2127236908)

connected to recovery catalog database

RMAN> sql select name from v$database;

NAME

———

CDB

RMAN> sql select user from dual;

USER

——————————

SYS

To list the contents of the wallet store, you just need to issue the following command:

mkstore -wrl /u01/app/oracle/product/12.1.0.2/dbhome_1/network/wallet -listCredential

Oracle Secret Store Tool : Version 12.1.0.2

Enter wallet password:

List credential (index: connect_string username)

2: target sys

1: catalog rmansys

Note: In version 12.1, I can still connect to sqlplus on the Linux command line as sysdba without passing in a password, thus using OS authentication.

sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 14 13:00:00 2020

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select user from dual;

USER

——————————

SYS

SQL> select name from v$database;

NAME

———

CDB

I’d like to offer one final word to clarify how you can ensure not just any OS user can now access the databases by using the connection string “target” or “catalog.” The wallet location directory permissions should be set to ensure that only the Oracle OS user (and maybe the Oracle group, if so desired) has access to use the wallet. All other OS users will still need to enter a user and password to connect to the appropriate databases. By default, the wallet files only have read-write permissions for the Oracle OS user that created the wallet.

[oracle@bigdatalite ~]$ cd $ORACLE_HOME/network/wallet

-rw——-. 1 oracle oinstall 904 Jul 14 12:50 ewallet.p12

-rw——-. 1 oracle oinstall 949 Jul 14 12:50 cwallet.sso

In this example, the OS user “test” (this could be a developer on your server) has the same OS group as the Oracle user. However, the wallet location was secured, but taking away rights to the group oinstall. Therefore, an error occurs when the test OS user attempts to connect using the @catalog.

[test@bigdatalite ~]$ id test

uid=501(test) gid=54321(oinstall) groups=54321(oinstall)

[test@bigdatalite ~]$ sqlplus /@catalog

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 14 13:25:57 2020

ERROR:

ORA-12578: TNS:wallet open failed

Enter user-name:

If you instead decide to allow access to OS users in the oinstall group, then the following permissions are needed for the credential file only:

Su – oracle

[oracle@bigdatalite wallet]$ chmod 660 cwallet.sso

[oracle@bigdatalite wallet]$ ls -l

total 8

-rw-rw—-. 1 oracle oinstall 949 Jul 14 12:50 cwallet.sso

-rw-rw-rw-. 1 oracle oinstall 0 Jul 14 12:41 cwallet.sso.lck

-rw——-. 1 oracle oinstall 904 Jul 14 12:50 ewallet.p12

-rw-rw-rw-. 1 oracle oinstall 0 Jul 14 12:41 ewallet.p12.lck

Now, any OS user with the same group as the Oracle user can log into sqlplus using the wallet by way of the tns alias created without the need to enter a user and password.

su – test

[test@bigdatalite network]$ sqlplus /@catalog

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 14 13:47:14 2020

Last Successful login time: Tue Jul 14 2020 13:41:32 -04:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

This tutorial went over a real-world example of using the Oracle Wallet Tool’s secure external password feature. As security best practices you should not be passing database credentials in clear text on your Linux server. This tool is a helpful way to keep database credentials hidden and secure.

If you have any comments regarding secure external credentials using the Oracle Wallet tool, don’t hesitate to reach out to us here or via the comments below.