ADA-C01 Question 467
Single answerCreate user and role for accessA Snowflake administrator needs to onboard a new analyst, JLEE, who will query tables in the ANALYTICS_DB database using an existing virtual warehouse named BI_WH. Company policy requires following least-privilege practices and avoiding use of the ACCOUNTADMIN role for routine access management. The analyst should be able to log in immediately and use a custom role named ANALYST_R. Which sequence of actions best satisfies the requirement?
- A
Using SECURITYADMIN, create role ANALYST_R; grant USAGE on warehouse BI_WH to ANALYST_R; grant USAGE on database ANALYTICS_DB and USAGE on schema ANALYTICS_DB.PUBLIC to ANALYST_R; grant SELECT on required tables to ANALYST_R; create user JLEE with a password and DEFAULT_ROLE = ANALYST_R; grant role ANALYST_R to user JLEE.
- B
Using SYSADMIN, create user JLEE and role ANALYST_R; grant ANALYST_R to JLEE; then use ACCOUNTADMIN to inherit all access needed for querying ANALYTICS_DB through the user's default role.
- C
Using USERADMIN, create user JLEE and set DEFAULT_ROLE = ANALYST_R; create role ANALYST_R; because the user has a default role, no explicit role grant to the user is required; grant USAGE on BI_WH to JLEE directly.
- D
Using SECURITYADMIN, create role ANALYST_R and user JLEE; grant SELECT on ANALYTICS_DB.PUBLIC tables to JLEE directly; assign BI_WH as the default warehouse; no schema or database privileges are needed if table SELECT is granted.
Show answer and explanation
Correct answer: A
Explanation
The best answer is Option 1 because it applies Snowflake's role-based access control model correctly and minimally. In Snowflake, access is typically granted to roles, and roles are granted to users. For a user to query a table, the active role must generally have: (1) USAGE on the virtual warehouse to run compute, (2) USAGE on the database, (3) USAGE on the schema, and (4) SELECT on the table or view. A DEFAULT_ROLE can be set on the user for convenience, but it does not replace the requirement to grant the role to the user. Snowflake documentation and best practices also emphasize avoiding ACCOUNTADMIN for day-to-day administration and using system roles such as USERADMIN and SECURITYADMIN appropriately. This scenario specifically tests the candidate's ability to create a user and role for access while applying least privilege and proper RBAC sequencing.
- A. Correct.
Correct. This sequence aligns with Snowflake RBAC best practices: create and grant privileges to a custom role, then grant that role to the user. To query objects, the role needs USAGE on the warehouse, database, and schema, plus SELECT on the tables or views. Creating the user with a password enables login, and setting DEFAULT_ROLE helps the user begin with the intended role, but the role must still be explicitly granted to the user. Using SECURITYADMIN is appropriate for role and grant management and avoids routine use of ACCOUNTADMIN.
- B. Incorrect.
Incorrect. SYSADMIN is not the appropriate role for creating users and security-focused role assignments in a least-privilege model; USERADMIN manages users and SECURITYADMIN manages grants/roles. More importantly, ACCOUNTADMIN should not be used routinely just to make access work, and privileges are not obtained by 'inheriting all access' through a user's default role. Access must be explicitly granted to roles and roles granted to users.
- C. Incorrect.
Incorrect. Setting DEFAULT_ROLE does not automatically grant the role to the user; the role must be granted explicitly with GRANT ROLE ... TO USER .... Also, granting warehouse access directly to a user bypasses the recommended role-based access model. Snowflake best practice is to grant privileges to roles, not individual users, except in limited special cases.
- D. Incorrect.
Incorrect. Granting SELECT directly to the user does not follow least-privilege RBAC best practice for scalable administration. In addition, table-level SELECT alone is insufficient for querying if the role lacks USAGE on the parent database and schema. Assigning a default warehouse does not replace the need for warehouse USAGE privileges.