OPERATE. In Snowflake, to pause or resume a pipe, theOPERATEprivilege is required on the pipe object.
TheOPERATEprivilege allows users to perform operational tasks on specific objects such as pipes, tasks, and streams. Specifically, for a pipe, theOPERATEprivilege enables the user to execute theALTER PIPE ... SET PIPE_EXECUTION_PAUSED=TRUEorALTER PIPE ... SET PIPE_EXECUTION_PAUSED=FALSEcommands, which are used to pause or resume the pipe, respectively.
Here's a step-by-step explanation and reference:
Understanding Pipe Operations:Pipes in Snowflake are used for continuous data loading from staging areas into Snowflake tables. Managing pipes involves operations such as creating, monitoring, pausing, and resuming.
Privileges for Pipe Operations:TheOPERATEprivilege is essential for pausing and resuming pipes. This privilege is more specific than general object access privileges likeSELECTorUSAGEand is tailored for operational control.
Granting the OPERATE Privilege:To grant theOPERATEprivilege on a pipe, an administrator or a user with the necessary grants can execute the SQL command:
GRANTOPERATEONPIPE <pipe_name>TOROLE <role_name>;
Pausing and Resuming Pipes:Once theOPERATEprivilege is granted, the user or role can pause the pipe using:
ALTERPIPE <pipe_name>SETPIPE_EXECUTION_PAUSED=TRUE;
To resume the pipe, they use:
ALTERPIPE <pipe_name>SETPIPE_EXECUTION_PAUSED=FALSE;
Reference:For more information on pipe operations and privileges, you can refer to Snowflake's official documentation on pipes and security access control: https://docs.snowflake.com/en/sql-reference/sql/grant- privilege.html and https://docs.snowflake.com/en/user-guide/data-pipelines-intro.html#managing-pipes
