DB User Privileges

To run OCI360 tool and handle all the generated metadata, you need to have a database user with the following required privileges:

System Privileges

The following system privileges are required:

• CREATE SESSION
• ALTER SESSION
• CREATE SEQUENCE
• CREATE TABLE
• CREATE VIEW

Tablespace Quota

You need to give to the user quota on his default tablespace:

• ALTER USER XXX DEFAULT TABLESPACE YYY QUOTA UNLIMITED ON YYY;

Object Privileges

The following object privileges are required:

• SELECT ON SYS.GV_$INSTANCE
• SELECT ON SYS.GV_$OSSTAT
• SELECT ON SYS.GV_$SYSTEM_PARAMETER2
• SELECT ON SYS.V_$DATABASE
• SELECT ON SYS.V_$INSTANCE
• SELECT ON SYS.V_$PARAMETER
• SELECT ON SYS.V_$PARAMETER2
• SELECT ON SYS.V_$PROCESS
• SELECT ON SYS.V_$SESSION
• SELECT ON SYS.V_$SYSTEM_PARAMETER2
• EXECUTE ON SYS.DBMS_LOCK
• EXECUTE ON SYS.UTL_FILE (If running in Local DB)
• EXECUTE ON DBMS_CLOUD (If running in Autonomous DB)

PS: SYS.UTL_FILE is required to read/write the JSON files from directories and load within DB tables.

Extra Privileges

Local Database

When running on a local database, the tool will read the JSON files from the directory where the OCI360 output is located.

So you can either previously CREATE the directory and give the account READ/WRITE/EXECUTE privileges OR give the account CREATE/DROP ANY DIRECTORY (folders will be dynamically created during execution).

• CREATE DIRECTORY ZZZ AS '/.../';
• GRANT READ, WRITE, EXECUTE ON DIRECTORY ZZZ TO XXX;

OR

• CREATE ANY DIRECTORY
• DROP ANY DIRECTORY

Autonomous Database

When running on an ADB, the tool will read the JSON files from the object storage bucket where the OCI360 output is located.

For this to work, the tool will dynamically create and drop External Tables using the DBMS_CLOUD.CREATE_EXTERNAL procedure.

This requires the following privilege:

• GRANT READ ON DIRECTORY DATA_PUMP_DIR TO XXX;

Also, you will need to create a credential that will point to the user who has access to the corresponting bucket.

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL (
	credential_name => 'OCI360_CRED',
	user_ocid       => 'ocid1.user.oc1..xxx',
	tenancy_ocid    => 'ocid1.tenancy.oc1..xxx',
	private_key     => '-----BEGIN RSA PRIVATE KEY-----
XXX==
-----END RSA PRIVATE KEY-----',
	fingerprint     => '00:00:00:00:00:00:00:00:00:00:00:00:00:00:00:00');
END;
/

Examples

Example 1 - For local databases only

Creating a schema to handle the OCI360 objects and execute the tool. In this example, the schema name will be OCI360ACC:

-- Connect as DBA to create the OCI360 specific user.
SQL> connect sys/xxx@localhost:1521/xepdb1 as sysdba
Connected.

SQL> create user OCI360ACC identified by "xxx";
User created.

SQL> alter user OCI360ACC default tablespace USERS quota unlimited on USERS;
User altered.

SQL> grant CREATE SESSION, ALTER SESSION, CREATE SEQUENCE, CREATE TABLE, CREATE VIEW to OCI360ACC;
Grant succeeded.

SQL> grant SELECT on SYS.GV_$INSTANCE to OCI360ACC;
Grant succeeded.

SQL> grant SELECT on SYS.GV_$OSSTAT to OCI360ACC;
Grant succeeded.

SQL> grant SELECT on SYS.GV_$SYSTEM_PARAMETER2 to OCI360ACC;
Grant succeeded.

SQL> grant SELECT on SYS.V_$DATABASE to OCI360ACC;
Grant succeeded.

