Page History
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.
- JS7 offers provides job templates from for JS7 - JITL Database Jobs that can be used to access Oracle databases. For this scenario see the JS7 - How to make JITL Jobs connect to an Oracle database using Wallet® article.
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 enables 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.
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
Anchor | ||||
---|---|---|---|---|
|
...
Anchor | ||||
---|---|---|---|---|
|
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. Technically the The wallet preferably consists of a number of keystores keystore and truststores truststore files that can be copied from a remote machine to the server that hosts JOC Cockpit.
Typical commands
to createfor 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
JOC Cockpit makes use of the Oracle JDBC Driver: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
Anchor | ||||
---|---|---|---|---|
|
- 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 releasefrom 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.
Anchor | ||||
---|---|---|---|---|
|
- The following Oracle Java PKI libraries are required that 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 and are offered by Oracle for download, for example from:
- $ORACLE
ORACLE_HOME/jlib/oraclepki.jar
- $ORACLE
ORACLE_HOME/jlib/osdt_cert.jar
- $ORACLE
ORACLE_HOME/jlib/osdt_core.jar
Store the
- $ORACLE
- For on premises installations, store the Oracle PKI libraries in the
JETTY_
HOMEBASE/lib/ext/
user_libjoc
directory of the JOC Cockpit installation configuration directory respectively. - When running JOC Cockpit for Docker® consider to store the JDBC Driver and containers, consider storing the Oracle PKI libraries in the
JETTY_BASE/resources/joc/lib
directory.
Anchor | ||||
---|---|---|---|---|
|
The JOC Cockpit is configured to connect to an Oracle database using Hibernate. In addition, the Oracle database without specifying a user account and password, instead, at run-time JOC Cockpit makes use of a walletlocation of Oracle configuration files and of the wallet has to be specified.
Anchor | ||||
---|---|---|---|---|
|
- Location:
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:@/js7@js7?tnsTNS_adminADMIN=/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 connection URL specifies
js7
as as the key to for an entry in thetnsnames.ora
configuration file and in the wallet. - The URL parameter
tns_admin
TNS_ADMIN
URL parameter is used to specify the directory of thetnsnames.ora
configuration file. JDBC Connections usually would do not need require this configuration file as connection details (Listener, Service Name, Service ID) are specified with in the URL. However, due to use of thejs7
wallet key to the wallet in the URL, it is preferable to manage that connection details from are managed in atnsnames.ora
configuration file. - In the example 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 required 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 for a JDBC connection.
Anchor | ||||
---|---|---|---|---|
|
The following example is not authoritative but is intended to explain a few basic settings:
Code Block | ||||||||
---|---|---|---|---|---|---|---|---|
| ||||||||
# 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) ) ) |
...
- 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 | ||||
---|---|---|---|---|
|
The wallet location is specified in a Java define.
- Configure the location of the wallet by use of using 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 with one of the following options:
Code Block | ||||||||
---|---|---|---|---|---|---|---|---|
| ||||||||
# 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 toJS7_AGENT_CONFIG_DIR/config/wallet
. Any location that is within reach of the Agent and that allows to read wallet files can be used- specify the Java define with the
jettyOptions
setting of thejoc_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 Cockpitjetty.sh
start script. - create/modify and make executable the
/home/js7/.jocrc
file, assuming thatjs7
is the JOC Cockpit run-time account. This file should export theJAVA_OPTIONS
environment variable like this:export JAVA_OPTIONS="-Doracle.net.wallet_location=/home/js7/wallet"
- add the
JAVA_OPTIONS
environment variable to thesystemd
service file, see as described in the JS7 - systemd Service Files for automated Startup / and Shutdown with Unix Systemsarticle.
- specify the
- Find further details from JS7 - How To - Apply Java Options.
- specify the Java define with the
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.
- 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
JS7_AGENT_HOME/lib/user_lib
directory of the Agent installation directory. When running Agents for Docker® consider to store the JDBC Driver and libraries in theJS7_AGENT_CONFIG_DIR/lib
directory.
- The .jar files are available from an Oracle Client installation and are offered by Oracle for download:
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 asjdbc:oracle:thin:@/js7?tns_admin=/home/js7/wallet
similarly apply. - For use with Oracle Wallet® the job arguments
db_user
anddb_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 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 / Shutdown with Unix Systems - Find further details from JS7 - How To - Apply Java Options.
- specify the
Using Oracle Wallet® for the JITL SQLPlusJob with Agents
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.
Prerequisites
Prerequisites to execute SQL*Plus with Oracle Wallet® 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 yoursqlnet.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 thetnsnames.ora
configuration file to identify the database connection settings and for the wallet to identify the matching credentials.
- This file is required by SQL*Plus and allows to execute the command line client like this:
Configuration
Environment Variables
The prerequisites to set environment variables for use of 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.
- For Unix add environment variables to the Agent Instance Start Script
- 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 that are 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.
...
- Further details can be found in the JS7 - How To - Apply Java Options article.