Table of Contents

Introduction

JS7 offers has provision for two levels of integration with an Oracle DBMS:
- JS7 supports use of Oracle as the JS7 - Database, for . For this scenario see see the JS7 - How to make JOC Cockpit connect to an Oracle database using Wallet® article.
- JS7 offers JS7 provides job templates from JS7 - JITL Database Jobs that can be used to access Oracle databases.
  - Such jobs are executed with by Agents, therefore the explanations below explanations apply to the servers for 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.
- Use 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 credential store to connect to an Oracle database without specifying a user account and password from parameters or from readable files.
The following JITL Jobs can be used 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

...

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

Configuring clients to use the External Password Store e.g. in 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.

...

Anchor
wallet
wallet
Oracle Wallet®

Except for the use of the JS7 - JITL SQLPLUSJob no , no Oracle Client installation is required at run-time for use of a wallet with JS7 Agents.

...

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 that which can be copied from a remote machine to the servers that host the JS7 Agents.

Typical commands to create for creating a wallet include for example:

Code Block

title	Example how to set up a wallet
linenumbers	true

# 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

...

The Oracle PKI libraries are required and have to match the version of the Oracle DBMS and Oracle JDBC Driver.
The .jar files are offered 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 to in the JS7_AGENT_HOME/lib/user_lib directory of the JS7 Agent installation directory.
When running JS7 Agent containers for Docker® consider to store storing the Oracle PKI libraries in the JS7_AGENT_CONFIG_DIR/lib directory.

...

The JS7 - JITL SQLExecutorJob makes use of uses a Hibernate configuration file.

The JS7 - JITL PLSQLJob does not use a Hibernate configuration file but uses the job argument db_url job argument for the database URL.

The explanations below explanations about describing use of a URL such as jdbc:oracle:thin:@/js7?tns_admin=/home/js7/wallet similarly apply in a similar manner.
For use with Oracle Wallet® the job arguments db_user and db_password arguments are omitted.

A Hibernate configuration file by default is looked up from JS7_AGENT_CONFIG_DIR/hibernate.cfg.xml, see JS7 - Database. The Hibernate configuration can may look like this:

Code Block

title	Example of a Hibernate configuration file
linenumbers	true
collapse	true

<?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:@/js7?tns_admin=/home/js7/wallet</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 Note the empty elements that are used for the account and password. Do not delete the respective these elements from the Hibernate configuration file.
The connection URL specifies js7 as the key to an entry in the wallet.
The URL parameter tns_admin URL parameter is used to specify the directory of the tnsnames.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 the js7 key to the wallet in the URL it is preferable to manage connection details from a tnsnames.ora configuration file.
In the above example this file is located in the /home/js7/wallet directory that which is in fact is 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.
Consider Note that an sqlnet.ora configuration file is not used with the above setup of a JDBC connection.

...

The following example is not authoritative but is intended to explain a few basic settings:

Code Block

language	text
title	Example of a tnsnames.ora configuration file
linenumbers	true
collapse	true

# tnsnames.ora Network Configuration File: /home/js7/product/18.0.0/dbhomeXE/NETWORK/ADMIN/tnsnames.ora
# Generated by Oracle configuration tools.

JS7 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.11.0.99)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = JS7)
    )
  )

LISTENER_JS7 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.11.0.99)(PORT = 1521))


ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

...

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 the agent_<port>.sh|.cmd Instance Start Script.
- for Unix, add the JAVA_OPTIONS environment variable to the systemd service file, see JS7 - systemd Service Files for automated Startup / and Shutdown with Unix Systems.
- Find further details from Further details can be found in the JS7 - How To - Apply Java Options article.

Using Oracle Wallet® for the JITL SQLExecutorJob

JS7 offers provides the JS7 - JITL SQLExecutorJob template for use with Oracle Wallet®.

The This job template is running 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 above explained Prerequisites explained above apply.

Configuration

All of the above explained Configuration items items explained above apply.

Using Oracle Wallet® for the JITL PLSQLJob

JS7 offers provides the JS7 - JITL PLSQLJob template for use with Oracle Wallet®.

The This job template is running 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 above explained Prerequisites explained above apply.

Configuration

All of the above explained Configuration items explained above apply.

Using Oracle Wallet® for the JITL SQLPlusJob

...

Prerequisites to execute SQL*Plus with Oracle Wallet® include that:

installation of the Oracle Client including SQL*Plus is installed
setting the following environment variables are set: ORACLE_HOME, LD_LIBRARY_PATH=$ORACLE_HOME/lib, TNS_ADMIN

The prerequisites for setting up the wallet are the same as explained above with chapter 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 to execute execution of the command line client like this: sqlplus /@js7.
The js7 is the key for the tnsnames.ora configuration file which is used to identify the database connection settings and for . It is also used by the wallet to identify the matching credentials.

...

Environment Variables

The prerequisites to set for setting environment variables for use of SQL*Plus with Oracle Wallet® can be met by:

by adding environment variable to the Agent Instance Start Script or
by setting up JS7 - Job Resources to inject environment variables to workflows and jobs.

Environment Variables from the Agent Instance Start Script

Adjust Modify the Agent Instance Start Script
- For Unix add environment variables to the Agent Instance Start Script JS7_AGENT_HOME/bin/agent_<port>.sh Agent Instance Start Script
  - ORACLE_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 Agent Instance Start Script JS7_AGENT_HOME\bin\agent_<port>.cmd Agent Instance Start Script
  - set 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.
Restart the Agent.

...

Instead of adding the above environment variables to the JS7 Agent's Instance Start Script, they can be added to JS7 - Job Resources that 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.

...

Space shortcuts

Page tree

Versions Compared

Old Version 19

New Version 20

Key

Introduction

Anchor
wallet
wallet
Oracle Wallet®

Using Oracle Wallet® for the JITL SQLExecutorJob

Prerequisites

Configuration

Using Oracle Wallet® for the JITL PLSQLJob

Prerequisites

Configuration

Using Oracle Wallet® for the JITL SQLPlusJob

Environment Variables

Environment Variables from the Agent Instance Start Script

Space shortcuts

Page tree

Page History

Versions Compared

Old Version 19

New Version 20

Key

Introduction

AnchorwalletwalletOracle Wallet®

Using Oracle Wallet® for the JITL SQLExecutorJob

Prerequisites

Configuration

Using Oracle Wallet® for the JITL PLSQLJob

Prerequisites

Configuration

Using Oracle Wallet® for the JITL SQLPlusJob

Environment Variables

Environment Variables from the Agent Instance Start Script

Anchor
wallet
wallet
Oracle Wallet®