ADA-C01 Question 33
Single answerCustom security roles and users (SHOW command)A Snowflake security administrator created several custom roles for a new finance data platform, including FINANCE_ANALYST, FINANCE_ETL, and FINANCE_READONLY. During an access review, the administrator needs to quickly verify which of these custom roles currently exist in the account without querying ACCOUNT_USAGE views and without relying on grants metadata. Which command is the most appropriate to use?
- A
SHOW ROLES LIKE 'FINANCE_%';
- B
SHOW GRANTS TO ROLE FINANCE_%';
- C
DESCRIBE ROLE FINANCE_%';
- D
SHOW USERS LIKE 'FINANCE_%';
Show answer and explanation
Correct answer: A
Explanation
The requirement is to verify which custom roles exist by using a SHOW command rather than querying usage views or grant relationships. In Snowflake, SHOW ROLES is the appropriate command for enumerating roles, and adding LIKE 'FINANCE_%' filters the output to the relevant custom roles. Commands such as SHOW GRANTS TO ROLE and DESCRIBE ROLE are useful only after a specific role has already been identified. SHOW USERS applies to user principals rather than roles, so it does not address the scenario. This aligns with Snowflake administrative best practice: use SHOW commands for immediate object discovery and lightweight metadata inspection, while using ACCOUNT_USAGE views for historical or broader reporting needs.
- A. Correct.
Correct. SHOW ROLES lists roles in the account, and the LIKE filter can be used to narrow the results to role names matching a pattern such as FINANCE_%. This is the most direct SHOW command for verifying the existence of custom roles by name.
- B. Incorrect.
Incorrect. SHOW GRANTS TO ROLE requires a specific role name, not a wildcard pattern representing multiple roles. It is used to inspect privileges granted to one role, not to discover which roles exist in the account.
- C. Incorrect.
Incorrect. DESCRIBE ROLE also requires a specific existing role name and returns metadata for that single role. It cannot be used with a wildcard pattern to enumerate multiple candidate roles.
- D. Incorrect.
Incorrect. SHOW USERS lists users, not roles. Even if some usernames happened to start with FINANCE_, this would not help verify the existence of custom security roles.