-- The following must be run as the RDS_MASTER_USER that was specified for the database.
-- SQL > connect <RDS_MASTER_USER>

CREATE ROLE DATICAL_SCHEMA_OWNER_ROLE NOT IDENTIFIED;

GRANT CREATE SESSION TO DATICAL_SCHEMA_OWNER_ROLE;

-- Standard Object Permissions
GRANT CREATE TRIGGER TO DATICAL_SCHEMA_OWNER_ROLE;
GRANT CREATE TABLESPACE TO DATICAL_SCHEMA_OWNER_ROLE;
GRANT CREATE SEQUENCE TO DATICAL_SCHEMA_OWNER_ROLE;
GRANT CREATE TABLE TO DATICAL_SCHEMA_OWNER_ROLE;
GRANT CREATE PROCEDURE TO DATICAL_SCHEMA_OWNER_ROLE;
GRANT CREATE SYNONYM TO DATICAL_SCHEMA_OWNER_ROLE;
GRANT CREATE VIEW TO DATICAL_SCHEMA_OWNER_ROLE;
GRANT CREATE TYPE TO DATICAL_SCHEMA_OWNER_ROLE;
GRANT CREATE MATERIALIZED VIEW TO DATICAL_SCHEMA_OWNER_ROLE;
GRANT CREATE DATABASE LINK TO DATICAL_SCHEMA_OWNER_ROLE;
 
-- Other, less common Object Permissions
GRANT CREATE ASSEMBLY TO DATICAL_SCHEMA_OWNER_ROLE;
GRANT CREATE OPERATOR TO DATICAL_SCHEMA_OWNER_ROLE;
GRANT CREATE EXTERNAL JOB TO DATICAL_SCHEMA_OWNER_ROLE;
GRANT CREATE LIBRARY TO DATICAL_SCHEMA_OWNER_ROLE;
GRANT CREATE JOB TO DATICAL_SCHEMA_OWNER_ROLE;
GRANT CREATE CLUSTER TO DATICAL_SCHEMA_OWNER_ROLE;
GRANT CREATE EVALUATION CONTEXT TO DATICAL_SCHEMA_OWNER_ROLE;
GRANT CREATE RULE TO DATICAL_SCHEMA_OWNER_ROLE;
GRANT CREATE RULE SET TO DATICAL_SCHEMA_OWNER_ROLE;
GRANT CREATE DIMENSION TO DATICAL_SCHEMA_OWNER_ROLE;
GRANT CREATE ROLLBACK SEGMENT TO DATICAL_SCHEMA_OWNER_ROLE;
GRANT CREATE CUBE TO DATICAL_SCHEMA_OWNER_ROLE;
GRANT CREATE CUBE DIMENSION TO DATICAL_SCHEMA_OWNER_ROLE;
GRANT CREATE CUBE BUILD PROCESS TO DATICAL_SCHEMA_OWNER_ROLE;
GRANT CREATE MEASURE FOLDER TO DATICAL_SCHEMA_OWNER_ROLE;
GRANT CREATE MINING MODEL TO DATICAL_SCHEMA_OWNER_ROLE;
GRANT CREATE INDEXTYPE TO DATICAL_SCHEMA_OWNER_ROLE;
GRANT CREATE PROFILE TO DATICAL_SCHEMA_OWNER_ROLE;
  
-- Permissions for working with EDITIONING VIEWS 
-- Use ALTER <user> ENABLE EDITIONS on the user to enable editioning
GRANT CREATE ANY EDITION TO DATICAL_SCHEMA_OWNER_ROLE;
GRANT DROP ANY EDITION TO DATICAL_SCHEMA_OWNER_ROLE;

-- Permissions That Are Not Recommended
-- GRANT CREATE PUBLIC SYNONYM TO DATICAL_SCHEMA_OWNER_ROLE;
-- GRANT CREATE PUBLIC DATABASE LINK TO DATICAL_SCHEMA_OWNER_ROLE;
 
-- Permissions required to run the AUTO GENERATED PERMISSIONS feature
GRANT SELECT ANY DICTIONARY TO DATICAL_SCHEMA_OWNER_ROLE;
 
-- Alternatively, this should be the more restrictive set of GRANTs that are needed for AUTO GENERATED PERMISSIONS:
-- GRANT SELECT ON DBA_SYS_PRIVS TO DATICAL_SCHEMA_OWNER_ROLE;
-- GRANT SELECT ON DBA_ROLE_PRIVS TO DATICAL_SCHEMA_OWNER_ROLE;
-- GRANT SELECT ON DBA_COL_PRIVS TO DATICAL_SCHEMA_OWNER_ROLE;
 
