Using MySQL Enterprise Audit Filtering for only Insert/Update/Delete statements for Users

How to Audit only DML Actions ?

Step by step to Enable Fine Grained MySQL Auditing for Insert/Update/Delete Queries for all Users.

Before i talk about MySQL Audit Filtering , it is more important to know below details:-

• What is MySQL Enterprise Audit ?
• Features of MySQL Audit.
• Why Audit is Important?
•  How to Audit only DML statements for Particular/All Users ?
• Audit Log Restrictions
• Conclusions

What is MySQL Enterprise Audit?

At high level, database auditing is the process to guard against misuse of information, track the use of database resources and activity like

-Who accessed the database(Users)

- Where these queries has been executed(Host Name).

-What kind of queries has been executed to db(events/statements).

-When did this happened(Time)

Then we can have idea of “Why this happened”? ( Q & A for 5W)

Most Important Auditing will help organization to comply with popular compliance regulations including GDPR,HIPAA, Sarbanes-Oxley, and the PCI Data Security Standard.

More Info: - https://dev.mysql.com/doc/refman/8.0/en/audit-log.html

Features of MySQL Audit

MySQL Enterprise Audit enable below top 10 features:-

1.       User Defined Custom Audit Log Events - Now you can add custom events to the MySQL Audit Log, providing added information related to SQL calls. For example, tracking numbers, help desk ticket references, or other application context.

2.       Encryption - Audit Files can now be encrypted using standard AES-256. These files can be shared and decrypted by external applications that have the encryption key.

3.       Compression - Using compression can Reduce audit storage by up to 10x. Files can be unzipped using standard tools.

4.       JSON Audit Data Format - Now you can decide between JSON or XML.

5.       Powerful Filtering to Protect Sensitive Data - Define what you audit using templates or design highly custom filters using simple JSON filter definition.

a.        Filter on connections, users, table access, access type, statement status (success/failure), query content, and more.

6.       Meet Regulatory compliance standards - Provide the data your organization and auditors need to be in compliance with requirements including PCI, HIPAA, FERPA, SOX and more.

7.       Achieve Security Goals through Comprehensive Auditing - Trust but verify DBA activity, prove your data’s validity and perform forensic analysis to investigate or discover data breaches.

8.       Easy Integration with Audit Vaults and Stores - Externally archive and analyze XML-based audit logs with ease using Oracle Audit Vault and other third party solutions including Splunk.

9.       Dynamic and Easy to Manage - Dynamically enable/disable audit stream, change filtering, and more with no downtime. Automatically rotate audit log files based on size.

10.   Low Overhead - Collects critical audit data without minimal performance impact. Use fine grain filtering to minimize audit log size and IO impact.

Why Audit is Important?

By auditing your database:-

1.       To enable accountability for actions. In order to hold users responsible for their actions, it is necessary to track what they have done. As example- Suppose some users has executed TRUCATE TABLE and you can investigate deeply if audit trail is in place through you will get to know why this user had executed this type of SQL statement.

2.       To investigate suspicious activity. Security may be set up correctly, but it is still useful to know if people are trying to access data or run commands that are beyond their authorization.

3.       Compliance issues. Many applications have quasi-legal requirements for audit. Typically, these are based around access to personal or financial information.

How to Audit only DML statements for Particular/All Users?

To use Audit Filtering, it consists of three basic steps:-

Installation In Details:-

Step 1:- Execute audit filter script .sql into mysql console as below

mysql> source /usr/share/mysql-8.0/audit_log_filter_linux_install.sql

Database changed

Query OK, 0 rows affected, 1 warning (0.89 sec)

Query OK, 0 rows affected, 3 warnings (0.15 sec)

Query OK, 0 rows affected (0.24 sec)

.

.

.

Query OK, 0 rows affected (0.01 sec)

+--------+

| Result |

+--------+

| OK     |

+--------+

1 row in set (0.01 sec)

 

Step 2:- Verify the Installation

SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit%';

+-------------+---------------+

| PLUGIN_NAME | PLUGIN_STATUS |

+-------------+---------------+

| audit_log   | ACTIVE        |

+-------------+---------------+

Step 3:- Define the filter

Example”- Our Requirement is , How to Track only Insert/Update/Delete for all Users.

mysql>SELECT audit_log_filter_set_filter(

'AuditLog_InsertUpdateDelete', '{

  "filter": {

    "class": {

      "name": "table_access",

        "event": {          "name": [ "insert", "update", "delete" ]        }    }  } }

');

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| audit_log_filter_set_filter(

'AuditLog_InsertUpdateDelete', '{

  "filter": {

    "class": {

      "name": "table_access",

        "event": {          "name": [ "insert", "update", "delete" ]        }    }  } }

') |

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| OK                                                                                                                                                                                                                   |

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.17 sec)

Step 4:- Assign the filter to all users.

SELECT audit_log_filter_set_user('%', 'AuditLog_InsertUpdateDelete') AS 'Result';

Here

 % à All Users

If you want to track particular DBAs then assign filter for the users( ex- User Name: Radha)

SELECT audit_log_filter_set_user('Radha@localhost', 'AuditLog_InsertUpdateDelete') AS 'Result';

Here

 'Radha@localhost' à Particular Username.

mysql> SELECT audit_log_filter_set_user('%', 'AuditLog_InsertUpdateDelete') AS 'Result';

+--------+

| Result |

+--------+

| OK     |

+--------+

1 row in set (0.18 sec)

Step 5:- Let’s Verify whether Audit for Insert/Delete/Update is working or not.

Login MySQL for any users and execute SQL commands, and verify the Log whether it contains only INSERT/UPDATE/DELETE query or not , If Only these commands are available the our MySQL Audit Filtering is working for us.

