COF-C03 Question 166
Single answerCustom rolesA Snowflake administrator is redesigning access control for a finance analytics team. The team needs read-only access to existing and future tables and views in the FINANCE_DB.REPORTING schema. They must not be able to create objects, modify grants, or access other schemas in the database. The administrator wants to follow least-privilege and make ongoing maintenance minimal. Which action should the administrator take?
- A
Create a custom role, grant USAGE on FINANCE_DB and the REPORTING schema, grant SELECT on all existing tables and views in the schema, and grant SELECT on future tables and views in the schema to that role.
- B
Grant the built-in SYSADMIN role to the finance analytics team and rely on object ownership to prevent changes.
- C
Create a custom role, grant OWNERSHIP on the REPORTING schema, and grant that role to the finance analytics team so they can inherit access to all current and future objects.
- D
Create a custom role, grant USAGE on FINANCE_DB only, and grant SELECT on all current and future tables in the REPORTING schema without granting schema USAGE.
Show answer and explanation
Correct answer: A
Explanation
In Snowflake RBAC, custom roles are commonly used to implement least-privilege access for job functions. For read-only access to objects in a schema, the required pattern is: grant USAGE on the database, grant USAGE on the schema, and grant SELECT on the relevant tables and views. To reduce maintenance, administrators can grant privileges on future objects in the schema, such as future tables and future views, so the role automatically receives access as new reporting objects are created. Built-in roles like SYSADMIN are too broad for this requirement, and OWNERSHIP is inappropriate because it grants full control rather than read-only access. This approach is consistent with Snowflake documentation on access control, role-based access control, and future grants.
- A. Correct.
Correct. A custom role is the appropriate least-privilege approach. To query objects in a schema, the role needs USAGE on the database and schema, plus SELECT on the tables/views. Granting SELECT on all existing tables and views covers current objects, while granting SELECT on future tables and views minimizes ongoing administration for new objects created later. This aligns with common Snowflake RBAC best practices.
- B. Incorrect.
Incorrect. SYSADMIN is a high-level system-defined role intended for broad object management, not limited read-only access. Granting SYSADMIN violates least-privilege and would provide far more access than required. Object ownership would not adequately constrain a role that already has extensive administrative capabilities.
- C. Incorrect.
Incorrect. OWNERSHIP is the highest privilege on an object and allows full control, including grant management and object changes. Granting OWNERSHIP on the schema would directly conflict with the requirement that the team must not create objects or modify grants. It is much broader than necessary for read-only reporting access.
- D. Incorrect.
Incorrect. Database USAGE alone is not enough. A role also needs USAGE on the schema to resolve and access objects within it. In addition, the scenario requires access to both tables and views, while this option only mentions tables. This is a common misconception when configuring object access in Snowflake.