Oracle Wallet Usage – External Passwords Store

December 17, 2021
()

Oracle Wallet Usage – External Passwords Store

In the previous blog post, we discussed Introduction to Oracle Wallet Click here to read more. In this blog post, we will see How to configure Oracle Wallet to store User passwords.

Oracle Wallet provides a simple and easy method to manage database credentials across multiple domains. It allows you to update database credentials by updating the Wallet instead of having to change individual datasource definitions. This is accomplished by using a database connection string in the datasource definition that is resolved by an entry in the wallet.

This feature can be taken a step further by also using the Oracle TNS (Transparent Network Substrate) administrative file to hide the details of the database connection string (host name, port number, and service name) from the datasource definition and instead use an alias. If the connection information changes, it is simply a matter of changing the tnsnames.ora file instead of potentially many datasource definitions.

Follow the below steps to configure External Password Store:
1. Demo Setup
2. Create a wallet
3. Setup sqlnet.ora to use wallet file
4. Add user to wallet and verify
5. Modify wallet user password
6. Delete  wallet  user


Step 1. Demo Setup: For the purpose of the demo, We have created user app_user with password test123 and also added tnsentry with alias app_user. Basically, Wallet db_connection_string and tnsentry alias should have the same name if you are doing connection using tnsnames.ora.

[oracle@test-machine01 wallet_1]$
[oracle@test-machine01 wallet_1]$
[oracle@test-machine01 wallet_1]$ tnsping app_user

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 14-DEC-2021 15:54:22

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/11.2.0.4/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = test-machine01)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ucastest)))
OK (0 msec)
[oracle@test-machine01 wallet_1]$
[oracle@test-machine01 wallet_1]$

[oracle@test-machine01 wallet_1]$
[oracle@test-machine01 wallet_1]$ sqlplus app_user/test123@app_user

SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 14 15:57:03 2021

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show user
USER is "APP_USER"


Step 2. Create a wallet: Create a wallet on the client by using the following syntax at the command line. If you have already an existing wallet you can skip Step 2 and continue with Step 3. Use option -create and -listCredential with mksore command to create and to list credential in the wallet.

[oracle@test-machine01 wallet_1]$
[oracle@test-machine01 wallet_1]$ mkstore -wrl /u01/app/oracle/admin/wallet_1 -create
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Enter password:   PassworD_#1
Enter password again:   PassworD_#1
[oracle@test-machine01 wallet_1]$
[oracle@test-machine01 wallet_1]$ ls -ltr
total 8
-rw-------. 1 oracle oinstall 3512 Dec 14 15:35 ewallet.p12
-rw-------. 1 oracle oinstall 3589 Dec 14 15:35 cwallet.sso
[oracle@test-machine01 wallet_1]$
[oracle@test-machine01 wallet_1]$

[oracle@test-machine01 wallet_1]$
[oracle@test-machine01 wallet_1]$ mkstore -wrl /u01/app/oracle/admin/wallet_1 -listCredential
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:   PassworD_#1
List credential (index: connect_string username)
[oracle@test-machine01 wallet_1]$
[oracle@test-machine01 wallet_1]$


Step 3. Setup sqlnet.ora to use wallet file: Update parameter WALLET_LOCATION in sqlnet.ora file in the client-side to specify wallet location. The WALLET_OVERRIDE in the sqlnet.ora file specifies to override any existing OS authentication password configuration.

[oracle@test-machine01 admin]$
[oracle@test-machine01 admin]$ vi sqlnet.ora
WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = /u01/app/oracle/admin/wallet_1)
     )
   )

SQLNET.WALLET_OVERRIDE = TRUE
:wq!
[oracle@test-machine01 admin]$


Step 4. Add user to wallet and verify: Syntax to add users in wallet, mkstore -wrl <wallet location> -createCredentail <db_connect_string> <user> <password>. In below example we are adding 2 entry in wallet.

1. db_connect_string = APP_USER – This APP_USER connection string should be the same as the tnsentry alias name if you want to use tnsnames.ora file.

2. db_connect_string = long form of the connection URL – With this format you can connect directly to the database without the need of tnsnames.ora file as you are providing full details like Hostname, Port , Service_Name in the connection string.

Once wallet entries are ready you can use tools like sqlplus to connect to the database. Syntax : sqlplus /@< db_connect_string > without mentioning Username and Password.

[oracle@test-machine01 wallet_1]$
[oracle@test-machine01 wallet_1]$ mkstore -wrl /u01/app/oracle/admin/wallet_1 -createCredential APP_USER app_user test123
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:   Pass   D_#1
Create credential oracle.security.client.connect_string1
[oracle@test-machine01 wallet_1]$
[oracle@test-machine01 wallet_1]$


