ALTER SUNDECK FLOW
Allows you to alter one hook at a time. Use these commands to create, replace, place hook in particular order in the flow, enable/disable or drop the hook.
Creates a new PRE or POST hook in the flow:
ALTER SUNDECK FLOW <name>
CREATE [ PRE | POST ] HOOK <hook_instance_name>
{ IF <condition_expression> THEN | ALWAYS }
<hook_instance_type_name>
<hook_configuration>
[ ENABLED ={ TRUE | FALSE } ]
[ { AFTER | BEFORE } <hook_instance_name> ]
Modifies the properties, conditions, or configuration of an existing hook in the flow:
ALTER SUNDECK FLOW <name>
REPLACE [ PRE | POST ] HOOK <hook_instance_name>
{ IF <condition_expression> THEN | ALWAYS }
<hook_instance_type_name>
<hook_configuration>
[ ENABLED = { TRUE | FALSE } ]
Removes hook from the flow.
ALTER SUNDECK FLOW <name>
DROP [ PRE | POST ] HOOK <hook_instance_name>
<name>- Specifies the identifier for the flow to alter. If the identifier contains spaces, special characters, or mixed-case characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive (e.g. “My Object”).
<hook-definition>- An hook instance definition (see hook parameters below).
<hook_instance_name>- The identifier you want to give the hook instance when you are creating a new hook or identifier of a hook that you are replacing. If the identifier contains spaces, special characters, or mixed-case characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive (e.g. “My Object”).
<condition_expression>- The condition expression is a boolean statement that includes one or more conditions along with parentheses, AND, OR, not, etc. Review the conditions reference for a complete overview.
<hook_instance_type_name>- The type of hook to create. Currently available built-in system Pre-hooks and built-in system Post-hooks
<hook_configuration>- A Snowflake object literal that describes the configuration for the provided hook.
ENABLED = { TRUE | FALSE }- Specifies whether hook will be activated or not. If omitted, hook is activated.
{ AFTER | BEFORE } <hook_instance_name>- Specifies position of the created hook in the flow. If no position mentioned, will be added at end of the flow.
Create sundeck flow “Rejections”:
CREATE SUNDECK FLOW "Rejections"
COMMENT 'This flow is used to control access to Snowflake based on criteria'
PRE HOOK "Reject drop statement"
IF sql_contains('DROP') or sql_contains('drop') THEN REJECT {'rejectMessage': 'DROP operations are not allowed'}
ENABLED = FALSE
DEFAULT_WAREHOUSE = "COMPUTE_WH"
;
You realize that flow should be enabled and error message needs to be more accurate. Use ALTER FLOW … REPLACE statement:
ALTER SUNDECK FLOW "Rejections"
REPLACE PRE HOOK "Reject drop statement"
IF sql_contains('DROP') or sql_contains('drop')
THEN REJECT {'rejectMessage': 'DROP statements are not allowed'}
ENABLED = TRUE
;
User vicky should not be allowed to run queries at all, add another hook that will run before any other:
ALTER SUNDECK FLOW "Rejections"
CREATE PRE HOOK "Reject queries from user vicky"
IF user = vicky THEN
REJECT
{'rejectMessage': 'User vicky is not allowed to run queries'}
ENABLED =TRUE
BEFORE "Reject drop statement";
Now let’s see what our flow looks like by describing the flow:
test#COMPUTE_WH@(no database).(no schema)>describe sundeck flow "Rejections";
+------------+-----------------+------------------------+----------------------+--------------------------------------+-----------------------+-------------------------------------------------------------------------------+-------------------+--------------------------------------------------------------------+
| FLOW_NAME | FLOW_IS_DEFAULT | FLOW_DEFAULT_WAREHOUSE | FLOW_HOSTNAME | FLOW_LAST_MODIFIED_AT | FLOW_LAST_MODIFIED_BY | FLOW_DDL | FLOW_RESULTS_PATH | FLOW_COMMENT |
|------------+-----------------+------------------------+----------------------+--------------------------------------+-----------------------+-------------------------------------------------------------------------------+-------------------+--------------------------------------------------------------------|
| Rejections | false | COMPUTE_WH | 08msfw20.dev.sndk.io | 2023-08-28 05:20:10.165505 +0000 UTC | TEST | CREATE OR REPLACE SUNDECK FLOW "Rejections" | NORMAL | This flow is used to control access to Snowflake based on criteria |
| | | | | | | COMMENT 'This flow is used to control access to Snowflake based on criteria' | | |
| | | | | | | PRE HOOK "Reject queries from user vicky" | | |
| | | | | | | IF USER = VICKY | | |
| | | | | | | THEN REJECT {'rejectMessage': 'User vicky is not allowed to run queries'} | | |
| | | | | | | ENABLED = TRUE | | |
| | | | | | | PRE HOOK "Reject drop statement" | | |
| | | | | | | IF SQL_CONTAINS('DROP') OR SQL_CONTAINS('drop') | | |
| | | | | | | THEN REJECT {'rejectMessage': 'DROP statements are not allowed'} | | |
| | | | | | | ENABLED = TRUE | | |
| | | | | | | | | |
| | | | | | | DEFAULT_WAREHOUSE = "COMPUTE_WH" | | |
| | | | | | | RESULTS_PATH = NORMAL | | |
+------------+-----------------+------------------------+----------------------+--------------------------------------+-----------------------+-------------------------------------------------------------------------------+-------------------+--------------------------------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.176s