Configure autosys to use oracle SSO wallets with certificates for database connection

Products

CA Workload Automation AE - Scheduler (AutoSys)

Issue/Introduction

During the autosys fresh installation, the installer prompts for the database access mode to be user/password or SSL certificate. The database access mode can be switched to use oracle wallets if the application is originally installed with the password as the database access mode. This document has all the necessary procedures explained with an example scenario using self-signed certificates.

Environment

Release: 11.3.6 SP4 and higher

Database: Oracle 12c and higher

Resolution

Take a backup of the product install directory containing autosys and autouser directories (/opt/CA/WorkloadAutomationAE) and follow the below procedure.

1) Configure the oracle SSO wallets with certificates for 3 users

1) SSLSYS (A user who is equivalent to the system or DBA permissions)

2) AEDBADMIN (Autosys database schema owner)

3) AUTOSYS (Autosys application user)

Procedure -

a. Log in to the Oracle database server. And create the directory to store the wallets

$ mkdir -p /u01/app/oracle/admin/wallet

b. Create an Oracle wallet with a Trusted Certificate - (This certificate is on the server-side)

$ cd /u01/app/oracle/admin/wallet

$ orapki wallet create -wallet ./root -pwd [email protected]

Oracle PKI Tool : Version 12.2.0.1.0

Operation is successfully completed.

$ orapki wallet remove -wallet ./root -trusted_cert_all -pwd [email protected]

Oracle PKI Tool : Version 12.2.0.1.0

$ orapki wallet add -wallet ./root -dn "CN=CA TrustedCertificate,OU=CA ITC,O=CA Technologies,C=IN" -keysize 2048 -self_signed -validity 9999 -pwd [email protected] -sign_alg sha512

Oracle PKI Tool : Version 12.2.0.1.0

Operation is successfully completed.

$ orapki wallet display -wallet ./root -pwd [email protected]

Oracle PKI Tool : Version 12.2.0.1.0

Requested Certificates:

User Certificates:

Subject: CN=CA TrustedCertificate,OU=CA ITC,O=CA Technologies,C=IN

Trusted Certificates:

Subject: CN=CA TrustedCertificate,OU=CA ITC,O=CA Technologies,C=IN

$ orapki wallet export -wallet ./root -dn "CN=CA TrustedCertificate,OU=CA ITC,O=CA Technologies,C=IN" -cert ./root/b64certificate.txt -pwd [email protected]

Oracle PKI Tool : Version 12.2.0.1.0

Operation is successfully completed.

c. Create an Oracle wallet with a user certificate for SSLSYS

$ orapki wallet create -wallet ./sslsys -auto_login -pwd [email protected]

Oracle PKI Tool : Version 12.2.0.1.0

Operation is successfully completed.

$ orapki wallet remove -wallet ./sslsys -trusted_cert_all -pwd [email protected]

Oracle PKI Tool : Version 12.2.0.1.0

$ orapki wallet add -wallet ./sslsys -dn "CN=SYS,OU=ITC,O=CA Technologies,C=IN" -keysize 2048 -pwd [email protected] -sign_alg sha512

Oracle PKI Tool : Version 12.2.0.1.0

Operation is successfully completed.

$ orapki wallet export -wallet ./sslsys -dn "CN=SYS,OU=ITC,O=CA Technologies,C=IN" -request ./sslsys/creq.txt

Oracle PKI Tool : Version 12.2.0.1.0

Operation is successfully completed.

$ orapki cert create -wallet ./root -request ./sslsys/creq.txt -cert ./sslsys/cert.txt -validity 9999 -pwd [email protected]

Oracle PKI Tool : Version 12.2.0.1.0

$ orapki cert display -cert ./sslsys/cert.txt -complete

Oracle PKI Tool : Version 12.2.0.1.0

