COF-C03 Question 165
Single answerDatabase rolesA company stores sales data in database SALES_DB and wants to simplify object-level access management for analysts. The security architect creates a database role named SALES_READER in SALES_DB and grants it SELECT on several tables in schema CURATED. Analysts already use account role ANALYST. The architect wants all users with the ANALYST role to inherit the table access from SALES_READER while keeping the privileges scoped to SALES_DB. Which action should be taken to meet this requirement?
- A
Grant the database role SALES_DB.SALES_READER to the account role ANALYST.
- B
Grant the account role ANALYST to the database role SALES_DB.SALES_READER.
- C
Grant SALES_DB.SALES_READER directly to each user who needs access.
- D
Convert SALES_DB.SALES_READER into a share so ANALYST can inherit its privileges.
Show answer and explanation
Correct answer: A
Explanation
The correct design is to grant the database role SALES_DB.SALES_READER to the account role ANALYST. In Snowflake, database roles are database-scoped containers for privileges on objects in a specific database. They are useful for delegating and organizing access within that database. To make those privileges available to users, the database role must be granted to an account role that users already hold, such as ANALYST. Snowflake documentation on database roles explains that database roles can be granted to account roles and to other database roles in the same database, but not directly to users. This approach follows best practice by centralizing object privilege management in the database role while preserving enterprise role assignment through account roles.
- A. Correct.
Correct. A database role can be granted to an account role, allowing users who hold the account role to inherit the database role's object privileges. This is the intended way to bridge database-scoped privileges to account-level role hierarchies while keeping the privileges managed within the database.
- B. Incorrect.
Incorrect. The grant direction is backwards. Database roles are granted to account roles or to other database roles within the same database, not the other way around for this use case. Granting the account role to the database role would not make ANALYST inherit the database object's privileges.
- C. Incorrect.
Incorrect. Database roles cannot be granted directly to users. In Snowflake, users receive privileges through roles, and database roles must be attached to account roles to be usable by end users.
- D. Incorrect.
Incorrect. Shares are used for secure data sharing across accounts, not for internal role inheritance within the same account. Converting a database role into a share is not a valid mechanism for propagating privileges to an account role.