SQL> grant SELECT on SYS.V_$INSTANCE to OCI360ACC;
Grant succeeded.

SQL> grant SELECT on SYS.V_$PARAMETER to OCI360ACC;
Grant succeeded.

SQL> grant SELECT on SYS.V_$PARAMETER2 to OCI360ACC;
Grant succeeded.

SQL> grant SELECT on SYS.V_$PROCESS to OCI360ACC;
Grant succeeded.

SQL> grant SELECT on SYS.V_$SESSION to OCI360ACC;
Grant succeeded.

SQL> grant SELECT on SYS.V_$SYSTEM_PARAMETER2 to OCI360ACC;
Grant succeeded.

SQL> grant EXECUTE on SYS.DBMS_LOCK to OCI360ACC;
Grant succeeded.

SQL> grant EXECUTE on SYS.UTL_FILE to OCI360ACC;
Grant succeeded.

SQL> create directory OCI360DIR as '/home/oracle/oci360_out/';
Directory created.

SQL> grant READ,WRITE on directory OCI360DIR to OCI360ACC;
Grant succeeded.

Example 2 - For Autonomous Databases only

Creating a schema to handle the OCI360 objects and execute the tool. In this example, the schema name will be OCI360ACC:

-- First connect as ADMIN to create the OCI360 specific user.
SQL> connect admin/xxx@oci360_medium
Connected.

SQL> create user OCI360ACC identified by "xxx";
User created.

SQL> alter user OCI360ACC default tablespace DATA quota unlimited on DATA;
User altered.

SQL> grant CREATE SESSION, ALTER SESSION, CREATE SEQUENCE, CREATE TABLE, CREATE VIEW to OCI360ACC;
Grant succeeded.

SQL> grant SELECT on SYS.GV_$INSTANCE to OCI360ACC;
Grant succeeded.

SQL> grant SELECT on SYS.GV_$OSSTAT to OCI360ACC;
Grant succeeded.

SQL> grant SELECT on SYS.GV_$SYSTEM_PARAMETER2 to OCI360ACC;
Grant succeeded.

SQL> grant SELECT on SYS.V_$DATABASE to OCI360ACC;
Grant succeeded.

SQL> grant SELECT on SYS.V_$INSTANCE to OCI360ACC;
Grant succeeded.

SQL> grant SELECT on SYS.V_$PARAMETER to OCI360ACC;
Grant succeeded.

SQL> grant SELECT on SYS.V_$PARAMETER2 to OCI360ACC;
Grant succeeded.

SQL> grant SELECT on SYS.V_$PROCESS to OCI360ACC;
Grant succeeded.

SQL> grant SELECT on SYS.V_$SESSION to OCI360ACC;
Grant succeeded.

SQL> grant SELECT on SYS.V_$SYSTEM_PARAMETER2 to OCI360ACC;
Grant succeeded.

SQL> grant EXECUTE on SYS.DBMS_LOCK to OCI360ACC;
Grant succeeded.

SQL> grant EXECUTE on DBMS_CLOUD to OCI360ACC;
Grant succeeded.

SQL> grant READ on directory DATA_PUMP_DIR to OCI360ACC;
Grant succeeded.

-- Now connect as the OCI360ACC to create the DB Credential used to get data from OCI Bucket.
SQL> connect OCI360ACC/xxx@oci360_medium
Connected.

SQL> BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL (
	credential_name => 'OCI360_CRED',
	user_ocid       => 'ocid1.user.oc1..aaaaaaaaunnaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',
	tenancy_ocid    => 'ocid1.tenancy.oc1..aaaaaaaaunnaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',
	private_key     => '-----BEGIN RSA PRIVATE KEY-----
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx==
-----END RSA PRIVATE KEY-----',
	fingerprint     => '00:00:00:00:00:00:00:00:00:00:00:00:00:00:00:00');
END;
/
PL/SQL procedure successfully completed.