SnowPro Advanced: Security Engineer Question 396
Single answerEstablish roles and permissions to ensure services access Snowflake data securelyA company runs a managed ingestion service that loads files from an internal stage into tables in the RAW database every 15 minutes by using a dedicated Snowflake service user. The security team requires that the service follow least-privilege access and must not inherit broad permissions from human administrator roles. The data engineering team also wants to avoid granting unnecessary access to future schemas and tables that the service does not use. Which approach best meets these requirements?
- A
Grant the service user the SYSADMIN role so ingestion jobs can create and load objects as needed, and rely on network policies to limit misuse.
- B
Create a dedicated custom role for the ingestion service, grant only USAGE on the required warehouse, database, and schema, plus the specific object privileges needed on the stage and target tables; then grant that role directly to the service user.
- C
Grant the service user the PUBLIC role and assign OWNERSHIP on the RAW database so the service can manage all current and future objects without additional grants.
- D
Create a custom role for the service user, but grant privileges to that role through a human-operated functional role that is already assigned to data engineers, so administration stays centralized.
Show answer and explanation
Correct answer: B
Explanation
The best answer is to create a dedicated custom role for the service and grant only the exact privileges needed for the ingestion workflow. In Snowflake, secure service access is typically implemented by assigning a machine identity its own role hierarchy or, preferably, a tightly scoped standalone role rather than reusing broad administrative or human functional roles. For this scenario, the service commonly needs USAGE on the virtual warehouse, database, and schema, plus object-level privileges required for the internal stage and target tables. Depending on the load pattern, that may include INSERT on target tables and appropriate privileges on stages and file formats if used. The key design goal is least privilege, role isolation, and avoidance of broad inherited access. This aligns with Snowflake access control best practices documented for role-based access control, system roles, and object privilege grants.
- A. Incorrect.
Incorrect. Granting SYSADMIN to a service user violates least-privilege principles because SYSADMIN is a powerful administrative role intended for broad object management across the account. Even if network policies are applied, they do not reduce the excessive authorization scope of the role itself. The misconception is that perimeter controls can compensate for overprivileged role design; in Snowflake, role-based access should still be narrowly scoped.
- B. Correct.
Correct. A dedicated custom role for the service aligns with least-privilege and separation-of-duties best practices. The role should receive only the minimum privileges required for the workload, such as USAGE on the warehouse, database, and schema, and the necessary privileges on the internal stage and target tables. Granting that dedicated role directly to the service user avoids accidental inheritance from broad human roles and limits exposure to only the objects the service actually needs.
- C. Incorrect.
Incorrect. The PUBLIC role is granted to every user and role, so it should not be used as the primary mechanism for sensitive service access. Granting OWNERSHIP on the RAW database is far too broad and would allow the service to control all objects in that database, including future objects, which directly conflicts with the requirement to avoid unnecessary access. A common misconception is that service automation needs ownership to function reliably; most ingestion workloads require only specific usage and DML-related privileges.
- D. Incorrect.
Incorrect. Although creating a custom role sounds appropriate, making the service role inherit privileges through a human-operated functional role undermines the requirement that the service must not inherit broad permissions from administrator or user roles. This increases the risk that future grants intended for engineers would also flow to the service account. Best practice is to isolate machine identities in dedicated roles that are managed independently from human access paths.