Snowflake revoke table/schema access

Question

I'm trying to give an entire database access to a user, but remove access for a specific table or a specific schema that has confidential data, but it's just not working out, tried multiple - grant and revoke statements but in vain.

This is what I've tested so far.

Initially, I had this role for the user

GRANT ALL ON DATABASE raw TO ROLE transformer; 

checked the grants and removed that

SHOW GRANTS TO ROLE transformer;
revoke select on all tables in schema raw.<secret_schema> from role transformer;
revoke all on DATABASE raw from ROLE transformer; 

Started giving access to individual schemas/tables, but the "grant usage on database" just gives every schema/table access to the user

grant usage on database raw to role transformer ;  -- usage gives all tables access 
grant usage on schema raw.<open_schema> to role transformer ;
grant all on schema raw.<open_schema> to role transformer ;
grant select on all tables raw.<open_schema> to role transformer ;

Lastly, tried these revoke too, but in vain

revoke select on table raw.<secret_schema>.s from ROLE transformer;
revoke usage on schema raw.<secret_schema> from role transformer;

For more information, this access is for a DBT user and an analyst user, who can hit/select/read the raw database , but just 1 schema/table should not be accessible, rest all should be with a "future tables" clause.

Design deep-dive: https://blog.getdbt.com/how-we-configure-snowflake/

Answer 1

grant usage on database raw to role transformer ; -- usage gives all tables access

This is not what's granting access to the tables; something else is. You can confirm that running a simple script like this one:

use role securityadmin;
create role new_role_1;
grant role new_role_1 to user my_user;
use role sysadmin;
grant usage on database test to role new_role_1;
use role new_role_1;
select * from test.public.foo;  --SQL compilation error: Object 'TEST.PUBLIC.FOO' does not exist or not authorized.
use role sysadmin;
select * from test.public.foo;  -- Works

Roles inherit from other roles. All roles inherit from the PUBLIC role by default. Could someone have granted the PUBLIC roles the privileges that this role is inheriting? Does it inherit from a role that has more permissions than PUBLIC?

Answer 2

As Greg, already mentioned (and demonstrated), "GRANT ALL ON DATABASE raw TO ROLE x" does not grant permission to access the objects in the database. It grants permission to modify the database object (in your case, it's not needed and I would suggest you not grant it according to the "Principle of least privilege").

https://docs.snowflake.com/en/user-guide/security-access-control-privileges.html#database-privileges

I think, the confusing thing is, "revoke from" command does not return any error if you try to revoke permission that was not granted:

create role r2;
revoke all on database gokhan_db from role r2;

So your revoke commands do not fail, but in fact, they do not revoke anything, as this permission were assigned to the role public:

revoke select on table raw.<secret_schema>.s from ROLE transformer;
revoke usage on schema raw.<secret_schema> from role transformer;

Could you check the permissions of the role public, again?

show grants to role public;