COF-C03 Question 27
Single answerSchemasA data engineering team is building a new analytics environment in Snowflake. They want analysts to create and modify tables in a dedicated schema without affecting production objects, while still allowing analysts to query reference tables stored in another schema in the same database. The security team also wants object ownership and privileges to be managed at the schema level as much as possible to reduce administrative overhead. Which action should the Snowflake administrator take to best meet these requirements?
- A
Create a separate database for analysts and grant them OWNERSHIP on the database so they can manage all schemas and objects they need.
- B
Create a dedicated schema for analysts, grant USAGE on the database and schema, and grant CREATE TABLE on that schema. Also grant USAGE on the reference schema and SELECT on its tables.
- C
Grant analysts IMPORTED PRIVILEGES on the database so they can create tables in one schema and query tables in another schema.
- D
Grant analysts CREATE TABLE on the database and SELECT on the reference schema so they can create objects anywhere they have access.
Show answer and explanation
Correct answer: B
Explanation
Snowflake privilege management for schemas follows a layered model. For object creation inside a schema, the role typically needs USAGE on the database, USAGE on the schema, and a schema-level CREATE privilege for the object type, such as CREATE TABLE. For querying objects in another schema, the role needs USAGE on the database, USAGE on that schema, and object-level privileges such as SELECT on the specific tables or views. This makes schemas a practical boundary for separating development or analyst-managed objects from production or shared reference objects. Using schema-level privileges supports least-privilege access and reduces administrative overhead compared with giving broad database ownership. These patterns align with Snowflake documentation on access control privileges and securable objects.
- A. Incorrect.
Incorrect. Granting OWNERSHIP on the database gives far broader control than required and increases risk, because database ownership enables extensive management capabilities across contained schemas and objects. This does not align with the goal of limiting changes to a dedicated schema and managing privileges with least privilege.
- B. Correct.
Correct. To create objects in a schema, a role needs USAGE on the parent database, USAGE on the schema, and the appropriate CREATE privilege on that schema, such as CREATE TABLE. To query objects in another schema, the role needs USAGE on the database, USAGE on the referenced schema, and SELECT on the tables or views. This approach isolates analyst-created objects to a dedicated schema while allowing controlled read access to reference data in another schema.
- C. Incorrect.
Incorrect. IMPORTED PRIVILEGES is used for certain shared databases and similar access patterns, not as a general mechanism for creating tables across schemas in a regular database. It does not replace schema-level CREATE privileges for standard object creation.
- D. Incorrect.
Incorrect. CREATE TABLE is granted at the schema level, not at the database level, for standard table creation. Also, SELECT is granted on tables or views, not on a schema itself. Someone might choose this option if they confuse database-level and schema-level privileges.