begin;

-- create variables for user / password / role / warehouse / database (needs to be uppercase for objects)
set role_name = 'NARRATOR_ROLE';
set user_name = 'narrator_user';
set user_password = 'pleasereplaceme';
set warehouse_name = 'COMPUTE_WH';
set database_name = 'DOH';

-- change role to securityadmin for user / role steps
use role securityadmin;

-- create role for narrator
create role if not exists identifier($role_name);
grant role identifier($role_name) to role SYSADMIN;

-- create a user for narrator
create user if not exists identifier($user_name)
password = $user_password
default_role = $role_name
default_warehouse = $warehouse_name;

grant role identifier($role_name) to user identifier($user_name);

-- change role to sysadmin for warehouse / database steps
use role sysadmin;


-- grant narrator role access to warehouse
grant USAGE
on warehouse identifier($warehouse_name)
to role identifier($role_name);

-- grant narrator access to database
grant CREATE SCHEMA, MONITOR, USAGE
on database identifier($database_name)
to role identifier($role_name);

-- grant narrator read access to database
grant usage on all schemas in database identifier($database_name)  to role identifier($role_name);
grant select on all tables in database identifier($database_name) to role identifier($role_name);

-- grant narrator read access to all future tables database
grant usage on database identifier($database_name) to role identifier($role_name);


-- change role to securityadmin for future schemas role assignment
use role securityadmin;

grant usage on future schemas in database identifier($database_name)  to role identifier($role_name);
grant select on future tables in database identifier($database_name)  to role identifier($role_name);

commit;

----
-- SQL BELOW TO GRANT ACCESS TO ONLY SPECIFIC SCHEMAS
----

set schema_name = 'MY_DB.MY_SCHEMA';

grant usage on schema identifier($schema_name) to role identifier($role_name);
grant select on all tables in schema identifier($schema_name) to role identifier($role_name);


-- change role to securityadmin for future schemas role assignment
use role securityadmin;

grant usage on future tables in schema identifier($schema_name) to role identifier($role_name);
grant select on future tables in schema identifier($schema_name) to role identifier($role_name);