List SQL Statement Executed on DB

mysql> create database AuditTest; Query OK, 1 row affected (0.27 sec) mysql> use AuditTest; Database changed mysql> create table employee(Empname Varchar(25),DeptName Varchar(20)); Query OK, 0 rows affected (0.39 sec) mysql> Insert Into employee Values('Chandani','Hindi'); Query OK, 1 row affected (0.17 sec) mysql> Insert Into employee Values('Palak','IT'); Query OK, 1 row affected (0.01 sec) mysql> select * from employee; +----------+----------+ | Empname  | DeptName | +----------+----------+ | Chandani | Hindi    | | Palak    | IT       | +----------+----------+ 2 rows in set (0.09 sec) mysql> Delete From employee where DeptName='IT'; Query OK, 1 row affected (0.13 sec) mysql> Update employee Set DeptName='English' where Empname='Chandani'; Query OK, 1 row affected (0.05 sec) Rows matched: 1  Changed: 1  Warnings: 0

Audit Log Output

<AUDIT_RECORD>   <TIMESTAMP>2020-03-16T11:54:54 UTC</TIMESTAMP>   <RECORD_ID>2_2020-03-16T11:22:24</RECORD_ID>   <NAME>TableInsert</NAME>   <CONNECTION_ID>13</CONNECTION_ID>   <USER>root[root] @ localhost []</USER>   <OS_LOGIN/>   <HOST>localhost</HOST>   <IP/>   <COMMAND_CLASS>insert</COMMAND_CLASS>   <SQLTEXT>Insert Into employee Values('Chandani','Hindi')</SQLTEXT>   <DB>AuditTest</DB>   <TABLE>employee</TABLE>  </AUDIT_RECORD>  <AUDIT_RECORD>   <TIMESTAMP>2020-03-16T11:55:02 UTC</TIMESTAMP>   <RECORD_ID>3_2020-03-16T11:22:24</RECORD_ID>   <NAME>TableInsert</NAME>   <CONNECTION_ID>13</CONNECTION_ID>   <USER>root[root] @ localhost []</USER>   <OS_LOGIN/>   <HOST>localhost</HOST>   <IP/>   <COMMAND_CLASS>insert</COMMAND_CLASS>   <SQLTEXT>Insert Into employee Values('Palak','IT')</SQLTEXT>   <DB>AuditTest</DB>   <TABLE>employee</TABLE>  </AUDIT_RECORD>  <AUDIT_RECORD>   <TIMESTAMP>2020-03-16T11:55:18 UTC</TIMESTAMP>   <RECORD_ID>4_2020-03-16T11:22:24</RECORD_ID>   <NAME>TableDelete</NAME>   <CONNECTION_ID>13</CONNECTION_ID>   <USER>root[root] @ localhost []</USER>   <OS_LOGIN/>   <HOST>localhost</HOST>   <IP/>   <COMMAND_CLASS>delete</COMMAND_CLASS>   <SQLTEXT>Delete From employee where DeptName='IT'</SQLTEXT>   <DB>AuditTest</DB>   <TABLE>employee</TABLE>  </AUDIT_RECORD>  <AUDIT_RECORD>   <TIMESTAMP>2020-03-16T11:55:34 UTC</TIMESTAMP>   <RECORD_ID>5_2020-03-16T11:22:24</RECORD_ID>   <NAME>TableUpdate</NAME>   <CONNECTION_ID>13</CONNECTION_ID>   <USER>root[root] @ localhost []</USER>   <OS_LOGIN/>   <HOST>localhost</HOST>   <IP/>   <COMMAND_CLASS>update</COMMAND_CLASS>   <SQLTEXT>Update employee Set DeptName='English' where Empname='Chandani'</SQLTEXT>   <DB>AuditTest</DB>   <TABLE>employee</TABLE>  </AUDIT_RECORD>  <AUDIT_RECORD>   <TIMESTAMP>2020-03-16T11:55:50 UTC</TIMESTAMP>   <RECORD_ID>6_2020-03-16T11:22:24</RECORD_ID>

In Above Audit Reports, THERE is not any Logging happened against  CREATE , USE , DROP , SELECT SQL Statements , which means our MySQL Fined Grain Auditing is Working Fine!!!.

Audit Viewer from various dashboard Tools

You can see audit reports from varios tools , always opening audit log is cumbersome , hence i recommend you can use below tools

1. MySQL Enterprise Workbench Tool

2.Oracle Audit Vault Server

3.Splunk Dashboard

Only You will get MySQL Workbench Tool when you procure MySQL Enterprise Edition License.

Below are Sample Dashboard from Workbench

Audit Log Restrictions

MySQL Enterprise Audit is subject to these general restrictions:

1.       Only SQL statements are logged. Changes made by no-SQL APIs, such as memcached, Node.JS, and the NDB API, are not logged.

2.       Only top-level statements are logged, not statements within stored programs such as triggers or stored procedures.

3.       Contents of files referenced by statements such as LOAD DATA are not logged.

More Info at:- https://dev.mysql.com/doc/refman/8.0/en/audit-log-restrictions.html

Conclusion:-

MySQL Enterprise Audit provides an easy to use, policy-based auditing solution that helps organizations implement stronger security controls and satisfy regulatory compliance.

As more sensitive data is collected, stored and used online, database auditing becomes an essential component of any security strategy. To guard against the misuse of information, popular compliance regulations including HIPAA, Sarbanes-Oxley, and the PCI Data Security Standard require organizations to track access to information.

Thank You for Using MySQL !!!.
Feel Free to try and share your feedback.