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.

TheTONIC_ORACLE_DBLINK_ENABLED environment setting is deprecated and eventually will be removed. When that happens, Structural will no longer create a database link between the source and destination databases during data generation runs.

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 not enabled. If database links are enabled, then set the environment setting TONIC_ORACLE_DBLINK_ENABLED to true for both the worker and the web server. You can configure this environment setting from Structural Settings. For more information, go to Configuring environment settings.

TONIC_ORACLE_DBLINK_ENABLED is false (default)

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;

TONIC_ORACLE_DBLINK_ENABLED is true

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.

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 Structural Settings.

When TONIC_ORACLE_SKIP_CREATE_DB is true, then Structural does not create the destination database schema. Before you run data generation, you must create the destination database with the full schema.

During data generation, Structural 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 3 months ago