Oracle (Data Direct - Service Name)

Configure an Oracle datasource with DataDirect connection on your Guardium® system.

Supported Authentication Methods

Authentication Supported
Local user Yes
LDAP Yes
Kerberos No
SSL Yes
Mutual SSL Yes

Parameters

Field Description
Host Name/IP Required. The hostname or IP address of the datasource.
Port number Required. Default value: 1521
Service name The service name of the database.
Schema The name of the database schema.
Connection property Properties that must be included in the JDBC URL to establish a JDBC connection with the datasource. The required format is property1=value;property2=value, where each property and value pair is separated by a semicolon.
For examples, refer to the database vendor's JDBC documentation.

The Oracle JDBC driver does not require a connection property. But the latest Oracle JDBC driver must be downloaded from Oracle. The filename is ojdbc7.jar. Search and download the open source JDBC drivers. For example: use the search query open source JDBC driver for Oracle. Then upload the driver to the appliance using the Guardium Customer Uploads function.

If you continue to use the Oracle DataDirect driver, then you must specify a connection property to the datasource.

  • Use the following definitions for the Oracle DataDirect driver connection property: DataIntegrityLevel=required;EncryptionLevel=required;DataIntegrityTypes=(MD5,SHA1)
  • If you use CRYPTO_CHECKSUM_TYPES in your sqlnet.ora, use the following examples:
    • oracle.net.encryption_client=rc4_256;oracle.net.crypto_checksum_types_client=MD5
    • oracle.net.encryption_client=aes256;oracle.net.crypto_checksum_types_client=MD5
    • oracle.net.encryption_client=rc4_256;oracle.net.crypto_checksum_types_client=SHA1
  • To authenticate to Oracle LDAP, which is also known as OID, use the LDAP server host or IP, the LDAP server port, the Oracle instance name and the realm. Enter the custom URL jdbc:guardium:oracle:@ldap://wi3ku2x32t4:389/on0maver;cn=OracleContext;dc=vguardium;dc=com
  • The Oracle default user sys, is the owner of the database instance and has super user privileges, similar to root on Unix. The SYSDBA role has administrative privileges that are required to perform many high-level administrative operations such as starting and stopping the database, as well as performing operations such as backup and recovery. This SYSDBA role can also be granted to other users. The phrase sys as SYSDBA refers to the connection method required to connect as the sys user.
  • To monitor values for Oracle 10 open source driver, enter the connection property: internal_logon=sysdba
  • To use the SYSDBA role, enter the connection property SysLoginRole=sysdba
  • To initiate an SSL datasource connection with server signed or mutual authentication, enter the connection property: EncryptionMethod=SSL.
For an Oracle encrypted connection, define the connection property as: oracle.net.encryption_client=REQUIRED;oracle.net.encryption_types_client=RC4_40.
Note: A datasource that is defined to use 3DES168 encryption, throws an ORA-17401 protocol error or ORA-17002 checksum error when it encounters any SQL error. To fix this error, close and reopen the connection.

If the connection is unsuccessful because the HostNameInCertificate does not match, enter this string as the connection property using the certificate name provided in the error message: EncryptionMethod=SSL;HostNameInCertificate=certificate name.
Custom URL The connection string to the datasource. When the custom URL is not provided, the datasource connection is made by using properties such as the hostname and port number.
  • When you specify a Custom URL field with the Oracle Open Source format, specify jdbc:guardium:oracle://;SID=<SID>.
  • When you create a datasource for an Oracle database with Oracle Advanced Security enabled, specify EncryptionLevel=required in the Custom URL field of the datasource definition.
To initiate an SSL datasource connection using the Oracle JDBC driver, setup the connection URL using one of the examples below. Refer to the Oracle JDBC connection syntax.

Example:

