How to create an Oracle WALLET for Applications to use

This is an easy one: I have been requested to show how Application could make use of Oracle Wallets instead of hard copying user password on the code, so here is it:

1:- Create a user on target DB:

	INST HOST           INST_NAME  STATUS     DB_ROLE      OPEN_MODE      STARTUP_TIME
	---- -------------- ---------- ---------- ------------ -------------- --------------------
	*1   persephone     orcl       OPEN       PRIMARY      READ WRITE     14-AUG-2023 16:22:34
	
	
	SQL> CREATE USER TESTUSER IDENTIFIED BY TESTUSER12345 account unlock;
	User created.
	SQL> GRANT CREATE SESSION,CONNECT  TO TESTUSER;
	Grant succeeded.
	SQL>

2:- Create WALLET on App server, this case we will use directory /u01/admin/orcl/wallet:

	[oracle@dune wallet]$ pwd
	/u01/admin/orcl/wallet
	[oracle@dune wallet]$
	
	[oracle@dune wallet]$ orapki wallet create -wallet /u01/admin/orcl/wallet -auto_login
	Oracle PKI Tool : Version 12.1.0.2
	Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.
	
	Enter password:
	Enter password again:
	[oracle@dune wallet]$
	
	
	[oracle@dune wallet]$ ls -tlr /u01/admin/orcl/wallet
	total 8
	-rw-------. 1 oracle oinstall   0 Aug 14 16:32 ewallet.p12.lck
	-rw-------. 1 oracle oinstall  75 Aug 14 16:32 ewallet.p12
	-rw------. 1 oracle oinstall   0 Aug 14 16:32 cwallet.sso.lck
	-rw-------. 1 oracle oinstall 120 Aug 14 16:32 cwallet.sso
	[oracle@dune wallet]$

3:- Update tnsnames.ora and sqlnet.ora, in this case we are going to create a directory TNS_ADMIN, but App can use wherever they are already using.

	[oracle@dune wallet]$ mkdir TNS_ADMIN
	[oracle@dune wallet]$ ls -tlr /u01/admin/orcl/wallet
	total 8
	drwxr-xr-x. 2 oracle oinstall   6 Aug 14 16:30 TNS_ADMIN
	-rw-------. 1 oracle oinstall   0 Aug 14 16:32 ewallet.p12.lck
	-rw-------. 1 oracle oinstall  75 Aug 14 16:32 ewallet.p12
	-rw------. 1 oracle oinstall   0 Aug 14 16:32 cwallet.sso.lck
	-rw-------. 1 oracle oinstall 120 Aug 14 16:32 cwallet.sso
	[oracle@dune wallet]$
	
	
	
	Contents of tnsnames.ora (ORCL_WALLET can be anything you want)
	
	[oracle@dune wallet]$ cat TNS_ADMIN/tnsnames.ora
	# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_home/network/admin/tnsnames.ora
	# Generated by Oracle configuration tools.
	
	ORCL_WALLET =
	(DESCRIPTION =
		(ADDRESS_LIST =
		(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.122)(PORT = 1521))
		)
		(CONNECT_DATA =
		(SERVICE_NAME = orcl)
		)
	)
	[oracle@dune wallet]$
	
	Contents of sqlnet.ora:
	
	[oracle@dune wallet]$ cat TNS_ADMIN/sqlnet.ora
	SQLNET.WALLET_OVERRIDE = TRUE
	WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/admin/orcl/wallet)))
	[oracle@dune wallet]$
	
	
	[oracle@dune wallet]$ export TNS_ADMIN=/u01/admin/orcl/wallet/TNS_ADMIN
	[oracle@dune wallet]$ tnsping ORCL_WALLET
	....
	Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.122)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCL)))
	OK (0 msec)
	[oracle@dune wallet]$

4:- Add DB Login credentials (you can add as many users you want)

	Add DB login credentials:
	
	[oracle@dune wallet]$ mkstore -wrl /u01/admin/orcl/wallet -createCredential ORCL_WALLET TESTUSER
	Oracle Secret Store Tool : Version 12.1.0.2
	Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.
	
	Your secret/Password is missing in the command line
	Enter your secret/Password:
	Re-enter your secret/Password:
	Enter wallet password:
	Create credential oracle.security.client.connect_string1
	[oracle@dune wallet]$
	
	
	List users on the wallet:
	
	[oracle@dune wallet]$ mkstore -wrl /u01/admin/orcl/wallet -listCredential
	Oracle Secret Store Tool : Version 12.1.0.2
	Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.
	
	Enter wallet password:
	List credential (index: connect_string username)
	1: ORCL_WALLET TESTUSER
	[oracle@dune wallet]$

5:- Connect DB using wallet:

	[oracle@dune wallet]$  sqlplus /@ORCL_WALLET
	SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 14 16:53:51 2023
	Copyright (c) 1982, 2014, Oracle.  All rights reserved.
	Last Successful login time: Mon Aug 14 2023 16:52:27 +01:00
	Connected to:
	Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
	With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
	and Real Application Testing options
	
	SQL> show user
	USER is "TESTUSER"
	SQL>

Now Application just need to refer to the tns alias to connect to DB.

Please remember to update credentials on WALLET if password changes on DB with:

mkstore -wrl /u01/admin/orcl/wallet -modifyCredential ORCL_WALLET TESTUSER NEWPASSWORD

That’s all Folks!