Multi-Schema Project Database Accounts, Roles and Permissions

Two datical database accounts are required for multi-schema projects. These accounts are referred to as DATICAL_USER and DATICAL_TRACKING. At runtime, Datical will connect to the database as the DATICAL_USER to process and deploy code. The DATICAL_TRACKING schema owns the DATABASECHANGELOG and DATABASECHANGELOGLOCK tables used to track and manage database changes. The database change log tables are created in the default tablespace assigned to the DATICAL_TRACKING account. The database account names must be the same across all databases in the pipeline. The DATICAL_USER and DATICAL_TRACKING accounts should not be one of the managed schemas in the pipeline.

Database permissions for Datical are managed through Roles. Three Datical Roles are needed:

DATICAL_ROLE - Required on all databases in the project. Grants permissions to deploy objects to the managed schemas.
DATICAL_PACKAGER_ROLE - Required only on the REF database in the project. Grants additional permissions required to execute Datical packaging operations.
DATICAL_TRACK_ROLE - Required on all databases in the project. Grants permissions to manage the database change log.

Multi-schema projects require the DATICAL_USER account to have elevated privileges because the account is used to create objects in each of the schemas managed by Liquibase.

DATICAL_ROLE for General Permissions on all Database Objects in all Managed Databases

DATICAL_ROLE

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

CREATE ROLE DATICAL_ROLE NOT IDENTIFIED;

GRANT ALTER ANY INDEX TO DATICAL_ROLE;
GRANT ALTER ANY MATERIALIZED VIEW TO DATICAL_ROLE;
GRANT ALTER ANY PROCEDURE TO DATICAL_ROLE;
GRANT ALTER ANY SEQUENCE TO DATICAL_ROLE;
GRANT ALTER ANY TABLE TO DATICAL_ROLE;
GRANT ALTER ANY TRIGGER TO DATICAL_ROLE;
GRANT ALTER ANY TYPE TO DATICAL_ROLE;
GRANT ALTER SESSION TO DATICAL_ROLE;
GRANT GRANT ANY OBJECT PRIVILEGE TO DATICAL_ROLE;
GRANT CREATE ANY DIRECTORY TO DATICAL_ROLE;
GRANT CREATE ANY INDEX TO DATICAL_ROLE;
GRANT CREATE ANY MATERIALIZED VIEW TO DATICAL_ROLE;
GRANT CREATE ANY PROCEDURE TO DATICAL_ROLE;
GRANT CREATE ANY SEQUENCE TO DATICAL_ROLE;
GRANT CREATE ANY SYNONYM TO DATICAL_ROLE;
GRANT CREATE PUBLIC SYNONYM TO DATICAL_ROLE;
GRANT CREATE ANY TABLE TO DATICAL_ROLE;
GRANT CREATE ANY TRIGGER TO DATICAL_ROLE;
GRANT CREATE ANY TYPE TO DATICAL_ROLE;
GRANT CREATE ANY VIEW TO DATICAL_ROLE;
GRANT CREATE SESSION TO DATICAL_ROLE;
GRANT DELETE ANY TABLE TO DATICAL_ROLE WITH ADMIN OPTION;
GRANT DROP ANY DIRECTORY TO DATICAL_ROLE;
GRANT DROP ANY INDEX TO DATICAL_ROLE;
GRANT DROP ANY MATERIALIZED VIEW TO DATICAL_ROLE;
GRANT DROP ANY PROCEDURE TO DATICAL_ROLE;
GRANT DROP ANY SEQUENCE TO DATICAL_ROLE;
GRANT DROP ANY SYNONYM TO DATICAL_ROLE;
GRANT DROP ANY TABLE TO DATICAL_ROLE;
GRANT DROP ANY TRIGGER TO DATICAL_ROLE;
GRANT DROP ANY TYPE TO DATICAL_ROLE;
GRANT DROP ANY VIEW TO DATICAL_ROLE;
GRANT INSERT ANY TABLE TO DATICAL_ROLE;
GRANT SELECT_CATALOG_ROLE TO DATICAL_ROLE;
GRANT UPDATE ANY TABLE TO DATICAL_ROLE ;
GRANT COMMENT ANY TABLE TO DATICAL_ROLE;

-- The following permission are needed for SNAPSHOT, COMPARE, FORECAST on all available schemas
GRANT SELECT ANY TABLE TO DATICAL_ROLE;
GRANT SELECT ANY SEQUENCE TO DATICAL_ROLE;
GRANT EXECUTE ANY PROCEDURE TO DATICAL_ROLE;

-- The following permissions are needed for auto generation of permissions
GRANT SELECT ANY DICTIONARY TO DATICAL_ROLE;

