MySQL Security – MySQL Enterprise Audit
When thinking about security within a MySQL installation, you should consider a wide range of possible procedures / best practices and how they affect the security of your MySQL server and related applications. MySQL provides many tools / features / plugins in order to protect your data including some advanced features like Transparent Data Encryption aka TDE, Data Masking & De-Identification, Firewall, Password Management, Password Validation Plugin, etc…
In order to spot database misuse and/or to prove compliance to popular regulations including GDPR, PCI DSS, HIPAA, … database administrators can be required to record and audit database activities. In this fifth episode of the MySQL Security series, we will see what MySQL Enterprise Audit provides to help organizations implement stronger security controls and satisfy regulatory compliance.
MySQL Enterprise Audit
MySQL Enterprise Edition includes MySQL Enterprise Audit which uses the open MySQL Audit API to enable standard, policy-based monitoring, logging, and blocking of connection and query activity executed on specific MySQL servers.
Audit plugin enables MySQL Server to produce a XML (default) or JSON log file (named audit.log) 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
I’m using MySQL 5.7.21 Enterprise Edition :
SELECT VERSION(); +-------------------------------------------+ | VERSION() | +-------------------------------------------+ | 5.7.21-enterprise-commercial-advanced-log | +-------------------------------------------+
Updated on 22nd of August 2018
Note: MySQL Enterprise Audit works with MySQL 8.0 as well. In other words examples below could be done with MySQL 8.0.12+
To install MySQL Enterprise Audit, look in the share directory of your MySQL installation and choose the script that is appropriate for your platform :
– audit_log_filter_win_install.sql : Choose this script for Windows systems that use .dll as the file name suffix.
– audit_log_filter_linux_install.sql : Choose this script for Linux and similar systems that use .so as the file name suffix.
Then run the script :
e.g.
$ mysql -u root -p < audit_log_filter_linux_install.sql
You can verify the plugin installation examining the INFORMATION_SCHEMA.PLUGINS table or use the SHOW PLUGINS statement
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit%'; +-------------+---------------+ | PLUGIN_NAME | PLUGIN_STATUS | +-------------+---------------+ | audit_log | ACTIVE | +-------------+---------------+
mysql> SHOW PLUGINS\G ... [snip] ... *************************** 45. row *************************** Name: audit_log Status: ACTIVE Type: AUDIT Library: audit_log.so License: PROPRIETARY
The Audit plugin installed a bunch of User-Defined Function (UDF) :
mysql> -- Display all the UDFs installed by the Audit plugin SELECT * FROM mysql.func; +-----------------------------------+-----+--------------+----------+ | name | ret | dl | type | +-----------------------------------+-----+--------------+----------+ | audit_log_filter_set_filter | 0 | audit_log.so | function | | audit_log_filter_remove_filter | 0 | audit_log.so | function | | audit_log_filter_set_user | 0 | audit_log.so | function | | audit_log_filter_remove_user | 0 | audit_log.so | function | | audit_log_filter_flush | 0 | audit_log.so | function | | audit_log_read_bookmark | 0 | audit_log.so | function | | audit_log_read | 0 | audit_log.so | function | | audit_log_encryption_password_set | 2 | audit_log.so | function | | audit_log_encryption_password_get | 0 | audit_log.so | function | +-----------------------------------+-----+--------------+----------+
These functions are described here.
The Audit plugin enables MySQL Server to produce a log file based in the datadir and named audit.log. By default, its contents is written in XML format, without compression nor encryption :
mysql> SHOW VARIABLES LIKE 'datadir'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | datadir | /var/lib/mysql/ | +---------------+-----------------+
$ cat /var/lib/mysql/audit.log <?xml version="1.0" encoding="UTF-8"?> <AUDIT> <AUDIT_RECORD> <TIMESTAMP>2018-03-28T13:42:01 UTC</TIMESTAMP> <RECORD_ID>1_2018-03-28T13:42:01</RECORD_ID> <NAME>Audit</NAME> <SERVER_ID>0</SERVER_ID> <VERSION>1</VERSION> <STARTUP_OPTIONS>mysqld</STARTUP_OPTIONS> <OS_VERSION>x86_64-linux-glibc2.12</OS_VERSION> <MYSQL_VERSION>5.7.21-enterprise-commercial-advanced</MYSQL_VERSION> </AUDIT_RECORD>
Configuration
After MySQL Enterprise Audit is installed, you can use the audit_log option for subsequent server startups to control the Audit plugin activation.
e.g. Prevent the plugin from being removed at runtime, select JSON as logging format and force log file rotation when it reaches 1MB :
[mysqld] audit_log=FORCE_PLUS_PERMANENT audit_log_format=JSON audit_log_rotate_on_size=1048576
Then restart the MySQL server.
$ cat /var/lib/mysql/audit.log [ { "timestamp":"2018-03-28 14:54:27", "id":0, "class":"audit", "event":"startup", "connection_id":0, "startup_data":{ "server_id":0, "os_version":"x86_64-linux-glibc2.12", "mysql_version":"5.7.21-enterprise-commercial-advanced", "args":[ "mysqld" ] } }
We have now a JSON format audit log file.
As you can see, by default, contents of audit log files produced by the audit log plugin are not encrypted and may contain sensitive information, such as the text of SQL statements. For security reasons, audit log files should be written to a directory accessible only to the MySQL server and to users with a legitimate reason to view the log.
For additional security, you can enable audit log file encryption.
Encryption
To encrypt your audit log file, the MySQL keyring must be enabled because audit logging uses it for password storage. Note that any keyring plugin can be used e.g. : file-based, encrypted file, OKV KMIP compatible, AWS, …
To control whether audit log file encryption is enabled, set the audit_log_encryption system variable at server startup. Permitted values are NONE (no encryption; the default) and AES (AES-256-CBC cipher encryption).
To set or get the encryption password, use these user-defined functions (UDFs):
- To set the encryption password, invoke audit_log_encryption_password_set(), which stores the password in the keyring, renames the current log file, and begins a new log file encrypted with the new password.
- To get the current encryption password, invoke audit_log_encryption_password_get(), which retrieves the password from the keyring.
More details here.
Let’s practice!
Keyring Plugin Installation : keyring_encrypted_file
Like I stated before many keyring plugins are available. I’ll use the keyring plugin : keyring_encrypted_file, that stores keyring data in an encrypted file local to the server host.
Warning
The keyring_encrypted_file plugin for encryption key management is not intended as a regulatory compliance solution. Security standards such as PCI, FIPS, and others require use of key management systems to secure, manage, and protect encryption keys in key vaults or hardware security modules (HSMs).
Update the MySQL configuration file and restart the MySQL server :
[mysqld] audit_log=FORCE_PLUS_PERMANENT audit_log_format=JSON audit_log_rotate_on_size=1048576 audit_log_encryption=AES early-plugin-load=keyring_encrypted_file.so keyring_encrypted_file_data=/var/lib/mysql/keyring/keyring-encrypted keyring_encrypted_file_password=myS3cureP4ssw%rd
- To be usable during the server startup process, keyring_encrypted_file must be loaded using the early-plugin-load option.
- To specify the password for encrypting the keyring data file, set the keyring_encrypted_file_password system variable.
- The keyring_encrypted_file_data system variable optionally configures the location of the file used by the keyring_encrypted_file plugin for data storage.
- Set the audit_log_encryption system variable to AES.
Warning
Because the my.cnf file stores a password when written as shown, it should have a restrictive mode and be accessible only to the account used to run the MySQL server.
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS, PLUGIN_LIBRARY, PLUGIN_DESCRIPTION FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME = 'keyring_encrypted_file'\G *************************** 1. row *************************** PLUGIN_NAME: keyring_encrypted_file PLUGIN_STATUS: ACTIVE PLUGIN_LIBRARY: keyring_encrypted_file.so PLUGIN_DESCRIPTION: store/fetch authentication data to/from an encrypted file
Now the default name of Audit log file is audit.log.enc instead of audit.log.
And obviously this is not the only change. Guess what? this new file is… encrypted (AES-256-CBC cipher encryption)
$ strings '/var/lib/mysql/audit.log.enc' Salted__ 9,]`<Lrc}P d.Zw !U\? v_l% Oq}{/ d_qa S7C0 ...
If you don’t want to use encryption anymore you’ll need to update audit_log_encryption option in your MySQL configuration file.
e.g.
[mysqld] audit_log_encryption=NONE
Then restart the MySQL server
Compression
You can lowering the disk footprint of your audit logs with compression.
To control whether audit log file compression is enabled, set the audit_log_compression system variable at server startup.
Permitted values are NONE (no compression; the default) and GZIP (GNU Zip compression).
More details here.
Let’s practice!
SHOW VARIABLES LIKE 'audit_log_compression'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | audit_log_compression | NONE | +-----------------------+-------+
Update the MySQL server configuration file :
[mysqld] audit_log=FORCE_PLUS_PERMANENT audit_log_format=JSON audit_log_rotate_on_size=1048576 audit_log_compression=GZIP
Then restart the server :
SHOW VARIABLES LIKE 'audit_log_compression'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | audit_log_compression | GZIP | +-----------------------+-------+
Now the default name of Audit log file is audit.log.gz instead of audit.log.
This new file is compressed with GNU Zip.
$ ls -l audit.* ... 832 Apr 3 14:38 audit.20180403T123833.log.enc ... 803 Apr 3 14:49 audit.20180403T124958.log ... 20 Apr 3 14:50 audit.log.gz
Note
If both compression and encryption are enabled, compression occurs before encryption. To recover the original file manually, first decrypt it, then uncompress it.
If you don’t want to use compression anymore you’ll need to update audit_log_compression option in your MySQL configuration file.
e.g.
[mysqld] audit_log_compression=NONE
Then restart the MySQL server
Audit Log Filtering
Another amazing feature is the audit log filtering functions. It enables filtering control in JSON format by providing an interface to create, modify, and remove filter definitions and assign filters to user accounts.
When a connection arrives, the audit log plugin determines which filter to use for the new session by searching for the user account name in the current filter assignments:
- If a filter is assigned to the user, the audit log uses that filter.
- Otherwise, if no user-specific filter assignment exists, but there is a filter assigned to the default account (%), the audit log uses the default filter.
- Otherwise, the audit log selects no audit events from the session for processing.
By default, no accounts have a filter assigned, so no processing of auditable events occurs for any account.
The following list briefly summarizes the UDFs that implement the SQL interface for audit filtering control:
- 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
Detailed description here.
Examples
Users and Filters can be monitored respectively with mysql.audit_log_user and mysql.audit_log_filter tables
e.g.
mysql> SELECT * FROM mysql.audit_log_user\G *************************** 1. row *************************** USER: % HOST: FILTERNAME: log_all SELECT * FROM mysql.audit_log_filter\G *************************** 1. row *************************** NAME: log_all FILTER: {"filter": {"log": true}}
Logging All Events
You can log all auditable events for all users by create a simple filter to enable logging and assign it to the default account:
mysql> SELECT audit_log_filter_set_filter('log_all', '{ "filter": { "log": true } }'); +-------------------------------------------------------------------------+ | audit_log_filter_set_filter('log_all', '{ "filter": { "log": true } }') | +-------------------------------------------------------------------------+ | OK | +-------------------------------------------------------------------------+ SELECT audit_log_filter_set_user('%', 'log_all'); +-------------------------------------------+ | audit_log_filter_set_user('%', 'log_all') | +-------------------------------------------+ | OK | +-------------------------------------------+
The filter assigned to % is used for connections from any account that has no explicitly assigned filter (which initially is true for all accounts).
To determine whether a filter has been assigned to the current session, check the session value of the read-only audit_log_filter_id system variable. If the value is 0, no filter is assigned. A nonzero value indicates the internally maintained ID of the assigned filter:
mysql> -- Check if a filter has been assigned to the current session SELECT @@audit_log_filter_id; +-----------------------+ | @@audit_log_filter_id | +-----------------------+ | 0 | +-----------------------+ -- Re-connection connect Connection id: 7 Current database: *** NONE *** SELECT @@audit_log_filter_id; +-----------------------+ | @@audit_log_filter_id | +-----------------------+ | 1 | +-----------------------+ -- Execute a query of your choice e.g. SHOW SCHEMAS; +--------------------+ | Database | +--------------------+ | information_schema | | drupal | ... [snip] ... -- Reads the most recently written audit log event from the audit log SELECT audit_log_read(audit_log_read_bookmark())\G *************************** 1. row *************************** audit_log_read(audit_log_read_bookmark()): [ {"timestamp":"2018-04-03 15:34:11","id":0,"class":"general","event":"status","connection_id":7,"account":{"user":"root","host":"localhost"},"login":{"user":"root","os":"","ip":"","proxy":""},"general_data":{"command":"Query","sql_command":"show_databases","query":"SHOW SCHEMAS","status":0}}, null ]
Note
Starting with MySQL 5.7.22, you can use JSON_PRETTY function that prints out a JSON value in a format that is easy to read.
Updated on 22nd of August 2018
For convenience, a JSON_PRETTY example with MySQL 8.0.12:
mysql> SELECT VERSION(); +-------------------+ | VERSION() | +-------------------+ | 8.0.12-commercial | +-------------------+ SELECT JSON_PRETTY(CONVERT(audit_log_read(audit_log_read_bookmark()) using utf8mb4))\G *************************** 1. row *************************** JSON_PRETTY(CONVERT(audit_log_read(audit_log_read_bookmark()) using utf8mb4)): [ { "id": 0, "class": "general", "event": "status", "login": { "ip": "", "os": "", "user": "msandbox", "proxy": "" }, "account": { "host": "localhost", "user": "msandbox" }, "timestamp": "2018-08-22 13:19:54", "general_data": { "query": "SELECT CONVERT(audit_log_read(audit_log_read_bookmark()), JSON)", "status": 0, "command": "Query", "sql_command": "select" }, "connection_id": 12 }, null ]
Remove a filter on a user account
I don’t want to audit all users anymore. Instead I’ll use the log_all filter to audit the user account olivier@%.
-- Stop auditing all users SELECT audit_log_filter_remove_user('%'); +-----------------------------------+ | audit_log_filter_remove_user('%') | +-----------------------------------+ | OK | +-----------------------------------+ SELECT audit_log_read(audit_log_read_bookmark())\G *************************** 1. row *************************** audit_log_read(audit_log_read_bookmark()): [ {"timestamp":"2018-04-03 15:48:08","id":0,"class":"general","event":"status","connection_id":7,"account":{"user":"root","host":"localhost"},"login":{"user":"root","os":"","ip":"","proxy":""},"general_data":{"command":"Query","sql_command":"select","query":"SELECT audit_log_filter_remove_user('%')","status":0}}, null ] -- Audit still enabled for the current session SELECT 1; +---+ | 1 | +---+ | 1 | +---+ SELECT audit_log_read(audit_log_read_bookmark())\G *************************** 1. row *************************** audit_log_read(audit_log_read_bookmark()): [ {"timestamp":"2018-04-03 15:50:42","id":0,"class":"general","event":"status","connection_id":7,"account":{"user":"root","host":"localhost"},"login":{"user":"root","os":"","ip":"","proxy":""},"general_data":{"command":"Query","sql_command":"select","query":"SELECT 1","status":0}}, null ] -- Reset the session connect Connection id: 10 Current database: *** NONE *** SELECT 2; +---+ | 2 | +---+ | 2 | +---+ -- No more entries in the audit log SELECT audit_log_read(audit_log_read_bookmark())\G *************************** 1. row *************************** audit_log_read(audit_log_read_bookmark()): [ {"timestamp":"2018-04-03 15:50:49","id":0,"class":"connection","event":"disconnect","connection_id":7,"account":{"user":"root","host":"localhost"},"login":{"user":"root","os":"","ip":"","proxy":""},"connection_data":{"connection_type":"socket"}}, null ]
Note
Filtering of current sessions for the user remains unaffected.
Assign a filter explicitly to a particular user account
The filter is already defined (log_all), let’s assign it to the olivier@% user account :
mysql_root> SELECT audit_log_filter_set_user('olivier@%', 'log_all'); +---------------------------------------------------+ | audit_log_filter_set_user('olivier@%', 'log_all') | +---------------------------------------------------+ | OK | +---------------------------------------------------+
In another session connection with user olivier :
$ mysql -uolivier -p
All commands from user olivier are audited :
mysql_root> -- Root session SELECT audit_log_read(audit_log_read_bookmark())\G *************************** 1. row *************************** audit_log_read(audit_log_read_bookmark()): [ {"timestamp":"2018-04-03 16:01:09","id":0,"class":"connection","event":"connect","connection_id":15,"account":{"user":"olivier","host":"localhost"},"login":{"user":"olivier","os":"","ip":"","proxy":""},"connection_data":{"connection_type":"socket","status":0,"db":""}}, {"timestamp":"2018-04-03 16:01:09","id":1,"class":"general","event":"status","connection_id":15,"account":{"user":"olivier","host":"localhost"},"login":{"user":"olivier","os":"","ip":"","proxy":""},"general_data":{"command":"Query","sql_command":"select","query":"select @@version_comment limit 1","status":0}}, {"timestamp":"2018-04-03 16:01:09","id":2,"class":"general","event":"status","connection_id":15,"account":{"user":"olivier","host":"localhost"},"login":{"user":"olivier","os":"","ip":"","proxy":""},"general_data":{"command":"Query","sql_command":"select","query":"select USER()","status":0}}, null ] -- Olivier session mysql_olivier> SELECT 'Olivier'; +---------+ | Olivier | +---------+ | Olivier | +---------+ -- Back to root session mysql_root> SELECT audit_log_read(audit_log_read_bookmark())\G *************************** 1. row *************************** audit_log_read(audit_log_read_bookmark()): [ {"timestamp":"2018-04-03 16:17:17","id":0,"class":"general","event":"status","connection_id":15,"account":{"user":"olivier","host":"localhost"},"login":{"user":"olivier","os":"","ip":"","proxy":""},"general_data":{"command":"Query","sql_command":"select","query":"SELECT 'Olivier'","status":0}}, null ]
Logging Specific Event Classes
Create a new filter : log_table_access_events
It allows to audit all table accesses. This filter is assigned to user olivier. So it replace any previous filter.
mysql_root> SELECT audit_log_filter_set_filter('log_table_access_events', '{ "filter": { "class": { "name": "table_access" } } }'); +-----------------------------------------------------------------------------------------------------------------+ | audit_log_filter_set_filter('log_table_access_events', '{ "filter": { "class": { "name": "table_access" } } }') | +-----------------------------------------------------------------------------------------------------------------+ | OK | +-----------------------------------------------------------------------------------------------------------------+ SELECT audit_log_filter_set_user('olivier@%', 'log_table_access_events'); +-------------------------------------------------------------------+ | audit_log_filter_set_user('olivier@%', 'log_table_access_events') | +-------------------------------------------------------------------+ | OK | +-------------------------------------------------------------------+ SELECT * FROM mysql.audit_log_user WHERE USER='olivier'\G *************************** 1. row *************************** USER: olivier HOST: % FILTERNAME: log_table_access_events SELECT * FROM mysql.audit_log_filter WHERE NAME='log_table_access_events'\G *************************** 1. row *************************** NAME: log_table_access_events FILTER: {"filter": {"class": {"name": "table_access"}}}
Connection with user olivier
$ mysql -u olivier -p
Run a query on a table (table access)
-- Olivier's session mysql_olivier> SELECT * FROM actor LIMIT 1\G *************************** 1. row *************************** actor_id: 1 first_name: PENELOPE last_name: GUINESS last_update: 2006-02-15 04:34:33
This command is logged in Audit log file :
mysql_root> SELECT audit_log_read(audit_log_read_bookmark())\G *************************** 1. row *************************** audit_log_read(audit_log_read_bookmark()): [ {"timestamp":"2018-04-03 17:03:03","id":0,"class":"table_access","event":"read","connection_id":25,"account":{"user":"olivier","host":"localhost"},"login":{"user":"olivier","os":"","ip":"","proxy":""},"table_access_data":{"db":"sakila","table":"actor","query":"SELECT * FROM actor LIMIT 1","sql_command":"select"}}, null ]
Other kinds of queries will not be logged :
-- Olivier's session mysql_olivier> CREATE SCHEMA s1; Query OK, 1 row affected (0,01 sec)
The Audit log file did not changed
mysql_root> SELECT audit_log_read(audit_log_read_bookmark())\G *************************** 1. row *************************** audit_log_read(audit_log_read_bookmark()): [ {"timestamp":"2018-04-03 17:03:03","id":0,"class":"table_access","event":"read","connection_id":25,"account":{"user":"olivier","host":"localhost"},"login":{"user":"olivier","os":"","ip":"","proxy":""},"table_access_data":{"db":"sakila","table":"actor","query":"SELECT * FROM actor LIMIT 1","sql_command":"select"}}, null ]
It’s also possible to select a specific event on these tables like INSERT, DELETE, UPDATE…
You’ll find all the details here.
Blocking Execution of Specific Events
Event items can include an abort item that indicates whether to prevent qualifying events from executing.
This is convenient to block execution of specific SQL statements.
Let’s block INSERTs, UPDATEs and DELETEs on sakila.payment table :
mysql_root> -- This filter blocks INSERT, UPDATE, and DELETE statements only for sakila.payment table SET @filter = '{"filter": {"class": {"name": "table_access","event": {"name": [ "insert", "update", "delete" ],"abort": {"and": [{ "field": { "name": "table_database.str", "value": "sakila" } }, { "field": { "name": "table_name.str", "value": "payment" } }]}}}}}'; Query OK, 0 rows affected (0,00 sec) SELECT audit_log_filter_set_filter('block_writes_on_table_payment_events', @filter); +------------------------------------------------------------------------------+ | audit_log_filter_set_filter('block_writes_on_table_payment_events', @filter) | +------------------------------------------------------------------------------+ | OK | +------------------------------------------------------------------------------+ SELECT audit_log_filter_set_user('olivier@%', 'block_writes_on_table_payment_events'); +--------------------------------------------------------------------------------+ | audit_log_filter_set_user('olivier@%', 'block_writes_on_table_payment_events') | +--------------------------------------------------------------------------------+ | OK | +--------------------------------------------------------------------------------+ SELECT * FROM mysql.audit_log_user WHERE USER='olivier'\G *************************** 1. row *************************** USER: olivier HOST: % FILTERNAME: block_writes_on_table_payment_events SELECT * FROM mysql.audit_log_filter WHERE NAME='block_writes_on_table_payment_events'\G *************************** 1. row *************************** NAME: block_writes_on_table_payment_events FILTER: {"filter": {"class": {"name": "table_access", "event": {"name": ["insert", "update", "delete"], "abort": {"and": [{"field": {"name": "table_database.str", "value": "sakila"}}, {"field": {"name": "table_name.str", "value": "payment"}}]}}}}}
When using user olivier :
# Connection with user olivier $ mysql -u olivier -p
Any forbidden queries will be blocked…
mysql_olivier> SELECT COUNT(*) FROM sakila.payment; +----------+ | COUNT(*) | +----------+ | 16049 | +----------+ -- MySQL Audit plugin prevents writes on sakila.payment DELETE FROM sakila.payment; ERROR 1045 (28000): Statement was aborted by an audit log filter SELECT COUNT(*) FROM sakila.payment; +----------+ | COUNT(*) | +----------+ | 16049 | +----------+
and logged by the MySQL Audit plugin.
mysql_root> SELECT audit_log_read(audit_log_read_bookmark())\G *************************** 1. row *************************** audit_log_read(audit_log_read_bookmark()): [ {"timestamp":"2018-04-03 17:29:22","id":0,"class":"table_access","event":"delete","connection_id":28,"account":{"user":"olivier","host":"localhost"},"login":{"user":"olivier","os":"","ip":"","proxy":""},"table_access_data":{"db":"sakila","table":"payment","query":"DELETE FROM sakila.payment","sql_command":"delete"}}, null ]
$ tail /var/lib/mysql/audit.log { "timestamp": "2018-04-03 17:28:00", "id": 0, "class": "table_access", "event": "read", "connection_id": 25, "account": { "user": "olivier", "host": "localhost" }, "login": { "user": "olivier", "os": "", "ip": "", "proxy": "" }, "table_access_data": { "db": "sakila", "table": "payment", "query": "SELECT COUNT(*) FROM sakila.payment", "sql_command": "select" } }, { "timestamp": "2018-04-03 17:29:22", "id": 0, "class": "table_access", "event": "delete", "connection_id": 28, "account": { "user": "olivier", "host": "localhost" }, "login": { "user": "olivier", "os": "", "ip": "", "proxy": "" }, "table_access_data": { "db": "sakila", "table": "payment", "query": "DELETE FROM sakila.payment", "sql_command": "delete" } }
And now Olivier is in big troubles!!! 🙂
MySQL Enterprise Audit plugin is very powerful!
Please read the documentation for more information.
Uninstallation
To remove MySQL Enterprise Audit, execute the following statements :
DROP TABLE IF EXISTS mysql.audit_log_filter; DROP TABLE IF EXISTS mysql.audit_log_user; 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;
Voilà!
MySQL Enterprise Edition
MySQL Enterprise Edition includes the most comprehensive set of advanced features, management tools and technical support to achieve the highest levels of MySQL scalability, security, reliability, and uptime.
It reduces the risk, cost, and complexity in developing, deploying, and managing business-critical MySQL applications.
MySQL Enterprise Edition server Trial Download (Note – Select Product Pack: MySQL Database).
In order to go further
MySQL Security Series
- Password Validation Plugin
- Password Management
- User Account Locking
- The Connection-Control Plugins
- Enterprise Audit
- Enterprise Transparent Data Encryption (TDE)
- Enterprise Firewall
- Enterprise Data Masking and De-Identification
Reference Manual
- MySQL 5.7 Installing the MySQL Enterprise Audit Plugin
- MySQL 5.7 Enterprise Audit
- Presentation of MySQL 5.7 Enterprise Audit
MySQL Security
Watch my videos on my YouTube channel and subscribe.
Thanks for using HeatWave & MySQL!
Cloud Solutions Architect at Oracle
MySQL Geek, author, blogger and speaker
I’m an insatiable hunger of learning.
—–
Blog: www.dasini.net/blog/en/
Twitter: https://twitter.com/freshdaz
SlideShare: www.slideshare.net/freshdaz
Youtube: https://www.youtube.com/channel/UC12TulyJsJZHoCmby3Nm3WQ
—–
[…] MySQL Enterprise Audit […]
[…] Enterprise Audit […]
[…] Enterprise Audit […]
[…] Enterprise Audit […]
[…] tools / features / plugins in order to protect your data including some advanced features like Audit, TDE, Password Management, Password Validation Plugin, User Account Locking, […]
[…] tools / features / plugins in order to protect your data including some advanced features like Audit, Firewall, Password Management, Password Validation Plugin, User Account Locking, […]
[…] protect your data including some advanced features like Transparent Data Encryption aka TDE, Audit, Firewall, Password Management, Password Validation Plugin, […]
[…] Enterprise Audit : Audit de la base de données afin de détecter une mauvaise utilisation et/ou de se conformer à la réglementation. […]
[…] to protect your data including some advanced features like Transparent Data Encryption (TDE), Audit, Data Masking & De-Identification, Firewall, Password Expiration Policy, Password Reuse Policy, […]
[…] to protect your data including some advanced features like Transparent Data Encryption (TDE), Audit, Data Masking & De-Identification, Firewall, Random Password Generation, Password Reuse Policy, […]
[…] to protect your data including some advanced features like Transparent Data Encryption (TDE), Audit, Data Masking & De-Identification, Firewall, Random Password Generation, Password Expiration […]
[…] to protect your data including some advanced features like Transparent Data Encryption (TDE), Audit, Data Masking & De-Identification, Firewall, Random Password Generation, Password Expiration […]
[…] to protect your data including some advanced features like Transparent Data Encryption (TDE), Audit, Data Masking & De-Identification, Firewall, Random Password Generation, Password Expiration […]
[…] to protect your data including some advanced features like Transparent Data Encryption (TDE), Audit, Data Masking & De-Identification, Firewall, Random Password Generation, Password Expiration […]
Hello, although I set mysql audit as follows, all data is logged(select-insert-update-delete). However, I just want the deletion and update statements to be logged.
Where am I making a mistake?
Thank you from now.
SELECT audit_log_filter_set_filter(‘test’, ‘{
“filter”: {
“class”: [
{
“name”: “connection”,
“event”: [
{ “name”: “connect” },
{ “name”: “disconnect” }
]
},
{ “name”: “general” },
{
“name”: “table_access”,
“event”: [
{ “name”: “read”, “log”: false },
{ “name”: “insert”, “log”: false },
{ “name”: “delete”, “log”: true },
{ “name”: “update”, “log”: true }
]
}
]
}
}’) AS ‘Result’;