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
Copyright (c) 1997, 2014, Oracle. All rights reserved.
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
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.
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
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.
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
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
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
Copyright (c) 1997, 2014, Oracle. All rights reserved.
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
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.
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
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
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
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.
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
Copyright (c) 1982, 2014, Oracle. All rights reserved.
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
Copyright (c) 1982, 2014, Oracle. All rights reserved.
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
Copyright (c) 1982, 2014, Oracle. All rights reserved.
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.