-- The following permissions are needed for working with EDITIONING VIEWS
GRANT CREATE ANY EDITION TO DATICAL_ROLE;
GRANT DROP ANY EDITION TO DATICAL_ROLE;

-- The following permissions are needed if your company allows Public Synonyms
GRANT CREATE PUBLIC SYNONYM TO DATICAL_ROLE;
GRANT DROP PUBLIC SYNONYM TO DATICAL_ROLE;

-- The following permissions are needed for management of database scoped triggers
GRANT ADMINISTER DATABASE TRIGGER TO DATICAL_ROLE;

-- The following permissions are needed to capture storage options for SNAPSHOT
GRANT EXECUTE on SYS.DBMS_METADATA TO DATICAL_ROLE;
GRANT EXECUTE ON SYS.UTL_FILE TO DATICAL_ROLE;

DATICAL_PACKAGER_ROLE for Additional Permissions on the REF Database

This role grants additional permissions needed for Packaging

DATICAL_PACKAGER_ROLE

CREATE ROLE DATICAL_PACKAGER_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 CREATE ANY DIRECTORY TO DATICAL_PACKAGER_ROLE;
GRANT DROP ANY DIRECTORY TO DATICAL_PACKAGER_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_ROLE;
GRANT IMP_FULL_DATABASE TO DATICAL_PACKAGER_ROLE;

-- The following permission is required if you have Data Vault installed. This is only needed on the packaging / reference database.
GRANT BECOME USER TO DATICAL_PACKAGER_ROLE;

-- The following permission is required only if you use security policies. This is only needed on the packaging / reference database.
GRANT EXEMPT ACCESS POLICY TO DATICAL_PACKAGER_ROLE;

DATICAL_TRACK_ROLE for Permissions on the Tracking Tables

In a multi-schema project you typically define a separate schema for the tracking tables. The DATICAL_TRACK_ROLE permissions are needed on that schema.

DATICAL_TRACK_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;

DATICAL_DATAPUMP_DIR Creation

Datical uses datapump as the default mechanism to backup and restore database schemas during Packaging. The DATICAL_DATAPUMP_DIR is only needed on the REF databases. Edit the script to set the path for the DATICAL_DATAPUMP_DIR to an appropriate location on the database server.

DATICAL_DATAPUMP_DIR

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

To use Data Pump in an Oracle RAC configuration, you must ensure that the directory object path (DATICAL_DATAPUMP_DIR by default) is on a cluster-wide file system. The directory object must point to shared physical storage that is visible to, and accessible from, all instances where Data Pump and/or external tables processes may run.

Granting Roles and Permissions

Grant the roles to the DATICAL_USER and DATICAL_TRACKING accounts.

REF Database Roles and Permissions

-- Permissions for REF databases.
-- Modify this to set the appropriate account name for your DATICAL_USER and DATICAL_TRACKING accounts.
GRANT DATICAL_ROLE TO DATICAL_USER;
GRANT DATICAL_PACKAGER_ROLE to DATICAL_USER;
-- Directory permissions cannot be granted by a ROLE because the directory is checked through the utl_file.file_exists function.
GRANT READ, WRITE, EXECUTE ON DIRECTORY DATICAL_DATAPUMP_DIR TO DATICAL_USER;
GRANT ALTER SESSION TO DATICAL_USER;

GRANT DATICAL_TRACK_ROLE TO DATICAL_TRACKING;

non-REF Database Roles and Permissions

-- Permissions for non-REF databases.
-- Modify this to set the appropriate account name for your DATICAL_USER and DATICAL_TRACKING accounts.
GRANT DATICAL_ROLE TO DATICAL_USER;
GRANT ALTER SESSION TO DATICAL_USER;
GRANT DATICAL_TRACK_ROLE TO DATICAL_TRACKING;

The roles granted to DATICAL_USER and DATICAL_TRACKING must be DEFAULT roles. Be sure to validate that the accounts have all the required DEFAULT roles.

Validate Default Roles

-- Verify permissions.
-- Modify this to set the appropriate account name for your DATICAL_USER and DATICAL_TRACKING accounts.

select grantee, granted_role, default_role from dba_role_privs where grantee like 'DATICAL%' order by 1;

-- If DEFAULT_ROLE is not YES.  Execute the following.
alter user DATICAL_USER default role DATICAL_ROLE;
alter user DATICAL_USER default role DATICAL_PACKAGER_ROLE;  /* REF database only */
alter user DATICAL_TRACKING 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 = 'DATICAL_USER';

Multi-Schema Projects - Oracle Roles and Permissions