What are the best practices for managing users and roles in Snowflake?

1 Use built-in roles

Snowflake provides four built-in roles that you can use to assign to your users: ACCOUNTADMIN, SECURITYADMIN, SYSADMIN, and PUBLIC. These roles have predefined privileges and access levels for various objects and operations in Snowflake. You can use these roles as they are or customize them to suit your needs. However, you should avoid granting the ACCOUNTADMIN role to too many users, as it has the highest level of authority and can perform any action in Snowflake.

2 Create custom roles

In addition to the built-in roles, you can also create your own custom roles to define more granular and specific privileges and permissions for your users. For example, you can create a role for each business unit, project, or function in your organization, and assign the appropriate access level to the relevant data objects and operations. You can also nest custom roles within other roles to create a hierarchy of roles and inherit privileges from the parent role.

3 Use role inheritance

One of the benefits of using roles in Snowflake is that you can leverage role inheritance to simplify and streamline your access control management. Role inheritance allows you to assign a role to another role, so that the child role inherits all the privileges and permissions of the parent role. This way, you can avoid duplicating or conflicting privileges across multiple roles, and reduce the number of roles you need to create and maintain.

4 Use secure views

Another way to control the access and visibility of your data in Snowflake is to use secure views. Secure views are similar to regular views, but they have an additional layer of security that filters the data based on the current user's role. Secure views allow you to create a single view that can serve different users with different roles, without exposing any sensitive or irrelevant data to them. Secure views can also improve the performance and efficiency of your queries, as they reduce the amount of data that needs to be scanned and processed.

5 Monitor and audit user and role activities

It is important to monitor and audit the activities of your users and roles in Snowflake, to ensure that they are following security policies and best practices. Snowflake provides various tools and features for tracking and logging user and role actions, such as the QUERY_HISTORY, ACCESS_HISTORY, and LOGIN_HISTORY functions. Additionally, the Snowflake Audit Trail records all changes made to the Snowflake account, like user or role creation, modification, or deletion. By using these tools and features, you can identify any issues or anomalies in your user and role management, so that your data warehouse is secure and compliant.

6 Here’s what else to consider

This is a space to share examples, stories, or insights that don’t fit into any of the previous sections. What else would you like to add?