SnowPro Advanced: Security Engineer Question 20
Single answerSNOWFLAKE database rolesA security engineer is redesigning access for a finance data platform in Snowflake. The company wants privileges on objects in the FIN_DB database to be managed by database-specific administrators, without requiring ACCOUNTADMIN or SECURITYADMIN for routine grants. The engineer has created a database role named FIN_DB.FIN_ANALYST_DR and wants to let users in the account consume the privileges assigned to that database role through existing account roles such as FINANCE_ANALYST. What is the correct way to make the privileges in FIN_DB.FIN_ANALYST_DR available to those users?
- A
Grant the database role FIN_DB.FIN_ANALYST_DR directly to users who need access.
- B
Grant the database role FIN_DB.FIN_ANALYST_DR to the account role FINANCE_ANALYST, then grant the account role to users.
- C
Grant the account role FINANCE_ANALYST to the database role FIN_DB.FIN_ANALYST_DR, then grant the database role to users.
- D
Convert FIN_DB.FIN_ANALYST_DR into a system role so it can be activated across the account without role hierarchy changes.
Show answer and explanation
Correct answer: B
Explanation
The correct pattern for Snowflake database roles is: grant object privileges to the database role, grant that database role to an account role, and grant the account role to users. Database roles are scoped to a specific database and help decentralize privilege management, which is especially useful when database owners or delegated administrators need to manage access without relying on higher-level account administrators for every privilege change. However, users do not activate database roles directly; users work through account roles. Snowflake documentation on database roles explains that database roles can be granted to account roles and can contain privileges on objects in the same database. This design supports least privilege and separation of duties by allowing database-local administration while preserving account-level user-role assignment practices.
- A. Incorrect.
Incorrect. Database roles cannot be granted directly to users. In Snowflake, users are granted account roles, and database roles are intended to be granted to account roles or other database roles within the same database hierarchy. A common misconception is to treat database roles like account roles, but their scope and grant targets are different.
- B. Correct.
Correct. This is the intended design for database roles in Snowflake. A database role can hold privileges on securable objects in its database, and then be granted to an account role such as FINANCE_ANALYST. Users receive the effective access by being granted the account role. This pattern enables decentralized privilege administration within a database while still using account roles for user assignment.
- C. Incorrect.
Incorrect. The grant direction is wrong. Snowflake allows granting a database role to an account role, not the other way around for the purpose of exposing database-role privileges to users. Also, granting a database role to users directly is not supported. This option reflects confusion about role hierarchy between account roles and database roles.
- D. Incorrect.
Incorrect. Snowflake does not provide a mechanism to convert a user-defined database role into a system role. System roles such as ACCOUNTADMIN and SECURITYADMIN are predefined account-level roles. Database roles are a separate construct scoped to a database and are used to manage privileges within that database.