Usability Helpers

The USABILITY_HELPERS Pre-hook is a package of preconfigured usability helpers for Snowflake SQL, which can be individually enabled/disabled via configuration.

USABILITY_HELPERS {
   'oopsProtection'      : <boolean-value>,
   'showRecursiveGrants' : <boolean-value>,
   'supportBackTicks'    : <boolean-value>
}

<boolean-value>

TRUE enables the helper, FALSE disables it.

To create a Sundeck flow with all helpers enabled:

CREATE OR REPLACE SUNDECK FLOW "Usability helpers"
  PRE HOOK helpers_hook
  IF user = TEST THEN
  USABILITY_HELPERS {'oopsProtection': true, 'showRecursiveGrants': true, 'supportBackTicks': true}
;

Rejects DELETE or UPDATE statements without WHERE clauses.

Helps avoid situations situations where users accidentally delete or update every row in a table.

This protection may be overridden by adding the --FORCE comment directive to the query.

Example:

test#COMPUTE_WH@TEST.PUBLIC>delete from t1;
001003 (42000): Please qualify your delete to limit which records you modify or add –-FORCE to execute this operation

test#COMPUTE_WH@TEST.PUBLIC>delete from t1; --FORCE
+------------------------+                                                      
| number of rows deleted |
|------------------------|
|                      0 |
+------------------------+
0 Row(s) produced. Time Elapsed: 0.934s

Adds extra syntax for displaying all grants (direct and transitive) for a particular role.

The full syntax is SHOW RECURSIVE GRANTS OF ROLE <rolename>.

Example:

test#COMPUTE_WH@(no database).(no schema)>SHOW RECURSIVE GRANTS OF ROLE PUBLIC;
+--------------+---------------+------------+-----------------------+--------+-----------------------+
| GRANTEE_NAME | PRIVILEGE     | GRANTED_ON | DB                    | SCHEMA | NAME                  |
|--------------+---------------+------------+-----------------------+--------+-----------------------|
| PUBLIC       | USAGE         | ACCOUNT    | NULL                  | NULL   | BMB19393              |
| PUBLIC       | OPERATE       | WAREHOUSE  | NULL                  | NULL   | COMPUTE_WH            |
| PUBLIC       | USAGE         | WAREHOUSE  | NULL                  | NULL   | COMPUTE_WH            |
| PUBLIC       | CREATE SCHEMA | DATABASE   | TEST                  | NULL   | TEST                  |
| PUBLIC       | USAGE         | DATABASE   | SNOWFLAKE_SAMPLE_DATA | NULL   | SNOWFLAKE_SAMPLE_DATA |
+--------------+---------------+------------+-----------------------+--------+-----------------------+

Allows the use of backticks (`) to execute arbitrary SQL statements and use their results in the current query.

Similar to behavior of backticked expressions in bash scripting; SQL commands enclosed in backticks are executed first, and then made available to the current query as if they were a table.

SupportBackTicks:

test#COMPUTE_WH@(no database).(no schema)>SELECT $1 as user_name FROM `show users`;
+-------------------------+
| USER_NAME               |
|-------------------------|
| MONITOR                 |
| ROOT                    |
| SNOWFLAKE               |
| TEST                    |
| UBE                   |
+-------------------------+