Introduction
- JS7 has provision for two levels of integration with an Oracle DBMS:
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
Usually, a user name and password are specified when connecting to a database.
- Such configurations are considered insecure as means
- credential store to connect to an Oracle database without specifying a user account and password from parameters or from readable files.
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 explained with in the Oracle documentation. At the time of writing the following links are available:
- Configuring To configur 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 in 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.
Anchor |
---|
| prerequisites |
---|
| prerequisites |
---|
|
PrerequisitesOracle Wallet®An Oracle Client installation is not required at run-time to allow a wallet to be used with the 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 the JOC Cockpit is located. The wallet preferably consists of a number of keystore and truststore files that can be copied from a remote machine to the server that hosts JOC Cockpit.
Typical commands for creating a wallet include:
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 |
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.
- To apply a version of the Oracle JDBC Driver that is different to the version that ships with JS7, see the JS7 - Database, chapter: Individual JDBC Driver Versions article.
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
JETTY_BASE/lib/ext/joc
directory of the JOC Cockpit configuration directory. - When running JOC Cockpit containers, consider storing the Oracle PKI libraries in the
JETTY_BASE/resources/joc/lib
directory.
Anchor |
---|
| configuration |
---|
| configuration |
---|
|
ConfigurationThe JOC Cockpit is configured to connect to an Oracle database using Hibernate. In addition, the location of Oracle configuration files and of the wallet has to be specified.
Anchor |
---|
| hibernate_cfg_xml |
---|
| hibernate_cfg_xml |
---|
|
Hibernate hibernate.cfg.xml Configuration File- Location:
JETTY
Hibernate configuration fileLocation: $JETTY_BASE/resources/joc/hibernate.cfg.xml
, see JS7 - Database.
The
hibernate Hibernate configuration
should file may look like this:
Code Block |
---|
title | Example of a Hibernate configuration file |
---|
|
for Oracle® database | 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: |
@//oraclesrv:1521/xe<@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 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 for an entry in the tnsnames.ora
configuration file and in the wallet. - The
TNS_ADMIN
URL parameter is used to specify the directory of the tnsnames.ora
configuration file. JDBC Connections usually do not require this configuration file as connection details (Listener, Service Name, Service ID) are specified in the URL. However, due to use of the js7
wallet key in the URL, it is preferable that connection details are managed in a tnsnames.ora
configuration file. - In the example above 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 for a JDBC connection.
Oracle tnsnames.ora Configuration FileThe 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 JavaThe wallet location is specified in a Java define.
- Configure the location of the wallet by using
- 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
. This setting should point to the directory where the wallet files of the JOC Cockpit run-time account are storedare located. This setting can be specified with one of the following options:- 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
/
etcdefault/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 /
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 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.
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,