ADA-C01 Question 471
Single answerCreate objectsA Snowflake administrator is onboarding a new analytics team. The team must be able to create transient tables and secure views in a new schema named ANALYTICS in database PROD, but they must not be able to create new schemas in PROD or grant privileges to other roles. The administrator has already created a custom role named ANALYTICS_DEV and granted it USAGE on database PROD. Which additional privilege configuration best meets these requirements while following least privilege?
- A
Grant CREATE SCHEMA on database PROD to ANALYTICS_DEV, and grant OWNERSHIP on schema PROD.ANALYTICS to ANALYTICS_DEV
- B
Grant USAGE on schema PROD.ANALYTICS to ANALYTICS_DEV, and grant CREATE TABLE and CREATE VIEW on schema PROD.ANALYTICS to ANALYTICS_DEV
- C
Grant ALL PRIVILEGES on schema PROD.ANALYTICS to ANALYTICS_DEV so the team can create all required objects without additional grants
- D
Grant USAGE on schema PROD.ANALYTICS to ANALYTICS_DEV, grant CREATE TABLE and CREATE VIEW on schema PROD.ANALYTICS to ANALYTICS_DEV, and grant MANAGE GRANTS on account to ANALYTICS_DEV
Show answer and explanation
Correct answer: B
Explanation
The best answer is to grant USAGE on the existing schema plus the specific CREATE privileges required for the object types the team needs to create. In Snowflake access control, creating objects in a schema requires USAGE on the parent database and schema, along with the object-specific CREATE privilege on the schema. For this scenario, CREATE TABLE covers standard, temporary, and transient tables because transient is specified in the CREATE TABLE statement rather than controlled by a distinct privilege. Similarly, CREATE VIEW covers secure views because SECURE is a property of the CREATE VIEW statement, not a separate privilege.
The other options are incorrect because they either grant unnecessary authority or violate explicit requirements. CREATE SCHEMA on the database would allow new schema creation. OWNERSHIP and ALL PRIVILEGES exceed least-privilege guidance. MANAGE GRANTS is an account-level administrative privilege inappropriate for a development team that must not delegate access.
This aligns with Snowflake documentation on access control and object privileges: roles need USAGE on parent containers and CREATE
- A. Incorrect.
Incorrect. CREATE SCHEMA on the database would allow the role to create new schemas in PROD, which explicitly violates the requirement. Granting OWNERSHIP on the schema is also excessive because OWNERSHIP is the highest privilege and would effectively allow broad control over the schema, including privilege management depending on grant paths. This does not follow least privilege.
- B. Correct.
Correct. To create tables or views in an existing schema, the role needs USAGE on the database and schema, plus the relevant CREATE privileges on the schema. In Snowflake, transient tables are created with CREATE TABLE privilege; transient is a table type specified at creation time, not a separate privilege. Secure views are created with CREATE VIEW privilege; SECURE is also a property of the view definition, not a separate privilege. This configuration allows object creation in the existing schema without permitting schema creation or privilege delegation.
- C. Incorrect.
Incorrect. ALL PRIVILEGES on a schema is broader than necessary and may include privileges beyond the stated requirement, such as creating other object types. It also does not align with least-privilege design. Exam questions often test whether candidates can avoid overly broad grants when narrower object-creation privileges are sufficient.
- D. Incorrect.
Incorrect. The USAGE and CREATE TABLE/CREATE VIEW grants on the schema are appropriate, but MANAGE GRANTS on the account is far too powerful. MANAGE GRANTS enables centralized grant management across objects and is not needed for the team to create transient tables and secure views. It directly conflicts with the requirement that the team must not be able to grant privileges to other roles.