In many cases, it is necessary or advisable to connect to the database quickly and, above all, securely, without the database connection details being visible. One example, as indicated on the Oracle website, is when we connect to the database from Shell scripts contained in the file system. If these scripts contain connection details, they can pose a significant security problem. For this example and others like it, we can use the operating system's own authentication, but starting with Oracle 10g Release 2, we have the option of using a secure external password store, saving the Oracle login credentials in a wallet stored on the client side. To understand this, what is created is a file with the credentials to connect to the database stored on the client side, which in the connection string will indicate where this "file" is located in order to connect. This will allow the user to connect using the following syntax "/@alias", where alias is the name chosen for the connection to the database.

Configure Secure External Password Store

First, we must choose where we want to store the Oracle wallet, i.e., the location where the wallet file with the connection data that Oracle will read to connect to the database will be stored, using the following syntax: "/@alias."

Note: The location can be a shared directory where several users have access to connect to the database.

In our case, since this is a test, we will leave the Oracle Wallet in the Oracle home directory on our client machine.

mkdir -p /home/oracle/wallet

We load the TNS_ADMIN environment variables, if you have not done it before, pointing to the file tnsnames.ora where we will specify the connection string to the database of our client machine.

export TNS_ADMIN=/u01/app/oracle/21.0.0/oracle/network/admin

We add the post to the sqlnet.ora file, located within the TNS_ADMIN path, in our case "/u01/app/oracle/21.0.0/oracle/network/admin/sqlnet.ora."

In this file we must specify the directory where the Wallet file we created earlier is located.

It is important to add the post .WALLET_OVERRIDE post , which allows you to override any existing operating system authentication settings.

WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = /home/oracle/wallet)
)
)
SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 0

At this point, we can create the wallet with the oracle mkstore tool using the -create option. The wallet is password protected, but it is defined with the "Automatic login" or "autologin" property enabled. This way, users who connect to the database will do so without requiring a password, which is, in principle, the goal we seek with the creation of a "secure external password."

[oracle@localhost bin]$ mkstore -wrl «/home/oracle/wallet» -create
Oracle Secret Store Tool Release 21.0.0.0.0 – Production
21.3.0.0.0: Versión {1}
Copyright (c) 2004, 2021, Oracle y/o sus subsidiarias. Todos los Derechos Reservados.
Introducir Contraseña:  
Volver a Introducir Contraseña:  
[oracle@localhost bin]$

Once the wallet is created, it can be modified using the "mkstore" command described below.

Add the user/password credentials to the wallet recently using the -createCredential option. To put it simply, at this point it is a matter of indicating the credentials we need to connect to the database that will be stored in the password file.

In our case, we are going to establish a connection, following this post tnsnames.ora with the user test.

TEST_EXTUSER =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = localhost)
(PORT = 1521)
)
(CONNECT_DATA = (server=dedicated) (service_name = srv_testing))
)

The ruling is as follows. Location of the wallet, post the connection to the database, username, and password of the user with whom you want to connect.

mkstore -wrl "/home/oracle/wallet" -createCredential TEST_EXTUSER test testpassword

The output of the command is as follows, it will ask you for the wallet password to create the credentials:

[oracle@localhost bin]$ mkstore -wrl «/home/oracle/wallet» -createCredential TEST_EXTUSER test testpassword
Oracle Secret Store Tool Release 21.0.0.0.0 – Production
21.3.0.0.0: Versión {1}
Copyright (c) 2004, 2021, Oracle y/o sus subsidiarias. Todos los Derechos Reservados.
Introducir Contraseña de Cartera:  

Once the wallet has been generated, we can connect to the database with the previously generated wallet. To connect, we will use the following syntax "/@db_alias," where db_alias is the reference to the post that we have added and configured in our tnsnames file, in our case: /@TEST_EXTUSER.