{ fingerprint = 8991ab034ba87fea29001c122c6a44fb, notBefore = Thu May 05 03:27:55 EDT 2022, notAfter = Sun Sep 19 03:27:55 EDT 2049, holder = CN=SYS,OU=ITC,O=CA Technologies,C=IN, issuer = CN=CA TrustedCertificate,OU=CA ITC,O=CA Technologies,C=IN, serialNo = 0, sigAlgOID = 1.2.840.113549.1.1.11, key = { modulus = 22239832317342563408511559227109486768735975312794297355062718518113714740605165032644945944227876464611642790049426339940365842419930336271752411240210986900958747256103816337154067184394717431545594629080816196954251891466041579122831498125851490747980975557156639550237326680478389270888776863211545861501662321149364294888156012582088635711590499073031533803594194746901932224640837449128816763449627748017357276976999071074137749986096465156920719366963711127973204192372507143100488631256866187557893406107616315602081150906333773119910002838627409444954120215478401605772214635314023218595374531463460238466189, exponent = 65537 } }

$ orapki wallet add -wallet ./sslsys -trusted_cert -cert ./root/b64certificate.txt -pwd [email protected] -sign_alg sha512

Oracle PKI Tool : Version 12.2.0.1.0

Operation is successfully completed.

$ orapki wallet add -wallet ./sslsys -user_cert -cert ./sslsys/cert.txt -pwd [email protected] -sign_alg sha512

Oracle PKI Tool : Version 12.2.0.1.0

Operation is successfully completed.

$

$ orapki wallet display -wallet ./sslsys -pwd [email protected]

Oracle PKI Tool : Version 12.2.0.1.0

Requested Certificates:

User Certificates:

Subject: CN=SYS,OU=ITC,O=CA Technologies,C=IN

Trusted Certificates:

Subject: CN=CA TrustedCertificate,OU=CA ITC,O=CA Technologies,C=IN

d. Create an Oracle wallet with a user certificate for AEDBADMIN

$ orapki wallet create -wallet ./aedbadmin -auto_login -pwd [email protected]

Oracle PKI Tool : Version 12.2.0.1.0

Operation is successfully completed.

$ orapki wallet remove -wallet ./aedbadmin -trusted_cert_all -pwd [email protected]

Oracle PKI Tool : Version 12.2.0.1.0

$ orapki wallet add -wallet ./aedbadmin -dn "CN=AEDBADMIN,OU=ITC,O=CA Technologies,C=IN" -keysize 2048 -pwd [email protected] -sign_alg sha512

Oracle PKI Tool : Version 12.2.0.1.0

Operation is successfully completed.

$ orapki wallet export -wallet ./aedbadmin -dn "CN=AEDBADMIN,OU=ITC,O=CA Technologies,C=IN" -request ./aedbadmin/creq.txt

Oracle PKI Tool : Version 12.2.0.1.0

Operation is successfully completed.

$ orapki cert create -wallet ./root -request ./aedbadmin/creq.txt -cert ./aedbadmin/cert.txt -validity 9999 -pwd [email protected]

Oracle PKI Tool : Version 12.2.0.1.0

$ orapki cert display -cert ./aedbadmin/cert.txt -complete

Oracle PKI Tool : Version 12.2.0.1.0

{ fingerprint = 914b0f0d2e74f66de1806f8637a918d5, notBefore = Thu May 05 03:30:08 EDT 2022, notAfter = Sun Sep 19 03:30:08 EDT 2049, holder = CN=AEDBADMIN,OU=ITC,O=CA Technologies,C=IN, issuer = CN=CA TrustedCertificate,OU=CA ITC,O=CA Technologies,C=IN, serialNo = 0, sigAlgOID = 1.2.840.113549.1.1.11, key = { modulus = 20178637887998195043655638569463997829945595500095812193164242866540085314671454398333772351653050606293930997178246308187627439486836237172138210640021328841930413480651466660558623949846625570303777718883237536603583635883112393588834923275966165498443051831867603507544450068969122740678396082801551923732977209413200898719573180734068812936522917262584310348197759271053002624975393069562093842335386142498160851407955171567115006695031543956223071374812660525650855702504766177195875431508647497422709100003287448750515422805257908586964818789983352264124145622402086532283896013630126930304818073407993454914327, exponent = 65537 } }

