SnowPro Associate: Platform Question 153
Single answer○ Use the PUBLIC SCHEMAA data engineering team created a new database named ANALYTICS for a reporting project. When analysts connect to Snowflake and run CREATE TABLE monthly_sales (...); without specifying a schema, the table is being created in ANALYTICS.PUBLIC. The security team wants to reduce accidental object creation in the default shared schema while still allowing analysts to query approved tables. Which action should the Snowflake administrator take to best meet this requirement?
- A
Revoke CREATE on schema ANALYTICS.PUBLIC from the analyst role, and grant USAGE on the schema plus SELECT on approved tables.
- B
Drop the PUBLIC schema from the ANALYTICS database so users must create their own schemas before creating objects.
- C
Rename the PUBLIC schema so Snowflake no longer uses it as the default schema for new sessions.
- D
Grant OWNERSHIP on ANALYTICS.PUBLIC to the analyst role so object creation can be controlled at the user level.
Show answer and explanation
Correct answer: A
Explanation
In Snowflake, each database includes standard schemas such as PUBLIC, and users often end up creating objects there when they omit the schema name or when PUBLIC is the active schema in context. From a governance and least-privilege perspective, the recommended approach is to control privileges on PUBLIC rather than rely on naming changes or elevated ownership. To let analysts query approved objects without creating new ones in the shared schema, administrators should grant the minimum required access: USAGE on the database and schema, plus SELECT on specific tables or views, while revoking CREATE privileges on the PUBLIC schema. This aligns with Snowflake access control best practices and the documented behavior of default database/schema context and schema-level privileges.
- A. Correct.
Correct. PUBLIC is a standard schema created automatically in each database and is commonly the default schema used when no other schema is specified. To prevent accidental object creation there, the administrator should remove object-creation privileges such as CREATE TABLE from the analyst role on ANALYTICS.PUBLIC. Analysts can still access approved data by retaining USAGE on the database and schema and receiving SELECT on the specific tables or views they need.
- B. Incorrect.
Incorrect. PUBLIC is a built-in schema that exists in each database. The practical control is privilege management, not dropping the schema as a normal governance approach. The requirement is to prevent accidental object creation while preserving read access, which is accomplished by revoking CREATE privileges rather than removing the schema.
- C. Incorrect.
Incorrect. PUBLIC is a standard schema name automatically created with a database. Renaming it is not the intended or typical mechanism for controlling where objects are created. Even if a different current schema were set for sessions, that would not by itself enforce least privilege or prevent creation unless CREATE privileges were also removed.
- D. Incorrect.
Incorrect. Granting OWNERSHIP would give the analyst role the highest level of control over the schema, including the ability to manage privileges and objects. That is the opposite of the stated requirement to reduce accidental creation in the shared default schema.