-- This script is provided as a sample setup to use database roles, warehouse, admin role, deploy role as an example.
-- YOu may choose to have your own RBAC and SCHEMACHANGE database setup depending on your organization objectives.
-- Set these to personalize your deployment
SET ADMIN_USER = 'CHANGEME';
SET TARGET_DB_NAME = 'SCHEMACHANGE_DEMO'; -- Name of database that will have the SCHEMACHANGE Schema for change tracking.

-- Dependent Variables; Change the naming pattern if you want but not necessary
SET ADMIN_ROLE = '"' || $TARGET_DB_NAME || '-ADMIN"'; -- This role will own the database and schemas.
-- The deploy role is name with hyphen is used to allow us to test the use of hyphenated identifiers.
SET SERVICE_USER = $TARGET_DB_NAME || '_SVC_USER'; -- This user will be granted the Deploy role.
SET WAREHOUSE_NAME = $TARGET_DB_NAME || '_WH';
SET AC_U = '_AC_U_' || $WAREHOUSE_NAME;
SET AC_O = '_AC_O_' || $WAREHOUSE_NAME;

USE ROLE USERADMIN;
-- Service user used to run SCHEMACHANGE deployments
-- Set up the service user that meets your organization's security requirements.
CREATE USER IF NOT EXISTS IDENTIFIER($SERVICE_USER) WITH TYPE='SERVICE';
-- Role granted to a human user to manage the database permissions and database roles.

CREATE ROLE IF NOT EXISTS IDENTIFIER($ADMIN_ROLE);
CREATE ROLE IF NOT EXISTS IDENTIFIER($AC_U);
CREATE ROLE IF NOT EXISTS IDENTIFIER($AC_O);
GRANT ROLE IDENTIFIER($AC_U) TO ROLE IDENTIFIER($AC_O);


-- Role hierarchy tied to SYSADMIN;
USE ROLE SECURITYADMIN;
GRANT ROLE IDENTIFIER($ADMIN_ROLE) TO ROLE SYSADMIN;

GRANT ROLE IDENTIFIER($ADMIN_ROLE) TO USER IDENTIFIER($SERVICE_USER);
GRANT ROLE IDENTIFIER($ADMIN_ROLE) TO USER IDENTIFIER($ADMIN_USER);

USE ROLE SYSADMIN;
CREATE TRANSIENT DATABASE IF NOT EXISTS IDENTIFIER($TARGET_DB_NAME);

USE ROLE SECURITYADMIN;
GRANT OWNERSHIP ON DATABASE IDENTIFIER($TARGET_DB_NAME) TO ROLE IDENTIFIER($ADMIN_ROLE) WITH GRANT OPTION;

USE ROLE SYSADMIN;
CREATE WAREHOUSE IF NOT EXISTS IDENTIFIER($WAREHOUSE_NAME)
WITH WAREHOUSE_SIZE='XSMALL'
WAREHOUSE_TYPE='STANDARD'
AUTO_SUSPEND=60
AUTO_RESUME=TRUE
MIN_CLUSTER_COUNT=1
MAX_CLUSTER_COUNT=1
INITIALLY_SUSPENDED=TRUE;
USE ROLE SECURITYADMIN;
GRANT OWNERSHIP ON WAREHOUSE IDENTIFIER($WAREHOUSE_NAME) TO ROLE IDENTIFIER($ADMIN_ROLE) WITH GRANT OPTION;
GRANT USAGE ON WAREHOUSE IDENTIFIER($WAREHOUSE_NAME) TO ROLE IDENTIFIER($AC_U);
GRANT OPERATE ON WAREHOUSE IDENTIFIER($WAREHOUSE_NAME) TO ROLE IDENTIFIER($AC_O);


SET TARGET_SCHEMA_NAME = 'SCHEMACHANGE';
SET TARGET_DB_NAME = 'SCHEMACHANGE_DEMO'; -- Name of database that will have the SCHEMACHANGE Schema for change tracking.
-- Dependent Variables; Change the naming pattern if you want but not necessary
SET ADMIN_ROLE = '"' || $TARGET_DB_NAME || '-ADMIN"'; -- This role will own the database and schemas.
SET WAREHOUSE_NAME = $TARGET_DB_NAME || '_WH';
SET SCHEMACHANGE_NAMESPACE = $TARGET_DB_NAME || '.' || $TARGET_SCHEMA_NAME;
SET SC_M = 'SC_M_' || $TARGET_SCHEMA_NAME;
SET SC_R = 'SC_R_' || $TARGET_SCHEMA_NAME;
SET SC_W = 'SC_W_' || $TARGET_SCHEMA_NAME;
SET SC_C = 'SC_C_' || $TARGET_SCHEMA_NAME;

USE ROLE IDENTIFIER($ADMIN_ROLE);
USE DATABASE IDENTIFIER($TARGET_DB_NAME);
USE WAREHOUSE IDENTIFIER($WAREHOUSE_NAME);
USE SCHEMA IDENTIFIER($TARGET_SCHEMA_NAME);

SET AUTH_POLICY_NAME = $SERVICE_USER || '_AUTHP';
SET ROLE_RESTRICTION = $TARGET_DB_NAME || '-ADMIN';
SET TOKEN_NAME = $TARGET_DB_NAME || '_TOKEN_365D';

