Before you create an Oracle workspace

Creating the source database user

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

The source destination 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 or read-only access.

The details for providing the required access are slightly different based on whether you use database links. By default, Structural assumes that database links are enabled. If database links are not enabled, then set the environment setting TONIC_ORACLE_DBLINK_ENABLED to false for both the worker and the web server. You can configure this environment setting from Tonic Settings. For more information, go to Configuring environment settings.

TONIC_ORACLE_DBLINK_ENABLED is true (Default)

If TONIC_ORACLE_DBLINK_ENABLED is true, then to create the source database user:

--create a user
CREATE USER TONIC IDENTIFIED BY "<tonic_password>";

--give the user the required access
GRANT CREATE SESSION TO TONIC;
GRANT SELECT ON SESSION_ROLES TO TONIC;
GRANT EXP_FULL_DATABASE TO TONIC;
GRANT EXECUTE ON DBMS_METADATA TO TONIC;

--give the user access to tables in your preferred schema
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;

If TONIC_ORACLE_DBLINK_ENABLED is true, then Structural must be able to create a database link between the destination database and the source database. Structural uses the network link to process a data generation job.

TONIC_ORACLE_DBLINK_ENABLED is false

If TONIC_ORACLE_DBLINK_ENABLED is false, then to create the source database user, you can use one of the following options to create the source database user and grant the required access:

Grant the SELECT ANY DICTIONARY privilege

One way to grant the required access is to grant the SELECT ANY DICTIONARY privilege.

If you choose that option, then to create the source database user:

--create a user
CREATE USER TONIC IDENTIFIED BY "<tonic_password>";

--give the user the required access
GRANT CREATE SESSION TO TONIC;
GRANT SELECT ON SESSION_ROLES TO TONIC;
GRANT EXECUTE ON DBMS_METADATA TO TONIC;
GRANT SELECT ANY DICTIONARY TO TONIC;

--give the user access to tables in your preferred schema
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;

Grant the SELECT_CATALOG_ROLE role

Instead of the SELECT ANY DICTIONARY privilege, you can grant the SELECT_CATALOG_ROLE role.

If you choose that option, then to create the source database user:

--create a user
CREATE USER TONIC IDENTIFIED BY "<tonic_password>";

--give the user the required access
GRANT CREATE SESSION TO TONIC;
GRANT SELECT ON SESSION_ROLES TO TONIC;
GRANT EXECUTE ON DBMS_METADATA TO TONIC;
GRANT SELECT_CATALOG_ROLE TO TONIC;

--give the user access to tables in your preferred schema
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;

Grant access to the ALL_ views (not recommended)

If for security reasons you cannot use either of the previous options, Structural can use the ALL_* views that are provided automatically in Oracle.

By default, an Oracle installation uses a GRANT to PUBLIC to grant all users access to all tables that start with ALL_. For example:

GRANT SELECT ALL_TABLES TO PUBLIC
GRANT SELECT ALL_COLUMNS TO PUBLIC
...

If you do not revoke the PUBLIC access to the ALL_* views, then Structural can use this access to connect to the source database. To create the source database user:

--create a user
CREATE USER TONIC IDENTIFIED BY "<tonic_password>";

--give the user the required access
GRANT CREATE SESSION TO TONIC;
GRANT SELECT ON SESSION_ROLES TO TONIC;
GRANT EXECUTE ON DBMS_METADATA TO TONIC;
GRANT SELECT DBA_SEGMENTS TO TONIC;
GRANT SELECT DBA_LOBS TO TONIC;

--give the user access to tables in your preferred schema
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;

If you do revoke the PUBLIC access to the ALL_* views, then you must specifically grant access to the source database user for the required ALL_* views. To create the source database user:

--create a user
CREATE USER TONIC IDENTIFIED BY "<tonic_password>";

--give the user the required access
GRANT CREATE SESSION TO TONIC;
GRANT SELECT ON SESSION_ROLES TO TONIC;
GRANT EXECUTE ON DBMS_METADATA TO TONIC;
GRANT SELECT DBA_SEGMENTS TO TONIC;
GRANT SELECT DBA_LOBS TO TONIC;
GRANT SELECT ALL_COLL_TYPES TO TONIC;
GRANT SELECT ALL_CONS_COLUMNS TO TONIC;
GRANT SELECT ALL_CONSTRAINTS TO TONIC;
GRANT SELECT ALL_DEPENDENCIES TO TONIC;
GRANT SELECT ALL_IND_COLUMNS TO TONIC;
GRANT SELECT ALL_INDEXES TO TONIC;
GRANT SELECT ALL_MVIEW_LOGS TO TONIC;
GRANT SELECT ALL_MVIEWS TO TONIC;
GRANT SELECT ALL_NESTED_TABLES TO TONIC;
GRANT SELECT ALL_OBJECTS TO TONIC;
GRANT SELECT ALL_PROCEDURES TO TONIC;
GRANT SELECT ALL_TAB_COLS TO TONIC;
GRANT SELECT ALL_TAB_COLUMNS TO TONIC;
GRANT SELECT ALL_TAB_PRIVS TO TONIC;
GRANT SELECT ALL_TABLES TO TONIC;
GRANT SELECT ALL_TYPE_ATTRS TO TONIC;
GRANT SELECT ALL_TYPES TO TONIC;
GRANT SELECT ALL_USERS TO TONIC;

--give the user access to tables in your preferred schema
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;

Creating the destination database user and schema

This is the Oracle user that connects to the destination database. This user cannot be the same user as the output schema of the data.

Structural does not create the schema or the tablespace in the destination database. You must create the schemas and tablespaces before you generate data.

--create a new user
CREATE USER TONIC IDENTIFIED BY "<tonic_password>";

--this user must be granted more extensive privileges
GRANT ALL PRIVILEGES, IMP_FULL_DATABASE to TONIC;

--create the destination schema
CREATE USER <OUTPUT_NAME> IDENTIFIED BY "<OUTPUT_PASSWORD>";

Configuring whether Structural creates the destination database schema

By default, during each data generation job, Structural creates the database schema for the destination database tables, then populates the database tables based on the workspace configuration.

If you prefer to manage the destination database schema yourself, then set the environment setting TONIC_ORACLE_SKIP_CREATE_DB to true. You can add this setting manually to the Environment Settings list on Tonic Settings.

When TONIC_ORACLE_SKIP_CREATE_DB is true, then Structural does not create the destination database schema. It deletes the data from the destination database tables, except in the following cases:

Tables that use Preserve Destination mode
Upsert data generation

It then populates the tables with the new destination data.

Last updated 1 month ago