$ orapki wallet add -wallet ./aedbadmin -trusted_cert -cert ./root/b64certificate.txt -pwd [email protected] -sign_alg sha512

Oracle PKI Tool : Version 12.2.0.1.0

Operation is successfully completed.

$ orapki wallet add -wallet ./aedbadmin -user_cert -cert ./aedbadmin/cert.txt -pwd [email protected] -sign_alg sha512

Oracle PKI Tool : Version 12.2.0.1.0

Operation is successfully completed.

$ orapki wallet display -wallet ./aedbadmin -pwd [email protected]

Oracle PKI Tool : Version 12.2.0.1.0

Requested Certificates:

User Certificates:

Subject: CN=AEDBADMIN,OU=ITC,O=CA Technologies,C=IN

Trusted Certificates:

Subject: CN=CA TrustedCertificate,OU=CA ITC,O=CA Technologies,C=IN

e. Create an Oracle wallet with a user certificate for AUTOSYS

$ orapki wallet create -wallet ./autosys -auto_login -pwd [email protected]

Oracle PKI Tool : Version 12.2.0.1.0

Operation is successfully completed.

$ orapki wallet remove -wallet ./autosys -trusted_cert_all -pwd [email protected]

Oracle PKI Tool : Version 12.2.0.1.0

$ orapki wallet add -wallet ./autosys -dn "CN=AUTOSYS,OU=ITC,O=CA Technologies,C=IN" -keysize 2048 -pwd [email protected] -sign_alg sha512

Oracle PKI Tool : Version 12.2.0.1.0

Operation is successfully completed.

$ orapki wallet export -wallet ./autosys -dn "CN=AUTOSYS,OU=ITC,O=CA Technologies,C=IN" -request ./autosys/creq.txt

Oracle PKI Tool : Version 12.2.0.1.0

Operation is successfully completed.

$ orapki cert create -wallet ./root -request ./autosys/creq.txt -cert ./autosys/cert.txt -validity 9999 -pwd [email protected]

Oracle PKI Tool : Version 12.2.0.1.0

$ orapki cert display -cert ./autosys/cert.txt -complete

Oracle PKI Tool : Version 12.2.0.1.0

{ fingerprint = c98261585d96485830ba45548f4aca8b, notBefore = Thu May 05 03:32:28 EDT 2022, notAfter = Sun Sep 19 03:32:28 EDT 2049, holder = CN=AUTOSYS,OU=ITC,O=CA Technologies,C=IN, issuer = CN=CA TrustedCertificate,OU=CA ITC,O=CA Technologies,C=IN, serialNo = 0, sigAlgOID = 1.2.840.113549.1.1.11, key = { modulus = 17421534934008591217216695924437566596731352577941147986604958024761395756274392746828399036515519803426068352701198615135711134359317995547020191800417160716153174026176011766794153921718627453599329522176446616224914288596821102671267799932039719045397716964410388922081072935227238286419460804927588110001045415582734776735579087247173825966890672893037681362046522778440467073220860729191861761455873726219385129532285637758305577390741212535747483486609263800431939712632242706599282597257795768612324513911838601696989299042659129101896903743144011495786705927816106658249163572836968864782723513729081391796229, exponent = 65537 } }

$ orapki wallet add -wallet ./autosys -trusted_cert -cert ./root/b64certificate.txt -pwd [email protected] -sign_alg sha512

Oracle PKI Tool : Version 12.2.0.1.0

Operation is successfully completed.

$ orapki wallet add -wallet ./autosys -user_cert -cert ./autosys/cert.txt -pwd [email protected] -sign_alg sha512

Oracle PKI Tool : Version 12.2.0.1.0

Operation is successfully completed.

