Secure your SQL scripts with an Oracle wallet

Introduction

Security is a real topic today: encryption, increased password complexity, regular patching, auditing, … But it’s tough to identify each security issue that needs a fix. Among these security flaws you may discover, the use of clear credentials in scripts is one of them. A password should never be in a text file, for sure.

Oracle wallets

A simple way to avoid passwords in script files is to use wallets to store passwords. Wallet is basically a file where passwords are stored with a strong encryption. A wallet is itself protected by a password, so you may think that it’s useless if you need to provide the password of the wallet in the script, but autologin wallets are there to address this problem. Actually, you won’t need any password for using this wallet, and you will be able to remove credentials from your scripts.

Test environment

My client has an alias DBITST for my test database on a remote server:

su – oracle
vi $ORACLE_HOME/network/admin/tnsnames.ora

DBITST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.36.0.241)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb1.dbi-lab.ch)
    )
  )

I need a script and this script first authenticates with a login/password:

vi script.sql

conn salty/visiblePWD@DBITST
show user;
select count(*) "Number of objects" from all_objects;
exit;

When I run this script, it returns the number of objects:

sqlplus -s /nolog @script
USER is "SALTY"

Number of objects
-----------------
	    55943

Creating the wallet

I will create a wallet directory on my client, then create the wallet and provide the password for this wallet:

mkdir /home/oracle/wallet

orapki wallet create -wallet /home/oracle/wallet -auto_login_local

Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2021, Oracle and/or its affiliates. All rights reserved.

Enter password: ***************
Enter password again: ***************

I will now add credentials for my database, the alias, the login and the password:

mkstore -wrl /home/oracle/wallet -createCredential DBITST salty visiblePWD

Enter wallet password: ***************

I need to configure the wallet location in the sqlnet.ora file of my client:

vi $ORACLE_HOME/network/admin/sqlnet.ora
WALLET_LOCATION=
  (SOURCE=
    (METHOD=FILE)
    (METHOD_DATA=
       (DIRECTORY=/home/oracle/wallet)
       ))

SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 0

Testing

Now I will create a new version of my script, this time without login/password in it:

cp script.sql nopwd_script.sql
vi nopwd_script.sql

conn /@DBITST
show user;
select count(*) "Number of objects" from all_objects;
exit;

Let’s test if it works:

sqlplus -s /nolog @nopwd_script
USER is "SALTY"

Number of objects
-----------------
	    55943

Fine!

Using different credentials for the same database

If different credentials for the same database are needed, another alias for the same database must be created, as well as another credentials line:

vi $ORACLE_HOME/network/admin/tnsnames.ora
DBITST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.36.0.241)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb1.dbi-lab.ch)
    )
  )

DBITST2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.36.0.241)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb1.dbi-lab.ch)
    )
  )

mkstore -wrl /home/oracle/wallet -createCredential DBITST2 tlas DWPelbisiv

Enter wallet password: **************

cp nopwd_script.sql nopwd_script_2.sql

vi nopwd_script.sql

conn /@DBITST
show user
select count(*) "Number of objects" from all_objects;
exit;

vi nopwd_script_2.sql


conn /@DBITST2
show user
select count(*) "Number of tables" from all_tables;
exit;

sqlplus -s /nolog @nopwd_script


USER is "SALTY"

Number of objects
-----------------
	    55943

sqlplus -s /nolog @nopwd_script_2


USER is "YTLAS"

Number of objects
-----------------
	      117

Managing the wallet

Let’s have a look at what’s inside the wallet:

mkstore -wrl /home/oracle/wallet -listCredential
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2021, Oracle and/or its affiliates. All rights reserved.

Enter wallet password: **************
List credential (index: connect_string username)
2: DBITST2 ytlas
1: DBITST salty

Let’s try to remove the entry for DBITST:

mkstore -wrl /home/oracle/wallet -deleteCredential DBITST

Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2021, Oracle and/or its affiliates. All rights reserved.

Enter wallet password: **************

mkstore -wrl /home/oracle/wallet -listCredential

Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2021, Oracle and/or its affiliates. All rights reserved.

Enter wallet password: **************
List credential (index: connect_string username)
2: DBITST2 tlas

Confirm that it doesn’t work anymore for the first script:

sqlplus -s /nolog @nopwd_script


ERROR:
ORA-01017: invalid username/password; logon denied

USER is ""
SP2-0640: Not connected

sqlplus -s /nolog @nopwd_script_2


USER is "TLAS"

Number of objects
-----------------
	      117

Conclusion

Using wallets is quite easy, the most difficult task being to identify these scripts on the clients.