ADA-C01 Question 17
Single answerRole-Based Access Control (RBAC)A Snowflake account uses a custom role hierarchy. The role ANALYST_RW is granted to analysts and has SELECT, INSERT, UPDATE, and DELETE privileges on several production tables. A new requirement states that analysts must continue querying those tables, but only a small operations team should retain write access. The operations team already uses a separate custom role named OPS_LEAD. The administrator wants to implement this change with the least ongoing maintenance and without interrupting analysts' current read access. Which action should the administrator take?
- A
Revoke INSERT, UPDATE, and DELETE privileges from ANALYST_RW on the production tables, create a new role for read-only access, grant SELECT to that role, and grant the new role to all analysts including OPS_LEAD.
- B
Grant ANALYST_RW to OPS_LEAD, revoke INSERT, UPDATE, and DELETE privileges from ANALYST_RW, create a new WRITE role with INSERT, UPDATE, and DELETE on the production tables, and grant the WRITE role to OPS_LEAD.
- C
Create a new READ role with SELECT on the production tables, grant that role to all analyst users, then drop ANALYST_RW after granting its existing write privileges directly to OPS_LEAD users.
- D
Transfer ownership of the production tables to OPS_LEAD, then revoke all DML privileges from ANALYST_RW because ownership automatically preserves SELECT access for existing analysts.
Show answer and explanation
Correct answer: B
Explanation
The best solution is to separate shared read access from restricted write access by using roles in a layered RBAC model. In Snowflake, privileges should generally be granted to roles, then roles granted to other roles or users. This minimizes maintenance and supports least-privilege administration. Here, ANALYST_RW currently mixes read and write access for a broad user population. The cleanest fix is to remove write privileges from that broadly assigned role and place write permissions into a dedicated role granted only to OPS_LEAD. Granting ANALYST_RW to OPS_LEAD preserves inherited baseline access through the role hierarchy. This aligns with Snowflake documentation and best practices around access control, role hierarchies, and minimizing direct grants to users.
- A. Incorrect.
Incorrect. This approach removes write access from ANALYST_RW, which is necessary, but it also creates a new read-only role that must be granted to all analysts and OPS_LEAD while leaving the original ANALYST_RW role in place for no clear purpose. It adds avoidable maintenance because existing analyst assignments would need to be revisited. A better RBAC design is to keep the broadly needed access in the shared role and separate elevated privileges into a narrower role.
- B. Correct.
Correct. This follows the RBAC best practice of assigning privileges to roles, not directly to users, and structuring roles so that common access is retained while elevated access is isolated. By revoking INSERT, UPDATE, and DELETE from ANALYST_RW, analysts keep their existing SELECT capability. By placing write privileges in a separate WRITE role and granting that role to OPS_LEAD, only the operations team retains write access. Granting ANALYST_RW to OPS_LEAD ensures the operations team inherits the same baseline read access as analysts without duplicating grants.
- C. Incorrect.
Incorrect. Creating a new READ role for all analyst users would work functionally, but granting write privileges directly to OPS_LEAD users violates Snowflake RBAC best practice. Snowflake recommends granting privileges to roles and then assigning roles to users. Direct user grants increase operational overhead and make auditing and future changes harder.
- D. Incorrect.
Incorrect. Ownership is a special privilege that includes full control over an object, but transferring ownership does not preserve SELECT access for other roles unless those privileges remain granted. Revoking DML privileges from ANALYST_RW may be appropriate, but changing ownership is unnecessary for this requirement and introduces risk. Ownership changes can also have broader administrative effects, especially if not handled with proper grant preservation logic.