Creating and configuring the destination database user

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.

To create and configure the destination database user, you can use the following set of steps:

1. Create the destination database user

2. Grant basic privileges to the user

3. Grant access to system catalog views

4. Grant additional privileges required for the destination user

5. Grant access to packages

6. Grant SELECT access to other database objects

Creating the destination database user

To create the destination user:

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

Granting basic privileges to the destination user

To grant basic privileges to the destination user:

GRANT CREATE SESSION TO tonic;

Granting destination 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 additional privileges to the destination user

Because the destination database user actually writes to the destination database, it requires a higher level of privileges than the source database user.

And because the database objects are not created until the data generation runs, the destination user requires the "ANY" privileges. It is not possible to grant privileges for specific objects.

The required privileges are slightly different based on whether Structural creates the destination database schema.

Determining the objects used in the source database schema

The required privileges also depend on the types of objects that the source schema contains.

For example, if the source schema contains views, then the destination database user requires CREATE ANY VIEW and DROP ANY VIEW. If the source schema does not contain any views, then the destination database user does not require those privileges.

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;

Required destination database user privileges

The following tables list the required privileges for the destination user, based on whether Structural creates the destination schema and whether the schema contains specific object types.

Table privileges

The following privileges are required to manage tables:

Privilege	Structural creates schema	You create schema
ALTER ANY TABLE	✔️	✔️
COMMENT ANY TABLE	✔️	✔️
CREATE ANY TABLE	✔️
CREATE SESSION	✔️	✔️
DELETE ANY TABLE	✔️	✔️
DROP ANY TABLE	✔️	✔️
INSERT ANY TABLE	✔️	✔️
LOCK ANY TABLE	✔️	✔️
SELECT ANY TABLE	✔️	✔️
UPDATE ANY TABLE	✔️	✔️

Index and index partition privileges

The following privileges are required when the source schema contains indexes or index partitions:

Privilege	Structural creates schema	You create schema
ALTER ANY INDEX	✔️	✔️
CREATE ANY INDEX	✔️

Indextype privileges

The following privileges are required when the source schema contains indextypes:

Privilege	Structural creates schema	You create schema
CREATE ANY INDEXTYPE	✔️
DROP ANY INDEXTYPE	✔️

Sequence privileges

The following privileges are required when the source schema contains sequences:

Privilege	Structural creates schema	You create schema
ALTER ANY SEQUENCE	✔️	✔️
CREATE ANY SEQUENCE	✔️
DROP ANY SEQUENCE	✔️
SELECT ANY SEQUENCE	✔️	✔️

Trigger privileges

The following privileges are required if the source schema contains triggers:

Privilege	Structural creates schema	You create schema
ALTER ANY TRIGGER	✔️	✔️
CREATE ANY TRIGGER	✔️
DROP ANY TRIGGER	✔️

Scheduler object privileges

The following privileges are required if the source schema contains scheduler objects such as jobs, programs, or job classes:

Privilege	Structural creates schema	You create schema
CREATE ANY JOB	✔️

Materialized view privileges

The following privileges are required if the source schema contains materialized views:

Privilege	Structural creates schema	You create schema
CREATE ANY MATERIALIZED VIEW	✔️
DROP ANY MATERIALIZED VIEW	✔️

Synonym privileges

The following privileges are required if the source schema contains synonyms:

Privilege	Structural creates schema	You create schema
CREATE ANY SYNONYM	✔️
DROP ANY SYNONYM	✔️

Type privileges

The following privileges are required if the source schema contains types:

Privilege	Structural creates schema	You create schema
CREATE ANY TYPE	✔️
EXECUTE ANY TYPE	✔️	✔️

View privileges

The following privileges are required if the source schema contains views:

Privilege	Structural creates schema	You create schema
CREATE ANY VIEW	✔️
DROP ANY VIEW	✔️

Advanced queues privileges

The following privileges are required if the source schema contains advanced queues:

Privilege	Structural creates schema	You create schema
DROP ANY EVALUATION CONTEXT	✔️
MANAGE ANY QUEUE	✔️	✔️

Procedures and functions privileges

The following privileges are required if the source schema contains procedures or functions:

Privilege	Structural creates schema	You create schema
CREATE ANY PROCEDURE	✔️
DROP ANY PROCEDURE	✔️

Granting destination database user access to packages

Depending on the object types that the source schema includes, the destination 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;

Granting SELECT access to other database objects

The following types of source database objects might depend on schemas other than the source schema:

• Views

• Materialized views

• Procedures

• Functions

• Packages

The destination database user must be granted at least SELECT access to all of these types of objects that are present in the source database.

If the destination database user does not have SELECT access for a source database object, then when Structural attempts to create the object in the destination database, an error is returned.