A Data Engineer is designing a near real-time ingestion pipeline for a retail company to ingest event logs into Snowflake to derive insights. A Snowflake Architect is asked to define security best practices to configure access control privileges for the data load for auto-ingest to Snowpipe.
What are the MINIMUM object privileges required for the Snowpipe user to execute Snowpipe?
To configure access control privileges for Snowpipe, the Snowpipe user needs OWNERSHIP on the named pipe to ensure full control over the pipe. USAGE on the named stage, target database, and schema allows the user to access these objects. INSERT and SELECT on the target table are required for writing and querying data, respectively. Therefore, the minimum required object privileges are OWNERSHIP on the named pipe, USAGE on the named stage, target database, and schema, and INSERT and SELECT on the target table.
Pipe does not have USAGE privilege and internal stage does not have READ privilege so has to be A
Sorry forgot to say pipe does not have CREATE privilege either. https://docs.snowflake.com/en/user-guide/security-access-control-privileges
Ans is A: -- Create a role to contain the Snowpipe privileges use role securityadmin; create or replace role snowpipe1; -- Grant the required privileges on the database objects grant usage on database snowpipe_db to role snowpipe1; grant usage on schema snowpipe_db.public to role snowpipe1; grant insert, select on snowpipe_db.public.mytable to role snowpipe1; grant usage on stage snowpipe_db.public.mystage to role snowpipe1; -- Grant the OWNERSHIP privilege on the pipe object grant ownership on pipe snowpipe_db.public.mypipe to role snowpipe1; -- Grant the role to a user grant role snowpipe1 to user jsmith; -- Set the role as the default role for the user alter user jsmith set default_role = snowpipe1;
So, it would be an internal stage by the USAGE on stage. This is the answer that makes the most sense, thank you!
Answer is A. OWNERSHIP on the named pipe, USAGE on the named stage, target database, and schema, and INSERT and SELECT on the target table
https://docs.snowflake.com/en/user-guide/data-load-snowpipe-intro#owning-pipes
To view details about the pipe, you must use a role with the MONITOR or OWNERSHIP privilege on the pipe and the USAGE privilege on both the database and schema that contain the pipe. See Pipe privileges for more details. https://docs.snowflake.com/user-guide/data-load-snowpipe-manage
A is the answer
Answer is D and is validated
it doesn't exist a GRANT USAGE on PIPE https://docs.snowflake.com/en/user-guide/security-access-control-privileges#pipe-privileges As it is about auto-ingest, it's not possible with internal STAGE, so it is not B or C as READ grant is only for internal STAGE Thus, it it response D
C https://docs.snowflake.com/en/user-guide/data-load-snowpipe-intro#creating-pipes