Interactive work
How to avoid cleartext passwords with Oracle? In this post I will focus on databases with traditional accounts with username/password, and not on other authentication methods like kerberos (AD). For interactive work, it is not a problem, you can iteractively type the password
sqlplus myuser@mydb
....
Enter password:
At least on Linux, the sqlplus and rman tool will rewrite the commandline and hide the arguments. Even if you login with user/password, it is not visible in the ps output for other users.
$ ps -ef |grep sqlplus |grep -v grep
oracle 265034 259996 0 10:06 pts/8 00:00:00 sqlplus
Automation
But for automation you usually need a password stored somewhere. Maybe you can get around that with OS authentication. That works well for all accounts with SYSDBA, SYSOPER, SYSBACKUP, … privileges
[oracle] sqlplus / as sysdba
[john] sqlplus sys/wrong-password as sysoper
If your [OS-user] is in the OS-group of sysdba (usually “dba”) or sysoper (usually “oper”) and you specify “as sys*” , then username and password is completely ignored if you do a local connect (not for remote connect user@service)
Questions about your own setup? A dbi services consultant can look at your case.
Talk to an expert (opens in a new tab)You are still using the non-cdb architecture? Also for normal users you can use OS-authentication. If your OS-user is “application”, create an OS-authenticated user
SQL> show parameter os_authent_prefix
os_authent_prefix string ops$
SQL> create user ops$application identified externally;
SQL> grant connect to ops$application;
But for normal users in pluggable databases that does no longer work, It is only possible for sys and system as Mike Dietrich mentioned.
The “good old” textfile
The alternative is to store the password in a file and to restrict the file-access so that only allowed users can read it.
chmod 700 batchscript.sh
Within the server, it will be well secured. But depending on the backup, the number of people with access to this file will increase, and so will increase the risk of misuse.
The Oracle way – Oracle Wallet
Overview and introduction
The Oracle wallet is based on a PKCS#12 archive file to store many cryptographic objects like private keys or certificates. Oracle can store passwords of users in it. This file is named ewallet.p12 and is password-protected.
To prevent Oracle from having to store a password for the ewallet.p12 file, it uses a trick. Every time we make a change, we have to unlock the ewallet.p12 file with the password. The Oracle tools orapki and mkstore then write the data simultaneously to the ewallet.p12 file and to a proprietary file named cwallet.sso.
Never edit the ewallet.p12 file with non-Oracle tools (e.g. openssl)!
Oracle has 3 types of auto-login wallets:
- auto-login : If you have the cwallet.sso file, you can use via Oracle-tools the credentials in it without providing a password.
- auto-login-local : like above, but it only works for the user and the host it was created. If you copy it to another host, it does not work.
- auto-login-only : There is no ewallet.p12 file, only the cwallte.sso and the content is readable without providing a password.
To see what type of wallet it is, see my blog post Different types of Oracle wallets.
How to implement
All Oracle tools like sqlplus, rman or dgmgrl have direct access to the stored passwords in the proprietary cwallet.sso; Let’s see how to configure it: Create the wallet and add a credential.
mkdir -p /u01/app/oracle/network/wallet
cd /u01/app/oracle/network/wallet # from now on, "." refers to the current dir.
orapki wallet create -wallet . -auto_login_local
mkstore -wrl . -createCredential alias.domain.ch user password
mkstore -wrl . -createCredential mydb myuser myPasswd
the 1st argument after createCredential must be resolvable by tnsping.
Now we have to configure that the wallet should be used, and where it is. This is done in the sqlnet.ora file. I strongly recommend to not configure it globally, but only for users that need it. If there is a problem, that can block the login of all users! For these users, we can configure a different TNS_ADMIN directory. I will use the wallet directory itself.
cd /u01/app/oracle/network/wallet
ln -s /u01/app/oracle/network/admin/*.ora . #symlink the files from normal TNS_ADMIN
rm sqlnet.ora
cp /u01/app/oracle/network/admin/sqlnet.ora .
vi sqlnet.ora #add the following lines
SQLNET.WALLET_OVERRIDE = TRUE
WALLET_LOCATION=(
SOURCE=(METHOD=FILE)
(METHOD_DATA=(DIRECTORY=/u01/app/oracle/network/wallet))
)
Now we are ready to connect
sqlplus /@mydb
...
Last Successful login time: Tue Nov 17 2026 10:15:26 +01:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0
SQL>
It works 🙂
You specify / and the alias. You will not provide the username. Oracle looks for username/password for this alias. This means that we now have to create a separate alias for each user/service combination and update this additional alias in tnsnames.ora or LDAP. If each user has their own wallet and their own TNS_ADMIN, then we can continue to use the global alias. However, for administrative purposes where we need SYS and SYSTEM, we will likely have to create two aliases: mydb_sys and mydb_system.
KeePass
Introduction
KeePass or KeePassXC are well-known password managers on Windows and Linux. Both programs can use the same passwod files, usually with the ending .kdbx
What is less well known is that the file can also be read and written using Perl from the command line. If you use a central KeePass file that can be centrally accessed by the DBA’s, and it is also avaliable on the db-servers, then you can use it interactively on your desktop, and for automation on the servers.
There are 2 modules available:
- File::KeePass
- Less additional modules required. An Oracle perl additionally needs Crypt::Rijndael
- Supports password and keyfile
- Unfortunately only understands the older KeePass < V4 files (KeePass automatically converts to V4, with KeePassXC you can configure)
- File::KDBX
- More additional modules required
- Understands the latest KeePass file format
- Working with keyfile seems not to work (at least not documented how to specify the file)
Implementation with File::KDBX
Easiest way is to use the Perl of the server-OS, e.g. for an Oracle Enterprise Linux. Enable the EPEL repo and in oracle-linux-ol10.repo the ol10_addons and ol10_codeready_builder
dnf install \
perl \
perl-Crypt-Rijndael \
perl-Digest-SHA \
perl-Compress-Raw-Zlib \
perl-XML-Parser \
perl-CryptX \
perl-Time-Piece \
perl-XML-LibXML \
perl-Devel-GlobalDestruction \
perl-Ref-Util \
perl-Scope-Guard \
perl-Test-Deep \
perl-Test-Fatal \
perl-Test-Warnings \
perl-boolean \
perl-namespace-clean \
perl-App-cpanminus
The modules not in the repository can be added with cpanm (it also installs the dependencies for these 2 modules)
cpanm File::KDBX
cpanm File::KeePass:KDBX
Now let’s see if it generally works. Copy an existing “Database.kdbx” file to the script location, adapt the password and run below test-script.
#!/usr/bin/env perl
use File::KeePass::KDBX;
my $k = File::KeePass::KDBX->new;
$k->load_db('Database.kdbx', 'KeePass-Password') or die;
my $groups = $k->groups->[0];
use Data::Dumper; print Dumper(\$groups);
I will not focus on how to lookup the passwords. For dbi-services customers, you can use the DMK package 2.3 (currently beta), which provides getpw.pl, setpw.pl and delpw.pl scripts. Examples:
getpw.pl system@pdb01 #returns system/Pa55word@pdb01 (*)
getpw.pl -p system@pdb01 #only prints the password (*)
getpw.pl -l #list all "/group/: user@alias"
sqlplus $(getpw.pl system@pdb01) #directly use the output for login in sqlplus
setpw.pl sys/Pa55word@CDB03
setpw.pl sys@CDB03 #interactively asks for password (hidden)
(*) If interactively used, getpw.pl prints the password in red font on red background. With this trick, it is not directly human-readable, but you can use it for copy/paste. If you want to see it human-readable, use the -r option
Encrypting a password
The main focus in this blog post is to hide the passwords. How we can hide the password of KeePass itself?
For that, I use the SSH RSA key-pair for encryption and decryption. These files are hopefully well-protected! If you do not yet have such a key-pair, create a new one, otherwise convert the existing key to the PEM format.
ssh-keygen -t rsa -b 2048 -m PEM -N '' -f $ssh_rsa_key
cp -p $ssh_rsa_key $ssh_rsa_key.kp.pem && ssh-keygen -p -m PEM -f $ssh_rsa_key.kp.pem -N ''
To encrypt the password, use the public key in PEM format. It can be derived from the private key.
openssl rsa -in $ssh_rsa_key.kp.pem -pubout -out /tmp/pub$$.pem
encrypted_pwd=`echo '$cleartext' | openssl pkeyutl -encrypt -pubin -inkey /tmp/pub$$.pem |base64`
rm -f /tmp/pub$$.pem
To decrypt the password, use
echo "$encrypted_pwd" |base64 -d |openssl pkeyutl -decrypt -inkey $ssh_rsa_key.kp.pem
Whenever possible, store the keepass file, the ssh-key and the encrypted password, whenever possible, on different storage that will be backed up differently. If someone have access to the backup. it needs the backup of all volumes, which increases the complexity.
Also possible would be to store the encrypted password in a database, then it is separated from the OS backup.