SnowPro Associate: Platform Question 123
Single answerRole-Based Access Control (RBAC)A company uses Snowflake to support both a Finance team and a Data Engineering team. The SECURITYADMIN role has created a custom role named FINANCE_ANALYST and granted it SELECT on tables in the FINANCE_DB database. A user named JLEE has been granted the FINANCE_ANALYST role, but when JLEE logs in and tries to query a finance table, the query fails with an insufficient privileges error. JLEE can log in successfully and can see that the FINANCE_ANALYST role is assigned to the user. What is the most likely reason for the failure?
- A
The FINANCE_ANALYST role was granted to JLEE, but the role is not active in the current session.
- B
The FINANCE_ANALYST role must also be granted the SYSADMIN role before it can query tables.
- C
The user needs OWNERSHIP on the finance tables in order to run SELECT statements.
- D
The SECURITYADMIN role cannot grant database object privileges such as SELECT on tables.
Show answer and explanation
Correct answer: A
Explanation
This question tests a practical RBAC troubleshooting scenario. In Snowflake, privileges are evaluated based on the active role in the current session, not simply on all roles granted to the user. A user may have a role assigned but still be unable to use its privileges until that role is active, either because it is set as the default role or selected with USE ROLE. For querying tables, the relevant role also needs the proper privilege chain, typically USAGE on the warehouse, database, and schema, plus SELECT on the table or view. However, the scenario specifically states that SELECT was granted and focuses on the fact that the user can see the role assignment but still receives an insufficient privileges error, making inactive role selection the most likely cause. This aligns with Snowflake documentation on role hierarchy, active roles, and RBAC best practices using least privilege.
- A. Correct.
Correct. In Snowflake RBAC, a role can be granted to a user without being the active role in the session. If JLEE logs in with a different default role, or has not executed USE ROLE FINANCE_ANALYST, the privileges of FINANCE_ANALYST will not apply to the query. This is a common operational issue: granted roles do not automatically become active unless they are the default role or explicitly selected.
- B. Incorrect.
Incorrect. A custom role does not need SYSADMIN in order to query tables. Snowflake best practice is to use least privilege and grant only the object privileges required, such as USAGE on the database and schema and SELECT on the tables or views. Granting SYSADMIN would be excessive and unnecessary.
- C. Incorrect.
Incorrect. OWNERSHIP is not required to read data. To query a table, the active role typically needs USAGE on the database, USAGE on the schema, and SELECT on the table or view. OWNERSHIP is a powerful administrative privilege used for full control of an object, not routine read access.
- D. Incorrect.
Incorrect. SECURITYADMIN can manage grants through role administration capabilities. In Snowflake's access control model, SECURITYADMIN is commonly used to create and manage roles and grant privileges. The issue described is not that SECURITYADMIN lacks authority to grant SELECT, but that the user's active role likely does not include the needed privileges during the session.