CODEWITHSUNDEEP
snowflake-cloud-data-platformsnowflake-schema
knozawa
knozawa

Reputation: 293

Snowflake - Would it be possible to drop, modify, replace the same table from two different roles?

There is a table created by a Sysadmin. I would like both Sysadmin and Analyst role to be able to drop, modify, or replace the same table if possible. I understand we can change ownership of the table, but if both roles can drop, modify, or replace the table, that would be great.

This ANALYST role has limited access to snowflake databases and has following grant privileges:

Upvotes: 1

Views: 557

Answers (2)

Marco Roy
Marco Roy

Reputation: 5273

We faced the exact same issue with many different functional roles wanting to manage/modify the same objects, but running into ownership issues (ex: Team A & Team B both wanting to modify/collaborate on the same objects in a shared database).

Trying to organize the functional roles into a role hierarchy does not work, since only one role can be at the top of the hierarchy (SYSADMIN in the case of the OP). So it only fixes a small part of the problem (it works for the role at the top, but not those at the bottom, which still do no have access to modify objects created by the top role).

A different approach is needed:

  • We need to give ownership of the objects to a structural role (ex: shared_database_admin).
  • Every object in the corresponding database/schema/etc. must be owned by that structural role.
  • That role can then be granted to functional roles (teams, departments, functions, etc.)
  • And most importantly: when creating objects, the functional roles must downgrade to the structural role in order to give it the right owner (or GRANT OWNERSHIP immediately afterwards).

Upvotes: 0

Marcel
Marcel

Reputation: 2612

Regarding Ownership: You are right, only one role can be the owner of an object at one particular point in time.

However, you can have several roles, which are able to DROP, MODIFY and REPLACE the same table. This can be achieved by either

  • Assigning the same privileges to the roles with two separate GRANT statements
  • Assigning Role A to Role B so that Role B is inheriting all the privileges from Role A

Upvotes: 2

Related Questions