Oracle
Oracle is a powerful relational database management system (RDBMS) designed for enterprise-scale applications. It supports high availability, scalability, and advanced features like partitioning, replication, and strong security. It is widely used across industries. Oracle enables efficient management of structured data with robust SQL and PL/SQL support.
Prerequisites
The following prerequisites must be met to create and test a successful connection.
- The Oracle server must be accessible from the iceDQ server.
- Valid credentials to access the database.
- Oracle version 11 or above.
Authentication Mechanisms
The following authentication mechanisms are supported.
- Username & Password
- Kerberos
Connection Properties
Username and Password Authentication
Use the following properties to create a valid connection. Properties marked with an asterisk (*) are required.
| Name | Description | Example Values |
|---|---|---|
| Connection Name * | Name that uniquely identifies the connection. | Sales_prod_conn |
| Driver * | Driver used to establish the connection. By default, one driver is available. | Oracle Native JDBC |
| Custom JDBC URL | Standardized string used to define the connection details. For more URL formats please refer the section JDBC URL Formats below. | jdbc:oracle:thin:@194.186.53.47:1521/prod_db |
| Host * | IP address or hostname of the database server. | oracle.acme.com or 194.186.53.47 |
| Port * | Port is the address on which the server listens. Default value is 1521 for Oracle. | 1521 |
| Database * | Oracle System Identifier (SID) or Service Name for the target database. | prod_db |
| Type * | The connection type – either System Connection or User Connection. Refer Connections for more details. | System or User |
| Username * | Database login username with necessary privileges. | john_doe |
| Password * | Password associated with the specified username. | Admin@123 |
Kerberos Authentication
Use the following properties to create a valid connection. Properties marked with an asterisk (*) are required.
| Name | Description | Example Values |
|---|---|---|
| Connection Name * | Name that uniquely identifies the connection. | Sales_prod_conn |
| Driver * | Driver used to establish the connection. Select from the available options. | Oracle Native JDBC |
| Custom JDBC URL | Standardized string used to define the connection details. For more URL formats please refer the section JDBC URL Formats below. | jdbc:oracle:thin:@194.186.53.47:orcl |
| Krb5 File * | Path to the Kerberos configuration file (typically krb5.conf). | User needs to upload the krb5 file. |
| Host * | IP address or hostname of the database server. | oracle.acme.com |
| Port * | Port on which the server listens. Default value is 1521. | 1521 |
| Database * | Oracle System Identifier (SID) or Service Name for the target database. | prod_db |
| Type * | The connection type – either System Connection or User Connection. Refer Connections for more details. | System or User |
| Principal * | Kerberos principal (e.g., user@REALM). | [email protected] |
| Keytab * | Path to the keytab file used for authentication. | User needs to upload the keytab file. |
JAAS Properties
With respect to Kerberos authentication, JAAS properties refer to configuration settings used by the Java Authentication and Authorization Service (JAAS) to authenticate a user or application using Kerberos. For a comprehensive list of supported properties, refer the JAAS Reference section of the Oracle Java SE Security Developer’s Guide here.
Custom Properties
Custom properties are optional connection parameters in the Oracle JDBC driver, allowing customization of settings like timeouts, compression, and secure connections. A list of supported properties is available here. The availability and behavior of custom connection properties may vary depending on the version of the Oracle JDBC driver in use. The following custom properties are used frequently:
| Property | Default Value | Possible Values | Description |
|---|---|---|---|
| loginTimeout | 30 | Integer Value | Timeout for socket connect (in seconds), with 0 being no timeout. |
| useFetchSizeWithLongColumn | true | true, false | Drivers ignore the fetch size when querying tables that include LONG or LONG RAW columns. |
| useDateAsTimestamp | false | true, false | If true, Oracle DATE columns are returned as Timestamp instead of Date objects. |
TLS / SSL Certificate
The Transport Layer Security (TLS) / Secure Sockets Layer (SSL) certificate option enables encrypted communication between iceDQ and the Oracle database server. iceDQ supports TLS/SSL through either Oracle Wallet or a Java Truststore.
Using Oracle Wallet (Username & Password Authentication)
Oracle Wallet is a secure, password-protected software container used to store cryptographic and authentication credentials, such as private keys and certificates, allowing connections without hardcoding passwords. An Oracle JDBC connection wallet is typically provided as a wallet file downloaded from Oracle Cloud. Follow these steps below to setup the connection using Oracle Wallet:
- Setup a valid
cwallet.ssofile prior to creating the connection. - Select the
Custom JDBC URLcheckbox and enter the URL in the following format.
jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=194.186.53.47)(PORT=2484))(CONNECT_DATA=(SERVICE_NAME=ORCL))) - On the TLS / SSL Certificate tab, select Use Custom certificates checkbox and upload the
cwallet.ssofile.
Using Java Truststore
A Java Truststore contains trusted certificate authorities (CAs) used to verify the Oracle server’s identity over SSL/TLS.
- A valid
.jksfile (Java Keystore) needs to be setup prior to creating the connection. - Select the
Custom JDBC URLcheckbox and enter the URL in the following format.
jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=194.186.53.47)(PORT=2484))(CONNECT_DATA=(SERVICE_NAME=ORCL))) - On the TLS / SSL Certificate tab, select Use Custom certificates checkbox and upload the
.jksfile. - A truststore password is mandatory to establish the connection.
- This method can be used with either username/password or Kerberos authentication, if configured properly.
SSL/TLS is only supported when using a Custom JDBC URL that includes the host, port, and service name.
Consideration - Alphanumeric Data
In case a column with alphanumeric data is used in the diff join of a Recon rule template, then the user must provide a custom ORDER BY clause with the NLSSORT function in the SQL Query section of the Oracle connection tab.
SELECT * FROM TABLE ORDER BY NLSSORT(<COL>, 'NLS_SORT=BINARY')
JDBC URL Formats
Below are the different URL formats that can be used in Full URL property.
| Method | Driver Type | Description | Format | Example |
|---|---|---|---|---|
| SID (Service ID) | Thin | Default method of connecting to Oracle from iCEDQ. | jdbc:oracle:thin:@<host>:<port>:<SID> | jdbc:oracle:thin:@192.168.1.100:1521:XE |
| Service Name | Thin | Connect using service name. Requires full URL override. | jdbc:oracle:thin:@<host>:<port>/<service_name> | jdbc:oracle:thin:@192.168.1.100:1521/ORCL |
| TNS Descriptor | Thin | Uses full TNS descriptor, often required in HA Oracle environments. | jdbc:oracle:thin:@<TNS Descriptor> | jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.100)(PORT=1521)))CONNECT_DATA=(SID=XE))) |
| OCI (Thick Client) | OCI (Thick) | Requires Oracle client installed on iCEDQ server and OCI jar files in lib. | jdbc:oracle:oci:@<host>:<port>:SID | jdbc:oracle:oci:@192.168.1.100:1521:XE |
Unsupported Datatypes
- BINARY_FLOAT
- BINARY_DOUBLE
- BIT
- ENUM
- GEOSPATIAL
- RAW
- LONG/ LONG RAW
- UROWID
- BFILE
- BLOB