Oracle RDBMS Connector

Overview

Used for OneTrust Data Discovery, the Oracle RDBMS connector connects to Oracle's cloud file sharing system and can be used to scan file system information directly from SQL systems.

Pod Residency

rdbms-scanner

Connection Method

JDBC URL

Note:

If the recommended JDBC URL command does not work for your Oracle instance, try using "/" instead of ":" when specifying the service name. This is also applicable and supported for scanning a singular or multiple databases.

CODE 
Example 1 (With port/service): "connectionUrl": "jdbc:oracle:thin:@//<host>:<port>/ServiceName",

Example 2 (With port:SID): "connectionUrl": "jdbc:oracle:thin:@<host>:<port>:<SID>",

Supported Scan Actions

Important:

OneTrust does not currently support scanning of columns with XMLType.

Scan Type

Description

Catalog

Only the metadata in the database like details of schema, table, or column are scanned.

Catalog & Scan

Scans both metadata and data under the tables and returns the appropriate labels.

Credentials and System Permissions

OneTrust recommends to adhere to your organization's security and infrastructure policy and include system engineer resources or other qualified subject matter experts to set up credentials on data sources.

Connect to the application using Client Credentials.

Note:

The presence of base_database (the database to be used for initial connection + calculation of data source level metadata) and database_list (csv list of databases that exist on this connection) the connection URL should either denote the location of the database in the URL with {db} or, if the databases in the database list are to be simply appended, the {db} can be omitted.

The same login credentials must be configured for all databases in the list.

Credentials for scanning a single database:

JSON 
{
    "parameters": {
	"password": "password",
	"user": "username"
    },
    "type": "actual",
    "connection_url": "jdbc:oracle:thin:system/oracle@localhost:XXXXX:XE"
}

Credentials for scanning multiple databases on a single connection.

JSON 
{
    "parameters": {
        "database_list": "XE,OTHERDB",
	"base_database": "XE",
	"password": "password",
	"user": "username"
    },
    "type": "actual",
    "connection_url": "jdbc:oracle:thin:system/oracle@localhost:XXXXX:{db}"
}

Scanning External Tables

This connector supports scanning external Oracle tables similar to a regular table. Please follow the below steps in case of access issue due to data cartridge error.

CODE 
Error Details: ORA-29400: data cartridge error

Cause: An error has occurred in a data cartridge external procedure. This message will be
followed by a second message giving more details about the data cartridge error.

Action: See the data cartridge documentation for an explanation of the second error message.

The flat files associated to the external table (ex: xxx.dat, xxx2.dat) do not exist in the OS directory pointed by the logical directory XXX_DIR. Assuming there are tables as xxx and xxx2 associated to XXXXT table and logical directory is XXX_DIR.

  1. Copy, move, and recreate the flat file xxx.dat so that it exists in the OS directory pointed by the logical XXX_DIR directory.

    • Get the directory location as follows: select * from dba_directories ;

      CODE 
      OWNER         DIRECTORY_NAME         DIRECTORY_PATH 
SYS           XXX_DIR                /oradata/external_files

    • Copy the .dat file to directory path: mv /tmp/xxx.dat /oradata/external_files

  2. If the issue persists, then ensure that all OS flat files associated to the external table exist in the OS directory pointed by the logical XXX_DIR directory: mv /tmp/xxx2.dat /oradata/external_files

  3. Verify the above steps by:

    PYTHON 
    Select * from dba_external_locations
2 where table_name='XXXXT';

OWNER     TABLE_NAME     LOCATION    DIRECTORY_OWNER     DIRECTORY_NAME
-----     -------------  --------    ---------------     ---------------
SYS       XXXXT          xxx.dat    SYS                 XXX_DIR
SYS       XXXXT          xxx2.dat   SYS                 XXX_DIR

  4. Execute the command:

    CODE 
    execute sys.dbms_stats.gather_table_stats('SYS','EMPXT');

Scan Profile Support

Note:

For additional details on scan profile configuration and management, see Setting Up Scan Profiles.

Limits

  • Max databases to categorize and scan

  • Max schema to categorize and scan per database

  • Max tables to categorize scan per schema

  • Max rows to scan per table

  • Include/Exclude support at database, schema and table level

Wildcard

Wildcards are not supported for column inclusion or exclusion.

  • Example: *Name* will not work to include/exclude both firstName and lastName columns

  • Instead, you must specify the exact column names separated by commas (e.g. firstName,lastName).

Important:

Columns Excluded in the Data Dictionary

When a column is excluded, it is still cataloged by the Data Dictionary. This means:

  • Basic metadata for the column is retrieved.

  • During extraction, the actual column content is skipped based on the configuration.

Include/Exclude Path Configuration Fields

  • Database

  • Schema

  • Table

  • Column

Collected Objects & Fields

Objects

Fields

Databases

  • Name

  • Filed Type (Columns)

  • Owner

  • Product Version

  • Product Name

  • Product Family

Tables

  • Name

  • Comment

Columns

  • Name

  • Column Index (Order)

  • Comment

  • Nullable (T/F)

  • Size Estimate

  • Comment

Keys

  • Primary Key Name

  • Primary Key Columns

  • Foreign Key Name

  • Foreign Key Columns