Snowflake user/role management

Question

I have different users with role "sysadmin". When user "AAA" which is a sysadmin, is creating a table, everything is working but then when user "BBB", wants to drop/create or replace the table that user "AAA" created, we receive an error that "insufficient privillages". What should I do? Is there anything I need to change?

**All of the users in sysadmin have "GRANT ALL" on the desired schema,database,table.

Answer 1

1. ROLE privileges, please get an overview of your options by reading eg. Configuring Access Control.
   You must consider a combination of the following:

   • Effective ROLE; make sure that both USER AAA and USER BBB are using the ROLE SYSADMIN when creating tables. Check ownership of the tables in question, and run the command SHOW ROLES to see if everything is set up as expected.
   • Alternatively use GRANT OWNERSHIP ... FUTURE ...TO ROLE SYSADMIN to make sure SYSADMIN owns every object
   • Use a MANAGED SCHEMA owned by SYSADMIN to control access

2. You can create a JavaScript Stored Procedure which runs any number of SQL statements and then set up a TASK to run/call that procedure.

Answer 2

A few points first up:

1. Snowflake uses role-based access control (RBAC). This means that it doesn't matter what user is being used, it's all about what role they have activated when doing something.

2. The role that is activated when an object is created (like a table) becomes the owner of that object

3. You can only drop an object when the active role is the owner of the object, OR the role is a member of that role that created it (it is higher in the role hierarchy)

If user "AAA" created the table using the SYSADMIN role then you need to make sure that user "BBB" is dropping the table while SYSADMIN is activated (or a role that is a member of SYSADMIN like ACCOUNTADMIN)

As an aside, I would suggest setting up a role hierarchy rather rather than assigning too many people SYSADMIN.