-- Gather metadata (forecast/snapshot/diff)
-- If we change the code to use the "USER" instead of the "DBA" views for snapshots in single user schema owner mode, then this permission could go away.
GRANT SELECT_CATALOG_ROLE TO DATICAL_SCHEMA_OWNER_ROLE;
 
-- OPTIONAL 
-- Permission so Datical can check if the database is 12c but is running in 11g compatible mode
-- This needs to be executed as SYSDBA
GRANT SELECT ON V_$PARAMETER TO DATICAL_SCHEMA_OWNER_ROLE;
 
-- The following permissions are needed to capture storage options for SNAPSHOT
-- On Oracle RDS the RDSADMIN_UTIL package must be used for grants to objects owned by SYS.

begin
    rdsadmin.rdsadmin_util.grant_sys_object(
        p_obj_name  => 'UTL_FILE',
        p_grantee   => 'DATICAL_SCHEMA_OWNER_ROLE',
        p_privilege => 'EXECUTE');
end;
/

CREATE ROLE DATICAL_PACKAGER_SCHEMA_OWNER_ROLE NOT IDENTIFIED;

-- When using the tracefileLocation=REMOTE, you will need to setup the following permissions.
-- Note: These permissions are only required on schemas running the Convert SQL Scripts command.  This is only needed on the packaging / reference database.
GRANT ALTER SESSION TO DATICAL_PACKAGER_SCHEMA_OWNER_ROLE;

-- The following permissions are needed for backing up and restoring the database.  This is only needed on the packaging / reference database.
GRANT EXP_FULL_DATABASE TO DATICAL_PACKAGER_SCHEMA_OWNER_ROLE;
GRANT IMP_FULL_DATABASE TO DATICAL_PACKAGER_SCHEMA_OWNER_ROLE;

CREATE ROLE DATICAL_TRACK_ROLE NOT IDENTIFIED;

-- will create 2 tables - DATABASECHANGELOG and DATABASECHANGELOGLOCK
-- will insert records
grant create session,
  create procedure, create sequence, create table, create trigger, create type,
  create synonym, create view,
  create cluster, create indextype, create operator
to DATICAL_TRACK_ROLE;

connect <RDS_MASTER_USER>
-- Create the DATICAL_DATAPUMP_DIR
EXEC rdsadmin.rdsadmin_util.create_directory(p_directory_name => 'DATICAL_DATA_PUMP_DIR');

-- Permissions for REF databases.
-- Modify this to set the appropriate account name for your SchemaOwner account.
GRANT DATICAL_SCHEMA_OWNER_ROLE TO <replace_SchemaOwner>;
GRANT DATICAL_PACKAGER_SCHEMA_OWNER ROLE to <replace_SchemaOwner>:

-- Directory permissions cannot be granted by a ROLE because the directory is checked through the utl_file.file_exists function.
-- Datapump backup directory.
GRANT READ, WRITE ON DIRECTORY DATICAL_DATA_PUMP_DIR TO <replace_SchemaOwner>;
-- Access to trace files.
GRANT READ, WRITE on DIRECTORY BDUMP to <replace_SchemaOwner>;

GRANT DATICAL_TRACK_ROLE TO <replace_SchemaOwner>;

-- Unlimited tablespace access
-- GRANT UNLIMITED TABLESPACE TO <replace_SchemaOwner>;
 -- –or-
-- Grant access to one or more specified tablespaces
-- ALTER USER <replace_SchemaOwner> QUOTA UNLIMITED ON <replace_tablespace>;

-- Permissions for non-REF databases.
-- Modify this to set the appropriate account name for your SchemaOwner account.
GRANT DATICAL_SCHEMA_OWNER_ROLE TO <replace_SchemaOwner>;
GRANT DATICAL_TRACK_ROLE TO <replace_SchemaOwner>;

-- Unlimited tablespace access
-- GRANT UNLIMITED TABLESPACE TO <replace_SchemaOwner>;
 -- –or-
-- Grant access to one or more specified tablespaces
-- ALTER USER <replace_SchemaOwner> QUOTA UNLIMITED ON <replace_tablespace>;

-- Verify permissions.
-- Modify this to set the appropriate account name for your SchemaOwner accounts.

select grantee, granted_role, default_role from dba_role_privs where grantee = '<replace_SchemaOwner>' order by 1;

-- If DEFAULT_ROLE is not YES.  Execute the following.
alter user <replace_SchemaOwner> default role DATICAL_ROLE;
alter user <replace_SchemaOwner> default role DATICAL_PACKAGER_ROLE;  /* REF database only */
alter user <replace_SchemaOwner> default role DATICAL_TRACK_ROLE;

-- Verify READ/WRITE permissions exist on the DATICAL_DATA_PUMP_DIR (REF database only).
select table_name, privilege from DBA_TAB_PRIVS where grantee = '<replace_SchemaOwner>';