Create Read Only Snowflake Group and User

worksheet.sql

# CREATE A READ ONLY GROUP
create role read_only_svc_rl comment = 'This role is limited to querying tables';
grant role read_only_svc_rl to role sysadmin;

# GRANT USAGE ON DATABASE
grant usage on database databots_prod to role read_only_svc_rl;

# GRANT USAGE ON CURRENT SCHEMAS AND FUTURE SCHEMAS
grant usage on all schemas in database databots_prod to role read_only_svc_rl;
grant usage on future schemas in database databots_prod to role read_only_svc_rl;

# GRANT USAGE ON CURRENT TABLES/VIEWS AND FUTURE TABLES/VIEWS
grant select on all tables in database databots_prod to role read_only_svc_rl;
grant select on future tables in database databots_prod to role read_only_svc_rl;
grant select on all views in database databots_prod to role read_only_svc_rl;
grant select on future views in database databots_prod to role read_only_svc_rl;

# GRANT USAGE ON WAREHOUSE
grant usage on warehouse read_data_wh to role read_only_svc_rl;

# CREATE A READ ONLY USER AND ASSIGN THE GROUP
create user read_only_svc_user password='XXXXXXXXXX' default_role = read_only_svc_rl must_change_password = false;
GRANT ROLE read_only_svc_rl TO USER read_only_svc_user