ADA-C01 Question 34
Single answerCustom security roles and users (SHOW command)A Snowflake administrator is troubleshooting an access issue after a recent role redesign. The company created custom roles named APP_READONLY, APP_ANALYST, and APP_ADMIN, and several users were reassigned. The administrator needs to quickly verify which custom roles exist in the account and whether a user named JLEE has been granted any of those roles, using only metadata commands before making changes. Which approach should the administrator use?
- A
Run SHOW ROLES LIKE 'APP_%'; then run SHOW GRANTS TO USER JLEE;
- B
Run SHOW USERS LIKE 'JLEE'; then inspect the DEFAULT_ROLE column to determine all assigned custom roles.
- C
Run SHOW GRANTS OF ROLE APP_% TO USER JLEE;
- D
Run SHOW ROLES; then query ACCOUNT_USAGE.GRANTS_TO_USERS immediately to check whether JLEE has the custom roles.
Show answer and explanation
Correct answer: A
Explanation
The best approach is to use SHOW commands that provide current metadata directly from Snowflake's control plane. SHOW ROLES LIKE 'APP_%' helps the administrator identify the custom security roles currently defined in the account, and SHOW GRANTS TO USER JLEE reveals which roles and privileges have been granted to that user. This is preferable to relying on the DEFAULT_ROLE from SHOW USERS, because a user can have many granted roles beyond the default one. It is also preferable to using ACCOUNT_USAGE for immediate troubleshooting, because Account Usage views may not reflect the latest changes right away. Snowflake documentation distinguishes between SHOW ROLES, SHOW USERS, and SHOW GRANTS syntax and scope: SHOW ROLES lists roles, SHOW USERS lists users and user properties, and SHOW GRANTS TO USER is the correct command for inspecting grants received by a user. This aligns with best practice for rapidly validating custom role assignments during administrative troubleshooting.
- A. Correct.
Correct. SHOW ROLES LIKE 'APP_%' is the appropriate metadata command to list existing roles that match the custom naming convention. SHOW GRANTS TO USER JLEE then returns role grants and other privileges granted to that user, allowing the administrator to verify whether JLEE has been assigned any of those APP_* roles. This is a practical and direct use of SHOW commands for role and user validation.
- B. Incorrect.
Incorrect. SHOW USERS LIKE 'JLEE' can confirm that the user exists and display properties such as DEFAULT_ROLE, but DEFAULT_ROLE is only the role selected by default at login. It does not represent the complete set of roles granted to the user. An administrator relying on this could miss additional assigned roles.
- C. Incorrect.
Incorrect. This is not valid SHOW syntax in Snowflake. SHOW GRANTS OF ROLE <role_name> shows grants made to a specific role, and SHOW GRANTS TO USER <user_name> shows grants received by a user. You cannot use a wildcarded role pattern in this way to directly test grants to a user.
- D. Incorrect.
Incorrect. SHOW ROLES would list roles, but querying ACCOUNT_USAGE.GRANTS_TO_USERS immediately is not the best answer for this scenario. ACCOUNT_USAGE views can have latency, so they are not ideal for quick verification right after changes. The scenario specifically emphasizes using metadata commands before making changes, and SHOW GRANTS TO USER provides more immediate operational validation.