Audit DROP Statements in Percona Server for MySQL

Managing database servers involves different aspects, among which security is critical. We know that we should always grant the minimal required permissions to the different user accounts in the database, as having a user with high-level permission can lead to unexpected results, such as having an index drop affecting the system performance or even more disastrous events such as having a table or a database dropped causing the system to crash and possible data loss. However, we know having a “runaway” grant is always a possibility, especially in environments with a large number of servers. One action item we can do is to trace all DROP events in the server and analyze them to understand if the behavior is normal.

Audit Log Plugin

One way to track these events is through auditing. Fortunately, in Percona Server for MySQL, this feature is free to use. The auditing plugin overview has been explained in the blog post Percona’s MySQL Audit Log Plugin – An Enterprise Feature at a Community Price. Installing the plugin is a straightforward operation that is explained in the blog post mentioned above.

How does it work?

In this blog post, we dig into the steps to track down the DROP statements in our database server. Once the plugin is installed, by default, it will track all events in the database because the variable “audit_log_policy” is set to ALL by default. The audit events are logged into a file defined using the variable “audit_log_file”; by default, it is “audit.log,” and it is stored in the “datadir”. As part of the initial setup, you may want to consider changing the default format to JSON, as you probably will have other tools absorbing the logs. To do this, you need to add the variable “audit_log_format” to your MySQL configuration file and restart the database instance, as this variable is not dynamic.

We will narrow down the events to log only the drop events for the database-related objects. The drop events are listed below.

We will focus on the DROP events for the database and tables, but be advised that it can be extended to any of the above events. Now, we will set the variable “audit_log_include_commands” to only track the DROP DATABASE and DROP TABLE events. Please note that we used the command SET PERSIST to make sure the changes survive a server restart. You can read more about the SET PERSIST option in SET PERSIST in MySQL: A Small Thing for Setting System Variable Values.

Now, let’s imagine we have a user called “evil_user,” and this user has all permissions over our important database:

Next, the “evil_user” connects to the database server and drops table “t3”:

Since we are tracking the DROP TABLE events, the “audit.log” file shows the DROP event, including the timestamp, the user, and the IP where the event originated:

Please notice the value of the “STATUS”; in this case, it is zero, which means the DROP operation succeeded. This is important because the audit plugin also logs the failed DROP commands. For example, the “evil_user” will try to drop a non-existent table “t4”; as expected, it gets an error.

However, the event was still logged in the audit log file, but notice the value for STATUS. It is the same error code (1051) shown when the DROP command was executed, you need to take this into account when analyzing this audit events.

Now, the “evil_user” will continue its evil actions and will DROP the important database:

This event is also tracked in the audit log file; please note the STATUS value. It indicates the operation was successful.

To capture these events in real-time, you can have a cron task to monitor the audit log file constantly and alert you if any suspicious activity is found. Please notice that only the DROP TABLE and DROP DATABASE events are being logged in the audit log file. You can add more events or further filter the events to capture. It is also important to mention that despite the filter, the “Connect” and “Quit” connection events are also captured.

Conclusion

The audit plugin is a powerful tool we have at our disposal in Percona Server for MySQL. This is just a simple example of a critical usage of this tool. It has many additional features that we will review in future blogs. Please remember that this is a paid feature for MySQL Enterprise, while it is a free feature for Percona Server for MySQL. If you need to use this tool, please consider switching to Percona Server for MySQL, a true drop-in replacement for MySQL Community Version. Drop us a line if you need assistance switching to Percona Server; we are happy to help.

Percona Distribution for MySQL is the most complete, stable, scalable, and secure open source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!

Try Percona Distribution for MySQL today!

Subscribe

Connect with

Login

Label

0 Comments