COF-C03 Question 138
Single answerRole-Based Access Control (RBAC)A company uses Snowflake to manage data for multiple business units. The SECURITYADMIN role has created a custom role named ANALYST_RL and granted it SELECT on the FINANCE_DB.REPORTING schema and its existing tables. Several users are then granted ANALYST_RL. A week later, a new table named MONTH_END_CLOSE is created in the same schema, and users with ANALYST_RL report that they cannot query it. The company wants future tables in the REPORTING schema to be queryable by ANALYST_RL without requiring manual grants each time, while still following Snowflake RBAC best practices. Which action should be taken?
- A
Grant OWNERSHIP on the REPORTING schema to ANALYST_RL so it automatically inherits access to all future tables
- B
Grant USAGE on the FINANCE_DB database to all users directly, because database-level privileges automatically allow querying new tables in child schemas
- C
Grant SELECT on future tables in schema FINANCE_DB.REPORTING to ANALYST_RL, and ensure ANALYST_RL also has the required USAGE privileges on the database and schema
- D
Grant the SYSADMIN role to ANALYST_RL so it inherits access to any objects created under FINANCE_DB.REPORTING
Show answer and explanation
Correct answer: C
Explanation
This scenario tests a common Snowflake RBAC pattern: privileges granted on existing objects do not automatically apply to future objects unless future grants are explicitly configured. The correct solution is to grant SELECT on future tables in the schema to the custom role. To query objects successfully, the role also needs USAGE on the containing database and schema. Snowflake best practice is to grant privileges to roles, then assign roles to users, rather than granting privileges directly to users. This aligns with Snowflake documentation on access control, future grants, and least-privilege role design.
- A. Incorrect.
Incorrect. OWNERSHIP is the highest privilege on an object and transfers control of the schema, which violates least-privilege principles for analyst access. It also is not the recommended way to ensure read access to future tables. In Snowflake, access to future objects is typically managed with future grants, such as granting SELECT on future tables in a schema to a role.
- B. Incorrect.
Incorrect. Users need more than a database-level privilege to query tables. Querying a table requires appropriate privileges along the path, including USAGE on the database, USAGE on the schema, and SELECT on the table. Granting privileges directly to users is also discouraged compared to granting roles to users under Snowflake RBAC best practices.
- C. Correct.
Correct. In Snowflake, grants on existing tables do not automatically apply to tables created later unless future grants are defined. To allow ANALYST_RL to query newly created tables in FINANCE_DB.REPORTING, the administrator should grant SELECT on future tables in that schema to the role. The role must also have USAGE on the parent database and schema, because object access depends on having the necessary container-level privileges as well.
- D. Incorrect.
Incorrect. SYSADMIN is a powerful system-defined role intended for managing objects, not for end-user read access. Granting SYSADMIN to an analyst role violates the principle of least privilege and is not how future table access should be implemented. Role hierarchy can simplify administration, but it should not be used to overprivilege analyst roles.