MySQL Audit Logging — How to Avoid Data Overload

  1. Review your schema and identify data that *must* be audited. It’s likely your company’s personal identifiable information (PII) is only in a few tables. MySQL Workbench has a great reverse engineer feature to create an ER Diagram, useful for double-checking where your sensitive data might reside.
Use MySQL Workbench to identify which tables contain PII and must be audited.
  • audit log encryption
  • log rotation
  • log compression
  • fine-grained filtering capabilities
  • audit specific users, tables or databases
  • audit access denied errors (see who may be searching for access)
  • audit failed connections
  • audit non-local subnets
  • audit insecure connections
# Change any option to true to enable logging.  Useful for testing 
# options. More examples in documentation
SET @f='
{
"filter": {
"log": false,
"class": [
{
"name": "connection",
"event": [
{ "name": "connect", "log": false},
{ "name": "disconnect", "log": false }
]
},
{
"name": "general",
"event": { "name": "status", "log": true}
},
{
"name": "table_access",
"event":[
{ "name": "insert", "log":false },
{ "name": "delete", "log":false },
{ "name": "update", "log":false },
{ "name": "read", "log":false }
]
}
]
}
}';
SET @f='                                                       
{
"filter": {
"class":
{
"name": "table_access",
"event":
{
"name": [ "insert", "update", "delete", "read" ],
"log": {
"and": [ { "field": { "name": "table_database.str",
"value": "employees" } },
{ "field": { "name": "table_name.str",
"value": "salaries" }]
}
}
}
}
}';
SET @f='
{
"filter": {
"log": false,
"class": {
"name": "connection",
"event": [
{ "name": "connect", "log" :
{ "not": { "field": { "name": "status", "value": 0 } } } },
{ "name": "disconnect", "log": false }
]
}
}
}';

--

--

--

I lead a team of MySQL Architects ( aka Sales Engineers) at Oracle. Interests: cloud, containers, kubernetes, terraform

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Breeding and fusing TheSlowPatrol NFTs

Data structures in ruby and variables in ruby

How to Colorize Google Calendar Event Using Apps Script (2/2)

Patract Monthly Report,June

Internship, Day One; Standing Up MySQL-Python

Launch a Container on Docker in GUI mode

7 Reasons Why Your Mac Is Slower Than Mine

How to Interactively Search and Replace Across Many Files With 2 Commands

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Lee Stigile

Lee Stigile

I lead a team of MySQL Architects ( aka Sales Engineers) at Oracle. Interests: cloud, containers, kubernetes, terraform

More from Medium

Scaling a skewed Database

Availability basic principles

Why I like using UUIDs on Database tables

Woman holding a key

Databases 101 : What are UUIDs? should we care?