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 · Accepted Answer · 2019-10-30 09:54:58Z

3

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.

answered Oct 30, 2019 at 9:54

Hans Henrik Eriksen

2,9008 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mike Walton Over a year ago

I agree with the answer above. These look good and either you are testing with a different role or that role has more grants in affect already. The only additional comment is you might want to consider adding a grant to future tables in the schema, as well, so you don't need to keep running your grants when new tables are created.

demircioglu Over a year ago

Like Mike said check your PUBLIC role or any other role that already has grants.

Collectives™ on Stack Overflow

Snowflake Grant Select on single schema

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related