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.
[[email protected] wallet_1]$
[[email protected] wallet_1]$
[[email protected] 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)
[[email protected] wallet_1]$
[[email protected] wallet_1]$
[[email protected] wallet_1]$
[[email protected] wallet_1]$ sqlplus app_user/[email protected]_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.
[[email protected] wallet_1]$
[[email protected] 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
[[email protected] wallet_1]$
[[email protected] 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
[[email protected] wallet_1]$
[[email protected] wallet_1]$
[[email protected] wallet_1]$
[[email protected] 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)
[[email protected] wallet_1]$
[[email protected] 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.
[[email protected] admin]$
[[email protected] admin]$ vi sqlnet.ora
WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = /u01/app/oracle/admin/wallet_1)
)
)
SQLNET.WALLET_OVERRIDE = TRUE
:wq!
[[email protected] 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.
[[email protected] wallet_1]$
[[email protected] 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
[[email protected] wallet_1]$
[[email protected] wallet_1]$
[[email protected] wallet_1]$
[[email protected] 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
[[email protected] wallet_1]$
[[email protected] wallet_1]$
[[email protected] db_wallet]$
[[email protected] 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
[[email protected] db_wallet]$
[[email protected] db_wallet]$
[[email protected] 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
[[email protected] db_wallet]$
[[email protected] wallet_1]$
[[email protected] 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>
[[email protected] admin]$
[[email protected] 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.
[[email protected] 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
[[email protected] wallet_1]$
[[email protected] wallet_1]$
[[email protected] 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
[[email protected] wallet_1]$
[[email protected] wallet_1]$
[[email protected] 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
[[email protected] wallet_1]$
[[email protected] wallet_1]$
[[email protected] 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.
[[email protected] wallet_1]$
[[email protected] 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
[[email protected] wallet_1]$
[[email protected] wallet_1]$
[[email protected] 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
[[email protected] wallet_1]$
[[email protected] wallet_1]$
[[email protected] wallet_1]$
[[email protected] 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)
[[email protected] 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
- Oracle Critical Database Patch ID for January 2023 along with enabled Download Link
- Steps to Apply Combo Patch (Oct 2022) on Clusterware in Two Node RAC in Oracle
- How to Enable PasswordLess SSH login in Linux
- Oracle Critical Database Patch ID for October 2022 along with enabled Download Link
- Solution for ORA-00600: Internal Error Code, Arguments: [4193] [2206] in Oracle
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!
