ADA-C01 Question 42
Single answerUse and manage database roles and use casesA Snowflake administrator is redesigning access for a shared analytics database named FINANCE_DB. The goal is to let the Finance data steward manage object privileges only within FINANCE_DB without granting broad account-level security administration. The steward must be able to grant SELECT on existing and future tables in schema REPORTING to analyst roles, and the design should follow least-privilege principles. Which approach best meets these requirements?
- A
Grant the SECURITYADMIN role to the Finance data steward so they can manage grants on objects in FINANCE_DB.
- B
Create a database role in FINANCE_DB, grant it the required privileges on schema REPORTING and its tables/future tables, then grant that database role to the analyst roles and to an account role used by the Finance data steward.
- C
Create a share on FINANCE_DB and add the analyst roles as consumers so the Finance data steward can manage access without account roles.
- D
Grant OWNERSHIP on FINANCE_DB to the Finance data steward's account role so they can manage all grants in the database.
Show answer and explanation
Correct answer: B
Explanation
The best answer is to use a database role because database roles are intended to manage privileges within a single database boundary. This makes them well suited for delegating administration to domain owners such as a Finance data steward without assigning powerful account-level roles like SECURITYADMIN. A typical implementation would include granting USAGE on FINANCE_DB and schema REPORTING, plus SELECT on all existing tables and SELECT on future tables in REPORTING, to the database role. That database role can then be granted to account roles used by analysts. To let the steward manage access through this design, the steward uses an account role that has been granted the database role. This pattern follows least-privilege and aligns with Snowflake guidance on using database roles for database-scoped access control and delegation.
- A. Incorrect.
Incorrect. SECURITYADMIN is an account-level administrative role intended for managing users, roles, and grants broadly across the account. Granting it to a data steward violates least-privilege for a use case limited to one database. While SECURITYADMIN can manage many grants, it is more privilege than necessary and does not specifically leverage database roles for scoped delegation.
- B. Correct.
Correct. Database roles are designed to scope privileges to a single database and are ideal for delegating database-specific access management. In this scenario, a database role in FINANCE_DB can be granted privileges such as USAGE on the database and schema, plus SELECT on existing and future tables in REPORTING. That database role can then be granted to analyst account roles for consumption, and also to an account role held by the Finance data steward so the steward can operate within the database-specific privilege model without receiving broad account-level authority.
- C. Incorrect.
Incorrect. Shares are used for secure data sharing across accounts or to readers, not for internal role-based access delegation within the same account. Analyst roles inside the same account are not managed through shares, so this does not solve the requirement to delegate internal access management for FINANCE_DB.
- D. Incorrect.
Incorrect. Granting OWNERSHIP on the entire database gives extensive control, including the ability to transfer ownership and manage all contained objects depending on ownership structure. This is broader than necessary for granting SELECT on tables in a specific schema and does not align well with least-privilege. It also introduces operational risk because OWNERSHIP is the highest privilege on an object.