-- http://www.br8dba.com/store-db-credentials-in-oracle-wallet/
-- https://franckpachot.medium.com/19c-ezconnect-and-wallet-easy-connect-and-external-password-file-8e326bb8c9f5

# create 2 users in a PDB
alter session set container=NIHILUS;
show con_name
show pdbs

grant create session to WOMBAT identified by "CuteAnimal99#";
grant create session to OTTER identified by "CuteAnimal@88";


# create directory for tnsnames.ora
export TNS_ADMIN=/home/oracle/tmp/tns
mkdir -p ${TNS_ADMIN}

# add TNS alias in $TNS_ADMIN/tnsnames.ora
WOMBAT_NIHILUS=(DESCRIPTION=
				(CONNECT_DATA=
					(SERVICE_NAME=NIHILUS)
				)
				(ADDRESS=
					(PROTOCOL=tcp)
					(HOST=bakura)
					(PORT=1521)
				)
			)

OTTER_NIHILUS=(DESCRIPTION=
				(CONNECT_DATA=
					(SERVICE_NAME=NIHILUS)
				)
				(ADDRESS=
					(PROTOCOL=tcp)
					(HOST=bakura)
					(PORT=1521)
				)
			)


# test connections using TNS alias
sqlplus /nolog
connect WOMBAT/"CuteAnimal99#"@WOMBAT_NIHILUS
connect OTTER/"CuteAnimal@88"@OTTER_NIHILUS



# add following lines in $TNS_ADMIN/sqlnet.ora
WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/home/oracle/tmp/wdir)))
SQLNET.WALLET_OVERRIDE=TRUE


# create wallet
export MY_WALLET_DIR=/home/oracle/tmp/wdir
mkdir -p ${MY_WALLET_DIR}
orapki wallet create -wallet ${MY_WALLET_DIR} -auto_login

# files generated in ${MY_WALLET_DIR} 
# check for 600 permissions on all theses files, otherwise the connection using the wallet will not work
ewallet.p12.lck
ewallet.p12
cwallet.sso.lck
cwallet.sso


# add credentials
mkstore -wrl ${MY_WALLET_DIR} -createCredential WOMBAT_NIHILUS WOMBAT "CuteAnimal99#"
mkstore -wrl ${MY_WALLET_DIR} -createCredential OTTER_NIHILUS OTTER "CuteAnimal@88"

# list wallet credentials
mkstore -wrl ${MY_WALLET_DIR} -listCredential

# update or delete entery
mkstore -wrl ${MY_WALLET_DIR} -modifyCredential WOMBAT_NIHILUS WOMBAT "CuteAnimal99#"
mkstore -wrl ${MY_WALLET_DIR} -deleteCredential OTTER_NIHILUS

# test connection using wallet
sqlplus /@WOMBAT_NIHILUS
show user

sqlplus /@OTTER_NIHILUS
show user

# NOTES
# if we want to store in the same wallet passwords of multiple users, we should use multiple TNS alias because the TNS alias is an unique key in the wallet
# if the wallet has bee created with -auto_login option, the wallet password is required to add/modify/delete/list wallet credentials
# but it is not required to establish connections


# using ezConnect
#################

# basicly, when using ezConnect we have the same "TNS alias", ex: //bakura:1521/NIHILUS
# how to add multiples credentials? using dummy ezConnect parameter introduced in 19c:
# https://franckpachot.medium.com/19c-easy-connect-e0c3b77968d7

# in our case we will add a dummy parameter "ConnectAs" in order to crerate distinct ezStrings depending by username
mkstore -wrl ${MY_WALLET_DIR} -createCredential //bakura:1521/NIHILUS?ConnectAs=WOMBAT WOMBAT "CuteAnimal99#"
mkstore -wrl ${MY_WALLET_DIR} -createCredential //bakura:1521/NIHILUS?ConnectAs=OTTER OTTER "CuteAnimal@88"

sqlplus /@//bakura:1521/NIHILUS?ConnectAs=WOMBAT
show user

sqlplus /@//bakura:1521/NIHILUS?ConnectAs=OTTER
show user

