Introduction
Usually, a user name and password are specified when connecting to a database.
- Such configurations are considered insecure as passwords are stored in clear text in external files or in job parameters.
- The Oracle Wallet® provides a means to connect to an Oracle database without specifying a user account and password.
Oracle Wallet®
The Oracle Wallet® configuration is explained with 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 as a more condensed version from the Oracle-Base web site e.g. in https://oracle-base.com/articles/10g/secure-external-password-store-10gr2
- The location of the docs depends on the specific Oracle version in use.
Using Oracle Wallet® for JOC Cockpit
Usage
JS7 JOC Cockpit connects to the Oracle database without specifying a database account and password, instead, the run-time account of JOC Cockpit is used.
- Hibernate configuration file
- Location:
$JETTY_BASE/resources/joc/hibernate.cfg.xml
The hibernate configuration should look like this:
Hibernate configuration file for Oracle® database<?xml version="1.0" encoding="UTF-8" standalone="no"?> <hibernate-configuration> <session-factory> <property name="hibernate.connection.driver_class">oracle.jdbc.OracleDriver</property> <property name="hibernate.connection.password"></property> <property name="hibernate.connection.url">jdbc:oracle:thin:@//oraclesrv:1521/xe</property> <property name="hibernate.connection.username"></property> <property name="hibernate.dialect">org.hibernate.dialect.Oracle12cDialect</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> <property name="hibernate.connection.provider_class">org.hibernate.hikaricp.internal.HikariCPConnectionProvider</property> <property name="hibernate.hikari.maximumPoolSize">10</property> </session-factory> </hibernate-configuration>
- Consider the empty elements that are used for the account and password. Do not delete the respective elements from the hibernate configuration file.
- The example makes use of the Oracle® database listener running for hostname
oraclesrv
and port1521
. The database Service Name isxe
. - Should you want to use a Service ID instead of a Service Name, then use this URL syntax:
jdbc:oracle:thin:@oraclesrv:1521:xe
- Should you want to directly specify additional settings as typically used from
tnsnames.ora
, then use this URL syntax:jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL=TCP)(HOST=oraclesrv)(PORT=1521)))(CONNECT_DATA=(SID=XE)(GLOBAL_NAME=XE.WORLD)(SERVER=DEDICATED))).
- Location:
- Configure the location of the Oracle Wallet® by use of a Java define like this:
-Doracle.net.wallet_location=/home/js7/wallet
. This setting should point to the directory where the wallet files of the JOC Cockpit run-time account are stored. This setting can be specified with one of the following options:- specify the Java define with the
jettyOptions
the setting of the installer response filejoc_install_xml
like this:<entry key="jettyOptions" value="-Doracle.net.wallet_location=/home/js7/wallet"/>
- alternatively specify the
JAVA_OPTIONS
environment variable before running the JOC Cockpitjetty.sh
start script. - alternatively, for Unix, create/modify and make executable the
/etc/default/joc
file to include theJAVA_OPTIONS
environment variable like this:export JAVA_OPTIONS="-Doracle.net.wallet_location=/home/js7/wallet"
- alternatively, for Unix, add the
JAVA_OPTIONS
environment variable to thesystemd
service file, see JS7 - systemd Service Files for automated Startup / Shutdown with Unix Systems
- specify the Java define with the
Prerequisites
- No Oracle Client installation is required, however, you might need an Oracle Client to set up and to configure the Oracle Wallet®.
- Typical commands to create a wallet include e.g.:
# create wallet in a directory that is accessible to the JOC Cockpit run-time account assumed to be "js7"
mkstore -wrl /home/js7/wallet -create
# add credentials to wallet; specify entry key, database account and password
mkstore -wrl /home/js7/wallet/ -createCredential js7 some_account some_password
- Consider that the
mkstore
command will add the location of the wallet to yoursqlnet.ora
configuration file.- This file is used e.g. by SQL*Plus and therefore allows e.g. to execute:
sqlplus /@js7
by specifying the entry key fortnsnames.ora
andsqlnet.ora
- This file is not considered when using the Oracle JDBC Driver, therefore the above Java define
-Doracle.net.wallet_location
has to be used.
- This file is used e.g. by SQL*Plus and therefore allows e.g. to execute:
- Typical commands to create a wallet include e.g.:
- JOC Cockpit makes use of the Oracle JDBC Driver:
- Check the Oracle JDBC Driver version that ships with the JS7 release, see JS7 - Database: - Individual JDBC Driver Versions. A newer Oracle JDBC Driver might be available for download as included with the JS7 release.
- 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 JS7.
- 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 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 the JOC Cockpit installation path respectively. When running JOC Cockpit for Docker® consider to store the JDBC Driver and libraries in the$JETTY_BASE/resources/joc/lib
directory.
- The .jar files are available from an Oracle Client installation and are offered by Oracle for download:
Using Oracle Wallet® for Workflow Execution with Agents
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 use of a password, e.g. by using sqlplus /@js7
Prerequisites
Prerequisites to execute SQL*Plus with Oracle Wallet® on Linux include that
- the Oracle Client is installed
- the following environment variables are set:
ORACLE_HOME
,LD_LIBRARY_PATH
=$ORACLE_HOME/lib
,TNS_ADMIN
The prerequisites to execute shell scripts from JS7 Agents that call SQL*Plus with Oracle Wallet® can be met by using the Agent Instance Start Script or by using JS7 - Job Resources to inject above environment variables to jobs.
Use of Agent Instance Start Script
- Add environment variables to the Agent Instance Start Script
./bin/agent_<port>.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 startup of the Agent in the context of the user account that the Agent is operated for. The environment variables are forwarded to subsequent jobs in a workflow.
- Restart the Agent
Use of Job Resources
Instead of adding the above environment variables to the Agent's Instance Start Script, they can be added to JS7 - Job Resources which then can be assigned to the workflow or job that requires access to an Oracle database. Job Resources are the name/value pairs that can be assigned any workflow or job.
Hints
- The Oracle Wallet® can be copied to other servers or to other accounts,