Scope
Usually, a user name and password are specified when connecting to a database.
- Such configurations are considered being insecure as the passwords are stored in clear text in external files or in job parameters.
- The Oracle Wallet® provides a means to connect to a database without specifying user and password.
Oracle Wallet
Documentation
The Oracle Wallet® configuration is described in the Oracle documentation:
- Configuring clients to use the External Password Store e.g. in http://docs.oracle.com/cd/B19306_01/network.102/b14266/cnctslsh.htm#CBHEHGCE
- Or in a bit more condensed version on 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 JS7 JOC Cockpit
Usage
JS7 JOC Cockpit connects to the Oracle database without specifying a database account and password, instead, the run-time account of the component is used.
- JOC Cockpit
$JETTY_BASE/resources/joc/hibernate.cfg.xml
The hibernate configuration should look like this:
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:@//oraclesrv:1521/xe</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 those 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 port1521
. The database Service Name isxe
. - 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)))
Consider updating the hibernate files for JOC Cockpit:
- JOC Cockpit
- 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 JS7's run-time account are stored. As the database configuration for JS7 is added to the JOC Cockpit so the setting for the Oracle wallet is to be added in the JOC:- specify the Java define with the
jettyOptions
the setting of the installer response filejoc_install_xml
like this:<entry key="jettyOptions" value="-Doracle.net.wallet_location=/home/JS7/wallet"/>
- alternatively, create/modify the
/etc/default/joc
file to include theJAVA_OPTIONS
environment variable like this:JAVA_OPTIONS="-Doracle.net.wallet_location=/home/JS7/wallet"
- specify the Java define with the
Prerequisites
- No Oracle Client installation is used, however, you might need an Oracle Client to set up and configure the Oracle Wallet®.
- Typical commands to create a wallet include e.g.:
# create wallet in a directory that is accessible to the JS7 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
the command will add the location of the wallet to yoursqlnet.ora
configuration file.- This file is used e.g. by SQLPlus and therefore allows e.g. to execute:
sqlplus /@JS7
by specifying the entry key fortnsnames.ora
andsqlnet.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.
- This file is used e.g. by SQLPlus and therefore allows e.g. to execute:
- Typical commands to create a wallet include e.g.:
- JS7 makes use of the Oracle JDBC Driver:
- Check the Oracle JDBC Driver version that ships with the JS7 release, see JS7-Database-DBMSandJDBCDriverVersions. A newer Oracle JDBC Driver might be available for download as the Open Source JS7 might not be bundled with the latest Oracle JDBC Driver.
- 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 that 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 JOC Cockpit installation path respectively.
- The .jar files are available from an Oracle Client installation and that are offered by Oracle for download:
Using Oracle Wallet® for Workflows
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 password specification, e.g. by using sqlplus /@db11g
Prerequisites
Prerequisites to execute SQL*Plus with Oracle Wallet® on Linux:
- Oracle client installed
- Environment variables being set:
ORACLE_HOME
,LD_LIBRARY_PATH
=$ORACLE_HOME/lib
,TNS_ADMIN
Prerequisites to execute shell scripts from JS7 Controller that call SQL*Plus with Oracle Wallet®:
- Copy declaration and initialization with the export of the environment variables to the script
./user_bin/environment_variables.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 Controller start in the context of the user account that Controller is operated for. The environment variables are forwarded to subsequent jobs in a workflow.
- Restart Controller
Hints
- The Oracle Wallet® cannot be copied to other servers or to other accounts, it should be configured separately per each environment.
- The same mechanism is available for JS7 Agent that allows to set and export environment variables from its instance script.