Passwords are like underwear. Don't leave them out where people can see them, change them regularly, and don't loan 'em out.
Unfortunately it is not uncommon to come across scripts in which passwords are part of the code, and —of course— never changed.
#!/bin/bash
sqlplus -s scott/tiger@orcl << EOF
select * from dual;
exit;
EOFSome consider it a better alternative to record passwords in files and read them from there.
#!/bin/bash
PASS=$(cat password_seguro.txt)
sqlplus -s scott/${PASS}@orcl << EOF
select * from dual;
exit;
EOFAnd there will surely be more clever tricks to try to hide passwords, but we will agree that having them in the clear is still a weakness in security.
The solution? Oracle Secure External Password Store. Let’s see what it’s all about.
Secure External Password Store
Oracle proposes to register the database connection credentials in a file called wallet, which is encrypted.
Once implemented, the code should only include the database connection string and Oracle will take care of the rest.
Since Oracle finds only the connection string, it checks in the sqlnet.ora file if the wallet is enabled and if so takes note of its location, reads it, and looks for an entry that matches the connection string, retrieves the user and password, and that’s it!
You must be eager to know how to do this magic by now, so here’s the step-by-step.
Create the wallet
For this task we will use mkstore, and we will start by creating a directory and in it the required files.
We will be asked for a password and re-enter it, taking care that it is a string of at least 8 characters, of which at least one must be a number or a symbol.
We must take note of this password because without it we will not be able to maintain the wallet.
[oracle@node1 ~]$ WALLET=/home/oracle/wallet
[oracle@node1 ~]$ mkdir ${WALLET}
[oracle@node1 ~]$ mkstore -wrl ${WALLET} -create
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2024, Oracle and/or its affiliates. All rights reserved.
Enter password: *********
Enter password again: *********1
2
Register credentials
We must have the following data at hand:
- Connection string (orcl)
- Database user (scott)
- User password (tiger)
We will be asked for the password of the wallet.
[oracle@node1 ~]$ mkstore -wrl ${WALLET} -createCredential orcl scott tiger
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2024, Oracle and/or its affiliates. All rights reserved.
Enter wallet password: *********Securing the wallet
Although it is not mandatory, as we are looking to improve security, in order to prevent unauthorized users from accessing the wallet, we must change some permissions at the directory and file level.
[oracle@node1 ~]$ chmod 755 ${WALLET}
[oracle@node1 ~]$ chmod 750 ${WALLET}/*
[oracle@node1 ~]$ ls -la ${WALLET}
total 12
drwxr-xr-x. 2 .
drwx------. 8 ..
-rwxr-x---. 1 cwallet.sso
-rwxr-x---. 1 cwallet.sso.lck
-rwxr-x---. 1 ewallet.p12
-rwxr-x---. 1 ewallet.p12.lck3
4
Register the wallet
For Oracle to know that the wallet exists, the sqlnet.ora file must be edited, adding two parameters: wallet_location and sqlnet.wallet_override.
In the first one we indicate the directory in which we have created the wallet, and with the second one we activate the functionality.
WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = /home/oracle/wallet))
)
SQLNET.WALLET_OVERRIDE = TRUEWe must verify that the database connection string is registered in the tnsnames.ora file.
ORCL =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = node1)
(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)We test the connection via wallet
We can use sqlplus, rman, expdp, etc., to verify that it is already possible to connect to the database without having to indicate the username and its password.
[oracle@node1 ~]$ sqlplus /@orcl
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Sep 8 12:16:04 2024
Version 19.23.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Last Successful login time: Sun Sep 08 2024 12:15:21 -05:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.23.0.0.0
SQL> show user
USER is "SCOTT"5
Wallet maintenance
Over time we will have registered many credentials, which we can consult at any time.
[oracle@node1 ~]$ mkstore -wrl ${WALLET} -listCredential
Enter wallet password: **********
List credential (index: connect_string username)
1: orcl scottAs it is a good practice to keep changing passwords, we can of course update the credentials when necessary.
[oracle@node1 ~]$ mkstore -wrl ${WALLET} -modifyCredential orcl scott TiGeR
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2024, Oracle and/or its affiliates. All rights reserved.
Enter wallet password: **********If we want to verify the password of some credential, we must obtain the value of the entry oracle.security.client.passwordNNN, where NNN is the credential number as shown in the listing.
[oracle@node1 ~]$ mkstore -wrl ${WALLET} -listCredential
Enter wallet password: *********
List credential (index: connect_string username)
2: orcl_rman sys
1: orcl scott
So, if we want to know the password associated to the user sys for the connection string orcl_rman, we will take into account that it is the second in the list, and therefore we must execute:
[oracle@node1 ~]$ mkstore -wrl ${WALLET} -viewEntry oracle.security.client.password2
Enter wallet password: *********
oracle.security.client.password2 = change_on_installConclusions
Following an uncomplicated procedure, and if we complement it with the gradual password rollover, we can improve security by removing credentials from scripts.
If you want to learn more about using Oracle Secure External Password Store, please read these Notes available on My Oracle Support:
| 340559.1 | Using The Secure External Password Store |
| 1383938.1 | How To Configure The Secure External Password Store To Allow The Connection To RMAN Catalog? |
| 1441745.1 | Using a Secure External Password Store with the JDBC Thin Driver |