----------------------
-- USER MANAGEMENT ---
----------------------
CREATE USER etl WITH PASSWORD '' ;
CREATE USER hevo WITH PASSWORD '' ;
CREATE USER dbt WITH PASSWORD '' ;
CREATE USER da WITH PASSWORD '' ;
CREATE USER nb WITH PASSWORD '' ;
CREATE USER looker WITH PASSWORD '' SYSLOG ACCESS UNRESTRICTED ;
CREATE USER ar WITH PASSWORD '' ;
CREATE USER ak WITH PASSWORD '' ;
-------------------------
--- SCHEMA MANAGEMENT ---
-------------------------
CREATE SCHEMA IF NOT EXISTS hevo AUTHORIZATION hevo ;
CREATE SCHEMA IF NOT EXISTS ext AUTHORIZATION etl ;
CREATE SCHEMA IF NOT EXISTS flatten AUTHORIZATION dbt ;
CREATE SCHEMA IF NOT EXISTS staging AUTHORIZATION dbt ;
CREATE SCHEMA IF NOT EXISTS intermediate AUTHORIZATION dbt ;
CREATE SCHEMA IF NOT EXISTS analytics AUTHORIZATION dbt ;
CREATE SCHEMA IF NOT EXISTS ad_hoc AUTHORIZATION dbt ;
CREATE SCHEMA IF NOT EXISTS meta AUTHORIZATION dbt ;
CREATE SCHEMA IF NOT EXISTS looker_scratch AUTHORIZATION looker ;
-----------------------
-- GROUP MANAGEMENT ---
-----------------------
CREATE GROUP etl
WITH USER etl, hevo ;
CREATE GROUP dbt
WITH USER dbt ;
CREATE GROUP analytics
WITH USER da, nb ;
CREATE GROUP bi
WITH USER looker ;
CREATE GROUP business_users
WITH USER ar, ak ;
----------------------------
-- PRIVILEGES MANAGEMENT ---
----------------------------
-- GROUP etl
GRANT USAGE ON SCHEMA hevo, staging, flatten, analytics, intermediate TO GROUP etl ;
-- GROUP dbt
GRANT USAGE ON SCHEMA hevo, snapshots TO GROUP dbt ;
GRANT USAGE, CREATE ON SCHEMA ext TO GROUP dbt ;
GRANT SELECT ON ALL tables IN SCHEMA hevo TO GROUP dbt ;
GRANT ALL PRIVILEGES ON ALL tables IN SCHEMA meta, ext, snapshots TO GROUP dbt ;
ALTER DEFAULT PRIVILEGES FOR USER hevo IN SCHEMA hevo
   GRANT SELECT ON tables TO GROUP dbt ;
ALTER DEFAULT PRIVILEGES FOR USER etl IN SCHEMA ext
   GRANT ALL PRIVILEGES ON tables TO GROUP dbt ;
-- GROUP analytics
GRANT USAGE ON SCHEMA hevo, ext, flatten, staging, intermediate, restricted, analytics, meta TO GROUP analytics ;
GRANT USAGE, CREATE ON SCHEMA ad_hoc TO GROUP analytics ;
GRANT SELECT ON ALL tables IN SCHEMA hevo, ext, flatten, staging, intermediate, restricted, analytics, meta TO GROUP analytics ;
GRANT ALL PRIVILEGES ON ALL tables IN SCHEMA ad_hoc TO GROUP analytics ;
ALTER DEFAULT PRIVILEGES FOR USER dbt IN SCHEMA flatten, staging, intermediate, restricted, analytics, meta
   GRANT SELECT ON tables TO GROUP analytics ;
ALTER DEFAULT PRIVILEGES FOR USER dbt IN SCHEMA ad_hoc
   GRANT ALL PRIVILEGES ON tables TO GROUP analytics ;
-- GROUP bi
GRANT USAGE ON SCHEMA analytics, meta, ad_hoc, snapshots TO GROUP bi ;
GRANT SELECT ON ALL tables IN SCHEMA analytics, meta, ad_hoc TO GROUP bi ;
ALTER DEFAULT PRIVILEGES FOR USER dbt IN SCHEMA analytics, meta, ad_hoc
   GRANT SELECT ON tables TO GROUP bi ;
-- GROUP business users
GRANT USAGE ON SCHEMA analytics TO GROUP business_users ;
GRANT USAGE, CREATE ON SCHEMA ad_hoc TO GROUP business_users ;
GRANT SELECT ON ALL tables IN SCHEMA analytics TO GROUP business_users ;
GRANT ALL PRIVILEGES ON ALL tables IN SCHEMA ad_hoc TO GROUP business_users ;
ALTER DEFAULT PRIVILEGES FOR USER dbt IN SCHEMA analytics
   GRANT SELECT ON tables TO GROUP business_users ;