ADA-C01 Question 41
Single answerUse and manage database roles and use casesA Snowflake administrator manages a shared analytics database named FINANCE_DB that contains sensitive tables and secure views. The company wants to let application support users query only objects in FINANCE_DB without granting them broad account-level privileges. The administrator also wants object access to remain contained within the database so it can be granted to different account roles as needed across environments. Which action should the administrator take to meet these requirements using Snowflake best practices?
- A
Create a database role in FINANCE_DB, grant the required schema and object privileges to that database role, then grant the database role to the appropriate account roles used by the application support users.
- B
Create an account role named FINANCE_DB_SUPPORT, grant USAGE on the warehouse and all FINANCE_DB objects directly to the account role, and assign that role to users.
- C
Grant privileges on FINANCE_DB objects directly to individual users, because database roles cannot be used to control access to tables and views.
- D
Create a database role in FINANCE_DB and grant it directly to users, because database roles can be activated in a user session without account roles.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use a database role inside FINANCE_DB, grant the needed privileges on schemas and objects to that database role, and then grant the database role to one or more account roles. In Snowflake, database roles are intended to manage privileges for securable objects within a specific database, while account roles are the entities assigned to users and used in active sessions. This separation helps administrators build modular access models: database-local permissions stay with the database role, and account roles determine who receives that access. This is especially useful for promoting consistent access patterns across teams or environments while maintaining least privilege. Snowflake documentation on access control and database roles describes that database roles are scoped to a database, can hold privileges on objects in that database, and are granted to account roles rather than directly to users.
- A. Correct.
Correct. Database roles are designed to hold privileges on objects within a single database. This makes them well suited for encapsulating access inside FINANCE_DB and then granting that access to account roles. Users activate account roles in sessions, and those account roles can inherit privileges from database roles. This pattern supports reusable, database-scoped access models and aligns with least-privilege administration.
- B. Incorrect.
Incorrect. While this can work functionally, it does not meet the stated goal of containing object access within the database for reusable delegation across environments. Granting all database object privileges directly to an account role bypasses the primary use case for database roles: encapsulating database-scoped privileges independently from account-level role design. Also, warehouse access is a separate concern from database object access.
- C. Incorrect.
Incorrect. Granting privileges directly to users is not a best practice in Snowflake and does not provide scalable, manageable access control. In addition, the statement that database roles cannot control access to tables and views is false. Database roles can hold privileges on database objects such as schemas, tables, and views within their database.
- D. Incorrect.
Incorrect. Database roles cannot be granted directly to users. They are granted to account roles, and users are assigned account roles. A common misconception is treating database roles like account roles in session activation. Only account roles are directly granted to users and activated in a session.