CODEWITHSUNDEEP
permissionssnowflake-cloud-data-platformsql-grant
snark17
snark17

Reputation: 399

FUTURE GRANTs on tables without MANAGE GRANTs access

In Snowflake, is it possible for a user to make FUTURE GRANTs on tables/schemas if their role doesn't have the MANAGE GRANTs role? From here, it seems like no, but I want to make sure I'm not missing anything.

For context: my team has several pipelines that do full replaces of tables (i.e., drops the old table and swaps the new table in). I'm in a larger organization and our head DBA is hesitant to give my team's role the MANAGE GRANT permission.

Upvotes: 0

Views: 1087

Answers (2)

Chris
Chris

Reputation: 690

Maybe I'm miss understanding, but it sounds like you need the same grants on future tables as those you currently have on existing tables, which is exactly how issuing future grants works.

For example if you need ALL PRIVILEGES (except ownership) on all tables and future tables in a schema, you should have your DBA execute the following:

For existing tables:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA DB1.SCHEMA1 TO MY_ROLE;

For future tables:

GRANT ALL PRIVILEGES ON FUTURE TABLES IN SCHEMA DB1.SCHEMA1 TO MY_ROLE;

As tables are created or dropped and re-created, you and your team will always maintain those same privileges. This will eliminate any repetitive work for your DBA and will assure that you always have access to your tables the moment they are created.

Upvotes: 1

David Garrison
David Garrison

Reputation: 2880

Giving FUTURE GRANTs requires running a GRANT statement, which requires MANAGE GRANTs.

From that documentation page:

The MANAGE GRANTS global privilege is required to grant or revoke privileges on future objects at the database level. By default, only the SECURITYADMIN and ACCOUNTADMIN roles have the MANAGE GRANTS privilege.

Upvotes: 0

Related Questions