SnowPro Advanced: Security Engineer Question 395
Single answerEstablish roles and permissions to ensure services access Snowflake data securelyA company uses a third-party ETL service to load customer transaction files into Snowflake every 15 minutes. The security team requires that the service be able to connect programmatically, use only a dedicated warehouse, and write data only into tables in the RAW.INGEST schema. The service must not be able to read sensitive curated data, manage users or roles, or create objects outside the target schema. Which design best meets these requirements using Snowflake best practices?
- A
Create a dedicated service user and a custom role. Grant the role USAGE on the database, schema, and warehouse; grant INSERT on the target tables in RAW.INGEST; grant the role to the service user; and set the custom role as the user's default role.
- B
Create a dedicated service user and assign the SYSADMIN role. Restrict access by setting the default warehouse to the ETL warehouse and relying on the ETL tool to write only to the RAW.INGEST schema.
- C
Create a dedicated service user and grant OWNERSHIP on the RAW database and ETL warehouse so the ETL process can manage permissions on any new tables it needs to load.
- D
Create a dedicated service user and grant the PUBLIC role SELECT on curated schemas, then grant INSERT on RAW.INGEST tables through a secondary custom role so the ETL tool can validate source-to-target mappings during loads.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to create a dedicated service user with a dedicated custom role that has only the minimum privileges required for the ETL task. In Snowflake RBAC, services should not use broad administrative roles such as SYSADMIN, and permissions should be granted directly to roles rather than relying on tool-side restrictions. For writing to existing tables, the service typically needs USAGE on the warehouse, database, and schema, plus INSERT on the target tables. If additional operations are required in a real deployment, such as creating stages or file formats, those should be granted explicitly and only as needed. Snowflake security best practices emphasize least privilege, role-based access control, avoidance of overuse of PUBLIC, and use of dedicated identities for programmatic access. Relevant Snowflake documentation includes access control and RBAC guidance, system-defined roles, and object privilege requirements for databases, schemas, warehouses, and tables.
- A. Correct.
Correct. This follows least-privilege design for service access in Snowflake. A dedicated service principal should receive only the minimum privileges needed: USAGE on the warehouse, database, and schema so objects can be resolved, plus INSERT on the specific target tables it must load. Assigning a custom role to the service user avoids use of broad system roles such as SYSADMIN. Setting it as the default role supports controlled programmatic access and reduces the chance that a more privileged role is used unintentionally.
- B. Incorrect.
Incorrect. SYSADMIN is a powerful system-defined role intended for broad object management, not narrowly scoped service access. Setting a default warehouse does not restrict what data the role can access. Relying on the ETL tool's behavior is not a security control; Snowflake RBAC should enforce access boundaries.
- C. Incorrect.
Incorrect. OWNERSHIP is the highest privilege on an object and allows full control, including grant management. Granting OWNERSHIP on the database is excessive for a loading service and violates least privilege. It would allow the service to manage or transfer privileges and potentially create or alter objects well beyond the required scope.
- D. Incorrect.
Incorrect. Granting SELECT on curated schemas through PUBLIC exposes sensitive data broadly because PUBLIC is granted to every user and role in the account. This directly conflicts with the requirement that the service must not read curated data. Using PUBLIC for sensitive access is a common RBAC mistake.