Audit Log Filtering Issue

MySQL & MariaDB
1

Hi,
We have been trying to use the audit log plugin for our use case but even after setting the required parameters for filtration, the audit plugin is logging everything into the log files. Its not filtering out anything at the moment which is a big issue for us at the moment.

The configuration that we are using for the audit log plugin looks like this:

plugin-load-add=audit_log.so
audit_log_file=/var/log/mysql/audit.log
audit_log_policy = QUERIES
audit_log_format=JSON
audit_log_include_databases=db_name
audit_log_exclude_commands=show_variables,show_engine_status,show_status,show_processlist,show_tables,show_table_status,show_fields,show_create_table,show_databases,show_storage_engines,PING,ping,select,set_option,reset_connection,show_function_status

Even with this configuration we are still seeing the select queries and ping statements etc and most of the statements are not specific to our database that we want to monitor. The audit log is logging the statements of all the database which should ideally not happen.

2

Hi @Tanmay129,
The audit log plugin is deprecated. Can you switch to the audit log component? The component is actively developed by our team.

The other recommendation I have is to reduce audit_log_exclude_commands to just 1 and check that works. Then add 1 more filter, and confirm. Repeat this until you have all your filters. There is a possible bug that an invalid entry in this list makes the entire list invalid, so test each filter.

3

Hi @matthewb

We’ve been trying to test the audit log component but are unable to make changes to the variables. There is no proper documentation on how to make changes to these variable and they are read only as well so can you please help with this issue?

4

Have a look at the 8.4 documentation which covers the component

5

Hi @matthewb,

I’ve gone through the docs and tried to change the values of variables like audit_log_filter.database but to no success as I am seeing this type of an error:

image
image1899×596 20.3 KB

Plus I wanted to test out the filters and was using the default values of the variables just to make it work but the filters are not functioning properly and there are no proper examples on how to go about creating complex filters in the documentation. I have been scouring the docs since morning to no avail so can you please check why this filter of mine doesn’t work as it should.

{
    "filter": {
        "log": true,
        "class": [
            {
                "name": "table_access",
                "event": [
                    {"name": "read", "log": false}
                ]
            },
            {
                "name": "message",
                "event":[
                    {"name": "internal", "log": false}
                ]
            },
            {
                "name": "general",
                "event": [
                    {"name": "status", "log": false}
                ]
            }
        ]
    }
}
6

Because the audit log filter component is not part of MySQL, mysql will not recognize the parameters within my.cnf. You need to prefix these parameters with loose- like this: loose-audit_log_filter.database=audit

This documentation on creating filters did not help?

7

Hi @matthewb,
Big thanks for your help we finally got everything figured out and the documentation also helped, just had to try out various filter settings and now its great, the variables are also being configured now. Once again thank for your quick and helpful responses.

8

If you don’t mind, could you share what you implemented?

9

Sure,
I added the following entries in my.cnf and now the variables are working fine:
image
Plus I created this filter using the audit_log_filter_set_filter() function:

    "filter": {
        "log": true,
        "class": [
            {
                "name": "connection",
                "log": false
            },
            {
                "name": "general",
                "log": false
            },
        ]    
    }
}```