[oracle@localhost admin]$ sqlplus /@TEST_EXTUSER
SQL*Plus: Release 21.0.0.0.0 – Production on Sun Oct 2 17:48:52 2022
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Last Successful login time: Sun Oct 02 2022 17:47:12 -04:00
Connected to:
Oracle Database 21c Standard Edition 2 Release 21.0.0.0.0 – Production
Version 21.3.0.0.0
SQL>

Once the Wallet is generated, we can perform several actions on the generated wallet:

List current entries in our Wallet

List the entries we have created in the wallet. To do this, we will add the syntax – listCredential pointing to the path of the wallet on which we want to see the credentials. Oracle will ask us for the wallet password.

oracle@localhost admin]$  mkstore -wrl «/home/oracle/wallet» -listCredential
Oracle Secret Store Tool Release 21.0.0.0.0 – Production
21.3.0.0.0: Versión {1}
Copyright (c) 2004, 2021, Oracle y/o sus subsidiarias. Todos los Derechos Reservados.
Introducir Contraseña de Cartera:  
List credential (index: connect_string username)
1: TEST_EXTUSER test
[oracle@localhost admin]$

Adding a new user connection to a previously created Wallet

If you want to add a new connection to your Wallet to connect to the database externally using an "external password," simply add the new user or a new connection string that matches a new post your tnsnames.ora. This will be the one you use to make the connection.

[oracle@localhost admin]$ mkstore -wrl «/home/oracle/wallet» -createCredential RAUL_EXTUSER raul testpassword
Oracle Secret Store Tool Release 21.0.0.0.0 – Production
21.3.0.0.0: Versión {1}
Copyright (c) 2004, 2021, Oracle y/o sus subsidiarias. Todos los Derechos Reservados.
Introducir Contraseña de Cartera:  

If we now list the wallet entries, we can see the new post have added, with RAUL_EXTUSER being our post .ora post

[oracle@localhost admin]$  mkstore -wrl «/home/oracle/wallet» -listCredential
Oracle Secret Store Tool Release 21.0.0.0.0 – Production
21.3.0.0.0: Versión {1}
Copyright (c) 2004, 2021, Oracle y/o sus subsidiarias. Todos los Derechos Reservados.
Introducir Contraseña de Cartera:  
List credential (index: connect_string username)
2: RAUL_EXTUSER raul
1: TEST_EXTUSER test

We can test the connection.

[oracle@localhost admin]$ sqlplus /@RAUL_EXTUSER
SQL*Plus: Release 21.0.0.0.0 – Production on Sun Oct 2 18:34:07 2022
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Last Successful login time: Sun Oct 02 2022 18:26:16 -04:00
Connected to:
Oracle Database 21c Standard Edition 2 Release 21.0.0.0.0 – Production
Version 21.3.0.0.0

Modifying the credentials of a Wallet

The credentials of the wallet can be modified, we can change the password of the current wallet simply with the sitaxis "modifyCredential".

[oracle@localhost admin]$  mkstore -wrl "/home/oracle/wallet" -modifyCredential RAUL_EXTUSER raul raulnewpassword
Oracle Secret Store Tool Release 21.0.0.0.0 - Production
21.3.0.0.0: Versión {1}
Copyright (c) 2004, 2021, Oracle y/o sus subsidiarias. Todos los Derechos Reservados.
Introducir Contraseña de Cartera:   

Remove credentials from a Wallet

The credentials of a wallet can be deleted with the following command.

mkstore -wrl <wallet_location> -deleteCredential <db_alias>
[oracle@localhost admin]$ mkstore -wrl "/home/oracle/wallet" -deleteCredential RAUL_EXTUSER
Oracle Secret Store Tool Release 21.0.0.0.0 - Production
21.3.0.0.0: Versión {1}
Copyright (c) 2004, 2021, Oracle y/o sus subsidiarias. Todos los Derechos Reservados.
Introducir Contraseña de Cartera:  
[oracle@localhost admin]$