Oracle

1. Create a service account in Oracle with the appropriate permissions for Validio. See Prerequisites.
2. Create a Validio credential to connect to the service account. See Add an Oracle Credential.
3. Create a Validio source to monitor the tables in the Oracle database. See Add an Oracle Source.

​

Prerequisites

-- Create a user.
CREATE USER VALIDIO_USER IDENTIFIED BY <password>;
GRANT CREATE SESSION TO VALIDIO_USER;

-- Grant read access to the following system tables
-- These permissions are required for Validio catalog functionality.
GRANT SELECT ON "SYS"."V_$SERVICES" TO VALIDIO_USER;
GRANT SELECT ON "SYS"."V_$SQL" TO VALIDIO_USER;
GRANT SELECT ON "SYS"."DBA_USERS" TO VALIDIO_USER;
GRANT SELECT ON "SYS"."ALL_USERS" TO VALIDIO_USER;
GRANT SELECT ON "SYS"."ALL_OBJECTS" TO VALIDIO_USER;
GRANT SELECT ON "SYS"."ALL_TAB_COLUMNS" TO VALIDIO_USER;
GRANT SELECT ON "SYS"."ALL_TAB_COMMENTS" TO VALIDIO_USER;
GRANT SELECT ON "SYS"."ALL_COL_COMMENTS" TO VALIDIO_USER;

-- For data monitoring and profiling, grant read access to all relevant tables and views.
-- The following grants read access to all existing and future tables and views.
GRANT SELECT ANY TABLE TO VALIDIO_USER;
/*
The following variants may be used to grant read access to specific tables and views.

-- Grant read access to an individual table/view.
GRANT SELECT ON <myschema>.<mytable> TO VALIDIO_USER;

-- Grant read access to all existing and future tables/views within a schema.
-- Oracle 23c and above.
GRANT SELECT ANY TABLE ON SCHEMA <myschema> TO VALIDIO_USER;
*/

​

Lineage requirements

-- Create an audit policy to capture lineage-carrying statements.
CREATE AUDIT POLICY validio_audit_pol ACTIONS
    ALTER TABLE,
    ALTER VIEW,
    ALTER MATERIALIZED VIEW,
    CREATE TABLE,
    CREATE VIEW,
    CREATE MATERIALIZED VIEW,
    RENAME,
    INSERT,
    UPDATE,
    DELETE,
    -- NOTE: MERGE is not a supported action in Oracle 12 and should be removed from this list on 12.x versions.
    MERGE,
    TRUNCATE TABLE,
    CREATE PROCEDURE,
    CREATE FUNCTION,
    CREATE PACKAGE BODY,
    CREATE TRIGGER;

-- Enable the policy.
AUDIT POLICY validio_audit_pol;

-- Grant read access to the audit trail view.
GRANT AUDIT_VIEWER TO VALIDIO_USER;

​

Add an Oracle credential

1. Navigate to Credentials and click + New credential.
2. Under Namespace, select a namespace where the resources will be created.
3. For Credential Type, select Oracle Credential.
4. Fill in the credential parameter fields. Refer to the Oracle Credential Parameters table.
5. Check Use for catalog and schema checks to automatically discover credentials and add them to the catalog page.
6. Click Create.

​

Oracle credential parameters

​

Add an Oracle source

1. Navigate to Sources and click + New source.
2. Under Source type, select Oracle.
3. Under Config,
   1. Select the valid Credential or create a new credential to authenticate your connection to the data warehouse.
   2. You have two options for specifying the dataset and tables to monitor:
      1. Select an existing table — Enter the Database, Schema, and Table to specify where the data comes from. Selecting more than one table will create a new source for each table. Refer to the Source Configuration Parameters table.
      2. Use Custom SQL — Write a valid SQL query to specify the tables to monitor.
   3. Set the Polling schedule, which is how frequently the validators on the source will check for changes.
4. Under Schema, click Continue to automatically infer the schema fields from the tables you selected. If you select many tables, this operation can take a few minutes to complete.
5. Under Source details,
   1. Add Tags to help group related sources or to use for routing notifications.
   2. Add an Owner who will be the contact for incident notifications.
   3. Assign a Priority, which indicates the importance of incidents detected on this source.
6. Click Continue to create the source. Source names are generated automatically and will be displayed when the source creation completes. If there are more than 5 sources, you will see the names for the first five and a count of the remaining sources.

​

Oracle source configuration parameters