06 Mar 20 · nekkanti009 · #general ·   Bookmark   ×

A role is automatically getting DML privileges for tables under a schema even though the current and future privileges are revoked

There is a role (XYZ)  whose select/insert/delete/update/truncate privileges were revoked for both current and future. But as soon as a new table is created in that schema this role xyz is getting the privileges on them and it shows the access is granted by the schema_owner_role. And the interesting part is the grant privileges is not run by the owner role. Not able to figure out what other privileges in snowflake can automatically grant access as soon as the object is created. Please advise if anyone has inputs.

nekkanti009

posted on 06 Mar 20

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds




npackMar 09 11:57

Were you able to find out what’s causing this ?

nekkanti009Mar 16 07:13

Yes, i was able to figure the root cause for this. I was revoking the privileges at database level but the dml privileges were granted to a role at schema level a while back and looks like schema level privileges take precedence compared to database level privileges and hence the objects were getting dml privileges when a new table/view is created. Once i revoked those current & future dml grants at schema level, the issue was taken care of.

npackMar 16 10:06

Interesting. Thank you for posting the answer. It will be helpful to the community

nekkanti009Mar 17 12:00

My pleasure, learned it hard that way but am glad to be able to figure out.


Community Software by Hittly