COF-C03 Question 347
Single answerPublicA Snowflake administrator is reviewing access controls after a security audit. The audit found that several analysts can create temporary tables and stages in every newly created database, even though no explicit grants were given on those databases. The administrator needs to explain why this is happening and identify the best way to prevent it for future databases while preserving least-privilege access. What should the administrator do?
- A
Revoke the relevant privileges from the PUBLIC role on each database and on future databases as they are created.
- B
Drop the PUBLIC role because it is a system-defined convenience role that can be removed when not needed.
- C
Transfer ownership of all databases to SECURITYADMIN so that PUBLIC no longer inherits any access.
- D
Revoke all privileges from SYSADMIN, because PUBLIC inherits object privileges from SYSADMIN by default.
Show answer and explanation
Correct answer: A
Explanation
This question tests understanding of Snowflake's built-in PUBLIC role and the default PUBLIC schema. In Snowflake, PUBLIC is a system-defined role that is automatically granted to every user and role in the account. In addition, standard databases include a PUBLIC schema by default. If privileges such as USAGE or CREATE are granted to PUBLIC on that schema, then all users effectively receive those privileges through the PUBLIC role, even without explicit database-specific grants. The correct response is to review and revoke unnecessary privileges from PUBLIC on existing objects and ensure database provisioning processes do the same for future databases. Best practice is to minimize grants to PUBLIC and instead assign privileges through carefully designed custom roles that follow least privilege. Relevant Snowflake documentation covers system-defined roles, the PUBLIC role, schema privileges, and access control best practices.
- A. Correct.
Correct. The PUBLIC role is a system-defined role automatically granted to every user and every role. Snowflake also grants certain privileges to PUBLIC by default, including privileges on the PUBLIC schema that exists in each database. Because the PUBLIC schema is created automatically in standard databases, users can often create objects there if CREATE privileges remain granted to PUBLIC. To prevent this behavior, an administrator should explicitly revoke unnecessary privileges from the PUBLIC role on existing databases/schemas and incorporate this step into provisioning for future databases.
- B. Incorrect.
Incorrect. The PUBLIC role is a built-in system role in Snowflake and cannot be dropped. It exists in every account and is granted to all users and roles. A common misconception is that PUBLIC behaves like an optional custom role, but it is foundational to Snowflake's access model.
- C. Incorrect.
Incorrect. Ownership transfer changes who controls the object and can grant privileges on it, but it does not automatically remove privileges already granted to PUBLIC. If PUBLIC retains privileges on a schema or database, changing ownership alone will not stop users from using those privileges.
- D. Incorrect.
Incorrect. PUBLIC does not inherit privileges from SYSADMIN. In Snowflake, role hierarchy works through explicit role grants, and PUBLIC is a separate built-in role granted to all users and roles. SYSADMIN is a higher-level administrative role, but its privileges are not implicitly inherited by PUBLIC.