SnowPro Associate: Platform Question 117
Single answer2.1 Define the roles that are used in Snowflake.A Snowflake administrator is onboarding a new data engineering team. The team needs to create schemas and tables in an existing database, load data into those tables, and grant access on the objects they create to analyst roles. The administrator wants to follow Snowflake best practices by giving the team only the privileges needed to manage objects they own, without giving them broad account-wide administrative capabilities. Which role should be granted to the team?
- A
ACCOUNTADMIN
- B
SECURITYADMIN
- C
SYSADMIN
- D
PUBLIC
Show answer and explanation
Correct answer: C
Explanation
Snowflake provides several system-defined roles with distinct purposes. For object creation and ongoing management of databases, schemas, tables, and warehouses, SYSADMIN is the appropriate role. SECURITYADMIN is focused on security-related administration such as users, roles, and grants, while ACCOUNTADMIN is a powerful role intended for full account administration and should be tightly restricted. PUBLIC is a baseline role granted broadly and is not suitable for administrative responsibilities. Snowflake best practices recommend separating duties and using least privilege, with SYSADMIN handling object management and SECURITYADMIN handling security management.
- A. Incorrect.
Incorrect. ACCOUNTADMIN is the highest-level system-defined role and combines broad administrative capabilities across the account. Granting it to a data engineering team would violate least-privilege principles and provide unnecessary access well beyond creating and managing database objects.
- B. Incorrect.
Incorrect. SECURITYADMIN is primarily intended for managing grants, users, and roles. While it can manage role assignments and privileges, it is not the best role for a team whose main responsibility is creating and managing schemas, tables, and other database objects. Using SECURITYADMIN for routine object creation mixes security administration with object administration.
- C. Correct.
Correct. SYSADMIN is the recommended system-defined role for creating and managing warehouses, databases, schemas, and other objects. In Snowflake's role hierarchy and best-practice model, object ownership typically rolls up to SYSADMIN. This makes it the appropriate role for teams responsible for creating objects and then managing access to those objects they own.
- D. Incorrect.
Incorrect. PUBLIC is a special role automatically granted to all users and roles, but it is not intended for administrative or object-management tasks. It should not be used as the primary role for a team that needs to create schemas, tables, and manage access on those objects.