Verify naming conventions in your database
The Multiple Objects Policy Checks feature allows you to verify whether objects in your database adhere to naming conventions you specify. You can use it to test multiple types of objects at the same time, rather than creating individual checks for each one.
The name of the multiple objects check depends on what kind of search you want to make:
- ObjectNameMustMatch specifies a positive search.
- ObjectNameMustNotMatch specifies a negative search.
Liquibase supports this check on the following objects:
TABLECOLUMNSEQUENCE
Using the multiple objects check
-
Create a new multiple objects check with the following command:
liquibase checks copy --check-name=[ObjectNameMustMatch|ObjectNameMustNotMatch] -
Liquibase prompts you to provide a short name. A short name is a descriptive name that indicates what you want the check to search for in the database or changelog.
In this example we will provideObjectNameMustMatchMountainTimeZoneinUSas the short name because we will be searching for any instances of any objects with Mountain Time Zone that are within the US. You are limited to 64 alpha-numeric characters only in the short name.
The CLI will indicate that the new check was created successfully.: -
Set the Severity to return a code of 0-4 when triggered.
Options:'INFO'=0, 'MINOR'=1, 'MAJOR'=2, 'CRITICAL'=3, 'BLOCKER'=4 -
Set the operator to any of the below options. In this example we will use
CONTAINS: MTto search for any instance of Mountain time zone. Options: Set the search string to a string or valid regular expression. In this example we will search for the
US statesstring to find any US states in the Mountain Time Zone.Options:
a string, or a valid regular expression) : helloworldSet the
OBJECT_TYPESto check for and separate them by commas. In this example we will useSEQUENCE.Options:
TABLE, COLUMN, SEQUENCE) : table,columnSet the case sensitivity to true or false in the CLI:
Set 'CASE_SENSITIVE' (options: true, false) [true]:To validate your changelog with the multiple objects check, run the following command:
liquibase checks run
New check 'ObjectNameMustMatchMountainTimeZone' created from 'ObjectNameMustMatch'
STARTS_WITH, ENDS_WITH, CONTAINS, REGEXP) [STARTS_WITH]:
Multiple objects check attributes
| Parameter | Type | Description | Customization | Default |
|---|---|---|---|---|
OPERATOR
|
String/enum |
The location to look for the provided |
|
|
SEARCH_STRING
|
String/enum |
The substring or regular expression to match with the one in the changelog file. Cannot be empty. |
Must be |
None |
CASE_SENSITIVE
|
String/enum | Set how the SearchString is processed |
User entry of When |
|
Note: Liquibase uses the java.util.regex engine to match regular expressions.
Multiple checks example output
Starting Liquibase at 14:20:01 (version 4.9.1 #1978 built at 2022-03-28 19:39+0000)
Liquibase Version: 4.9.1
Give your check a short name for easier identification (up to 64 alpha-numeric characters only) [ObjectNameMustNotMatch1]:
Operator
New check 'Operator' created from 'ObjectNameMustNotMatch'
Set the Severity to return a code of 0-4 when triggered. (options: 'INFO'|0, 'MINOR'|1, 'MAJOR'|2, 'CRITICAL'|3, 'BLOCKER'|4)? [MINOR]:
0
Set 'OPERATOR' (options: STARTS_WITH, ENDS_WITH, CONTAINS, REGEXP) [STARTS_WITH]:
Set 'SEARCH_STRING' (options: a string, or a valid regular expression):
operator
Set 'OBJECT_TYPES' to check, separated by commas (options: TABLE, COLUMN, SEQUENCE):
Column
Set 'CASE_SENSITIVE' (options: true, false) [true]:
false
Customization complete. Review the table below to confirm your changes.
+-----+--------------------------------+----------------------------+--------------------------------+---------+--------------------------------+----------+
| # | Check Name | Short Name | Description | Enabled | Customization | Severity |
+-----+--------------------------------+----------------------------+--------------------------------+---------+--------------------------------+----------+
| 10 | Warn on Detection of 'GRANT' | SqlGrantWarn | This check warns a user when | true | None | 1 |
| | Statements | | SQL contains 'GRANT' | | | |
| | | | statements so that they can | | | |
| | | | ensure that the privilege | | | |
| | | | being granted won't lead to | | | |
| | | | security issues. | | | |
+-----+--------------------------------+----------------------------+--------------------------------+---------+--------------------------------+----------+
| 20 | Warn on Detection of 'REVOKE' | SqlRevokeWarn | This check warns a user when | true | None | 1 |
| | Statements | | SQL contains 'REVOKE' | | | |
| | | | statements so that they can | | | |
| | | | ensure that the privilege | | | |
| | | | being revoked won't lead to | | | |
| | | | data access and dependency | | | |
| | | | issues. | | | |
+-----+--------------------------------+----------------------------+--------------------------------+---------+--------------------------------+----------+
| 30 | Warn when 'DROP TABLE' | ChangeDropTableWarn | This check warns a user when a | true | None | 1 |
| | detected | | table is being dropped so that | | | |
| | | | they can ensure that dropping | | | |
| | | | the table won't lead to | | | |
| | | | unintentional loss of data. | | | |
+-----+--------------------------------+----------------------------+--------------------------------+---------+--------------------------------+----------+
| 40 | Warn when 'DROP COLUMN' | ChangeDropColumnWarn | This check warns a user when a | true | None | 1 |
| | detected | | column is being dropped so | | | |
| | | | that they can ensure that | | | |
| | | | dropping the column won't lead | | | |
| | | | to unintentional loss of data. | | | |
+-----+--------------------------------+----------------------------+--------------------------------+---------+--------------------------------+----------+
| 50 | Warn when 'MODIFY column' | ModifyDataTypeWarn | This check warns a user when a | true | None | 1 |
| | detected | | change will result in | | | |
| | | | modification of a data type so | | | |
| | | | they can ensure that modifying | | | |
| | | | the data type won't lead to | | | |
| | | | unintentional loss of data | | | |
+-----+--------------------------------+----------------------------+--------------------------------+---------+--------------------------------+----------+
| 60 | Check for specific patterns in | SqlUserDefinedPatternCheck | This check scans raw SQL for | false | SEARCH_STRING = null | 1 |
| | sql | | the presence of specific | | MESSAGE = A match for regular | |
| | | | patterns and warns the user | | expression SEARCH_STRING was | |
| | | | when they are found. | | detected in Changeset | |
| | | | | | CHANGESET. | |
| | | | | | STRIP_COMMENTS = true | |
+-----+--------------------------------+----------------------------+--------------------------------+---------+--------------------------------+----------+
| 70 | Check Table Column Count | 1234 | Ensures that no table has more | true | MAX_COLUMNS = 5 | 1 |
| | | | than a threshold number of | | | |
| | | | columns. | | | |
+-----+--------------------------------+----------------------------+--------------------------------+---------+--------------------------------+----------+
| 70 | Check Table Column Count | TableColumnLimit | Ensures that no table has more | true | MAX_COLUMNS = 50 | 0 |
| | | | than a threshold number of | | | |
| | | | columns. | | | |
+-----+--------------------------------+----------------------------+--------------------------------+---------+--------------------------------+----------+
| 80 | Object name pattern match | ObjectNameMustMatch | This check confirms the listed | false | OPERATOR = STARTS_WITH | 1 |
| | | | object names conform to the | | SEARCH_STRING = null | |
| | | | supplied pattern. | | OBJECT_TYPES = null | |
| | | | | | CASE_SENSITIVE = true | |
+-----+--------------------------------+----------------------------+--------------------------------+---------+--------------------------------+----------+
| 85 | Object name pattern not match | ObjectNameMustNotMatch | This check confirms the listed | false | OPERATOR = STARTS_WITH | 1 |
| | | | object names do not match the | | SEARCH_STRING = null | |
| | | | supplied pattern. | | OBJECT_TYPES = null | |
| | | | | | CASE_SENSITIVE = true | |
+-----+--------------------------------+----------------------------+--------------------------------+---------+--------------------------------+----------+
| 85 | Object name pattern not match | Operator | This check confirms the listed | true | OPERATOR = STARTS_WITH | 0 |
| | | | object names do not match the | | SEARCH_STRING = operator | |
| | | | supplied pattern. | | OBJECT_TYPES = Column | |
| | | | | | CASE_SENSITIVE = false | |
+-----+--------------------------------+----------------------------+--------------------------------+---------+--------------------------------+----------+
| 90 | Warn on Grant of Specific | SqlGrantSpecificPrivsWarn | This check warns a user when | false | PRIVILEGE_LIST = null | 1 |
| | Privileges | | changeset includes or | | | |
| | | | generates sql that grants | | | |
| | | | specific privileges to a user | | | |
| | | | or role | | | |
+-----+--------------------------------+----------------------------+--------------------------------+---------+--------------------------------+----------+
| 100 | Warn when 'TRUNCATE TABLE' | ChangeTruncateTableWarn | This check warns a user when a | true | None | 1 |
| | detected | | table is being truncated so | | | |
| | | | that they can ensure that | | | |
| | | | truncating the table won't | | | |
| | | | lead to unintentional loss of | | | |
| | | | data. | | | |
+-----+--------------------------------+----------------------------+--------------------------------+---------+--------------------------------+----------+
| 110 | Warn on Detection of grant | SqlGrantOptionWarn | This check warns a user when | true | None | 1 |
| | that contains 'WITH GRANT | | SQL contains 'GRANT' | | | |
| | OPTION' | | statements that include the | | | |
| | | | 'WITH GRANT OPTION' clause so | | | |
| | | | that they can ensure that the | | | |
| | | | privilege being granted won't | | | |
| | | | lead to security issues | | | |
+-----+--------------------------------+----------------------------+--------------------------------+---------+--------------------------------+----------+
| 130 | Warn on Detection of grant | SqlGrantAdminWarn | This check warns a user when | true | None | 1 |
| | that contains 'WITH ADMIN | | SQL contains 'GRANT' | | | |
| | OPTION' | | statements that include the | | | |
| | | | 'WITH ADMIN OPTION' clause so | | | |
| | | | that they can ensure that the | | | |
| | | | privilege being granted won't | | | |
| | | | lead to security issues | | | |
+-----+--------------------------------+----------------------------+--------------------------------+---------+--------------------------------+----------+
| 160 | Rollback Required for | RollbackRequired | This check triggers when a | true | None | 1 |
| | Changeset | | changeset does not have a | | | |
| | | | rollback defined. | | | |
+-----+--------------------------------+----------------------------+--------------------------------+---------+--------------------------------+----------+
| 170 | Changesets Must Have a Label | ChangesetLabelCheck | This check enforces the | true | None | 1 |
| | Assigned | | Liquibase recommendation that | | | |
| | | | labels be assigned to each | | | |
| | | | changeset to provide better | | | |
| | | | deployment control and to | | | |
| | | | enhance traceability of | | | |
| | | | efforts across changesets. | | | |
+-----+--------------------------------+----------------------------+--------------------------------+---------+--------------------------------+----------+
| 173 | Changesets Must Have a Context | ChangesetContextCheck | This check enforces the | true | None | 1 |
| | Assigned | | Liquibase recommendation that | | | |
| | | | contexts be assigned to each | | | |
| | | | changeset to provide better | | | |
| | | | deployment control and to | | | |
| | | | enhance traceability of | | | |
| | | | efforts across changesets. | | | |
+-----+--------------------------------+----------------------------+--------------------------------+---------+--------------------------------+----------+
| 176 | Changesets Must Have a Comment | ChangesetCommentCheck | This check enforces the | true | None | 1 |
| | Assigned | | Liquibase recommendation that | | | |
| | | | comments be added to each | | | |
| | | | changeset to document the | | | |
| | | | purpose of a changeset for | | | |
| | | | other/future consumers of this | | | |
| | | | changelog | | | |
+-----+--------------------------------+----------------------------+--------------------------------+---------+--------------------------------+----------+
Liquibase command 'checks copy' was executed successfully.