Detecting Database Changes to Improve Security & Quality

Verifying that the current state of a database matches the expected state is a fundamental dimension of database change management. It’s important to have a well-defined process for ensuring your databases are in an expected state for two reasons: quality and security. In this blog post, I’ll cover why it’s so important to detect differences between databases and how you can use a tool like Liquibase to ensure both the quality and security of your database environments.

Why Inspecting Databases is Useful

Having the capability to inspect your database’s state allows you to do interesting things, like periodically checking your integration environment database to see if the development team added new tables or columns. Your team could trigger an alert to the Test Data Management team to modify their datasets. Another example might be to check Production regularly to see if any unexpected stored procedures have appeared so your team can send an alert to the Security team to investigate them for malware or ransomware.

I’ll show you a simple pattern that can be applied in a variety of situations. How you want to use it will depend on your organization’s priorities, the architecture of your application, and your database design. Liquibase’s comparison capabilities are very adaptable to different teams’ needs.

Liquibase Comparison Capabilities

A core human behavior is comparing things to learn. Just about anyone will tell you that in order to verify the state of anything, you need a ‘known good’ reference point against which you can compare your subject. In our case, the subject is our target database. If the target database matches the reference point, all is well. If there is a mismatch — for example, the presence of an unexpected object or the absence of an expected object — you have a problem that needs to be resolved.

Liquibase provides a number of capabilities that, when used together, provide you with a convenient and easy-to-implement database change detection mechanism that can be added to an automation system. These capabilities are based on the Liquibase diff or diffChangeLog commands, which allow you to compare a target database against another database (a snapshot file) representing the state of a database at a point in the past. Users can create a snapshot file using the Liquibase snapshot command.

Usage Pattern

Whether you are concerned with quality or security, the process for detecting unexpected changes with Liquibase is straightforward. Here are the basic steps for using Liquibase to check for changes:

1. Create a ‘known good’ reference point with the snapshot command
2. Compare database states by running Liquibase’s diff or diffChangeLog command
3. Examine the output
4. Take action

Create a Reference Point

Liquibase snapshot files provide a convenient method for understanding the state of a database at a given point in time. They can be saved in any version control system to enable easy retrieval and use in comparison operations in any environment. Further, by saving them regularly, they provide a technologically generated history of the evolution of a database over time. Even better, since they are easily generated using the Liquibase command line, you can easily add them to automation jobs.

Let’s say you want to capture a snapshot of Production every time you deploy new changes to that database. With Liquibase, you can set up the automation job so that the snapshot action immediately follows the deployment so you always have a current reference point. You can then use this official ‘known good’ state of Production as a comparison against any other database.

Example:

#deploy the latest changes
liquibase update
#capture a snapshot of the database after successful deployment as a file called mySnapshot.json
liquibase --outputFile=mySnapshot.json snapshot --snapshotFormat=json

Compare Database States

Liquibase has two main comparison commands: diff and diffChangeLog. Both commands provide the same list of differences, but the format of the output will be different. The one you choose to use will depend on what you want to do with the output.

• Using the diff command generates a report — either as plain text or JSON file — of what is different between the database and the reference snapshot. Differences are identified as missing, unexpected, or changed.
• Using the diffChangeLog command generates a Liquibase changelog file that contains runnable changesets that can be used to synchronize the target’s configuration to that of the reference point. (Performing this synchronization can be risky to data. Make sure to review carefully.)

Examples:

#compare a database to a snapshot and generate a text report
liquibase --referenceURL=offline:mssql?snapshot=mySnapshot.json diff
#compare a database to a snapshot and generate a JSON file
liquibase --referenceURL=offline:mssql?snapshot=mySnapshot.json diff \
--format=json
#compare a database to a snapshot and generate a changelog
liquibase --referenceURL=offline:mssql?snapshot=mySnapshot.json \
--changelogfile=diff1.xml diffChangeLog

Examine the Output

The Liquibase comparison commands can produce output in several different formats. The diff command produces a plain text report by default. That can be examined with typical text parsing tools. If you have a Liquibase Pro license, it can also produce a JSON version of the output that can be inspected with deeper analytical programming logic. The diffChangeLog command produces runnable changelogs that can be generated in any format that Liquibase supports for changelogs (XML, YAML, JSON, or SQL). These are structured files that can also be analyzed with scripts, but their executable nature means you should exercise additional diligence in handling them.

Of course, all these file formats are just text files. That means they are easy to store in versioning repositories and examine with common tools and automation tools.

Take Action

If nothing is different you will not want to take any action. The interesting activities happen once you find a difference. At that point, you can use the full extent of your automation system to take any action you please. For example, some Liquibase customers send a message via Slack and others choose to send a message via Jira. If security is your primary concern, you might couple the messaging with an API call to shut down or isolate the database. It all depends on your creativity and the strength of the automation system you are using to control Liquibase.

Putting it All Together

The following is a simple example script combining the Liquibase snapshot and diffChangeLog commands to create an automated monitoring capability that issues a simple alert message if a difference is found between the database and the snapshot.

The goal here is not to produce a new monitoring tool, but instead to illustrate a pattern. You can take this pattern and easily replicate it in more sophisticated automation tools, and send alerts through more sophisticated messaging services, such as Slack, Discord, or even classic email.

Go Forth & Build Safer Database Environments!

I hope you can use this Liquibase pattern to help your team inspect databases to improve the quality and security of your releases. If you’re interested in learning more about this topic, join us for a webinar on December 15th where I’ll be covering Building Trustworthy Database Environments: Using Liquibase to Keep Malware Out and Quality In.