Liquibase Pro – Add Custom Quality Checks

As of September 9, 2024, Quality Checks have been renamed to Policy Checks. Check out this blog to learn more.

‍

Why automate database changes when you still require your DBAs to review your scripts? Particularly when you have documented coding standards for database changes. A key benefit of database deployment automation is to deploy to target databases as quickly as possible without requiring manual intervention. Liquibase is the preferred tool for deployment automation, and now with Quality Checks, Liquibase Pro can also perform the most common DBA functions: validate scripts for coding standards.

What's Inside the Box?

Database coding standards can span a wide range depending on the database and the types of changes. For example, object naming convention, no permission statements allowed in a script, or no DROP statements allowed. 

Liquibase Pro offers many out of the box Quality Checks. But let's take a look at capabilities to add new Quality Checks. You will use the Liquibase command line interface (CLI) to configure new checks. New checks can be created by copying existing checks and customizing them. Liquibase makes this easy by using only one command:

liquibase checks customize --check-name=<existing_check_name>

Based on the check being customized, Liquibase will prompt the user for additional inputs. The newly customized check is saved in the checks configuration file, which will need to be committed into a repository for it to be consumed during the pull request automated review or before database deployment automation.

Check: Oracle Reserved Keywords in Object Name

For example, let's say you want to disallow users from using specific Oracle-reserved keywords in database object names. You will use the existing check, OracleReservedKeywords, to customize the check for additional keywords: 

liquibase checks customize --check-name=OracleReservedKeywords

Liquibase will prompt as follows:

PromptCommand or User InputGive your check a short name for easier identification (up to 64 alpha-numeric characters only):OracleReservedKeywords1Set the Severity to return a code of 0-4 when triggered. (options: 'INFO'|0, 'MINOR'|1, 'MAJOR'|2, 'CRITICAL'|3, 'BLOCKER'|4)?<Choose a value: 0, 1, 2, 3, 4>Set 'OBJECT_TYPES' to check, separated by commas (options: TABLE, COLUMN, SEQUENCE, INDEX):<TABLE, COLUMN, SEQUENCE or INDEX>Set 'ALLOWED_LIST':<List of comma separated keywords allowed>Set 'CASE_SENSITIVE' (options: true, false)<true or false>

<style type="text/css">
.tg  {border-collapse:collapse;border-spacing:0;}
.tg td{border-style:solid;border-width:0px;font-family:Arial, sans-serif;font-size:14px;overflow:hidden;
  padding:10px 5px;word-break:normal;}
.tg th{border-style:solid;border-width:0px;font-family:Arial, sans-serif;font-size:14px;font-weight:normal;
  overflow:hidden;padding:10px 5px;word-break:normal;}
.tg .tg-r25x{background-color:#9b9b9b;color:#ffffff;text-align:left;vertical-align:top}
.tg .tg-c9ly{background-color:#303498;border-color:#ffffff;color:#ffffff;font-size:18px;text-align:left;vertical-align:top}
.tg .tg-bxwd{background-color:#303498;color:#ffffff;font-size:18px;text-align:left;vertical-align:top}
.tg .tg-gmm0{background-color:#9b9b9b;border-color:#ffffff;color:#ffffff;text-align:left;vertical-align:top}
</style>
<table class="tg">
<thead>
  <tr>
    <th class="tg-c9ly"><span style="font-weight:600">Prompt</span></th>
    <th class="tg-bxwd"><span style="font-weight:600">Command or User Input</span></th>
  </tr>
</thead>
<tbody>
  <tr>
    <td class="tg-gmm0"><span style="font-weight:400;font-style:normal">Give your check a short name for easier identification (up to 64 alpha-numeric characters only):</span></td>
    <td class="tg-r25x"><span style="font-weight:400;font-style:normal">OracleReservedKeywords1</span></td>
  </tr>
  <tr>
    <td class="tg-gmm0"><span style="font-weight:400;font-style:normal">Set the Severity to return a code of 0-4 when triggered. (options: ‘INFO’|0, ‘MINOR’|1, ‘MAJOR’|2, ‘CRITICAL’|3, ‘BLOCKER’|4)?</span></td>
    <td class="tg-r25x"><span style="font-weight:400;font-style:normal">&lt;Choose a value: 0, 1, 2, 3, 4&gt;</span></td>
  </tr>
  <tr>
    <td class="tg-gmm0"><span style="font-weight:400;font-style:normal">Set ‘OBJECT_TYPES’ to check, separated by commas (options: TABLE, COLUMN, SEQUENCE, INDEX):</span></td>
    <td class="tg-r25x"><span style="font-weight:400;font-style:normal">&lt;TABLE, COLUMN, SEQUENCE or INDEX&gt;</span></td>
  </tr>
  <tr>
    <td class="tg-gmm0"><span style="font-weight:400;font-style:normal">Set ‘ALLOWED_LIST’:</span></td>
    <td class="tg-r25x"><span style="font-weight:400;font-style:normal">&lt;List of comma separated keywords allowed&gt;</span></td>
  </tr>
  <tr>
    <td class="tg-gmm0"><span style="font-weight:400;font-style:normal">Set ‘CASE_SENSITIVE’ (options: true, false)</span></td>
    <td class="tg-r25x"><span style="font-weight:400;font-style:normal">&lt;true or false&gt;</span></td>
  </tr>
</tbody>
</table>

Check: No Grants to SYSTEM

Another example is to disallow an operation but that the operation can only be identified by a collection of words. Here are a few SQL commands that should be disallowed to prevent any GRANT operation to SYSTEM objects:

GRANT EXECUTE TO SYSTEM;
GRANT SELECT ON TABLE sales TO SYSTEM;
GRANT UPDATE, TRIGGER ON TABLE sales TO SYSTEM;

In order to add a check for such statements, you will need to customize a SQL pattern check, SqlUserDefinedPatternCheck, as follows:

PromptCommand or User Input> liquibase checks customize --check-name=SqlUserDefinedPatternCheckGive your check a short name for easier identification (up to 64 alpha-numeric characters only) [SqlUserDefinedPatternCheck1]:NoGrantsToSystemSet the Severity to return a code of 0-4 when triggered. (options: 'INFO'|0, 'MINOR'|1, 'MAJOR'|2, 'CRITICAL'|3, 'BLOCKER'|4)? [INFO]:<Choose a value: 0, 1, 2, 3, 4>Set 'SEARCH_STRING' (options: a string, or a valid regular expression):(?i:grant)[\t\n\r\s\S]*(?i:to system)Set 'MESSAGE' [A match for regular expression <SEARCH_STRING> was detected in Changeset.]:Error! GRANT to SYSTEM not allowed.Set 'STRIP_COMMENTS' (options: true, false) [true]:true

Custom Quality Checks - A GitHub Repository

A number of new and custom Liquibase Pro Quality Checks are made available at this GitHub repo. This repository is organized according to the database that the quality checks are compatible with. Each file is a description of the custom Quality Check and contains step-by-step instructions for adding the check. Each file also contains sample failing SQL scripts to show the context against which these checks are performed. 

Recall that once you configure your custom checks, commit the checks configuration file to the DBA repository. During automation, you will pull down the checks configuration file into your workspace and run the following command to execute checks against incoming SQL scripts: 

liquibase checks run

We welcome any contributions from the community to improve these checks or add new checks.