Table of Contents

Introduction

JS7 offers two levels of integration with an Oracle DBMS:
- JS7 supports use of Oracle as the JS7 - Database.
- JS7 offers job templates from JS7 - JITL Database Jobs that can be used to access Oracle databases.
For both scenarios users might prefer not to provide a user account and password for authentication with the DBMS.
- Use of passwords is considered insecure when
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.
- 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 means to connect to an Oracle database without specifying a user account and password.

...

Using Oracle Wallet® for JOC Cockpit

Usage

Anchor
prerequisites
prerequisites
Prerequisites

No Oracle Client installation is required for JOC Cockpit. 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 machine where JOC Cockpit is located. Technically the wallet preferably consists of a number of keystores and truststores that can be copied from a remote machine to the server that hosts JOC Cockpit.
- Typical commands to create a wallet include for example:
  - # 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
JOC Cockpit makes 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 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, for example 12c, are reported not to work with Oracle Wallet® when used by JS7. If in doubt use the JDBC Driver version that matches the version of the DBMS.
The following Oracle Java libraries are required that have to match the version of the Oracle DBMS and 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 JETTY_HOME/lib/user_lib directory of the JOC Cockpit installation directory respectively. When running JOC Cockpit for Docker® consider to store the JDBC Driver and libraries in the JETTY_BASE/resources/joc/lib directory.

Anchor
configuration
configuration
Configuration

JOC Cockpit is configured to connect JS7 JOC Cockpit connects to the Oracle database without specifying a database user account and password, instead, the at run-time account of JOC Cockpit is used.

...

makes use of a wallet.

Anchor
hibernate_cfg_xml
hibernate_cfg_xml
Hibernate hibernate.cfg.xml Configuration File

Location: $JETTY JETTY_BASE/resources/joc/hibernate.cfg.xml, see JS7 - Database.

The hibernate configuration should look like this:

Code Block

title	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:@/js7?tns_admin=/home/

oraclesrv:1521

js7/

xe<

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 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 port 1521. The database Service Name is xe.connection URL specifies js7 as the key to an entry in the wallet.
- The URL parameter tns_admin is used to specify the directory of the tnsnames.ora configuration file. JDBC Connections usually would 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 in fact is the directory where the wallet is located. This location not required as the file can reside in any directory that is accessible to JOC Cockpit.
- Consider that an sqlnet.ora configuration file is not used with the above setup for a JDBC connection.

Anchor
tnsnames_ora
tnsnames_ora
Oracle tnsnames.ora Configuration File

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)
    )
  )

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.

Anchor
wallet_location
wallet_location
Wallet Location for Java

Configure the location of the wallet by
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))).

Configure the location of the Oracle Wallet

® by use of a Java define like this:

-Doracle.net.wallet_location=/home/js7/wallet

of the JOC Cockpit run-time account are stored

are located

specify the Java define with the jettyOptions the setting of the installer response file setting of the joc_install_xml installer response file like this:
<entry key="jettyOptions" value="-Doracle.net.wallet_location=/home/js7/wallet"/>
alternatively, for Unix use one of the following options:
- specify the JAVA_OPTIONS environment variable before running the JOC Cockpit jetty.sh start script.
alternatively, for Unix,
- create/modify and make executable the /
etc
- home/
default/joc file to include
- js7/.jocrc file, assuming that js7 is the JOC Cockpit run-time account. This file should export the JAVA_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 the systemd service file, see JS7 - systemd Service Files for automated Startup / Shutdown with Unix Systems

Prerequisites

- Find further details from JS7 - How To - Apply Java Options.

Using Oracle Wallet® for the JITL SQLExecutorJob and PLSQLJob with Agents

The JS7 offers the following job templates for use with a Oracle Wallet®:

JS7 - JITL SQLExecutorJob : Standard JDBC Job for any DBMS
JS7 - JITL PLSQLJob : PL/SQL JDBC job for Oracle DBMS

Both template jobs are running with Agents, therefore the wallet configuration is applied to the respective Agent.

Prerequisites

The prerequisites for setting up the wallet and use of the Oracle JDBC Driver are the same as explained above with chapter 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 your sqlnet.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 for tnsnames.ora and sqlnet.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.
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 have to match the version of the Oracle DBMS and 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 .JS7_AGENT_HOME/lib/user_lib directory of the JOC Cockpit Agent installation path respectivelydirectory. When running JOC Cockpit Agents for Docker® consider to store the JDBC Driver and libraries in the $JETTY_BASE/resources/joc/lib directoryJS7_AGENT_CONFIG_DIR/lib directory.

Configuration

Hibernate hibernate.cfg.xml configuration file

The JS7 - JITL SQLExecutorJob optionally makes use of a Hibernate configuration file. The explanations above from chapter Hibernate hibernate.cfg.xml Configuration File apply. A Hibernate file by default is looked up from JS7_AGENT_CONFIG_DIR/hibernate.cfg.xml.
The JS7 - JITL PLSQLJob does not use a Hibernate file but the job argument db_url for the database URL. Above explanations about use of a URL such as jdbc:oracle:thin:@/js7?tns_admin=/home/js7/wallet similarly apply.
For use with Oracle Wallet® the job arguments db_user and db_password are omitted.