CREATE AUTHENTICATION POLICY IF NOT EXISTS IDENTIFIER($AUTH_POLICY_NAME)
AUTHENTICATION_METHODS = ('PROGRAMMATIC_ACCESS_TOKEN')
PAT_POLICY = (NETWORK_POLICY_EVALUATION = ENFORCED_NOT_REQUIRED);

USE ROLE SECURITYADMIN;
ALTER USER IDENTIFIER($SERVICE_USER) SET AUTHENTICATION POLICY IDENTIFIER($AUTH_POLICY_NAME);
ALTER USER IDENTIFIER($SERVICE_USER) ADD PROGRAMMATIC ACCESS TOKEN IDENTIFIER($TOKEN_NAME)
ROLE_RESTRICTION = $ROLE_RESTRICTION
DAYS_TO_EXPIRY = 365;

USE ROLE IDENTIFIER($ADMIN_ROLE);
USE DATABASE IDENTIFIER($TARGET_DB_NAME);
USE WAREHOUSE IDENTIFIER($WAREHOUSE_NAME);
USE SCHEMA IDENTIFIER($TARGET_SCHEMA_NAME);

CREATE DATABASE ROLE IF NOT EXISTS DB_M;
CREATE DATABASE ROLE IF NOT EXISTS DB_R;
CREATE DATABASE ROLE IF NOT EXISTS DB_W;
CREATE DATABASE ROLE IF NOT EXISTS DB_C;

GRANT CREATE SCHEMA ON DATABASE IDENTIFIER($TARGET_DB_NAME) TO DATABASE ROLE DB_C;
CREATE DATABASE ROLE IF NOT EXISTS IDENTIFIER($SC_M);
CREATE DATABASE ROLE IF NOT EXISTS IDENTIFIER($SC_R);
CREATE DATABASE ROLE IF NOT EXISTS IDENTIFIER($SC_W);
CREATE DATABASE ROLE IF NOT EXISTS IDENTIFIER($SC_C);

GRANT DATABASE ROLE IDENTIFIER($SC_M) TO DATABASE ROLE DB_M;
GRANT DATABASE ROLE IDENTIFIER($SC_R) TO DATABASE ROLE DB_R;
GRANT DATABASE ROLE IDENTIFIER($SC_W) TO DATABASE ROLE DB_W;
GRANT DATABASE ROLE IDENTIFIER($SC_C) TO DATABASE ROLE DB_C;
GRANT DATABASE ROLE IDENTIFIER($SC_M) TO DATABASE ROLE IDENTIFIER($SC_R);
GRANT DATABASE ROLE IDENTIFIER($SC_R) TO DATABASE ROLE IDENTIFIER($SC_W);
GRANT DATABASE ROLE IDENTIFIER($SC_W) TO DATABASE ROLE IDENTIFIER($SC_C);

CREATE TRANSIENT SCHEMA IF NOT EXISTS IDENTIFIER($TARGET_SCHEMA_NAME) WITH MANAGED ACCESS;
-- USE SCHEMA INFORMATION_SCHEMA;
-- DROP SCHEMA IF EXISTS PUBLIC;
USE SCHEMA IDENTIFIER($SCHEMACHANGE_NAMESPACE);
-- SCHEMA
-- SC_M
GRANT USAGE ON DATABASE IDENTIFIER($TARGET_DB_NAME) TO DATABASE ROLE IDENTIFIER($SC_M);
GRANT USAGE ON SCHEMA IDENTIFIER($SCHEMACHANGE_NAMESPACE) TO DATABASE ROLE IDENTIFIER($SC_M);
-- SC_R
GRANT MONITOR ON DATABASE IDENTIFIER($TARGET_DB_NAME) TO DATABASE ROLE IDENTIFIER($SC_R);
GRANT MONITOR ON SCHEMA IDENTIFIER($SCHEMACHANGE_NAMESPACE) TO DATABASE ROLE IDENTIFIER($SC_R);
-- SC_W
-- None
-- SC_C

-- TABLES
-- SC_M
GRANT REFERENCES ON ALL TABLES IN SCHEMA IDENTIFIER($SCHEMACHANGE_NAMESPACE) TO DATABASE ROLE IDENTIFIER($SC_M);
GRANT REFERENCES ON FUTURE TABLES IN SCHEMA IDENTIFIER($SCHEMACHANGE_NAMESPACE) TO DATABASE ROLE IDENTIFIER($SC_M);
-- SC_R
GRANT SELECT ON ALL TABLES IN SCHEMA IDENTIFIER($SCHEMACHANGE_NAMESPACE) TO DATABASE ROLE IDENTIFIER($SC_R);
GRANT SELECT ON FUTURE TABLES IN SCHEMA IDENTIFIER($SCHEMACHANGE_NAMESPACE) TO DATABASE ROLE IDENTIFIER($SC_R);
-- SC_W
GRANT INSERT, UPDATE, DELETE, TRUNCATE, EVOLVE SCHEMA ON ALL TABLES IN SCHEMA IDENTIFIER($SCHEMACHANGE_NAMESPACE) TO DATABASE ROLE IDENTIFIER($SC_W);
GRANT INSERT, UPDATE, DELETE, TRUNCATE, EVOLVE SCHEMA ON FUTURE TABLES IN SCHEMA IDENTIFIER($SCHEMACHANGE_NAMESPACE) TO DATABASE ROLE IDENTIFIER($SC_W);
-- SC_C
GRANT CREATE TABLE ON SCHEMA IDENTIFIER($SCHEMACHANGE_NAMESPACE) TO DATABASE ROLE IDENTIFIER($SC_C);