Snowflake

-- change role to ACCOUNTADMIN
USE ROLE ACCOUNTADMIN;
 
-- create role for datapostie user
CREATE ROLE IF NOT EXISTS datapostie_role;
GRANT ROLE datapostie_role TO ROLE SYSADMIN;
    -- Note that we are not making the datapostie_role a SYSADMIN,
    -- but rather granting users with the SYSADMIN role to modify the datapostie_role
 
-- create a user for DataPostie
CREATE USER IF NOT EXISTS datapostie_user
password = '<enter password here>';
GRANT ROLE datapostie_role TO USER datapostie_user;
ALTER USER datapostie_user
SET default_role = datapostie_role
default_warehouse = '<your-warehouse>';
 
-- change role
USE ROLE SYSADMIN;
 
-- grant warehouse privileges to datapostie_role 
GRANT ALL PRIVILEGES ON WAREHOUSE <your-warehouse> TO ROLE datapostie_role;
 
-- grant read only database access (repeat for all database/schemas)
GRANT USAGE ON DATABASE <database> TO ROLE datapostie_role;
GRANT USAGE ON SCHEMA <database>.<schema> TO ROLE datapostie_role;
 
-- rerun the following any time a table is added to the schema
GRANT SELECT ON ALL TABLES IN SCHEMA <database>.<schema> TO ROLE datapostie_role;
GRANT SELECT ON FUTURE TABLES IN SCHEMA <database>.<schema> TO ROLE datapostie_role;
 
-- create a schema for DataPostie to write back to
USE DATABASE <database>;
CREATE SCHEMA IF NOT EXISTS datapostie_temp;
USE ROLE ACCOUNTADMIN;
GRANT OWNERSHIP ON SCHEMA datapostie_temp TO ROLE SYSADMIN REVOKE CURRENT GRANTS;
GRANT ALL ON SCHEMA datapostie_temp TO ROLE datapostie_role;