49 lines
1.8 KiB
SQL
49 lines
1.8 KiB
SQL
|
|
--Sandbox Database
|
|
|
|
USE ROLE DATAADMIN;
|
|
CREATE DATABASE IF NOT EXISTS DATALAKE_SANDBOX COMMENT = 'Sandbox environment for datalake';
|
|
GRANT USAGE ON DATABASE DATALAKE_SANDBOX TO ROLE DATAANALYST;
|
|
GRANT CREATE SCHEMA ON DATABASE DATALAKE_SANDBOX TO ROLE DATAANALYST;
|
|
|
|
USE ROLE DATAADMIN;
|
|
USE DATABASE DATALAKE_SANDBOX;
|
|
CREATE SCHEMA IF NOT EXISTS DATA WITH MANAGED ACCESS;
|
|
ALTER SCHEMA IF EXISTS DATA ENABLE MANAGED ACCESS;
|
|
GRANT USAGE ON SCHEMA DATA TO ROLE DATAANALYST;
|
|
GRANT SELECT ON ALL TABLES IN SCHEMA DATA TO ROLE DATAANALYST;
|
|
GRANT SELECT ON FUTURE TABLES IN SCHEMA DATA TO ROLE DATAANALYST;
|
|
|
|
|
|
--Staging Database
|
|
|
|
USE ROLE DATAADMIN;
|
|
CREATE DATABASE IF NOT EXISTS DATALAKE_STAGING COMMENT = 'Staging environment for datalake';
|
|
GRANT USAGE ON DATABASE DATALAKE_STAGING TO ROLE DATAANALYST;
|
|
GRANT CREATE SCHEMA ON DATABASE DATALAKE_STAGING TO ROLE DATAANALYST;
|
|
|
|
USE ROLE DATAADMIN;
|
|
USE DATABASE DATALAKE_STAGING;
|
|
CREATE SCHEMA IF NOT EXISTS DATA WITH MANAGED ACCESS;
|
|
ALTER SCHEMA IF EXISTS DATA ENABLE MANAGED ACCESS;
|
|
GRANT USAGE ON SCHEMA DATA TO ROLE DATAANALYST;
|
|
GRANT SELECT ON ALL TABLES IN SCHEMA DATA TO ROLE DATAANALYST;
|
|
GRANT SELECT ON FUTURE TABLES IN SCHEMA DATA TO ROLE DATAANALYST;
|
|
|
|
|
|
--Production Database
|
|
|
|
USE ROLE DATAADMIN;
|
|
CREATE DATABASE IF NOT EXISTS DATALAKE_PROD COMMENT = 'Production environment for datalake';
|
|
GRANT USAGE ON DATABASE DATALAKE_PROD TO ROLE DATAANALYST;
|
|
GRANT CREATE SCHEMA ON DATABASE DATALAKE_PROD TO ROLE DATAANALYST;
|
|
|
|
USE ROLE DATAADMIN;
|
|
USE DATABASE DATALAKE_PROD;
|
|
CREATE SCHEMA IF NOT EXISTS DATA WITH MANAGED ACCESS;
|
|
ALTER SCHEMA IF EXISTS DATA ENABLE MANAGED ACCESS;
|
|
GRANT USAGE ON SCHEMA DATA TO ROLE DATAANALYST;
|
|
GRANT SELECT ON ALL TABLES IN SCHEMA DATA TO ROLE DATAANALYST;
|
|
GRANT SELECT ON FUTURE TABLES IN SCHEMA DATA TO ROLE DATAANALYST;
|
|
|