06 Nov 19 · nekkanti009 · #Snowflake ·   Bookmark   ×

Grant a DBA Role, all the required privileges across schemas without granting sysadmin or account admin access in Snowflake?

Is there a way to grant development privileges to one role (Role_Dev) in a snowflake database with which a user can perform development activities such as,

  • ddl (create/alter/drop tables/views/stored procedures),
  • dml (insert/update/delete rows in tables) activities,
  • create modify stages/functions/sequences,
  • running stored procedures/functions across schemas where each schema can have a different owner an they might created. 

The purpose of this role is to avoid granting sys admin/account admin role to a user to support the development activities. Please little 'r' me if anyone has done something like this before or have an idea?


posted on 06 Nov 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

npackNov 06 12:28

This can be achieved using ROLE to ROLE mapping or commonly "ROLE Hierarchy"

For example, you have two databases, Marketing and Sales. And each of them have their own developer roles, Marketing_dev and Sales_dev

Your requirement is to have a developer access to all databases, Then all we have to do is grant these roles to the Global_developer_role that you can then assign to the new users

Granting a role to another role creates a “parent-child” relationship between the roles (also referred to as a role hierarchy)

grant role Marketing_dev to role Global_developer_role;
grant role Sales_dev to role Global_developer_role;

<slightly formatted the question for better readability>

nekkanti009Nov 06 01:16

Thanks npack this was really helpful. I have tested it and it worked perfect.

Community Software by Hittly