SqlUserDefinedPatternCheck

This check scans SQL for the presence of specific patterns and warns the user when they are found.

Uses

Use the check to notify you when your custom specific patterns in the changelog are found. This quality check is an "open-ended" check which you can copy and customize for as many custom patterns, defined as simple strings or regex, as your naming, security, compliance, and other policies require. For example, if your polices require all tables to begin with tbl_ or indexes to end with _id, this check is easy copied and customized into two checks with those patterns. If you are handy with regex, the limit for pattern finding is greatly expanded. This quality check, like other checks, can be configured with a severity level which returns an exit code designed to stop automated jobs, giving your team time to investigate your user-defined patterns before they are deployed.

Run the SqlUserDefinedPatternCheck check

Prerequisites: To run the SqlUserDefinedPatternCheck check, ensure you have a Liquibase Pro license key and the checks-scope property includes changelog. The checks-scope parameter is set in the default properties file, environment variable, or any standard method. Then, check the changelog to see if SqlUserDefinedPatternCheck is enabled.

1. Enable the SqlUserDefinedPatternCheck check by running the following command in the CLI:

   liquibase checks enable --check-name=SqlUserDefinedPatternCheck

2. You will see this message in the CLI:

   This check can not be enabled directly because one or more fields does not have a default value.  Create a copy of this check and initiate the customization workflow.
   
   Give your check a short name for easier identification (up to 64 alpha-numeric characters only) [SqlUserDefinedPatternCheck1]:
   

   In this example we will use NoGrantAdminInGrantsPath as our short name.

3. You will see this message in the CLI:

   New check 'NoGrantAdminInGrantsPath' created from 'SqlUserDefinedPatternCheck'
   
   
   
   Set the Severity to return a code of 0-4 when triggered. (options: 'INFO'|0, 'MINOR'|1, 'MAJOR'|2, 'CRITICAL'|3, 'BLOCKER'|4)? [INFO]:

   In this example, we will set the severity to 3.

4. Set your SEARCH_STRING (options: a string, or a valid regular expression).
   In this example we will use tbl_.

5. Set the message you want to display when the search string is found if you would like it to be something other than the default message.
   Default message:

   A match for regular expression <SEARCH_STRING> was detected in Changeset <CHANGESET>.

6. Set STRIP_COMMENTS if you would like to remove any text (options: true, false).

   In this example we will set it to false because removing comments is not necessary in this example.

7. Optional

   Tip: In Liquibase 4.27.0+ you can choose to enable the PATH_FILTER option. This can be used to filter all changesets in changelogs in a specific directory that contain a specified string or regex.

   You will see this message in the CLI:

   Set 'PATH_FILTER_REGEX':

   In this example we will set the PATH_FILTER_REGEX to admin.

   Your filter is successfully set. Now your new quality check will look for all changelogs that include the string admin and notify you when it finds any that fit the filter criteria.

   The sqlUserDefinedPatternCheck is enabled and executed successfully.

Short Name	Scope	Enabled	Severity	Customization	Description
SqlUserDefinedPatternCheck	changelog	false	Default value: 0 'INFO' | 0 'MINOR' | 1 'MAJOR' | 2 'CRITICAL' | 3 'BLOCKER' | 4	SEARCH_STRING = null MESSAGE = A match for regular expression <SEARCH_STRING> was detected in Changeset <CHANGESET>. Strip_COMMENTS = true	This check scans SQL for the presence of specific patterns and warns the user when they are found.

Configurable Filter Option	Value	Path
PATH_FILTER_REGEX	String or Regex	Relative Compatible with: S3 and GitHub paths include/includeAll changelogs changelogs using relative paths