30 Oct 19 · nekkanti009 · #Snowflake ·   Bookmark   ×

Granting Access to a Role on newly created schema's automatically without manual intervention

I have a role created in snowflake and would like to grant all privilege on existing schema's in a database. Actual requirement for me is, when a new schema is added in that particular database at a later time i would like to grant same access without manually running "grant all on schema.." command in a database. Any suggestions on how this can be achieved?  I know we can grant future grants on schema objects, but i am looking for future grants on the new schema itself.Please advise.


posted on 30 Oct 19

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

npackOct 31 12:17

Forget Snowflake, this cannot be achieved in any database. And its not a secure practice.

If you still want to do it. 

1. You will need to manually check every 5 mins (or whatever interval is comfortable for you) for all the schemas that are newly created

2.And apply the grants to them

You can write a simple unix script utilizing snowsql commands to achieve this. You can then schedule this unix script to run using a cron scheduler or any other scheduler that your company uses

nekkanti009Oct 31 12:00

Thank you for the details, this helps. Really appreciate it.

Community Software by Hittly