$ orapki wallet display -wallet ./autosys -pwd [email protected]

Oracle PKI Tool : Version 12.2.0.1.0

Requested Certificates:

User Certificates:

Subject: CN=AUTOSYS,OU=ITC,O=CA Technologies,C=IN

Trusted Certificates:

Subject: CN=CA TrustedCertificate,OU=CA ITC,O=CA Technologies,C=IN

$

f. Create users in the database pointing to the respective certificate DN.

$ sqlplus sys/[email protected] as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu May 5 04:04:28 2022

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create user sslsys identified externally as 'CN=SYS,OU=ITC,O=CA Technologies,C=IN';

User created.

SQL> grant all privileges to sslsys;

Grant succeeded.

SQL> grant select on dba_tablespaces to sslsys with grant option;

Grant succeeded.

SQL> grant select on v_$parameter to sslsys;

Grant succeeded.

SQL> grant DBA to sslsys;

Grant succeeded.

SQL> GRANT CREATE ANY DIRECTORY TO sslsys;

Grant succeeded.

SQL> GRANT CREATE PUBLIC DATABASE LINK TO sslsys;

Grant succeeded.

SQL> grant CONNECT,RESOURCE to sslsys;

Grant succeeded.

SQL>

Since the users, AUTOSYS and AEDBADMIN exist in the database, alter the authentication mode to use an external certificate.

SQL>

SQL> alter user autosys identified externally as 'CN=autosys,OU=ITC,O=CA Technologies,C=IN';

User altered.

SQL> alter user aedbadmin identified externally as 'CN=AEDBADMIN,OU=ITC,O=CA Technologies,C=IN';

User altered.

SQL>

2) Update "listener.ora" and "sqlnet.ora" files on the server.

a. Update DIRECTORY, PROTOCOL, HOST and the PORT information in the listener.ora file and restart the listener service.

For example, The listener file content post updating the information -

$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SSL_CLIENT_AUTHENTICATION = FALSE

WALLET_LOCATION =

(SOURCE =

(METHOD = FILE)

(METHOD_DATA =

(DIRECTORY = /u01/app/oracle/admin/wallet/sslsys)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = lvntest027671.bpc.broadcom.net)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCPS)(HOST = lvntest027671.bpc.broadcom.net)(PORT = 1531))

)

b. Update the file sqlnet.ora with the server-side wallet configuration. The following is an example -

$ cat sqlnet.ora

# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/db_1/network/admin/sqlnet.ora

# Generated by Oracle configuration tools.

SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCPS, NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

SSL_CLIENT_AUTHENTICATION = TRUE

WALLET_LOCATION =

(SOURCE =

(METHOD = FILE)

(METHOD_DATA =

(DIRECTORY = /u01/app/oracle/admin/wallet/sslsys)

)

3) Each user will have its own $TNS_ADMIN location containing the wallet file entry in "sqlnet.ora" file.

(There will be 3 separate directories containing "tnsnames.ora" along with "sqlnet.ora" files)

For this implementation, the wallet location created for each user has been used as $TNS_ADMIN, by creating the "tnsnames.ora" and "sqlnet.ora" files in the same directory with appropriate permissions. The "sqlnet.ora" file must contain the wallet references for the respective database user. For example, the following files are from the $TNS_ADMIN of the user SSLSYS.

1. File sqlnet.ora

$ cat sqlnet.ora

# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/db_1/network/admin/sqlnet.ora

# Generated by Oracle configuration tools.

SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCPS, NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

SSL_CLIENT_AUTHENTICATION = TRUE

WALLET_LOCATION =

(SOURCE =

(METHOD = FILE)

(METHOD_DATA =

(DIRECTORY = /u01/app/oracle/admin/wallet/sslsys)

)

$

2. File tnsnames.ora

$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

AEDB=

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCPS)(HOST = lvntest027671.bpc.broadcom.net)(PORT = 1531))

