Snowflake SQL_POLICY

This policy item executes a SQL statement on the DBMS being audited and evaluates the resulting columns returned from the SQL result.

Usage

Copy 
<custom_item>
  type: SQL_POLICY
  description: ["description"]
  sql_request: ["sql statement to run"]
  sql_types: [STRING|REGEX|INTEGER][,....]
  sql_expect: ["text"|"regex"|number]
  (optional) match_all : [YES|NO]
  (optional) match_case : [YES|NO]
  (optional) num_rows : [number|range]
</custom_item>

sql_request

The SQL statement that is queried against the DBMS.

sql_types

A comma separate list of the types for the columns that are returned from the SQL query. The values that are allowed are:

  • STRING—Text based results.
  • REGEX—Text based results, evaluated by a regular expression.
  • INTEGER—Numeric based results, including the use of a range as designated by [MIN..MAX].
  • NULL—NULL value return.
  • STRING_OR_NULL—Text based results or NULL value.
  • REGEX_OR_NULL—Text based results, evaluated by a regular expression or NULL value.
  • INTEGER_OR_NULL—Numeric based results or NULL value.

sql_expect

A comma separated list of the values, or regular expression, to evaluate the results from the SQL query.

The values for each of the columns must match the types that are defined in the sql_types. With this, text values need to be wrapped in double quotes (") and numbers do not need the double quotes.

A special value of NO_ROWS_RETURNED can be used as the only expected value to match on cases that no rows are returned. This is more explicit than using a check_option as described below.

Note: The number of sql_expect must match the number of sql_types, with the exception of NO_ROWS_RETURNED expect.

match_all

(Optional) Setting match_all to YES requires the expectation to match all rows of query result, and not just a row. If match_all is set to the default of NO, only one row must match for the check to pass.

match_case

(Optional) Setting match_case to YES makes all the column comparisons to be case sensitive. If match_case is set to the default of NO, the comparison is case insensitive.

num_rows

(Optional) Setting num_rows to an integer value allows the check to pass if that number of rows is returned, and fail if more or less rows are returned. Not setting num_rows should not affect the result based on the number of rows returned. A variable number of rows can be evaluated by specifying a range in the form of [MIN..MAX].

Example

Copy 
<custom_item>
  type        : SQL_POLICY
  description : "SQL4-00-037700 - SQL generates Trace/Audit records for all activities or other sys-level access - DATABASE_CHANGE_GROUP"
  sql_request : "SELECT audit_action_name, audited_result FROM sys.server_audit_specification_details WHERE server_specification_id =(SELECT server_specification_id FROM sys.server_audit_specifications) AND audit_action_name = 'DATABASE_CHANGE_GROUP';"
  sql_types   : STRING, REGEX
  sql_expect  : "DATABASE_CHANGE_GROUP", "(SUCCESS|SUCCESS AND FAILURE)"
</custom_item>