Configure autosys to use oracle SSO wallets with certificates for database connection
Article ID: 241010
Updated On: 10-02-2023
Products
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)
Note: Substitute your password for <password> in all steps below:
$ cd /u01/app/oracle/admin/wallet
$ orapki wallet create -wallet ./root -pwd <password>
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
Operation is successfully completed.
$ orapki wallet remove -wallet ./root -trusted_cert_all -pwd <password>
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
$ orapki wallet add -wallet ./root -dn "CN=CA TrustedCertificate,OU=CA ITC,O=CA Technologies,C=IN" -keysize 2048 -self_signed -validity 9999 -pwd <password> -sign_alg sha512
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
Operation is successfully completed.
$ orapki wallet display -wallet ./root -pwd <password>
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
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 <password>
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
Operation is successfully completed.
c. Create an Oracle wallet with a user certificate for SSLSYS
$ orapki wallet create -wallet ./sslsys -auto_login -pwd <password>
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
Operation is successfully completed.
$ orapki wallet remove -wallet ./sslsys -trusted_cert_all -pwd <password>
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
$ orapki wallet add -wallet ./sslsys -dn "CN=SYS,OU=ITC,O=CA Technologies,C=IN" -keysize 2048 -pwd <password> -sign_alg sha512
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
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
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
Operation is successfully completed.
$ orapki cert create -wallet ./root -request ./sslsys/creq.txt -cert ./sslsys/cert.txt -validity 9999 -pwd <password>
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
$ orapki cert display -cert ./sslsys/cert.txt -complete
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
{ 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 <password> -sign_alg sha512
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
Operation is successfully completed.
$ orapki wallet add -wallet ./sslsys -user_cert -cert ./sslsys/cert.txt -pwd <password> -sign_alg sha512
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
Operation is successfully completed.
$
$ orapki wallet display -wallet ./sslsys -pwd <password>
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
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 <password>
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
Operation is successfully completed.
$ orapki wallet remove -wallet ./aedbadmin -trusted_cert_all -pwd <password>
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
$ orapki wallet add -wallet ./aedbadmin -dn "CN=AEDBADMIN,OU=ITC,O=CA Technologies,C=IN" -keysize 2048 -pwd <password> -sign_alg sha512
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
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
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
Operation is successfully completed.
$ orapki cert create -wallet ./root -request ./aedbadmin/creq.txt -cert ./aedbadmin/cert.txt -validity 9999 -pwd <password>
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
$ orapki cert display -cert ./aedbadmin/cert.txt -complete
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
{ 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 <password> -sign_alg sha512
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
Operation is successfully completed.
$ orapki wallet add -wallet ./aedbadmin -user_cert -cert ./aedbadmin/cert.txt -pwd <password> -sign_alg sha512
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
Operation is successfully completed.
$ orapki wallet display -wallet ./aedbadmin -pwd <password>
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
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 <password>
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
Operation is successfully completed.
$ orapki wallet remove -wallet ./autosys -trusted_cert_all -pwd <password>
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
$ orapki wallet add -wallet ./autosys -dn "CN=AUTOSYS,OU=ITC,O=CA Technologies,C=IN" -keysize 2048 -pwd <password> -sign_alg sha512
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
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
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
Operation is successfully completed.
$ orapki cert create -wallet ./root -request ./autosys/creq.txt -cert ./autosys/cert.txt -validity 9999 -pwd <password>
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
$ orapki cert display -cert ./autosys/cert.txt -complete
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
{ 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 <password> -sign_alg sha512
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
Operation is successfully completed.
$ orapki wallet add -wallet ./autosys -user_cert -cert ./autosys/cert.txt -pwd <password> -sign_alg sha512
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
Operation is successfully completed.
$ orapki wallet display -wallet ./autosys -pwd <password>
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
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/redhat@AEDB as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Thu May 5 04:04:28 2022
Copyright (c) 1982, 2016, Oracle. All rights reserved.
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 = oracle-server-hostname.company.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCPS)(HOST = oracle-server-hostname.company.com)(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 = oracle-server-hostname.company.com)(PORT = 1531))
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle-server-hostname.company.com)(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
Copyright (c) 1982, 2016, Oracle. All rights reserved.
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
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.
Feedback
