Snowflake - how do i grant read only to ALL Databases for a role?

Question

Is there way to grant a role view/read-only permission to ALL Databases? Both present and future databases? I read the GRANT FUTURE Command doesnt work for databases, but will be added down the road?

Answer 1

You can do this. Just not all in one command. Fortunately, we can generate the commands we need with the Information schema.

I would think carefully if you really want to grant access to all future tables that could ever be added to a database. Depending on the type of data in the database and who has access to create tables, this could become a security concern.

That being said, the script below might help

-- set your role with access to make these changes
use role accountadmin;

-- set context to any database you have create table privlages in, this is only for the temp table.
use schema sandbox_db.public;

-- set the role you want to give access to
set v_role = 'TEST ROLE';

-- list out shares. db's made from share use different permissions
show shares;

-- add databases made from shares into a temp table
create or replace temp table inbound_shares_temp as (
SELECT "database_name" as db_name FROM TABLE(RESULT_SCAN (LAST_QUERY_ID()))
WHERE "kind" = 'INBOUND'
);

-- generate your select on all current and future tables commands
-- copy and paste the results and run
select 'grant usage on database ' || DATABASE_NAME || ' to role $v_role;' as "--commands" from information_schema.databases
where database_name not in (select db_name from inbound_shares_temp)
union all
select 'grant usage on all schemas in database ' || DATABASE_NAME || ' to role $v_role;' from information_schema.databases
where database_name not in (select db_name from inbound_shares_temp)
union all
select 'grant select on all tables in database ' || DATABASE_NAME || ' to role $v_role;' from information_schema.databases
where database_name not in (select db_name from inbound_shares_temp)
union all
select 'grant usage on future schemas in database ' || DATABASE_NAME || ' to role $v_role;' from information_schema.databases
where database_name not in (select db_name from inbound_shares_temp)
union all
select 'grant select on future tables in database ' || DATABASE_NAME || ' to role $v_role;' from information_schema.databases
where database_name not in (select db_name from inbound_shares_temp);