Audit plugin - audit_log_policy=query for some users but login for all users - possible?

MySQL & MariaDB Other MySQL® Questions
1

Hi,
I am interested in figuring out a way to log all connection attempts for all users but only queries from users not in the audit_log_exclude_accounts list. Would that be at all possible?

cheers
Darren

2

Hi Darren,
Welcome to the forum!

This is not possible. The filters you use will apply in all cases, and it’s not possible to selectively have them apply to only some of the users in some cases, and some in others.
You can check the online documentation for more info on this:

Best,
Agustín.

1 Like
3

Hi @darren

As @Agustin_G pointed, you can’t achieve the desired effect using the audit plugin only, but I think you could get the same results using the audit plugin and logging the events in syslog. Then apply additional filters in syslog.

This is a bit more complex, but it should work.

Pep

4

Thank you both Agustin and Pep,
Pep, with your solution would I need to log “All” logins and queries from all users without excluding anything then do the filtering in syslog? For systems which run tens of thousands of queries per second from an application user but a handful a minute for anything else it would cause a huge influx in logging should I remove the application user which is my concern in that case. Would that also be your understanding?

Darren

5

Hi @darren

Auditing has a performance impact for sure. In this case, most of the impact should go to CPU usage. There are multiple ways to reduce this impact. It depends on how you perform the filtering.

My recommendation is to make some tests, as there are several syslog implementations available that provide different tuning options.

Two alternatives that could be used (in no particular order):

  • Clone the network switch port to another port and use a dedicated server with a tool like Wireshark to analyze the traffic and generate the log. (Will not generate audit information for local connections)
  • Use ProxySQL and use it to perform the audit.

I still think that the combination of audit plugin + syslog is the better option, but you can use any of these alternatives if performance is a problem.

Pep

6

Thank you Pep. Appreciate you sharing your thoughts and suggestions. Have a great rest of your week.

Darren

1 Like