0

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.

Aynaz
  • 11
  • 2
  • Please read the [How to Ask][1] guide. You should avoid putting multiple questions into one. [1]: https://stackoverflow.com/help/how-to-ask – Joel Bodenmann Feb 02 '20 at 09:18

2 Answers2

2

  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.

Hans Henrik Eriksen
  • 2,690
  • 5
  • 12
  • 2
    The key concept to read here that most new Snowflake users miss is that roles are NOT additive. So, just because they have SYSADMIN as a role doesn't mean they are using it. – Mike Walton Feb 02 '20 at 14:23
  • Thanks! I think it was a matter of grant ownership. It seems that it is working now. I did not understabd what do you mena by use "MANAGED SCHEMA" though. about the second part that I actually deleted that but thanks for answering, Actually I wanted to find a way around the JavaScript part and that is why I am trying a Python script instead ... not sure if it is going to woek :/ – Aynaz Feb 02 '20 at 17:36
  • A Python script should definitively work, but requires an external compute resource. You can achieve the same using a "JavaScript" `TASK` with Snowflake compute resources, but then you need to learn JavaScript... – Hans Henrik Eriksen Feb 03 '20 at 07:05
  • A `MANAGED SCHEMA` is not essential here, it is only an assurance that `SYSADMIN` can't ever completely lose access to an object within the schema. – Hans Henrik Eriksen Feb 03 '20 at 07:10
  • Hi Hans, thanks for the comments:) About "updating tables" what I do not understand is that I think it should be something that the DWH tool do it automatically and does not need to have some kind of Python or JavaScript. In terms of management , I am pretty new to snowflake and we are currently on our Trial account. I am trying to model our data into our DWH and it is really not easy for me to understand that why this kind of tasks, need lots of management!! Maybe I am new and expecting too much :) – Aynaz Feb 03 '20 at 09:57
  • I'm really not sure from above exactly what you expect. Snowflake behaves pretty much like any other database, but since it is a relatively new product, it lacks some of the admin tools that eg. Oracle and Microsoft have. – Hans Henrik Eriksen Feb 04 '20 at 17:37
  • Databases won't let you execute scripts noninteractively in general. The Snowflake `TASK` automates a single SQL command, but you can chain any number of them in a hierarchy. If you want multiple commands (such as in a script) you'll need to put those into a stored procedure. This applies to most databases I know. – Hans Henrik Eriksen Feb 04 '20 at 17:38
0

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.

Simon D
  • 5,730
  • 2
  • 17
  • 31
  • Good points. I would suggest best practice is to have `SYSADMIN` *own* schema objects, but have one or more ROLES assigned to USERS, and granting the roles access to the objects. Uncertain ownership leads to objects seemingly getting lost. – Hans Henrik Eriksen Feb 03 '20 at 07:50
  • Hi , actually I knew this and I am pretty sure that both users were using their sysadmin role but then I do not understand why they were not able to drop/create the tables. when I ran grant ownership comment, everything got fixed. I know it sounds strange but is it possible that I need to run 'use role sysadmin' sql comment instead of choosing the role from the drop down menue? – Aynaz Feb 03 '20 at 08:36
  • @Aynaz it could be that someone ran a `FUTURE` grant that assigned ownership to some other role automatically on all tables in that schema. You could test this by creating a new table as `SYSADMIN` and checking the ownership of the object. – Simon D Feb 03 '20 at 09:46