jdbc:oracle:thin:@(description= (address=(protocol=tcps)(port=1522)(host=adwc.uscom-west-1.oraclecloud.com))(connect_data=(service_name=VOVO0MKSEWWJ3PSJ_ISVDRIVERSDB_medium.dwcs.oracle.com))(security=(ssl_server_cert_dn="CN=adwc-dev.uscom-east-1.oraclecloud.com,OU=Testing Domain,O=End Point,L=Redwood Shores,ST=California,C=US")))

Adding custom properties for classification

By default, the classifier uses count * to determine cardinality for random sampling. For Oracle datasources, Guardium supports alternative methods for determining cardinality: the DATA-CARDINALITY-FOR-SAMPLING-TABLES custom property for tables, and the DATA-CARDINALITY-FOR-SAMPLING-VIEWS-PERCENTAGE custom property for views.
DATA-CARDINALITY-FOR-SAMPLING-TABLES

When sampling tables, this custom property uses database statistics to determine cardinality. To enable the database statistics method, add DATA-CARDINALITY-FOR-SAMPLING-TABLES = STATISTICS as a custom property for the datasource. When using this property, the classifier retrieves the number of rows from the all_tables data dictionary view.

Notes:
  • This custom property requires Guardium patch 11.0p470 or later.
  • The property name must be entered as DATA-CARDINALITY-FOR-SAMPLING-TABLES (all capital letters, with hyphens), and the only valid value is STATISTICS.
  • The value of num_rows in all_tables might not be up-to-date. When using this property, work with your Oracle administrator to ensure the database statistics are current.
  • After you run the discovery scenario with DATA-CARDINALITY-FOR-SAMPLING-TABLES = STATISTICS, click the Info icon for the data source in the Process Run Log. You can see that the datasource is using the custom property.
DATA-CARDINALITY-FOR-SAMPLING-VIEWS-PERCENTAGE

When sampling views, this custom property provides a percentage value that the classifier uses to determine the sample. To enable this method, add DATA-CARDINALITY-FOR-SAMPLING-VIEWS-PERCENTAGE = [0.000001 to <100] as a custom property for the datasource. This method uses sample_percent where the specified value is the probability that each row is selected for the sample.

Note:
  • This custom property requires Guardium patch 11.0p474 or later.
  • The property name must be entered as DATA-CARDINALITY-FOR-SAMPLING-VIEWS-PERCENTAGE (all capital letters, with hyphens), and the allowed values are 0.000001 to <100.

You can add or change custom properties from either the Guardium UI or by using GRDAPI commands. After you add the custom properties, the information displays in the Details for datasource output. For more information about adding custom properties from the UI, see Configuring custom properties for your datasources.

You can also use GRDAPIs to manage custom properties. For example, assuming a datasource created with the following command:
grdapi create_datasource type="Oracle (DataDirect - Service Name)" user="dbusername" password="yourdbpassword" host="dbhostname" name="nameofthisdbconnection" shared=true application="Classifier" port=1521 serviceName="on9stuff" dbName="nameofschema"
Use the following commands to create the DATA-CARDINALITY-FOR-SAMPLING-TABLES and DATA-CARDINALITY-FOR-SAMPLING-VIEWS-PERCENTAGE custom properties:
grdapi create_datasource_custom_property name=DATA-CARDINALITY-FOR-SAMPLING-TABLES values=STATISTICS
grdapi create_datasource_custom_property name=DATA-CARDINALITY-FOR-SAMPLING-VIEWS-PERCENTAGE values=10
Then use the following commands to add the properties to the Oracle datasource:
grdapi add_custom_property_to_datasource_by_name customProps="DATA-CARDINALITY-FOR-SAMPLING-TABLES=STATISTICS" name="nameofthisdbconnection"
grdapi add_custom_property_to_datasource_by_name customProps="DATA-CARDINALITY-FOR-SAMPLING-VIEWS-PERCENTAGE=10" name="nameofthisdbconnection"
For more information about the custom property APIs, see Datasource custom property APIs.

CAS (Configuration Auditing System) database instance

If you are a CAS user, configure the CAS database instance.

Field Description
Account The Oracle installation user.
Directory The directory of $ORACLE_HOME.