Oracle tnsnames.ora configuration file

The above explanations from chapter Oracle tnsnames.ora Configuration File apply.

Wallet Location for Java

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 / Shutdown with Unix Systems
- Find further details from JS7 - How To - Apply Java Options.

Using Oracle Wallet® for

...

the JITL SQLPlusJob with Agents

Usage

Once JS7 offers the following job templates for use with a Oracle Wallet®:

JS7 - JITL SQLPLUSJob : SQL*Plus Command Line Client

The template job is running with Agents and makes use of the sqlplus Command Line Client, therefore the wallet configuration is applied to the respective Agent. 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 include that

the Oracle Client including SQL*Plus is installed
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 Prerequisites.

Consider that the mkstore command will add the location of the wallet to your sqlnet.ora configuration file.
- This file is required by SQL*Plus and allows to execute the command line client like this: sqlplus /@js7.
- The js7 is the key for the tnsnames.ora configuration file to identify the database connection settings and for the wallet to identify the matching credentials.

Configuration

Environment Variables

The prerequisites to set environment variables for use of execute shell scripts from JS7 Agents that call SQL*Plus with Oracle Wallet® can be met

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.

...

Agent Instance Start Script

Adjust

...

Agent Instance Start Script

...

- For Unix 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
- For Windows add environment variables to the Agent Instance Start Script ./bin/agent_<port>.cmd
  - 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.

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 that are assigned to the workflow or job that requires access to an Oracle database. Job Resources are the include name/value pairs that can be assigned any workflow or job.

Hints

Oracle sqlnet.ora Configuration File

This file is located in the directory specified by the TNS_ADMIN environment variable.

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

Code Block

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

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

# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

WALLET_LOCATION = (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/var/sos-berlin.com/js7/agent/var_4445/config/wallet)))
SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 0

Explanation:

The wallet location specified from /var/sos-berlin.com/js7/agent/var_4445/config/wallet is a possible location that corresponds to JS7_AGENT_CONFIG_DIR/config/wallet. Any location that is within reach of the Agent and that allows to read wallet files can be used.The Oracle Wallet® can be copied to other servers or to other accounts,

Space shortcuts

Page tree

Versions Compared

Old Version 7

New Version 8

Key

Introduction

Using Oracle Wallet® for JOC Cockpit

Usage

Anchor
prerequisites
prerequisites
Prerequisites

Anchor
configuration
configuration
Configuration

Anchor
hibernate_cfg_xml
hibernate_cfg_xml
Hibernate hibernate.cfg.xml Configuration File

Anchor
tnsnames_ora
tnsnames_ora
Oracle tnsnames.ora Configuration File

Anchor
wallet_location
wallet_location
Wallet Location for Java

Prerequisites

Using Oracle Wallet® for the JITL SQLExecutorJob and PLSQLJob with Agents

Prerequisites

Configuration

Hibernate hibernate.cfg.xml configuration file

Oracle tnsnames.ora configuration file

Wallet Location for Java

Using Oracle Wallet® for

the JITL SQLPlusJob with Agents

Usage

Prerequisites

Configuration

Environment Variables

Agent Instance Start Script

Use of Job Resources

Hints

Oracle sqlnet.ora Configuration File

Space shortcuts

Page tree

Page History

Versions Compared

Old Version 7

New Version 8

Key

Introduction

Using Oracle Wallet® for JOC Cockpit

Usage

AnchorprerequisitesprerequisitesPrerequisites

AnchorconfigurationconfigurationConfiguration

Anchorhibernate_cfg_xmlhibernate_cfg_xmlHibernate hibernate.cfg.xml Configuration File

Anchortnsnames_oratnsnames_oraOracle tnsnames.ora Configuration File

Anchorwallet_locationwallet_locationWallet Location for Java

Prerequisites

Using Oracle Wallet® for the JITL SQLExecutorJob and PLSQLJob with Agents

Prerequisites

Configuration

Hibernate hibernate.cfg.xml configuration file

Oracle tnsnames.ora configuration file

Wallet Location for Java

Using Oracle Wallet® for

the JITL SQLPlusJob with Agents

Usage

Prerequisites

Configuration

Environment Variables

Agent Instance Start Script

Use of Job Resources

Hints

Oracle sqlnet.ora Configuration File

Anchor
prerequisites
prerequisites
Prerequisites

Anchor
configuration
configuration
Configuration

Anchor
hibernate_cfg_xml
hibernate_cfg_xml
Hibernate hibernate.cfg.xml Configuration File

Anchor
tnsnames_ora
tnsnames_ora
Oracle tnsnames.ora Configuration File

Anchor
wallet_location
wallet_location
Wallet Location for Java