Using the Oracle Wallet to store database credentials
Table of Contents
___________________________________________________________________________________________________
Step 0: Overview
Step 1: Create Oracle Wallet Directory
Step 2: Create TNS ADMIN Directory (For OS user raj)
Step 3: Add TNS Entry (For OS user oracle)
Step 4: Export Oracle Variables
Step 5: Create password protected Oracle Wallet in secured location using orapki
Step 6: Add database login credentials to the wallet using mkstore
Step 7: Verify DB connection
Step 8: List Credential stored in a wallet
Step 9: Modify credential stored in wallet
Step 10: To view an Oracle wallet
Step 11: To change an Oracle wallet password
Step 12: Delete Credential stored in wallet
Step 13: Delete Oracle Wallet
___________________________________________________________________________________________________
The Oracle Wallet can be used to store the user's credentials, so instead of exposing passwords in clear text format in a shell script. Multiple credentials for multiple database can be stored in a single wallet file. This procedure stores a database user's credentials inside the Wallet. This features uses the auto login feature, so it is not required to provide the Wallet password to access to the user's credentials, the OS file permissions regulate access to the Wallet. In this demo i have created wallet on same database server, but used different os user "raj" which is NOT oracle binary owner. I have created two separate directories for wallet creation which is owned by os user "raj" 1. Oracle Wallet Directory 2. Oracle TNS ADMIN directory for OS user "raj" The status of the auto-login wallet is shows as OPEN_NO_MASTER_KEY in V$ENCRYPTION_WALLET even after the database is restarted. --Datapump using wallet nohup expdp /@BSA1EP directory=DUMP_DIR dumpfile=scott_%U.dmp logfile=EXP_SCOTT.log schemas=scott parallel=4 & -- JDBC using wallet Connection conn = DriverManager.getConnection ("jdbc:oracle:oci:/@BSA1EP");
Step 1: Create Oracle Wallet Directory
SET LINES 180 COL wrl_type FORMAT a12 COL wrl_parameter FORMAT a35 COL status FORMAT a30 select * from v$encryption_wallet; WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID ------------ ----------------------------------- --------------- -------------------- --------- -------- --------- ---------- FILE /oracle/admin/BSA1EP/wallet NOT_AVAILABLE UNKNOWN SINGLE NONE UNDEFINED 0 SQL> SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 [oracle@dg1 ~]$ cd /oracle/admin/BSA1EP/wallet -bash: cd: /oracle/admin/BSA1EP/wallet: No such file or directory [oracle@dg1 ~]$ [root@dg1 ~]# mkdir -p /u01/admin/BSA1EP/wallet [root@dg1 ~]# chown -R raj:raj /u01/admin/BSA1EP/wallet
Step 2: Create TNS ADMIN Directory (For OS user raj)
[root@dg1 ~]# mkdir -p /u01/admin/BSA1EP/wallet/TNS_ADMIN [root@dg1 ~]# chown -R raj:raj /u01/admin/BSA1EP/wallet/TNS_ADMIN
Step 3: Add TNS Entry (For OS user oracle)
[oracle@dg1 admin]$ cat /oracle/1900/network/admin/sqlnet.ora # sqlnet.ora Network Configuration File: /oracle/1900/network/admin/sqlnet.ora # Generated by Oracle configuration tools. NAMES.DIRECTORY_PATH= (TNSNAMES) SQLNET.WALLET_OVERRIDE = FALSE SSL_CLIENT_AUTHENTICATION = FALSE SSL_VERSION = 0 WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/admin/BSA1EP/wallet))) [oracle@dg1 admin]$ [oracle@dg1 admin]$ cat /oracle/1900/network/admin/tnsnames.ora # tnsnames.ora Network Configuration File: /oracle/1900/network/admin/tnsnames.ora # Generated by Oracle configuration tools. BSA1EP = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dg1.rajasekhar.com)(PORT = 1622)) ) (CONNECT_DATA = (SERVICE_NAME = BSA1EP) ) ) [oracle@dg1 admin]$
Step 4: Export Oracle Variables
[raj@dg1 ~]$ export ORACLE_SID=BSA1EP [raj@dg1 ~]$ export ORACLE_HOME=/oracle/1900 [raj@dg1 ~]$ export PATH=$ORACLE_HOME/bin:$PATH [raj@dg1 ~]$ export TNS_ADMIN=/u01/admin/BSA1EP/wallet/TNS_ADMIN [raj@dg1 ~]$ export DB_LOGIN_ID=test [OR] [raj@dg1 ~]$ . oraenv ORACLE_SID = [BSA1EP] ? ORACLE_BASE environment variable is not being set since this information is not available for the current user ID raj. You can set ORACLE_BASE manually if it is required. Resetting ORACLE_BASE to its previous value or ORACLE_HOME The Oracle base remains unchanged with value /oracle/1900 [raj@dg1 ~]$ [raj@dg1 ~]$ export TNS_ADMIN=/u01/admin/BSA1EP/wallet/TNS_ADMIN [raj@dg1 ~]$
Step 5: Create password protected Oracle Wallet in secured location using orapki
[raj@dg1 wallet]$ pwd /u01/admin/BSA1EP/wallet [raj@dg1 wallet]$ ls -ltr total 0 drwxr-xr-x 2 raj raj 44 Feb 5 23:15 TNS_ADMIN [raj@dg1 wallet]$ [raj@dg1 ~]$ id uid=1000(raj) gid=1000(raj) groups=1000(raj),10(wheel) [raj@dg1 ~]$ [raj@dg1 wallet]$ orapki wallet create -wallet /u01/admin/BSA1EP/wallet -auto_login Oracle PKI Tool Release 19.0.0.0.0 - Production Version 19.3.0.0.0 Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved. Enter password: Enter password again: Operation is successfully completed. [raj@dg1 wallet]$ [raj@dg1 wallet]$ ls -ltr /u01/admin/BSA1EP/wallet total 8 drwxr-xr-x 2 raj raj 44 Feb 5 23:15 TNS_ADMIN -rw------- 1 raj raj 0 Feb 5 23:57 ewallet.p12.lck -rw------- 1 raj raj 149 Feb 5 23:57 ewallet.p12 -rw------- 1 raj raj 0 Feb 5 23:57 cwallet.sso.lck -rw------- 1 raj raj 194 Feb 5 23:57 cwallet.sso [raj@dg1 wallet]$ [raj@dg1 wallet]$ chmod 644 *wallet* [raj@dg1 wallet]$ ll total 8 -rw-r--r-- 1 raj raj 581 Feb 6 02:14 cwallet.sso -rw-r--r-- 1 raj raj 0 Feb 5 23:57 cwallet.sso.lck -rw-r--r-- 1 raj raj 536 Feb 6 02:11 ewallet.p12 -rw-r--r-- 1 raj raj 0 Feb 5 23:57 ewallet.p12.lck drwxr----- 2 raj raj 44 Feb 5 23:15 TNS_ADMIN [raj@dg1 wallet]$ [oracle@dg1 admin]$ id oracle uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54331(asmadmin),54332(asmdba) [oracle@dg1 admin]$ [oracle@dg1 admin]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sun Feb 6 00:19:59 2022 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> set lines 190 SQL> COL wrl_type FORMAT a12 SQL> COL wrl_parameter FORMAT a35 SQL> COL status FORMAT a30 SQL> select * from v$encryption_wallet; WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID ------------ ----------------------------------- ------------------------------ -------------------- --------- -------- --------- ---------- FILE /u01/admin/BSA1EP/wallet/ OPEN_NO_MASTER_KEY UNKNOWN SINGLE NONE UNDEFINED 0 SQL>
Step 6: Add database login credentials to the wallet using mkstore
Enter user-name: / as sysdba Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> alter user test identified by test123; User altered. SQL> grant connect to test; Grant succeeded. SQL> [raj@dg1 ~]$ id uid=1000(raj) gid=1000(raj) groups=1000(raj),10(wheel) [raj@dg1 ~]$ [raj@dg1 ~]$ mkstore -wrl /u01/admin/BSA1EP/wallet -createCredential BSA1EP test Oracle Secret Store Tool Release 19.0.0.0.0 - Production Version 19.3.0.0.0 Copyright (c) 2004, 2019, 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: [raj@dg1 ~]$ [raj@dg1 ~]$
[raj@dg1 ~]$ id uid=1000(raj) gid=1000(raj) groups=1000(raj),10(wheel) [raj@dg1 ~]$ [raj@dg1 ~]$ export TNS_ADMIN=/u01/admin/BSA1EP/wallet/TNS_ADMIN [raj@dg1 ~]$ [raj@dg1 ~]$ sqlplus /@BSA1EP SQL*Plus: Release 19.0.0.0.0 - Production on Sun Feb 6 00:40:23 2022 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Last Successful login time: Sat Feb 05 2022 20:15:40 +08:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> show user USER is "TEST" SQL>
Step 8: List Credential stored in a wallet
[raj@dg1 ~]$ id uid=1000(raj) gid=1000(raj) groups=1000(raj),10(wheel) [raj@dg1 ~]$ [raj@dg1 ~]$ mkstore -wrl /u01/admin/BSA1EP/wallet -listCredential Oracle Secret Store Tool Release 19.0.0.0.0 - Production Version 19.3.0.0.0 Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved. Enter wallet password: List credential (index: connect_string username) 1: BSA1EP test [raj@dg1 ~]$
Step 9: Modify credential stored in wallet (Update DB user password)
[oracle@dg1 ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sun Feb 6 00:45:36 2022 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> alter user test identified by test999; User altered. SQL> [raj@dg1 ~]$ id uid=1000(raj) gid=1000(raj) groups=1000(raj),10(wheel) [raj@dg1 ~]$ [raj@dg1 ~]$ mkstore -wrl /u01/admin/BSA1EP/wallet -modifyCredential BSA1EP test test999 Oracle Secret Store Tool Release 19.0.0.0.0 - Production Version 19.3.0.0.0 Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved. Enter wallet password: [raj@dg1 ~]$ [raj@dg1 ~]$ [raj@dg1 ~]$ export TNS_ADMIN=/u01/admin/BSA1EP/wallet/TNS_ADMIN [raj@dg1 ~]$ [raj@dg1 ~]$ sqlplus /@BSA1EP SQL*Plus: Release 19.0.0.0.0 - Production on Sun Feb 6 00:48:03 2022 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Last Successful login time: Sun Feb 06 2022 00:40:24 +08:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> show user USER is "TEST" SQL>
Step 10: To view an Oracle wallet
[raj@dg1 ~]$ orapki wallet display -wallet /u01/admin/BSA1EP/wallet Oracle PKI Tool Release 19.0.0.0.0 - Production Version 19.3.0.0.0 Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved. Requested Certificates: User Certificates: Oracle Secret Store entries: oracle.security.client.connect_string1 oracle.security.client.password1 oracle.security.client.username1 Trusted Certificates: [raj@dg1 ~]$
Step 11: To change an Oracle wallet password
orapki wallet change_pwd -wallet /u01/admin/BSA1EP/wallet [-oldpwd password ] [-newpwd password] orapki wallet change_pwd -wallet /u01/admin/BSA1EP/wallet -oldpwd "Oracle@123" -newpwd "oracle123" [raj@dg1 ~]$ orapki wallet change_pwd -wallet /u01/admin/BSA1EP/wallet Oracle PKI Tool Release 19.0.0.0.0 - Production Version 19.3.0.0.0 Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved. Enter wallet password: Enter New Wallet Password: Confirm Wallet Password: Wallet password has been changed. [raj@dg1 ~]$
Step 12: Delete Credential stored in wallet
[raj@dg1 ~]$ mkstore -wrl /u01/admin/BSA1EP/wallet -deleteCredential BSA1EP Oracle Secret Store Tool Release 19.0.0.0.0 - Production Version 19.3.0.0.0 Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved. Enter wallet password: [raj@dg1 ~]$ [raj@dg1 ~]$ [raj@dg1 ~]$ mkstore -wrl /u01/admin/BSA1EP/wallet -listCredential Oracle Secret Store Tool Release 19.0.0.0.0 - Production Version 19.3.0.0.0 Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved. Enter wallet password: List credential (index: connect_string username) <--- no output [raj@dg1 ~]$
To delete an Oracle wallet, we can directly delete the wallet direectory and files [raj@dg1 ~]$ ls -ltr /u01/admin/BSA1EP/wallet total 8 drwxr-xr-x 2 raj raj 44 Feb 5 23:15 TNS_ADMIN -rw------- 1 raj raj 0 Feb 5 23:57 ewallet.p12.lck -rw------- 1 raj raj 0 Feb 5 23:57 cwallet.sso.lck -rw------- 1 raj raj 149 Feb 6 01:06 ewallet.p12 -rw------- 1 raj raj 194 Feb 6 01:06 cwallet.sso [raj@dg1 ~]$ [raj@dg1 ~]$ rm -f /u01/admin/BSA1EP/wallet/*wallet*
Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.
Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp : +65-94693551
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/
Reference: