Introduction

• JS7 has provision for two levels of integration with an Oracle DBMS:
  • JS7 supports use of Oracle as the JS7 - Database.
  • JS7 provides job templates 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.

  • The use of passwords is considered insecure when passwords are stored in clear text in external files or in job parameters.
  • JS7 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.

Oracle Wallet®

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

• 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 in 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
prerequisites prerequisites

Prerequisites

Anchor
wallet wallet

Oracle 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

Anchor
jdbc jdbc

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.

Anchor
pki pki

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_HOME/lib/user_lib directory of the JOC Cockpit installation directory.
• When running JOC Cockpit containers for Docker® consider storing the Oracle PKI libraries in the JETTY_BASE/resources/joc/lib directory.

Anchor
configuration configuration

Configuration

The JOC Cockpit is configured to connect to an Oracle database using Hibernate. In addition, the locations of Oracle configuration files and of the wallet have to be specified.

Anchor
hibernate_cfg_xml hibernate_cfg_xml

Hibernate hibernate.cfg.xml Configuration File

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

• The Hibernate configuration file 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>

  
  
  

• 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 for an entry 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.

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:

...

• 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

The wallet location is specified in a Java define.

...