Although it is well documented, it shouldn’t hurt to review a few of the basic Snowflake account administration best practices, as I often see in practice that they are not followed consistently.
In my experience, there are so many IT administrators that are pressed for time that they don’t bother to create security roles and policies to be followed. To make it quick, they just use the ACCOUNTADMIN role on Snowflake (or an equivalent all powerful admin role on other systems) because it allows them to do anything and everything without having to think about security best practices.
Using an admin role for day-to-day tasks is a bad idea
Consider this true story. The administrator tries some functionality, and it works. The user (who doesn’t have admin privileges) tries, and it’s not working. The administrator doesn’t believe the user when they report that the functionality is not working, dismissing them as incompetent. This goes back and forth a few times, until someone gives up. Either the user doesn’t use the functionality at all, or the administrator finally stoops down, focuses their attention on the user’s complaint, and then says matter-of-factly that of course it’s not working because the user doesn’t have the necessary privileges. And then grudgingly grants the privileges to the user. I have witnessed a similar situation so many times in the past, it’s not even funny any longer.
Anecdotes aside, there are other reasons why using an admin role (ACCOUNTADMIN on Snowflake) for day-to-day tasks is a bad idea, such as:
- The ACCOUNTADMIN role is the most powerful role in the system. It allows the role grantee to configure parameters at the account level, view and operate on all objects in the account, view and manage Snowflake billing and credit data, and can stop any running SQL statements. In the wrong hands, someone with this role can do serious damage, whether intentional or unintentional. They can also get their hands on sensitive and personal information which may not be desired.
- The ACCOUNTADMIN role should not be used to create objects in the Snowflake account unless these objects absolutely must have the highest level of secure access. When objects are created with the ACCOUNTADMIN role users don’t have access to these objects. Privileges to access these objects must be explicitly granted to the roles of the users.
- The ACCOUNTADMIN role is often used when the administrator is in a hurry and doesn’t have time to figure out which role is needed, then just uses this role because it can do anything. Results of any actions taken are then owned by this role, even if a lesser role would have had sufficient privileges to accomplish the task.
Using roles with lesser privileges
In addition to ACCOUNTADMIN, there are other Snowflake administrator roles with lesser privileges that can be used in many situations:
- The security administrator (SECURITYADMIN) role allows to grant or revoke privileges on objects in the account.
- The user administrator (USERADMIN) role allows to create and manage users and roles and assumes ownership of those roles
- The system administrator (SYSADMIN) role allows to create warehouses, databases, and all database objects (schemas, tables, etc.).
Instead of immediately going to ACCOUNTADMIN for basic administration tasks, one of these lesser roles is usually sufficient and can be used instead.
Summary of best practices
To reiterate, here is a summary of best practices regarding Snowflake account administration:
- Never set ACCOUNTADMIN as the default role. Even if a user has been justifiably granted the ACCOUNTADMIN role, using this role must be a conscious decision by the user. They should have a different role as their default role so that they are reminded that they will now switch to the ACCOUNTADMIN role before any tasks are performed.
- Never grant ACCOUNTADMIN as the first course of action when someone requests privileges to do something that their current role doesn’t allow. Always consider whether a lesser role can be used instead and grant the lesser role when it is sufficient, even if this means creating a custom role that would be granted to the user to give them only the set of permissions that they requested.
- Never grant the ACCOUNTADMIN role to users “just in case”. While it is completely acceptable to grant the ACCOUNTADMIN role to anyone who needs it to perform administration tasks, it is still advisable to grant this role after careful consideration, keeping in mind the principle to grant the least amount of privileges that are needed. While most users can be trusted to use the privileges granted to them wisely, they may still cause damage due to not knowing how to use the privileges assigned to them correctly.
- Never grant the ACCOUNTADMIN role to any other role, as this role is reserved to be the top-level role in the hierarchy.
- The ACCOUNTADMIN role is required to query the SNOWFLAKE database metadata tables. To avoid granting the ACCOUNTADMIN role to users who want to query the SNOWFLAKE database, consider creating a dedicated role for accessing this database and grant privileges on the SNOWFLAKE database to this role. Then this role may be granted to users which will allow them to query the SNOWFLAKE database but will not give them any additional ACCOUNTADMIN privileges.