[oracle@test-machine01 wallet_1]$
[oracle@test-machine01 wallet_1]$ mkstore -wrl /u01/app/oracle/admin/wallet_1 -listCredential
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:   PassworD_#1
List credential (index: connect_string username)
1: APP_USER app_user
[oracle@test-machine01 wallet_1]$
[oracle@test-machine01 wallet_1]$

[oracle@test-machine01 db_wallet]$
[oracle@test-machine01 db_wallet]$ mkstore -wrl /u01/app/oracle/admin/wallet_1 -createCredential "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.114.177)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ucastest)))" app_user test123
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:   PassworD_#1
Create credential oracle.security.client.connect_string1
[oracle@test-machine01 db_wallet]$
[oracle@test-machine01 db_wallet]$
[oracle@test-machine01 db_wallet]$ mkstore -wrl /u01/app/oracle/admin/wallet_1 -listCredential
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:   PassworD_#1
List credential (index: connect_string username)
2: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.114.177)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ucastest))) app_user
1: APP_USER app_user
[oracle@test-machine01 db_wallet]$


[oracle@test-machine01 wallet_1]$
[oracle@test-machine01 wallet_1]$ sqlplus /@APP_USER

SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 14 15:53:56 2021

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL> show user
USER is "APP_USER"
SQL>
SQL>

[oracle@test-machine01 admin]$
[oracle@test-machine01 admin]$ sqlplus /@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.114.177)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ucastest)))

SQL*Plus: Release 11.2.0.4.0 Production on Thu Dec 16 10:55:45 2021

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show user
USER is "APP_USER"


Step 5. Modify wallet user password: Here we are modifying the APP_USER password from test123 and app123. As soon as the password is changed. We need to update wallet user password as well otherwise end users will not be able able to connect to the database and will receive the error “ORA-01017: invalid username/password; logon denied“. Use mkstore -modifyCredential to modify wallet entries.

[oracle@test-machine01 wallet_1]$ sqlplus sys as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 14 15:57:58 2021

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter user app_user identified by app123;

User altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@test-machine01 wallet_1]$
[oracle@test-machine01 wallet_1]$
[oracle@test-machine01 wallet_1]$ sqlplus /@APP_USER

SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 14 15:58:18 2021

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name: ^C
[oracle@test-machine01 wallet_1]$

[oracle@test-machine01 wallet_1]$
[oracle@test-machine01 wallet_1]$ mkstore -wrl /u01/app/oracle/admin/wallet_1 -modifyCredential APP_USER app_user app123
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:   PassworD_#1
Modify credential
Modify 1
[oracle@test-machine01 wallet_1]$

[oracle@test-machine01 wallet_1]$
[oracle@test-machine01 wallet_1]$ sqlplus /@APP_USER

SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 14 16:01:38 2021

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show user
USER is "APP_USER"


Step 6. Delete  wallet  user: Use mkstore -deleteCredential to delete wallet entries.

[oracle@test-machine01 wallet_1]$
[oracle@test-machine01 wallet_1]$ mkstore -wrl /u01/app/oracle/admin/wallet_1 -deleteCredential APP_USER
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:   PassworD_#1
Delete credential
Delete 1
[oracle@test-machine01 wallet_1]$
[oracle@test-machine01 wallet_1]$
[oracle@test-machine01 wallet_1]$ mkstore -wrl /u01/app/oracle/admin/wallet_1 -deleteCredential "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.114.177)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ucastest)))"
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:   PassworD_#1
Delete credential
Delete 1
[oracle@test-machine01 wallet_1]$
[oracle@test-machine01 wallet_1]$

[oracle@test-machine01 wallet_1]$
[oracle@test-machine01 wallet_1]$ mkstore -wrl /u01/app/oracle/admin/wallet_1 -listCredential
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:   PassworD_#1
List credential (index: connect_string username)
[oracle@test-machine01 wallet_1]$


Reference: Oracle Doc: https://docs.oracle.com/middleware/1213/wls/JDBCA/oraclewallet.htm#JDBCA596


This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.


Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.


Related Articles

, , ,


Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Jamsher Khan

🌐

Hello and welcome to DBsGuru,I’m Jamsher Khan working as Senior Oracle DBA based in KSA-Jeddah, I have working experience in Oracle DBA, SQL Server, MySql, PostgreSQL, Linux, Golden Gate, ODA.Thanks for the visits!Share Learn Grow!

Leave a Reply

Your email address will not be published. Required fields are marked *