Creating and configuring the source database user

This is the Oracle user that connects to the source database.

The source database user can be the same as the schema of the data. However, we recommend that you create a Tonic Structural-specific user that has more restricted access.

To create the source database user, use the following set of steps:

1. Create the source database user

2. Grant basic privileges to the user

3. Grant access to system catalog views

4. Grant access to source database tables

5. Grant access to packages

Creating the source database user

To create the source database user:

CREATE USER tonic IDENTIFIED BY "<tonic password>";

Granting basic privileges to the source database user

To grant basic privileges to the source user:

GRANT CREATE SESSION TO tonic;

Granting the source database user access to system catalog views

For information on options for granting access to system catalog views, go to Granting access to system catalog views.

Granting the source database user access to source tables

The source database user must be able to select data from the tables.

To grant the required access, you can either:

• Grant SELECT ANY TABLE to the user

• Grant SELECT access to the individual tables in the source database

To grant SELECT ANY TABLE:

GRANT SELECT ANY TABLE TO tonic;

To grant SELECT access to the individual tables in the database:

BEGIN
    FOR x IN (SELECT owner, table_name FROM all_tables WHERE owner = '<source_schema>')
    LOOP
        EXECUTE IMMEDIATE 'GRANT SELECT ON "' || x.owner || '"."' || x.table_name || '" TO tonic';
    END LOOP;
END;

Granting the source database user access to packages

Depending on the object types that the source schema includes, the source database user must have EXECUTE ON privileges for the following packages:

Package	Object types that require it
DBMS_JAVA	JAVA CLASS JAVA CLASS JAVA RESOURCE JAVA SOURCE
DBMS_SCHEDULER	JOB JOB CLASS PROGRAM
DBMS_MVIEW	MATERIALIZED VIEW
DBMS_AQADM	QUEUE

To identify the types of objects that are present in the source schema, run the following:

SELECT object_type FROM dba_objects WHERE owner = '<source-schema>' ORDER BY object_type;

This access might already be in place for the PUBLIC user. If it is not, then to grant the access:

GRANT EXECUTE ON DBMS_JAVA TO tonic;
GRANT EXECUTE ON DBMS_SCHEDULER TO tonic;
GRANT EXECUTE ON DBMS_MVIEW TO tonic;
GRANT EXECUTE ON DBMS_AQADM TO tonic;

In addition to those required packages, if either:

• TONIC_ORACLE_SKIP_CREATE_DB is true

• The workspace overrides the setting to true

Then the source database user must also have EXECUTE ON privileges for the DBMS_METADATA package:

GRANT EXECUTE ON DBMS_METADATA TO tonic;