Snowflake Grant Select on single schema

Question

db: my_db

schemas: my_schema_1, my_schema_2

tables:

my_schema_1: table_a, table_b
my_schema_2: table_c, table_d

I would like to grant select to all tables in my_schema_2.

use role securityadmin;
grant usage 
  on database my_db 
  to role dw_ro_role;

grant usage on schema my_db.my_schema_2
  to role dw_ro_role;

grant select
  on all tables in schema my_db.my_schema_2
  to role dw_ro_role;

However, this grants access to ALL schemas in the database. (I think through granting usage) In fact, if I:

grant usage on my_db...
revoke select on all tables in my_schema_2

I can still query everything.

If I revoke usage on database my_db, I cannot query anything.

The owner of my_db is sysadmin. The owner of the schemas is etl_tool_role.

How can I allow access to select from tables in a only chosen schema?

Hans Henrik Eriksen Hans Henrik Eriksen · Accepted Answer · 2019-10-30T09:54:58

Your grants look just fine, so I'm wondering how you are accessing/testing this.
Or maybe the role dw_ro_role has more grants in effect than you've shown above.

If you log onto the DB with a user that has just the role dw_ro_role and the same as the default role, you should only get access to schema my_db.my_schema_2.

Snowflake Grant Select on single schema

1 Answers