Secure your Oracle accounts using a wallet.

Do you have Oracle schemas that are frequently shared among multiple users or contain sensitive information in your database, and want to prevent passwords for these schemas from being distributed among your developers and end users?

Do you utilize shell scripts that run via Crontab or Python programs that, connect to Oracle, and store passwords within the scripts?

This is where wallets come in. This article describes how you set up a central wallet store on an Oracle database server and how to use it to protect passwords for your shell scripts, Python programs or sensible schemas in your database.

An Oracle Wallet is a secure software container used to store sensitive information, such as database credentials, SSL certificates, and encryption keys, in Oracle databases. It provides a way to centrally manage and protect these credentials, ensuring they are encrypted and accessible only to authorized users and processes. This enhances security by minimizing the exposure of sensitive data and facilitating secure communication between different components of an Oracle system.

Set up a wallet store.

In this example, we will use the "HR" demo schema as an example.

If you want to try out this yourself you can download the latest version of Oracle database sample schemas at

https://github.com/oracle-samples/db-sample-schemas

As the Oracle software user on your database server, create a wallet in a directory for which Oracle has read and write permissions. In this example, we create a wallet store in the Oracle user home catalog "/home/oracle/wallet".

Example:

mkstore -wrl "/home/oracle/wallet" –create

You must create a password that you cannot forget, so store it in a secure location. If you lose the password, you will lose access to the wallet.

Create a tnsnames.ora entry for the database you intend to use for secure connections.

We need to set up a unique TNS entry in the "tnsnames.ora" file, which the wallet will utilize for connecting without requiring a password. In this example, as we aim to secure the "HR" schema, we name our TNS entry "HRWALLET".

In our example, we connect to the "HR" schema on the server called "bilbo" using the standard port 1521 for a database with the service name "FREEPDB2".

HRWALLET = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = bilbo)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = FREEPDB2) ) )

Edit your sqlnet.ora file to create a reference to your wallet file.

You need to add the following to the sqlnet.ora file on your Oracle database server:

SQLNET.WALLET_OVERRIDE = TRUE

WALLET_LOCATION = (SOURCE= (METHOD= FILE) (METHOD_DATA = (DIRECTORY=/home/oracle/wallet)))

Secure a database username and password in your wallet.

The syntax is

mkstore -wrl "" -createCredential <dbuser> <dbpwd>

For our example, we input the following to create a new entry in our wallet.

This is where you link the TNS entry "HRWALLET" to the wallet store.

mkstore -wrl /home/oracle/wallet -createCredential hrwallet hr <my_top_secret_password!>

List stored credentials.

The following command allows you to list all stored credentials you have created in the wallet store.

mkstore –wrl /home/oracle/wallet -listCredential

Test and verify your connection.

Now, it's time to test your connection directly on your database server.

No password is needed, as we utilize the TNS entry, which in turn accesses the wallet store to provide Oracle with the correct password.

In the next step, I will demonstrate how to distribute the wallet to your end users and developers.

SQL> conn /@HRWALLET

Connected.

SQL>

Using a wallet with an Oracle client.

If you are using Oracle Instant Client, you can copy the wallet setup from your database server.

Ask your DBA to send you a zipped version of the wallet or to place it on central storage, allowing multiple users to download the wallet store.

cd /home/oracle

zip -r wallet.zip ./wallet

Download the zip file to your local client where the Instant Client is installed. Unzip the file, and then modify your sqlnet.ora to include the following:

Note: I have unzipped the wallet.zip file in my home directory "/home/uhellstr/opt" and changed the permissions for the wallet directory to my local user. Afterward, I simply updated my Instant Client's sqlnet.ora with the following changes.

SQLNET.WALLET_OVERRIDE = TRUE

WALLET_LOCATION = (SOURCE= (METHOD= FILE) (METHOD_DATA = (DIRECTORY=/home/uhellstr/opt/wallet)))

I also need to add a TNS entry in my tnsnames.ora

HRWALLET = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = bilbo)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = FREEPDB2) ) )

Now, I can connect to the HR account on the remote database using SQLcl, SQL*Plus, or SQL*Developer.

SQL> conn /@HRWALLET

Connected.

SQL>

I hope this helps you get started with using a wallet to secure connections, ensuring the security of both client connections and shell scripts running through Crontab.

Over & out.