Setting up Password-less authentication to Oracle Database
As the world moves into end-to-end automation, it is also important for us to secure the passwords into a vault or wallet so they are encrypted and also helps the application or automation script to authenticate to the database without calling a password as variable from a file or other means.
Refer here to see how to setup SSL/TLS certs on your Oracle database to secure your connection to the database.
Before we initialise, it is understood that that wallet is created and the SSL/TLS certs are imported for the use of client.
If not, let's do that first.
orapki wallet create -wallet $ORACLE_HOME/apps/wallet/ssl -auto_login
Below steps are to import the Root and CA certs into the wallet and these two cert file should generally by generated by the DBA team.
Refer: Securing Oracle Database connection with SSL/TLS
orapki wallet add -wallet $ORACLE_HOME/apps/wallet/ssl -trusted_cert -cert DBGENRE_CA_Root.cert -pwd <myWalletPassword> orapki wallet add -wallet $ORACLE_HOME/apps/wallet/ssl -trusted_cert -cert DBGENRE_CA.cert -pwd <myWalletPassword>
As we move ahead with this article, let's see how to store the application schema password into the wallet.
Note: It is important for us to protect the wallet only to be accessed by the application unix user. Hence, change the wallet file permission with 700 or 770
mkstore -wrl $ORACLE_HOME/apps/wallet/ssl -listCredential mkstore -wrl $ORACLE_HOME/apps/wallet/ssl -createCredential ORARAC01_SSL schemaUser <schemaPassword>
ORARAC01_SSL is the tns entry name in tnsnames.ora, and this will be maintained in application batch job side to connect the DB
schemaUser => application user
schemaPasswd => Application user password
It is important that you keep in mind about the sqlnet.ora file and point the wallet location for the client to use the wallet for certs and credentials.
WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=$ORACLE_HOME/apps/wallet/ssl))) SQLNET.WALLET_OVERRIDE = TRUE SSL_CLIENT_AUTHENTICATION = FALSE SSL_VERSION = 0
ORARAC01= (DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST= racdb.apps.dbgenre) (PORT=4422)) (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORARAC01) ) ) ORARAC01_SSL= (DESCRIPTION= (ADDRESS=(PROTOCOL=TCPS)(HOST= racdb.apps.dbgenre)(PORT=2484)) (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORARAC01) ) (SECURITY=(SSL_SERVER_CERT_DN="CN=CN=racdb-scan.dbgenre.com,OU=Technology,O=DBGENRE,L=Chennai,ST=Tamilnadu,C=IN") ) )
After you complete above steps, just follow below steps to verify it’s working :
tnsping ORARAC01_SSL TNS Ping Utility for Solaris: Version 18.104.22.168.0 - Production on 19-DEC-2021 16:22:38 Copyright (c) 1997, 2020, Oracle. All rights reserved. Used parameter files: /app/oragrid/product/19c/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCPS)(HOST = racdb.apps.dbgenre)(PORT = 2484)) (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = ORARAC01)) (SECURITY = (SSL_SERVER_CERT_DN ="CN=CN=racdb-scan.dbgenre.com,OU=Technology,O=DBGENRE,L=Chennai,ST=Tamilnadu,C=IN"))) OK (100 msec)
sqlplus /nolog > conn / @ ORARAC01_SSL > show user USER is "schema_user" > select * from global_name ; GLOBAL_NAME ------------- ORARAC01
Hope this article was helpful to you.