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.

sqlnet.ora

WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=$ORACLE_HOME/apps/wallet/ssl)))
SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 0

tnsnames.ora

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 19.0.0.0.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.

Cheers,

Balaaji Dhananjayan