-- The following must be run as sysdba
-- SQL > conn / as sysdba

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;
 
-- Permissions needed to capture storage options
GRANT EXECUTE ON SYS.UTL_FILE TO DATICAL_SCHEMA_OWNER_ROLE;

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;

-- The following permission is required if you you have Data Vault installed. This is only needed on the packaging / reference database.
GRANT BECOME USER 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;

-- Modify this to set the appropriate directory path.
CREATE OR REPLACE DIRECTORY DATICAL_DATAPUMP_DIR AS '&PathToDatapumpDir';

-- Modify this to set the appropriate directory path.
CREATE OR REPLACE DIRECTORY TRACE_FILE_DIR AS '&PathToTracefileDir';

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

-- Directory permissions cannot be granted by a ROLE because the directory is checked through the utl_file.file_exists function.
GRANT READ, WRITE ON DIRECTORY DATICAL_DATAPUMP_DIR TO &SchemaOwner;
GRANT ALL ON DIRECTORY TRACE_FILE_DIR TO &SchemaOwner;

GRANT DATICAL_TRACK_ROLE TO &SchemaOwner;

-- Unlimited tablespace access
-- GRANT UNLIMITED TABLESPACE TO &SchemaOwner;
 -- –or-
-- Grant access to one or more specified tablespaces
-- ALTER USER &SchemaOwner QUOTA UNLIMITED ON &TablespaceName;

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

-- Unlimited tablespace access
-- GRANT UNLIMITED TABLESPACE TO &SchemaOwner;
 -- –or-
-- Grant access to one or more specified tablespaces
-- ALTER USER &SchemaOwner QUOTA UNLIMITED ON &SchemaOwner;

-- 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 = '&SchemaOwner' order by 1;

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

-- Verify READ/WRITE permissions exist on the DATICAL_DATAPUMP_DIR (REF database only).
select table_name, privilege from DBA_TAB_PRIVS where grantee = '&SchemaOwner';