SnowPro Advanced: Security Engineer Question 316
Single answerOver-privileged roles and usersA Snowflake security engineer is reviewing access after an internal audit finds that several analysts can create users, grant roles, and read sensitive finance tables, even though their job is limited to querying curated reporting views. The current design relies on a single custom role, ANALYST_POWER, which has been granted imported privileges on a shared database, broad privileges on multiple schemas, and the ability to manage role assignments. Management wants to reduce risk quickly without disrupting the analysts' ability to run reports. Which action is the BEST first step to remediate this over-privileged access while following Snowflake security best practices?
- A
Replace ANALYST_POWER with separate functional roles, grant analysts only a read-focused role for required reporting objects, and remove user and role administration privileges from analyst-facing roles
- B
Grant the analysts the SECURITYADMIN role so they can manage their own grants instead of relying on ANALYST_POWER
- C
Leave ANALYST_POWER in place, but revoke USAGE on the analysts' warehouse so they cannot query sensitive objects until requests are reviewed manually
- D
Convert ANALYST_POWER to the account-level PUBLIC role so privileges are easier to manage centrally
- E
Grant MANAGE GRANTS to all analysts, because centralized grant management is less risky than object ownership
Show answer and explanation
Correct answer: A
Explanation
The best answer is to redesign access around least privilege and role separation. In Snowflake, access control is role-based, and best practice is to create roles aligned to business functions, then grant only the minimum object privileges required. Analysts who only query curated reporting views generally need privileges such as USAGE on the relevant database and schema plus SELECT on the approved views or tables. They should not have administrative privileges like creating users, granting roles, or broad grant-management capabilities.
This scenario highlights a common anti-pattern: combining read access and security administration into a single business role. A practical remediation path is to decompose the over-privileged role into narrower roles, remove administrative privileges from end-user roles, and assign dedicated admin responsibilities to appropriate system or custom admin roles. Snowflake documentation on access control and best practices emphasizes least privilege, role hierarchy design, and careful use of powerful roles such as SECURITYADMIN and privileges such as MANAGE GRANTS. PUBLIC should also be treated cautiously because it applies to all users.
- A. Correct.
Correct. Snowflake best practice is to use least privilege and role-based access control with separate roles for distinct job functions. Analysts who only need to query curated reporting views should receive a narrowly scoped read role with only the required database, schema, and object privileges. Administrative capabilities such as creating users, granting roles, or broader privilege management should be held by dedicated admin roles, not analyst-facing roles. This is the most effective first step because it directly removes unnecessary privilege while preserving required reporting access.
- B. Incorrect.
Incorrect. SECURITYADMIN is a powerful system role intended for user and role management. Granting it to analysts would significantly increase privilege and worsen the audit finding. This option reflects a common misconception that giving users direct admin capability improves agility; in reality, it violates least-privilege design.
- C. Incorrect.
Incorrect. Revoking warehouse USAGE might temporarily stop queries, but it does not remediate the underlying over-privileged role design. The analysts would still retain excessive object and administrative privileges. This approach is disruptive and treats compute access as a substitute for proper authorization cleanup.
- D. Incorrect.
Incorrect. The PUBLIC role is granted to every user in Snowflake. Moving broad privileges into PUBLIC would expand access instead of restricting it, creating a far larger security exposure. PUBLIC should be used very carefully and not as a place to centralize sensitive privileges.
- E. Incorrect.
Incorrect. MANAGE GRANTS is a highly privileged capability that allows broad control over grants. Assigning it to all analysts would directly conflict with the goal of reducing over-privilege. While object ownership and grant management are important design considerations, this option increases risk rather than reducing it.