SnowPro Advanced: Security Engineer Question 6
Single answerConfigure and implement Role-Based Access Control (RBAC):A security engineer is redesigning access in a Snowflake account to align with least-privilege RBAC. The company has three requirements: (1) data analysts in the ANALYST role must be able to query curated tables in the PROD_DB.ANALYTICS schema, (2) ETL developers in the ETL_DEV role must be able to create and modify objects only in the PROD_DB.STAGING schema, and (3) security administrators must manage grants without using ACCOUNTADMIN for routine operations. Which approach best meets these requirements?
- A
Grant OWNERSHIP on PROD_DB to ANALYST and ETL_DEV, and grant ACCOUNTADMIN to the security administrators so they can manage all future privileges centrally.
- B
Create separate functional roles for ANALYST and ETL_DEV, grant USAGE on PROD_DB and the relevant schemas, grant SELECT on curated tables in PROD_DB.ANALYTICS to ANALYST, grant CREATE and object-level privileges in PROD_DB.STAGING to ETL_DEV, and assign SECURITYADMIN or a custom role with MANAGE GRANTS to the security administrators.
- C
Grant imported privileges on PROD_DB to ANALYST and ETL_DEV, then allow security administrators to manage access by granting SYSADMIN to themselves when needed.
- D
Use one shared role for both analysts and ETL developers, grant ALL PRIVILEGES on both schemas to simplify operations, and rely on future grants to prevent excessive access.
Show answer and explanation
Correct answer: B
Explanation
The best answer is the role design that separates privileges by function and limits each role to the minimum required scope. In Snowflake RBAC, best practice is to grant privileges to roles, then assign roles to users, rather than granting broad object control directly to users or using highly privileged system roles for routine work. For read-only access, analysts typically need USAGE on the database and schema plus SELECT on tables or views. For development or ETL work limited to a specific schema, a role should receive only the necessary schema-level and object-level privileges in that schema, such as CREATE TABLE, CREATE VIEW, CREATE STAGE, or DML privileges as needed. For security operations, SECURITYADMIN is the built-in role intended to manage roles and grants, and Snowflake also supports grant delegation patterns through MANAGE GRANTS where appropriate. This aligns with Snowflake documentation and guidance on access control, least privilege, separation of duties, and the role hierarchy model.
- A. Incorrect.
Incorrect. Granting OWNERSHIP on the entire database to end-user roles violates least privilege and gives excessive control, including the ability to transfer ownership and manage grants on owned objects. Granting ACCOUNTADMIN for routine security administration is also a poor practice because ACCOUNTADMIN is the most powerful system role and should be tightly limited.
- B. Correct.
Correct. This design follows Snowflake RBAC best practices by separating access by job function and scope. ANALYST receives only the minimum required privileges to query curated data: typically USAGE on the database and schema plus SELECT on the relevant tables or views. ETL_DEV is limited to the STAGING schema with privileges such as USAGE on the database and schema and CREATE privileges appropriate to that schema, plus object-level modification privileges where needed. For grant management, SECURITYADMIN is the standard system role for managing roles and grants, and a custom role with MANAGE GRANTS can also be appropriate depending on the governance model.
- C. Incorrect.
Incorrect. Imported privileges are used for specific shared resources such as privileges on the SNOWFLAKE database or other shared databases, not as a general mechanism for internal database access design. Allowing administrators to assume SYSADMIN ad hoc for security tasks mixes operational and security responsibilities and does not align with the requirement to avoid overly broad admin roles for routine grant management.
- D. Incorrect.
Incorrect. A single shared role for distinct job functions undermines separation of duties and makes least-privilege enforcement difficult. Granting ALL PRIVILEGES on both schemas gives analysts more access than required and exposes production staging objects unnecessarily. Future grants can simplify privilege management, but they do not justify over-granting access.