ADA-C01 Question 44
Single answerCreate custom rolesA Snowflake administrator is redesigning access control for a finance analytics team. The company wants to follow least-privilege principles and avoid assigning broad system roles directly to end users. Analysts need read access to FIN_DB and its reporting schema, while team leads also need the ability to create and manage reports in that schema. The administrator wants a maintainable role design that can be extended later to additional teams. Which approach best meets these requirements?
- A
Grant the SYSADMIN role directly to team leads, grant the PUBLIC role to analysts, and rely on object ownership to control access to reporting objects.
- B
Create custom functional roles such as FIN_REPORT_READER and FIN_REPORT_DEVELOPER, grant the required privileges to those roles, then grant those roles to a higher-level team role that is assigned to users as needed.
- C
Create one custom role for each user, grant all required privileges directly to each user-specific role, and avoid role hierarchy so permissions remain explicit.
- D
Grant privileges directly to users for existing reporting objects in FIN_DB, and use future grants only for team leads to reduce administrative overhead.
Show answer and explanation
Correct answer: B
Explanation
The best answer is to create custom functional roles and use role hierarchy. In Snowflake RBAC, administrators should define custom roles aligned to business functions, grant object privileges to those roles, and assign roles to users through higher-level grouping roles when appropriate. This approach supports least privilege, separation of duties, and maintainability. In the given scenario, a reader role and a developer role let the administrator distinguish read-only analysts from leads who need additional schema-level object creation privileges. Snowflake documentation and best practices emphasize granting privileges to roles instead of users, and avoiding direct assignment of broad built-in system roles such as SYSADMIN to end users except where truly required. A hierarchical custom role model is also easier to extend to future teams and simplifies auditing and entitlement reviews.
- A. Incorrect.
Incorrect. SYSADMIN is a powerful built-in system role intended for managing objects, not for routine end-user assignment. Granting SYSADMIN to team leads violates least-privilege principles and creates unnecessary risk. PUBLIC is also not an appropriate mechanism for restricting analyst access because it is granted to all users automatically. This option reflects a common misconception that built-in broad roles can substitute for proper custom role design.
- B. Correct.
Correct. This is the recommended RBAC design pattern in Snowflake: create custom access roles based on job functions, grant object privileges to those roles, and then use role hierarchy to aggregate them into higher-level team or business roles for assignment to users. For example, FIN_REPORT_READER can have USAGE on the database and schema plus SELECT on reporting tables/views, while FIN_REPORT_DEVELOPER can add CREATE privileges as needed. This design supports least privilege, avoids assigning system roles directly to users, and is easier to extend and maintain.
- C. Incorrect.
Incorrect. Creating a separate custom role per user is typically difficult to maintain and does not scale well. It also defeats the purpose of role-based access control, which is to map privileges to functions or responsibilities rather than individuals. While technically possible, it is not a best-practice approach for a team-based access model.
- D. Incorrect.
Incorrect. Snowflake supports granting privileges directly to users, but best practice is to grant privileges to roles and then grant roles to users. Direct user grants increase operational complexity, make audits harder, and are less maintainable. Applying future grants only to team leads would also create inconsistent access management across the team.