(ADDRESS = (PROTOCOL = TCP)(HOST = lvntest027671.bpc.broadcom.net)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = AEDB)

)

Ensure all 3 users are able to access the DB using "sqlplus /@DB" command by changing the "$TNS_ADMIN" variable.

$ export TNS_ADMIN=/u01/app/oracle/admin/wallet/sslsys

$ sqlplus /@AEDB

SQL*Plus: Release 12.2.0.1.0 Production on Thu May 5 07:04:19 2022

Last Successful login time: Thu May 05 2022 06:35:10 -04:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show user;

USER is "SSLSYS"

SQL>

5) Configuration changes -

a. File $AUTOSYS/config/connection.properties

oracle.tns.connection.string=jdbc:oracle:thin:/@${dbname}

(Add "/" before the "@${dbname}" if not there already)

b. File $JAVA_HOME/lib/security/java.security

security.provider.11=oracle.security.pki.OraclePKIProvider

(Add line if does not exist)

c. Copy the following libs from oracle JDBC

ojdbc8.jar 
oraclepki.jar 
osdt_core.jar 
osdt_cert.jar

to -

$AUTOSYS/lib
$JAVA_HOME/lib
$AUTOUSER/webserver/webapps/AEWS/WEB-INF/lib/

Verify the ownership and permissions of the libraries are aligned with other files in the directories.

6) Update parameter file "$AUTOSYS/dbobj/switchDBAccessMode/parameter.oracle.CERTFILE"

( The parameter file itself has the description of each property. Review and update the values accordingly)

DB_ACCESS_MODE=CERTIFICATE

ALTER_AEDB_USER_LOGIN=YES

SYSTEM_USER_TNS_ADMIN=/home/client/system_user_tns_admin

AEDBADMIN_USER_DN='CN=AEDBADMIN,OU=ITC,O=CA Technologies,C=IN'

AUTOSYS_USER_TNS_ADMIN=/home/client/autosys_user_tns_admin

AUTOSYS_USER_DN='CN=AUTOSYS,OU=ITC,O=CA Technologies,C=IN'

AUTOSYS_USER_WALLET_LOCATION=/home/client/client_wallet/cwallet.sso

ADDITIONAL_JARS_PATH=/opt/oracle/app/oracle/product/12.1.0/dbhome_1/jlib

SSL_SERVER_DN_MATCH=

VERIFYONLY=NO

RESTART_CAWAAE=YES

SYSTEM_USER_TNS_ADMIN2=/home/client/system_user_tns_admin

AEDBADMIN_USER_DN2='CN=AEDBADMIN,OU=ITC,O=CA Technologies,C=IN'

Note: "VERIFYONLY=TRUE" verifies the system. It is recommended to run with the VERIFYONLY option at least once to identify and resolve the potential problems.

7) Execute the Perl script switchDBAccessMode.pl

Source autosys user profile ($AUTOUSER/autosys.$SHELL.$HOSTNAME) and execute the script

$perl switchDBAccessMode.pl parameter.oracle.CERTFILE

The command makes all the necessary changes and restarts the services automatically with a confirmation message upon successful completion.

INFO: Completed the AutoSys Workload Automation services restart operation. Refer to the above logs for more information.

INFO: switchDBAccessMode.pl execution completed successfully.

It automatically changes all the autosys configurations to use the certificates from the sso wallet file instead of user/password.

Perform usual health checks for the Application server, scheduler and Web Servers

Additional Information

(Oracle Only) Switch the Database Access Mode

https://techdocs.broadcom.com/us/en/ca-enterprise-software/intelligent-automation/autosys-workload-automation/12-0-01/Before-You-Begin/environment-and-database-connection/oracle-only-switch-the-database-access-mode.html

The syntaxes or procedures used for creating the oracle wallets could be different depending on the oracle database release. Contact the Oracle DBA for any clarification on the procedures while configuring SSO wallets. It is recommended to review the oracle documentation and understand the concepts before implementing them for autosys environments.