📋Steps in this guide1/11

Using the Oracle Wallet to store database credentials

oracle clusteringintermediate

by OracleDba

25 views

Overview

Step 0: Overview --Datapump using wallet -- JDBC using wallet Step 1: Create Oracle Wallet Directory

Code/Command (click line numbers to comment):

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
");

Section 2

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)

Code/Command (click line numbers to comment):

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

[root@dg1 ~]#
mkdir -p /u01/admin/BSA1EP/wallet/TNS_ADMIN
[root@dg1 ~]#
chown -R raj:raj /u01/admin/BSA1EP/wallet/TNS_ADMIN

Section 3

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

Code/Command (click line numbers to comment):

[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]$

[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 ~]$

Section 4

Step 5: Create password protected Oracle Wallet in secured location using orapki orapki wallet create -wallet /u01/admin/BSA1EP/wallet -auto_login Enter password: Enter password again: Step 6: Add database login credentials to the wallet using mkstore

Code/Command (click line numbers to comment):

[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>

Section 5

alter user test identified by test123; grant connect to test; mkstore -wrl /u01/admin/BSA1EP/wallet -createCredential BSA1EP test Enter your secret/Password: Re-enter your secret/Password: Enter wallet password: Step 7: Verify DB connection

Code/Command (click line numbers to comment):

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 ~]$

Section 6

Step 7: Verify DB connection sqlplus /@BSA1EP Step 8: List Credential stored in a wallet

Code/Command (click line numbers to comment):

[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>

[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 ~]$

Section 7

mkstore -wrl /u01/admin/BSA1EP/wallet -listCredential Step 9: Modify credential stored in wallet (Update DB user password) alter user test identified by test999 ; mkstore -wrl /u01/admin/BSA1EP/wallet - modifyCredential BSA1EP test test999

Code/Command (click line numbers to comment):

[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>

Section 8

sqlplus /@BSA1EP Step 10: To view an Oracle wallet orapki wallet display -wallet /u01/admin/BSA1EP/wallet Step 11: To change an Oracle wallet password

Code/Command (click line numbers to comment):

[
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 ~]$

Section 9

Step 11: To change an Oracle wallet password orapki wallet change_pwd -wallet /u01/admin/BSA1EP/wallet -oldpwd "Oracle@123" -newpwd "oracle123" Step 12: Delete Credential stored in wallet

Code/Command (click line numbers to comment):

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 ~]$

[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 ~]$

Section 10

mkstore -wrl /u01/admin/BSA1EP/wallet -deleteCredential BSA1EP mkstore -wrl /u01/admin/BSA1EP/wallet -listCredential Step 13: Delete Oracle Wallet rm -f /u01/admin/BSA1EP/wallet/*wallet*

Code/Command (click line numbers to comment):

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*

Section 11

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. 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. Reference:

Comments (0)

Please to add comments

No comments yet. Be the first to comment!