Audit logs are crucial when it comes to production database instances, there must be logging available every time when someone gets into the database. Audits logs are almost on top of the checklist if you have security policies and compliances. Yet many clients are either not aware nor interested to enable audit logs. Audit logs play a vital role in security practices recommended by data security and privacy certifications like PCI DSS, GDPR, SOX etc
Why we need an audit log?
Who accessed the database?
When they accessed it?
Why they accessed it? What are the changes done?
Was any data or schema modified without proper knowledge?
Which user or host was the reason for bad queries that caused a downtime?
To be honest, these are fairly very simple reasons why we need to enable the audit log. Now let’s see what are the options to enable audit log.
Audit logging On MySQL
MySQL audit plugin comes with enterprise edition only, it can be loaded in runtime.
Formats - new/old/json
Simple query, select * from roles
is logged as,
XML
2020-01-02T10:23:17 UTC
12_2020-01-02T10:23:03
Query
53
0
0
root[root] @ localhost []
localhost
select
select * from roles
Old Format
TIMESTAMP="2020-01-02T10:22:27 UTC" RECORD_ID="12_2020-01-02T10:22:01" NAME="Query" CONNECTION_ID="31" STATUS="0" STATUS_CODE="0" USER="root[root] @ localhost []" OS_LOGIN="" HOST="localhost" IP="" COMMAND_CLASS="select" SQLTEXT="select * from roles"/>
JSON
{ "timestamp": "2020-01-02 10:18:19", "id": 0, "class": "general", "event": "status", "connection_id": 28, "account": { "user": "root", "host": "localhost" }, "login": { "user": "root", "os": "", "ip": "", "proxy": "" }, "general_data": { "command": "Query", "sql_command": "select", "query": "select * from roles", "status": 0 } }
Features
Rotate size Threshold - It will rotate the audit log once the size is reached
Events logging - Controls what are the elements those are to be logged (All, None, Logins, Queries)
Audit logging on MariaDB
Unlike MySQL, the audit log comes with all versions of MariaDB even on community editions.
20200102 10:40:31,mariadblab,root,localhost,49,15,QUERY,foo,'select * from roles',0
Features
Events logging - Connect,query, table, query_ddl,query_dml,query_dcl,query_dml_no_select (latest audit version)
User-based logging - It is used to exclude/include certain users from logging.
Rotate size Threshold - Can describe the size to rotate the audit log
NOTE: Apart from MySQL and MariaDB, we can enable an audit log on the load balancer such as ProxySQL
Audit logging on ProxySQL
Currently ProxySQL supports JSON and traditional format
{"client_addr":"127.0.0.1:50100","creation_time":"2020-01-02 11:05:08.363","duration":"13.355ms","event":"MySQL_Client_Close","extra_info":"MySQL_Thread.cpp:4398:process_all_sessions()",
"proxy_addr":"0.0.0.0:6033","schemaname":"information_schema","ssl":false,"thread_id":7,"time":"2020-01-02 11:05:08.376","timestamp":1577963108376,"username":"appuser"}
Separate query logging is available which can be configured as per business needs
Summary