snowflake db구성에 관한 쿼리

 

-- CREATE
-- WAREHOUSE
CREATE OR REPLACE WAREHOUSE  A_WH
WAREHOUSE_SIZE=XSMALL
MAX_CLUSTER_COUNT=1
MIN_CLUSTER_COUNT=1
SCALING_POLICY=ECONOMY
AUTO_SUSPEND=60
AUTO_RESUME = TRUE;

-- DATABASE
CREATE OR REPLACE TRANSIENT DATABASE A_WDB;

-- SCHEMA
CREATE OR REPLACE TRANSIENT SCHEMA ODS;
CREATE OR REPLACE TRANSIENT SCHEMA DW;
CREATE OR REPLACE TRANSIENT SCHEMA DM;

-- ROLE
CREATE OR REPLACE ROLE READ_ROLE COMMENT = 'A ROLE FOR SELECT ONLY';
CREATE OR REPLACE ROLE CRUD_ROLE COMMENT = 'A ROLE FOR CREATE/SELECT/UPDATE/DELETE';
CREATE OR REPLACE ROLE OBJECT_ROLE COMMENT = 'A ROLE FOR DBA';

-- USER
CREATE OR REPLACE USER A_DBA
  PASSWORD = '***'
  LOGIN_NAME = A_DBA
  DISPLAY_NAME = A_DBA
  MUST_CHANGE_PASSWORD = FALSE
  DEFAULT_WAREHOUSE = A_WDB
  DEFAULT_ROLE = OBJECT_ROLE
  COMMENT = 'DBA';


CREATE OR REPLACE USER A_ETL
  PASSWORD = '***'
  LOGIN_NAME = A_ETL
  DISPLAY_NAME = A_ETL
  MUST_CHANGE_PASSWORD = FALSE
  DEFAULT_WAREHOUSE = A_WDB
  DEFAULT_ROLE = CRUD_ROLE
  COMMENT = 'CREATE/SELECT/UPDATE/DELETE';


CREATE OR REPLACE USER A_BI
  PASSWORD = '***'
  LOGIN_NAME = A_BI
  DISPLAY_NAME = A_BI
  MUST_CHANGE_PASSWORD = FALSE
  DEFAULT_WAREHOUSE = A_WDB
  DEFAULT_ROLE = READ_ROLE
  COMMENT = 'SELECT';

CREATE OR REPLACE USER A_WEB
  PASSWORD = '***'
  LOGIN_NAME = A_WEB
  DISPLAY_NAME = A_WEB
  MUST_CHANGE_PASSWORD = FALSE
  DEFAULT_WAREHOUSE = A_WDB
  DEFAULT_ROLE = READ_ROLE
  COMMENT = 'SELECT';


-- GRANT
GRANT USAGE ON WAREHOUSE A_WH TO ROLE READ_ROLE;
GRANT USAGE ON WAREHOUSE A_WH TO ROLE CRUD_ROLE;
GRANT USAGE ON WAREHOUSE A_WH TO ROLE OBJECT_ROLE;

GRANT USAGE  ON DATABASE A_WDB TO ROLE READ_ROLE;
GRANT USAGE  ON DATABASE A_WDB TO ROLE CRUD_ROLE;
GRANT USAGE  ON DATABASE A_WDB TO ROLE OBJECT_ROLE;

GRANT USAGE  ON SCHEMA A_WDB.ODS TO ROLE CRUD_ROLE;
GRANT USAGE  ON SCHEMA A_WDB.ODS TO ROLE OBJECT_ROLE;

GRANT USAGE  ON SCHEMA A_WDB.DW TO ROLE CRUD_ROLE;
GRANT USAGE  ON SCHEMA A_WDB.DW TO ROLE READ_ROLE;
GRANT USAGE  ON SCHEMA A_WDB.DW TO ROLE OBJECT_ROLE;

GRANT USAGE  ON SCHEMA A_WDB.DM TO ROLE CRUD_ROLE;
GRANT USAGE  ON SCHEMA A_WDB.DM TO ROLE READ_ROLE;
GRANT USAGE  ON SCHEMA A_WDB.DM TO ROLE OBJECT_ROLE;

GRANT OWNERSHIP ON ALL TABLES IN SCHEMA A_WDB.ODS TO ROLE OBJECT_ROLE;
GRANT OWNERSHIP ON ALL TABLES IN SCHEMA A_WDB.DW TO ROLE OBJECT_ROLE;
GRANT OWNERSHIP ON ALL TABLES IN SCHEMA A_WDB.DM TO ROLE OBJECT_ROLE;

GRANT SELECT, INSERT, UPDATE, TRUNCATE, DELETE, REFERENCES ON ALL TABLES IN SCHEMA A_WDB.ODS TO ROLE CRUD_ROLE;
GRANT SELECT, INSERT, UPDATE, TRUNCATE, DELETE, REFERENCES ON ALL TABLES IN SCHEMA A_WDB.DW TO ROLE CRUD_ROLE;
GRANT SELECT, INSERT, UPDATE, TRUNCATE, DELETE, REFERENCES ON ALL TABLES IN SCHEMA A_WDB.DM TO ROLE CRUD_ROLE;

GRANT SELECT ON ALL TABLES IN SCHEMA A_WDB.ODS TO ROLE READ_ROLE;
GRANT SELECT ON ALL TABLES IN SCHEMA A_WDB.DW TO ROLE READ_ROLE;
GRANT SELECT ON ALL TABLES IN SCHEMA A_WDB.DM TO ROLE READ_ROLE;


GRANT ROLE READ_ROLE TO USER A_WEB;
GRANT ROLE READ_ROLE TO USER A_BI;
GRANT ROLE CRUD_ROLE TO USER A_ETL;
GRANT ROLE OBJECT_ROLE TO USER A_DBA;