ADA-C01 Question 18
Single answerRole-Based Access Control (RBAC)A Snowflake account uses a layered RBAC model with separate functional roles. The SECURITYADMIN role created a custom role named ANALYST_READONLY and granted it SELECT on all existing tables in database FINANCE and on future tables in schema FINANCE.REPORTING. The role was then granted to several analyst users. A week later, analysts report that they can query older tables in FINANCE.REPORTING, but they cannot query a newly created table FINANCE.RAW.NEW_TRANSACTIONS. The database owner wants analysts to continue having read-only access only to reporting-ready data, while minimizing ongoing administration. Which action should the administrator take?
- A
Grant SELECT on future tables in database FINANCE to ANALYST_READONLY so the role automatically receives access to new tables in every schema in the database.
- B
Grant USAGE on schema FINANCE.RAW to ANALYST_READONLY so analysts can query the new table without changing table privileges.
- C
Grant OWNERSHIP on schema FINANCE.RAW to ANALYST_READONLY so the role inherits access to new objects created in that schema.
- D
Grant ANALYST_READONLY to the SYSADMIN role so object ownership and role hierarchy propagate read access to the analysts.
Show answer and explanation
Correct answer: A
Explanation
The current configuration works as designed: ANALYST_READONLY has SELECT on existing tables in the FINANCE database and future SELECT only for tables created in FINANCE.REPORTING. Therefore, a newly created table in FINANCE.RAW is outside the scope of the schema-level future grant. To minimize administration while extending automatic access, the administrator should use a database-level future grant for SELECT on tables in FINANCE. In Snowflake RBAC, effective access requires both object privileges such as SELECT and container privileges such as USAGE on the database and schema. Best practice is to assign privileges to custom roles rather than directly to users, and to use future grants carefully to align with least privilege. Relevant Snowflake documentation includes the access control model, role hierarchy behavior, and GRANT ... ON FUTURE objects semantics.
- A. Correct.
Correct. The issue is that the role has future SELECT only on tables in the FINANCE.REPORTING schema, not in FINANCE.RAW. If the business requirement changes so analysts need automatic access to new tables across the database, granting SELECT on future tables at the database level would cover new tables in all schemas, including FINANCE.RAW. This minimizes ongoing administration because future grants are applied automatically. Administrators should also ensure the role has the required USAGE on the database and relevant schemas. In Snowflake, future grants can be defined at both the database and schema levels, with database-level future grants helping reduce repetitive manual grants across schemas.
- B. Incorrect.
Incorrect. USAGE on a schema is necessary to resolve objects in that schema, but it does not grant the ability to query tables. To select from a table, the role also needs SELECT on the table itself. Since the new table is in FINANCE.RAW and the role was not granted SELECT on future tables there, adding only schema USAGE would not solve the problem. This option reflects a common misconception that container visibility privileges are sufficient for data access.
- C. Incorrect.
Incorrect. Granting OWNERSHIP would violate the stated requirement for read-only access and would give the role full control over objects, including the ability to transfer ownership and manage grants depending on the scenario. OWNERSHIP is the highest privilege on an object and is not appropriate for analyst consumers. This distractor targets the misunderstanding that ownership is needed for inheritance of access; in Snowflake, access is controlled through explicit grants and future grants, not by giving consumers ownership.
- D. Incorrect.
Incorrect. Granting ANALYST_READONLY to SYSADMIN changes the role hierarchy in the wrong direction and does not grant additional privileges to analyst users who already hold ANALYST_READONLY. In Snowflake RBAC, privileges flow from granted roles to the parent role receiving them, not from a higher administrative role down to child roles or users automatically. This option reflects confusion about how role inheritance works.