Introduction
- JS7 has provision for two levels of integration with an Oracle DBMS:
- JS7 supports use of Oracle as the JS7 - Database. For this scenario see the JS7 - How to make JOC Cockpit connect to an Oracle database using Wallet® article.
- JS7 provides job templates from JS7 - JITL Database Jobs that can be used to access Oracle databases.
- Such jobs are executed by Agents, therefore the explanations below apply to the servers which Agents are operated for.
For both scenarios users might prefer not to provide a user account and password for authentication with the DBMS from readable files.
- The use of passwords is considered insecure when passwords are stored in clear text in external files or in job parameters.
- JS7 offers JS7 - Use of Credential Store with JITL Jobs as an alternative way to store and to retrieve passwords.
- The Oracle Wallet® provides a keystore to connect to an Oracle database without specifying a user account and password from parameters or from readable files.
- The following JITL Jobs are prepared for use with Oracle Wallet®:
- JS7 - JITL SQLExecutorJob: Standard JDBC Job for any DBMS
- JS7 - JITL PLSQLJob: PL/SQL JDBC job for Oracle DBMS
- JS7 - JITL SQLPLUSJob: SQL*Plus Command Line Client
SOS does not accept any liability for use of JS7 with Oracle Wallet®. Configuration of Oracle Wallet® is the user's responsibility and can change based on the version of the DBMS. The following explanations offer an example how to integrate with Oracle 18c, the example is not authoritative and does not cover future versions of the DBMS. The database vendor's documentation offers authoritative instruction how to connect to Oracle Wallet® and how to analyze connection problems.
Oracle Wallet®
The Oracle Wallet® configuration is described in the Oracle documentation. At the time of writing the following links are available:
- Configuring clients to use the External Password Store see, for example, http://docs.oracle.com/cd/B19306_01/network.102/b14266/cnctslsh.htm#CBHEHGCE
- An introduction to the technical configuration in https://www.oracle.com/technetwork/database/enterprise-edition/wp-oracle-jdbc-thin-ssl-130128.pdf
- 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.
Prerequisites
Oracle Wallet®
Except for the use of the JS7 - JITL SQLPLUSJob, no Oracle Client installation is required at run-time for use of a wallet with JS7 Agents.
However, users need an Oracle Client to set up and to configure the wallet.
- The wallet does not necessarily have to be created on the machines where JS7 Agents are located. The wallet preferably consists of a number of keystore and truststore files which can be copied from a remote machine to the servers that host the JS7 Agents.
Typical commands for creating a wallet include:
Example how to set up a wallet# create the wallet in an arbitrary location mkstore -wrl /home/js7/wallet -create # add credentials to the wallet; specify key, user account and password for database access mkstore -wrl /home/js7/wallet/ -createCredential js7 some_account some_password # check that the key has been added to the wallet mkstore -wrl /home/js7/wallet/ -listCredential
Oracle JDBC Driver
The JS7 - JITL SQLExecutorJob and JS7 - JITL PLSQLJob make use of the Oracle JDBC Driver.
- Check the Oracle JDBC Driver version that ships with the JS7 release, see JS7 - Database, chapter: Individual JDBC Driver Versions. A newer JDBC Driver might be available for download from Oracle.
- Oracle JDBC Drivers that ship for release 18c of the DBMS are reported to work. Previous Oracle JDBC Driver releases, for example 12c, are reported not to work with Oracle Wallet® when used by JS7. If in doubt use the Oracle JDBC Driver version that matches the version of the DBMS.
- Users who want to use a specific version of the Oracle JDBC Driver can apply the following steps:
- For on premises installations store the Oracle JDBC Driver's .jar file in the
JS7_AGENT_HOME/lib/user_lib
directory of the Agent installation directory. - When running JS7 Agent containers consider to store the Oracle JDBC Driver's .jar file in the
JS7_AGENT_CONFIG_DIR/lib
directory.
- For on premises installations store the Oracle JDBC Driver's .jar file in the
Oracle PKI Libraries
The JS7 - JITL SQLExecutorJob and JS7 - JITL PLSQLJob make use of Oracle PKI Libraries.
- The Oracle PKI libraries are required and have to match the version of the Oracle DBMS and Oracle JDBC Driver.
- The .jar files are provided by Oracle for download and are available from an Oracle Client installation, for example from:
ORACLE_HOME/jlib/oraclepki.jar
ORACLE_HOME/jlib/osdt_cert.jar
ORACLE_HOME/jlib/osdt_core.jar
- For on premises installations store the Oracle PKI libraries in the
JS7_AGENT_HOME/lib/user_lib
directory of the JS7 Agent installation directory. - When running JS7 Agent containers consider storing the Oracle PKI libraries in the
JS7_AGENT_CONFIG_DIR/lib
directory.
Configuration
Hibernate hibernate.cfg.xml Configuration File
The JS7 - JITL SQLExecutorJob uses a Hibernate configuration file.
The JS7 - JITL PLSQLJob does not use a Hibernate configuration file but uses the db_url
job argument for the database URL.
- The explanations below describing use of a URL such as
jdbc:oracle:thin:@/js7?TNS_ADMIN=/home/js7/wallet
apply in a similar manner. - For use with Oracle Wallet® the job
db_user
anddb_password
arguments are omitted.
A Hibernate configuration file by default is looked up from
, see JS7 - Database. The Hibernate configuration may look like this:JS7_AGENT_CONFIG_DIR
/hibernate.cfg.xml
- Note the empty elements that are used for the account and password. Do not delete these elements from the Hibernate configuration file.
- The connection URL specifies
js7
as the key to an entry in thetnsnames.ora
configuration file and in the wallet. - The
TNS_ADMIN
URL parameter is used to specify the directory of thetnsnames.ora
configuration file. JDBC Connections usually do not need this configuration file as connection details (Listener, Service Name, Service ID) are specified with the URL. However, due to use of thejs7
key to the wallet in the URL it is preferable to manage connection details from atnsnames.ora
configuration file. - In the above example this file is located in the
/home/js7/wallet
directory which is in fact the directory where the wallet is located. This location is not authoritative as the file can reside in any directory that is accessible to JOC Cockpit. - Note that an
sqlnet.ora
configuration file is not used with the above setup of a JDBC connection.
Oracle tnsnames.ora Configuration File
Use of the tnsnames.ora
file applies to all JITL Jobs.
The following example is not authoritative but is intended to explain a few basic settings:
Explanation:
- Line 4: The name
JS7
of the first entry in this file corresponds to the key for which credentials have been stored to the wallet. - Line 5-9: The settings indicate the Listener's host and port and the database Service Name or Service ID.
Wallet Location for Java
The JS7 - JITL SQLPLUSJob identifies the wallet location from its sqlnet.ora
configuration file.
The JS7 - JITL SQLExecutorJob and JS7 - JITL PLSQLJob identify the wallet location from a Java define.
- Configure the location of the 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 are located. This setting can be specified for an Agent with one of the following options:- specify the
JAVA_OPTIONS
environment variable before running theagent_<port>.sh|.cmd
Instance Start Script. - for Unix, add the
JAVA_OPTIONS
environment variable to thesystemd
service file, see JS7 - systemd Service Files for automated Startup and Shutdown with Unix Systems. - Further details can be found in the JS7 - How To - Apply Java Options article.
- specify the
Using Oracle Wallet® for the JITL SQLExecutorJob
JS7 provides the JS7 - JITL SQLExecutorJob template for use with Oracle Wallet®.
This job template is run with JS7 Agents and performs standard SQL operations for any DBMS including Oracle. This job template cannot be used to execute PL/SQL code that is specific for Oracle.
Prerequisites
All of the Prerequisites explained above apply.
Configuration
All of the Configuration items explained above apply.
Using Oracle Wallet® for the JITL PLSQLJob
JS7 provides the JS7 - JITL PLSQLJob template for use with Oracle Wallet®.
This job template is run with JS7 Agents and can be used to execute PL/SQL code that is specific for Oracle. This job template can be used for the Oracle DBMS only.
Prerequisites
All of the Prerequisites explained above apply.
Configuration
All of the Configuration items explained above apply.
Using Oracle Wallet® for the JITL SQLPlusJob
JS7 offers the JS7 - JITL SQLPLUSJob template for use with Oracle Wallet®.
The job template is running with JS7 Agents and makes use of the sqlplus
Command Line Client. This job template requires prior installation of an Oracle Client that includes the SQL*Plus Command Line Client.
Prerequisites
Prerequisites to execute SQL*Plus with Oracle Wallet® include:
- installation of the Oracle Client including SQL*Plus
- setting the following environment variables:
ORACLE_HOME
,LD_LIBRARY_PATH
=$ORACLE_HOME/lib
,TNS_ADMIN
The prerequisites for setting up the wallet are the same as explained above in the Prerequisites, Oracle Wallet® section.
- Add the location of the wallet to your
sqlnet.ora
configuration file, for example:WALLET_LOCATION = (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/home/js7/wallet)))
- Additional entries will be required for this file, please check Oracle's documentation.
- This file is required by SQL*Plus and allows execution of the command line client like this:
sqlplus /@js7.
js7
is the key for thetnsnames.ora
configuration file which is used to identify the database connection settings. It is also used by the wallet to identify the matching credentials.
Configuration
Environment Variables
The prerequisites for setting environment variables for use of SQL*Plus with Oracle Wallet® can be met by:
- adding environment variable to the Agent Instance Start Script or
- setting up JS7 - Job Resources to inject environment variables to workflows and jobs.
Environment Variables from the Agent Instance Start Script
- Modify the Agent Instance Start Script
- For Unix add environment variables to the
JS7_AGENT_HOME/bin/agent_<port>.sh
Agent Instance Start ScriptORACLE_HOME=/some_location
LD_LIBRARY_PATH=$ORACLE_HOME/lib
TNS_ADMIN=/some_location
export ORACLE_HOME LD_LIBRARY_PATH TNS_ADMIN
- For Windows add environment variables to the
JS7_AGENT_HOME\bin\agent_<port>.cmd
Agent Instance Start Scriptset ORACLE_HOME=C:\some_location
set LD_LIBRARY_PATH=%ORACLE_HOME%\lib
set TNS_ADMIN=C:\some_location
- The Instance Start 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.
- For Unix add environment variables to the
- Restart the Agent.
Environment Variables from Job Resources
Instead of adding the above environment variables to the JS7 Agent's Instance Start Script, they can be added to JS7 - Job Resources which are then assigned to the workflow or job that requires access to an Oracle database. Job Resources include name/value pairs that can be assigned any workflow or job.
Oracle sqlnet.ora Configuration File
This file is located in the directory specified by the TNS_ADMIN
environment variable. The wallet location is identified from the sqlnet.ora
configuration file.
The following example is not authoritative but is intended to explain a few basic settings:
Explanation:
- The wallet location specified from
/var/sos-berlin.com/js7/agent/var_4445/config/wallet
is a possible location that corresponds toJS7_AGENT_CONFIG_DIR/config/wallet
. Any location that is within reach of the JS7 Agent and that allows to read the wallet's files can be used.