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 policy 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 policy 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.
-
Enable the
SqlUserDefinedPatternCheck
check by running the following command in the CLI:liquibase checks enable --check-name=SqlUserDefinedPatternCheck
-
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. -
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.
- Set your
SEARCH_STRING
(options: a string, or a valid regular expression).
In this example we will usetbl_
. -
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>.
-
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.
-
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
toadmin
.Your filter is successfully set. Now your new policy 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
|
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:
|
liquibase checks enable --check-name=SqlUserDefinedPatternCheck
example output
Customization complete. Review the table below to confirm your changes.
...
+-------------------------------------+------------+----------+----------+--------------------------------+--------------------------------+
| SqlUserDefinedPatternCheck | changelog | disabled | 0 | SEARCH_STRING = null | This check scans SQL for the |
| | | | | MESSAGE = A match for regular | presence of specific patterns |
| | | | | expression <SEARCH_STRING> was | in specified changelog paths, |
| | | | | detected in Changeset | and warns the user when they |
| | | | | <CHANGESET>. | are found. |
| | | | | STRIP_COMMENTS = true | |
| | | | | PATH_FILTER_REGEX = null | |
+-------------------------------------+------------+----------+----------+--------------------------------+--------------------------------+
Liquibase command 'checks enable' was executed successfully.