How to connect to an Oracle database without using passwords

Scope

• Usually a user name and password are specified when connecting to a database.

• Such configurations are considered being insecure as the passwords are stored in clear text in external files or in job parameters.
• The Oracle Wallet® provides a means to connect to a database without specifying user and password.

Oracle Wallet

Documentation

The Oracle Wallet® configuration is described in the Oracle documentation:

• Configuring clients to use the External Password Store e.g. in http://docs.oracle.com/cd/B19306_01/network.102/b14266/cnctslsh.htm#CBHEHGCE
• Or in a bit more condensed version on Oracle-Base web site e.g. in https://oracle-base.com/articles/10g/secure-external-password-store-10gr2
• The location of the docs depend on the specific Oracle version in use.

Using Oracle Wallet® for JobScheduler Master and JOC Cockpit

Usage

JobScheduler Master and JOC Cockpit connect to the Oracle database without specifying a database account and password, instead the run-time account of the components is used.

• Consider to use hibernate files when running the installer instead of specifying the account/password with the installer response files jobscheduler_install.xml and joc_install.xml.
  • The hibernate configuration should look like this:
    • <?xml version="1.0" encoding="UTF-8" standalone="no"?>
<hibernate-configuration>
<session-factory>
<property name="hibernate.connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
<property name="hibernate.connection.password"></property>
<property name="hibernate.connection.url">jdbc:oracle:thin:/@jobscheduler</property>
<property name="hibernate.connection.username"></property>
<property name="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</property>
<property name="hibernate.show_sql">false</property>
<property name="hibernate.connection.autocommit">false</property>
<property name="hibernate.format_sql">true</property>
<property name="hibernate.temp.use_jdbc_metadata_defaults">false</property>
</session-factory>
</hibernate-configuration>
  • Consider that empty elements are used for the account and password. Do not delete the respective elements from the hibernate configuration file.
  • The above example specifies the URL jdbc:oracle:thin:/@jobscheduler that indicates that the database alias name jobscheduler is configured with your tnsnames.ora file.
  • Alternatively the database connect string can be specified directly with the URL like this:
    • jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=minos.sos)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=jobscheduler)))
    • The above connect string specifies the database host minos.sos listening on port 1521 running a database jobscheduler.
    • When using the above URL syntax then no tnsnames.ora file is used.
• Configure the location of the Oracle Wallet® by use of a Java define like this: -Doracle.net.wallet_location=/home/jobscheduler/wallet. This setting should point to the directory where the wallet files of the JobScheduler's run-time account are stored.
  • For JobScheduler Master
    • specify the Java define with the environment variable JAVA_OPTIONS before running the installer, e.g.
      JAVA_OPTIONS="-Doracle.net.wallet_location=/home/jobscheduler/wallet"
    • alternatively activate/modify the options setting in the [java] section of the ./config/factory.ini file like this:
      [java]
options = ... -Doracle.net.wallet_location=/home/jobscheduler/wallet
  • For JOC Cockpit
    • specify the Java define with the jettyOptions setting of the installer response file joc_install_xml like this:
      <entry key="jettyOptions" value="-Doracle.net.wallet_location=/home/jobscheduler/wallet"/>
      
    • alternatively create/modify the /etc/default/joc file to include the JAVA_OPTIONS environment variable like this:
      JAVA_OPTIONS="-Doracle.net.wallet_location=/home/jobscheduler/wallet"
      

Prerequisites

• No Oracle Client installation is used, however, you might need an Oracle Client to set up and to configure the Oracle Wallet®.
• JobScheduler makes use of the Oracle JDBC Driver:
  • Check the Oracle JDBC Driver version that ships with the JobScheduler release, see Which Database Management Systems are supported by JobScheduler? A newer Oracle JDBC Driver might be available for download as the Open Source JobScheduler might not be bundled with the latest Oracle JDBC Driver.
  • Oracle JDBC Drivers that ship for release 18c of the DBMS are reported to work. Previous JDBC Driver releases as e.g. 12c are reported not to work with Oracle Wallet® when used by JobScheduler.
• The following Oracle Java libraries are required that should match the version of the Oracle JDBC Driver.
  • The .jar files are available from an Oracle Client installation and that are offered by Oracle for download:
    • $ORACLE_HOME/jlib/oraclepki.jar
    • $ORACLE_HOME/jlib/osdt_cert.jar
    • $ORACLE_HOME/jlib/osdt_core.jar
  • Store the libraries in the ./lib/user_lib directory of our JobScheduler Master and JOC Cockpit installation path respectively.

Using Oracle Wallet® for Jobs

Usage

Once Oracle Wallet® is configured for the account that will trigger the jobs, the account is able to connect to an Oracle database without password specification, e.g. by using sqlplus /@db11g

Prerequisites

Prerequisites to execute SQL*Plus with Oracle Wallet® on Linux:

1. Oracle client installed
2. Environment variables being set: ORACLE_HOME, LD_LIBRARY_PATH=$ORACLE_HOME/lib, TNS_ADMIN

Prerequisites to execute shell scripts from JobScheduler Master that call SQL*Plus with Oracle Wallet®:

1. Copy declaration and initialization with export of the environment variables to the script ./user_bin/jobscheduler_environment_variables.sh
   • ORACLE_HOME=/some_location
LD_LIBRARY_PATH=$ORACLE_HOME/lib
TNS_ADMIN=/some_location
export ORACLE_HOME LD_LIBRARY_PATH TNS_ADMIN
   • This script is executed on JobScheduler start in the context of the user account that JobScheduler is operated for. The environment variables are forwarded to subsequent jobs.
2. Restart JobScheduler

Hints

• The Oracle Wallet® cannot be copied to other servers or to other accounts, it should be configured separately per each environment.
• The same mechanism is available for JobScheduler Agent that allows to set and to export environment variables from its instance script.