COF-C03 Question 164
Single answerDatabase rolesA data platform team wants to simplify object-level security inside the SALES_DB database. They create a database role named ANALYST_DB_ROLE in SALES_DB and grant it SELECT on several tables and views in the CURATED schema. They want users with the account role BI_ANALYST to inherit those privileges without granting the table privileges directly to BI_ANALYST. Which action should the security administrator take next to meet this requirement?
- A
Grant the database role ANALYST_DB_ROLE to the account role BI_ANALYST.
- B
Grant the account role BI_ANALYST to the database role ANALYST_DB_ROLE.
- C
Grant USAGE on SALES_DB to BI_ANALYST and the table privileges in ANALYST_DB_ROLE will automatically apply.
- D
Convert ANALYST_DB_ROLE into a system role so it can be activated by BI_ANALYST sessions directly.
Show answer and explanation
Correct answer: A
Explanation
The correct design is to grant the database role to an account role: GRANT DATABASE ROLE SALES_DB.ANALYST_DB_ROLE TO ROLE BI_ANALYST. In Snowflake, database roles are scoped to a single database and are useful for packaging privileges on schemas, tables, views, and other database objects. However, users do not activate database roles directly in a session the same way they use account roles. Instead, database roles are granted to account roles, and users inherit those privileges through the account role hierarchy. In practice, the account role still needs the required path privileges such as USAGE on the database and schema where applicable. This approach aligns with Snowflake best practices for modular, database-centric access control and separation of duties.
- A. Correct.
Correct. Database roles are intended to manage privileges on objects within a single database. To let users inherit those privileges, the database role must be granted to an account role. Once ANALYST_DB_ROLE is granted to BI_ANALYST, users with BI_ANALYST can inherit the database role's object privileges, assuming the necessary database and schema access path is also in place.
- B. Incorrect.
Incorrect. The grant direction is reversed. Snowflake allows a database role to be granted to an account role, not an account role to a database role. This is a common misunderstanding when working with role hierarchies.
- C. Incorrect.
Incorrect. USAGE on the database alone does not cause privileges from the database role to flow to BI_ANALYST. The account role must receive the database role explicitly. Also, access typically requires the appropriate USAGE privileges on the database and schema in addition to object privileges.
- D. Incorrect.
Incorrect. Database roles are a distinct role type in Snowflake and cannot be converted into system roles. They are designed for database-scoped privilege management and are activated through account roles that are granted the database role.