SnowPro Advanced: Security Engineer Question 15
Single answerUnderstand best practices for role design (functional vs. access roles):A global retailer is redesigning Snowflake access management after several audits found that analysts were granted direct object privileges through job-title roles such as ANALYST_US, ANALYST_EU, and SENIOR_ANALYST. This caused duplicated grants, inconsistent access across regions, and difficult onboarding. The security team wants a role design that supports least privilege, simplifies provisioning, and makes it easy to reuse data access patterns across multiple business functions. Which approach should the security engineer recommend?
- A
Create functional roles for job responsibilities (for example, ANALYST, DATA_SCIENTIST) and grant object privileges directly to those functional roles so each business role remains self-contained.
- B
Create access roles that hold object privileges (for example, SALES_RW, FINANCE_RO), create functional roles for business responsibilities, and grant the access roles to the functional roles.
- C
Grant all required privileges to SYSADMIN, then grant SYSADMIN to each business role so access inheritance is centralized and easier to manage.
- D
Create one role per user that combines job function and data access, then assign those custom roles directly to users to avoid nested role complexity.
Show answer and explanation
Correct answer: B
Explanation
The recommended design is to separate functional roles from access roles. Functional roles model a user's job function or responsibility, while access roles contain the actual object privileges needed to perform work. By granting access roles to functional roles, organizations can reuse common access patterns, reduce duplicated grants, and support least privilege more effectively. This also makes onboarding and offboarding simpler because users are assigned to business-aligned roles rather than receiving many direct privileges or one-off custom roles. Snowflake documentation and security best practices emphasize role hierarchies and reusable role design, with roles granted to roles to simplify privilege administration and auditing. Highly privileged system roles such as SYSADMIN should not be used as a shortcut for business access because they exceed the permissions required by typical users.
- A. Incorrect.
Incorrect. Functional roles should represent what a user does in the organization, while access roles should represent what data or objects can be accessed. Granting object privileges directly to functional roles recreates the same coupling problem the company is trying to eliminate. It leads to duplicated grants across many business roles and makes reuse and auditing harder.
- B. Correct.
Correct. This follows Snowflake role design best practices by separating business/job responsibilities from object access. Access roles encapsulate privileges on schemas, tables, views, warehouses, and other securable objects. Functional roles are then composed from one or more access roles based on job needs. This improves reuse, reduces grant duplication, supports least privilege, and simplifies onboarding and change management.
- C. Incorrect.
Incorrect. SYSADMIN is a powerful system-defined role intended for broad object management, not routine end-user data access. Granting SYSADMIN to business roles violates least-privilege principles and creates excessive privilege exposure. Centralization alone is not a valid reason to use a highly privileged administrative role for standard access patterns.
- D. Incorrect.
Incorrect. Per-user custom roles are generally difficult to scale and maintain. They increase administrative overhead, reduce standardization, and make audits harder because access patterns are no longer reusable or consistently modeled. Snowflake best practice is to design reusable hierarchical roles rather than role sprawl tied to individual users.