Brief Look Into Mysql Enterprise Audit
When installed, the audit plugin enables MySQL Server to produce a log file containing an audit record of server activity. The log contents include when clients connect and disconnect, and what actions they perform while connected, such as which databases and tables they access.
Installation and Uninstallation of Audit
To be usable by the server, the plugin library must be located in MYSQL plugin dir(select @@plugin_dir)
Cd /usr/share/mysql-8.0/
Mysql -uroot -p < audit_log_filter_linux_install.sql
Uninstallation
DROP TABLE IF EXISTS mysql.audit_log_user;
DROP TABLE IF EXISTS mysql.audit_log_filter;
UNINSTALL PLUGIN audit_log;
DROP FUNCTION audit_log_filter_set_filter;
DROP FUNCTION audit_log_filter_remove_filter;
DROP FUNCTION audit_log_filter_set_user;
DROP FUNCTION audit_log_filter_remove_user;
DROP FUNCTION audit_log_filter_flush;
DROP FUNCTION audit_log_encryption_password_get;
DROP FUNCTION audit_log_encryption_password_set;
DROP FUNCTION audit_log_read;
DROP FUNCTION audit_log_read_bookmark;
DROP FUNCTION audit_log_rotate;
Audit Security Concerns
By default, the contents of the audit log files are not encrypted and may contain sensitive information, such as text of SQL statements. So, logs should be written to a dir accessible only to Mysql server and to legitimate users. The default name is audit.log in the data dir. Can be changed by setting audit_log_file system variable at server startup.
Audit Log File Formats
3 Types of formats:-
New - New XML format
Old - Old XML format
JSON - JSON array
The format is changed using audit_log_format system variable. If the variable is changed, it is recommended to change audit_log_file. Eg:- if audit_log_format is set to JSON, set audit_log_file to audit.json. The only purpose is to differentiate the files after changing the format.
—--------------------------------------------------------------------------------
—---------------------------------------------------------------------------
Audit Log Naming Conventions
Pwd_id indicates the id of the password used to encrypt/decrypt the file.
Pwd_id is composed of —---> pwd_timestamp-seq where:
Pwd_timestamp - UTC value in YYYYMMDDThhmmss format indicating when the pwd was created.
Seq - Sequence number. Starts at 1 and increases for pwd that have same timestamp value
Variables for Setup
Audit_log_file (setting audit log file dir to a location other than data dir may cause error. It is caused mostly by selinux in linux servers. Make sure to configure it or disable selinux)
Audit_log_format
Audit_log_compression: (permitted values - none, gzip(GNU Zip Compression))
Audit_log_encryption: (permitted values - none, aes(aes-256-cbc cipher encryption))
Audit_log_rotate_on_size : Must be a multiple of 4096, else truncated to nearest value
Audit log pruning occurs only if the file is set to JSON. So follow the given steps:-
Set audit_log_format to JSON
Set audit_log_rotate_on_size to a value greater than 0 to specify the limit in bytes
Set audit_log_prune_seconds greater than 0 to specify the number of seconds after which rotated log files become subject to pruning.
Or set audit_log_max_size greater than 0 to specify the total number of bytes of logs(along with the rotated ones). Must be at least 7 times the values of audit_log_rotate_on_size. Also must be a multiple of 4096, else will be truncated to the nearest value.
Note: Non zero value of audit_log_rotate_on_size takes precedence over nonzero values of audit_log_prune_seconds.
The audit log size in MySQL should be a multiple of 4096 because this is the typical block size used by the underlying file system. Most file systems store data in blocks of 4096 bytes, so having the audit log size be a multiple of 4096 ensures that the log entries are aligned with these blocks and can be stored and read efficiently.
Reading Audit Log Files
Function: audit_log_read()
Helper: audit_log_read_bookmark()
Arguments to pass:
Start: The position within the log of first event to read
“Start” : {............... }
Timestamp,id : timestamp species the time to read the log when the entry was made. Id specifies the particular event.
Max_array_length : The maximum number of events to read from the log. If the item is omitted, the default is to read to the end of the log or until the read buffer is full, whichever comes first.
audit_log_read('{ "timestamp": "2020-05-24 12:30:00", "id": 0, "max_array_length": 3 }')
audit_log_read('{ "start": { "timestamp": "2020-05-24" } }')
Audit Log Filtering
Can be filtered using the following characteristics:
User Account
Audit event class
Audit event subclass
Audit event fields such as those that indicate operation status or SQL statement executed.
Filter based audit logging
The following functions are used for the purpose:-
audit_log_filter_set_filter() - define a filter
audit_log_filter_remove_filter() - Remove a filter
audit_log_filter_set_user() - Start filtering a user account
audit_log_filter_remove_user() - Stop filtering a user account
audit_log_filter_flush() - Flush manual changes to the filter tables to affect ongoing filtering
Usage:
audit_log_filter_set_filter() :
Select audit_log_filter_set_filter(‘filter_name’, filter_definition);
Example
– logs every event
Select audit_log_filter_set_filter(‘filter_1’,
{
“Filter”:{“log”: TRUE}
}
)
audit_log_filter_set_user():
Select audit_log_filter_set_user(‘user@host’,’filter_name’);
Example
Select audit_log_filter_set_user(‘test@localhost’,’filter_1’);
audit_log_filter_remove_filter(“filter_name”)
audit_log_filter_remove_user(‘user@host’)
Filter based logging
Always specify “filter” at the beginning
Each new set needs {}
Under a class, sub-class / event is written with a comma
For using and, or and not, use [], each set of rule inside it is written within {}
Eg:
{
“Filter” : {“log”: true}
}
Filtering only specific class and subclass
There are 3 types of classes that are mostly used:
Connection
General
Table_access
Each class has its own subclasses:
Logging according to field values:
“log”:
{
“Field” : {“name” : “Field_name”, “value” : “field_value”}
}
The field_names are different for different classes.
For connection event
General Events
Table Access
Eg:- Writing an audit log to log only table access read events on a table named ‘test’ for user test_user only.
Select audit_log_filter_set_filter(
‘Filter_1’,
‘
{
“filter” : {
“class” : {
“name” : “table_access”,
“event” : {
“name” : “read”,
“log” : {
“field” : {“name”: “table_name.str” , “value” : “test”}
}
}
}
}
}
’
);
Select audit_log_filter_set_user(‘test_user@%’, ‘Filter_1’);
Blocking An Query:
Drill down to specific class, event or field, and use (“abort” : “condition”) to block queries to that specific level.
Blocking read queries for user test_user to table test:
Select audit_log_filter_set_filter(
‘Filter_1’,
‘
{
“filter” : {
“class” : {
“name” : “table_access”,
“event” : {
“name” : “read”,
“abort” : {
“field” : {“name”: “table_name.str” , “value” : “test”}
}
}
}
}
}
’
);
Using logical operator:
Operators and, or and not can be used for constructing complex logging rules.
Eg: Logging read queries of user_test on table test of database abc.
Select audit_log_filter_set_filter(
‘Filter_1’,
‘
{
“filter” : {
“class” : {
“name” : “table_access”,
“event” : {
“name” : “read”,
“log” : {
“and” : [
{“field” : {“name”: “table_database.str” , “value” : “abc”}},
{“field” : {“name”: “table_name.str” , “value” : “test”}}
]
}
}
}
}
}’
);
Note: Logging can be set in inclusive or exclusive mode ie.
Inclusive - exclude all events, except that is written in rules.
Exclusive - Include all events, except that is written in rules.
By default filter works in inclusive mode
Using predefined variables:
Predefined variables can be used as condition validators, such that if the value for variable name matches with specified value, it returns true.
Under log/ abort:
“log” : {
“variable” : {
“name” : “variable_name”,
“value” : “value”
}
}
The set of predefined variables are:
audit_log_connection_policy_value
audit_log_policy_value
audit_log_statement_policy_value
Eg: logging only if the database name is airflow and variable audit_log_connection_policy_value is errors.
Select audit_log_filter_set_filter(
‘Filter_1’,
‘
{
“filter” : {
“class” : {
“name” : “table_access”,
“event” : {
“name” : “read”,
“log” : {
“and” : [
{"field" : {"name": "table_database.str", "value" : "airflow"}},
{"variable" : {"name" : "audit_log_connection_policy_value", "value" : "::errors"}}
]
}
}
}
}
}’
);
Using predefined functions:
Can be used in the same way for creating a condition.
“function” : {
“name” : “function_name”,
“args” : {}
}
Eg:
“function” : {
“name” : “find_in_include_list”,
“args” : “root@%”
}
Adding function in above condition
Select audit_log_filter_set_filter(
‘Filter_1’,
‘
{
“filter” : {
“class” : {
“name” : “table_access”,
“event” : {
“name” : “read”,
“log” : {
“and” : [
{"field" : {"name": "table_database.str", "value" : "airflow"}},
{"variable" : {"name" : "audit_log_connection_policy_value", "value" : "::errors"}},
{“function” : {“name” : “find_in_include_list”,“args” : “root@%”}}
]
}
}
}
}
}’
);
See other functions in official docs. Just see what type of argument the function takes and construct a condition.
Replacement of Event Field Values
Query digest can be used in place of actual queries, such that sensitive data are preserved which may be included in the query. However the use is very limited as of this blog. Can be used only with mentioned fields:
Field replacement can take place at different levels of event granularity:
To perform field replacement for all events in a class, filter events at the class level.
To perform replacement on a more fine-grained basis, include additional event-selection items.
Syntax:
"print": {
"field": {
"name": "field_name",
"print": condition,
"replace": replacement_value
}
}
Here,
name: name of field to apply replacement
print : specify a condition (if false: replaces, if true doesn’t replace)
replace: replacement value to use when print condition evaluates to true
Example of rule:
SELECT audit_log_filter_set_filter('filter_1',
'{
"filter" : {
"class": [
{"name" : "connection",
"event" : [
{"name": "connect"},
{"name": "disconnect"}
]
},
{"name" : "general",
"event" : {
"name" : "status",
"log" : {
"or" : [
{"field" : {"name": "general_sql_command.str","value" : "create_table"}},
{"field" : {"name": "general_sql_command.str","value" : "create_db"}},
{"field" : {"name": "general_sql_command.str","value" : "drop_table"}},
{"field" : {"name": "general_sql_command.str","value" : "drop_db"}},
{"field" : {"name": "general_sql_command.str","value" : "truncate"}}
]
}
}
},
{"name" : "table_access",
"event": { "name" : ["read","delete","update","insert"],
"print" : {
"field" : {
"name" : "query.str",
"print" : false,
"replace" : {
"function" : {
"name": "query_digest"
}
}}
}
}
}
]
}}'
)
If we select with a statement:
Select * from db.table where id = 5 limit 1000;
Audit will be written as:
Select * from db.table where id = ? limit ?,....
Filter Writing Guidelines:
First make up your mind for each rule that is going to be used. For example, i need the following filters:
Connection - connect, disconnect
Table_access - select, update, insert, delete
Create table, drop table, create db, drop db, truncate table
Here, we need to use 3 classes: connection, table_access, general
First define a rule with 3 classes:
Select audit_log_filter_set_filter(‘filter_1’,
‘{
“filter” : {
“class” :[
{“name” : “connection”},
{“name”: “table_access”},
{“name”: “general”}
]
}}’)
Now add connect and disconnect event on connection class, read, read, update and insert in table access class :
Select audit_log_filter_set_filter(‘filter_1’,
‘{
“filter” : {
“class” :[
{“name” : “connection”,
“event” : {“name” : [“connect”, “disconnect”]}
“event” : [
{“name” : “connect”},
{“name”: “disconnect”}
]
},
{“name”: “table_access”,
“event” : {“name” : ["read","delete","update","insert"]}
},
{“name”: “general”}
]
}}’)
DDL statements are logged using the general class. Now add filtering rules inside the general class.
Select audit_log_filter_set_filter(‘filter_1’,
‘{
“filter” : {
“class” :[
{“name” : “connection”,
“event” : {“name” : [“connect”, “disconnect”]}
“event” : [
{“name” : “connect”},
{“name”: “disconnect”}
]
},
{“name”: “table_access”,
“event” : {“name” : ["read","delete","update","insert"]}
},
{“name”: “general”,
“event” : {
“name” : “status”,
“log”: {
“or” : [
{“field” : {“name”: "general_sql_command.str","value" : "create_table"}},
{“field” : {“name”: "general_sql_command.str","value" : "create_db"}},
{“field” : {“name”: "general_sql_command.str","value" : "drop_table"}},
{“field” : {“name”: "general_sql_command.str","value" : "drop_db"}},
{“field” : {“name”: "general_sql_command.str","value" : "truncate"}}
]
}
}
}
]
}}’)
Comments
Post a Comment