COF-C03 Question 139
Single answerRole-Based Access Control (RBAC)A company uses Snowflake to store finance and sales data. The SECURITYADMIN creates a custom role named ANALYST_READONLY and grants it SELECT on all existing tables in the FINANCE.REPORTING schema. A week later, analysts report that they can query older tables but receive authorization errors on newly created tables in the same schema. The company wants a solution that preserves least privilege and minimizes ongoing administrative effort. What should the administrator do?
- A
Grant OWNERSHIP on the FINANCE.REPORTING schema to ANALYST_READONLY so the role automatically inherits access to new tables
- B
Grant USAGE on the FINANCE database and REPORTING schema, then grant SELECT on future tables in the FINANCE.REPORTING schema to ANALYST_READONLY
- C
Grant the ANALYST_READONLY role to SYSADMIN so new tables created by SYSADMIN are automatically readable by ANALYST_READONLY
- D
Grant IMPORTED PRIVILEGES on the FINANCE database to ANALYST_READONLY so the role can read objects added later
Show answer and explanation
Correct answer: B
Explanation
In Snowflake RBAC, querying a table requires the active role to have USAGE on the parent database and schema and the appropriate privilege on the object itself, such as SELECT on a table. Granting privileges on existing tables does not automatically apply to tables created later. To address that gap, administrators should use future grants, for example: GRANT SELECT ON FUTURE TABLES IN SCHEMA FINANCE.REPORTING TO ROLE ANALYST_READONLY. This is the most practical solution because it maintains least privilege, avoids over-permissioning with OWNERSHIP, and minimizes recurring manual administration. This behavior is consistent with Snowflake documentation on access control, role hierarchy, and future grants.
- A. Incorrect.
Incorrect. OWNERSHIP is the highest-level privilege on an object and transfers control of the schema. It does not represent least privilege for read-only analysts. Also, granting OWNERSHIP on the schema to a read-only role is excessive and would allow actions such as altering or dropping the schema, which violates the scenario requirement.
- B. Correct.
Correct. To query objects, a role needs USAGE on the database and schema, plus object-level privileges such as SELECT on tables. Because the issue is specifically with newly created tables, the administrator should grant SELECT on future tables in the schema. This aligns with Snowflake RBAC best practices by preserving least privilege and reducing repeated manual grants.
- C. Incorrect.
Incorrect. Granting a role to SYSADMIN changes role hierarchy and inheritance but does not automatically grant object privileges from future tables to ANALYST_READONLY. Access to tables is controlled through explicit grants, including future grants where appropriate. This option confuses role hierarchy with object privilege assignment.
- D. Incorrect.
Incorrect. IMPORTED PRIVILEGES is used primarily for accessing privileges on shared databases, not for standard databases owned within the account. It does not solve access to newly created local tables in a schema. This distractor reflects a common misunderstanding between data sharing privileges and regular RBAC grants.