{"id":2222,"date":"2018-04-04T13:48:29","date_gmt":"2018-04-04T12:48:29","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=2222"},"modified":"2020-12-08T16:24:42","modified_gmt":"2020-12-08T15:24:42","slug":"mysql-security-mysql-enterprise-audit","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2018\/04\/04\/mysql-security-mysql-enterprise-audit\/","title":{"rendered":"MySQL Security – MySQL Enterprise Audit"},"content":{"rendered":"

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<\/a>, Data Masking & De-Identification<\/a>, Firewall<\/a>, Password Management<\/a>, Password Validation Plugin<\/a>, etc…<\/p>\n

\"MySQL<\/p>\n

In order to spot database misuse<\/strong> and\/or to prove compliance<\/strong> to popular regulations including GDPR<\/strong><\/a>, PCI DSS<\/strong><\/a>, HIPAA<\/strong><\/a>, … database administrators can be required to record and audit database activities. In this fifth episode of the\u00a0MySQL\u00a0 Security<\/strong> series, we will see what MySQL Enterprise Audit<\/a> provides to help organizations implement stronger security controls and satisfy regulatory compliance.<\/p>\n

MySQL Enterprise Audit<\/h2>\n

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.<\/p>\n

Audit plugin enables MySQL Server to produce a XML (default) or JSON log file (named audit.log<\/em>) 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.<\/p>\n

\"MySQL<\/p>\n

Installation<\/h3>\n

I’m using MySQL 5.7.21 Enterprise Edition<\/strong> :<\/p>\n

SELECT VERSION();\n+-------------------------------------------+\n| VERSION()                                 |\n+-------------------------------------------+\n| 5.7.21-enterprise-commercial-advanced-log |\n+-------------------------------------------+<\/pre>\n
Updated on 22nd of August 2018
Note<\/span>: MySQL Enterprise Audit\u00a0works with MySQL 8.0<\/strong><\/span> as well. In other words examples below could be done with MySQL 8.0.12<\/strong>+<\/em><\/p>\n
To install MySQL Enterprise Audit, look in the share directory of your MySQL installation and choose the script that is appropriate for your platform :<\/p>\n
– audit_log_filter_win_install.sql<\/strong> : Choose this script for Windows systems that use .dll as the file name suffix.<\/p>\n
– audit_log_filter_linux_install.sql<\/strong> : Choose this script for Linux and similar systems that use .so as the file name suffix.<\/p>\n
Then run the script :<\/p>\n
e.g.<\/p>\n
$ mysql -u root -p < audit_log_filter_linux_install.sql<\/pre>\n
You can verify the plugin installation examining the INFORMATION_SCHEMA.PLUGINS<\/strong> table or use the SHOW PLUGINS<\/strong> statement<\/p>\n
mysql> \nSELECT PLUGIN_NAME, PLUGIN_STATUS \nFROM INFORMATION_SCHEMA.PLUGINS \nWHERE PLUGIN_NAME LIKE 'audit%';\n+-------------+---------------+\n| PLUGIN_NAME | PLUGIN_STATUS |\n+-------------+---------------+\n| audit_log   | ACTIVE        |\n+-------------+---------------+<\/pre>\n
mysql> \nSHOW PLUGINS\\G\n... [snip] ...\n*************************** 45. row ***************************\n   Name: audit_log\n Status: ACTIVE\n   Type: AUDIT\nLibrary: audit_log.so\nLicense: PROPRIETARY\n<\/pre>\n
The Audit plugin installed a bunch of User-Defined Function (UDF) :<\/p>\n
mysql> \n-- Display all the UDFs installed by the Audit plugin\nSELECT * FROM  mysql.func;\n+-----------------------------------+-----+--------------+----------+\n| name                              | ret | dl           | type     |\n+-----------------------------------+-----+--------------+----------+\n| audit_log_filter_set_filter       |   0 | audit_log.so | function |\n| audit_log_filter_remove_filter    |   0 | audit_log.so | function |\n| audit_log_filter_set_user         |   0 | audit_log.so | function |\n| audit_log_filter_remove_user      |   0 | audit_log.so | function |\n| audit_log_filter_flush            |   0 | audit_log.so | function |\n| audit_log_read_bookmark           |   0 | audit_log.so | function |\n| audit_log_read                    |   0 | audit_log.so | function |\n| audit_log_encryption_password_set |   2 | audit_log.so | function |\n| audit_log_encryption_password_get |   0 | audit_log.so | function |\n+-----------------------------------+-----+--------------+----------+<\/pre>\n
These functions are described here<\/a>.<\/p>\n
The Audit plugin enables MySQL Server to produce a log file based in the datadir<\/em>\u00a0and named audit.log<\/em><\/strong>. By default, its contents is written in XML format, without compression nor encryption :<\/p>\n
mysql> \nSHOW VARIABLES LIKE 'datadir';\n+---------------+-----------------+\n| Variable_name | Value           |\n+---------------+-----------------+\n| datadir       | \/var\/lib\/mysql\/ |\n+---------------+-----------------+<\/pre>\n
$ cat \/var\/lib\/mysql\/audit.log\n<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n<AUDIT>\n <AUDIT_RECORD>\n  <TIMESTAMP>2018-03-28T13:42:01 UTC<\/TIMESTAMP>\n  <RECORD_ID>1_2018-03-28T13:42:01<\/RECORD_ID>\n  <NAME>Audit<\/NAME>\n  <SERVER_ID>0<\/SERVER_ID>\n  <VERSION>1<\/VERSION>\n  <STARTUP_OPTIONS>mysqld<\/STARTUP_OPTIONS>\n  <OS_VERSION>x86_64-linux-glibc2.12<\/OS_VERSION>\n  <MYSQL_VERSION>5.7.21-enterprise-commercial-advanced<\/MYSQL_VERSION>\n <\/AUDIT_RECORD><\/pre>\n
Configuration<\/h3>\n
After MySQL Enterprise Audit is installed, you can use the audit_log<\/strong><\/em><\/a> option for subsequent server startups to control the Audit plugin activation.<\/p>\n
e.g. Prevent the plugin from being removed at runtime, select JSON as logging format and force log file rotation when it reaches 1MB :<\/p>\n
[mysqld]\naudit_log=FORCE_PLUS_PERMANENT\naudit_log_format=JSON\naudit_log_rotate_on_size=1048576<\/pre>\n
Then restart the MySQL server.<\/p>\n
$ cat \/var\/lib\/mysql\/audit.log\n[  \n   {  \n      \"timestamp\":\"2018-03-28 14:54:27\",\n      \"id\":0,\n      \"class\":\"audit\",\n      \"event\":\"startup\",\n      \"connection_id\":0,\n      \"startup_data\":{  \n         \"server_id\":0,\n         \"os_version\":\"x86_64-linux-glibc2.12\",\n         \"mysql_version\":\"5.7.21-enterprise-commercial-advanced\",\n         \"args\":[  \n            \"mysqld\"\n         ]\n      }\n   }<\/pre>\n
We have now a JSON format audit log file.<\/p>\n
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.\u00a0For security reasons, audit log files should be written to a directory accessible only to the MySQL server<\/span> and to users with a legitimate reason<\/span> to view the log.<\/p>\n
For additional security, you can enable audit log file encryption<\/strong><\/a>.<\/p>\n
Encryption<\/h3>\n
To encrypt your audit log file, the MySQL keyring<\/a> must be enabled because audit logging uses it for password storage. Note that any keyring plugin can be used e.g. :\u00a0file-based<\/a>, encrypted file<\/a>, OKV KMIP compatible<\/a>, AWS<\/a>, …<\/p>\n
To control whether audit log file encryption is enabled, set the audit_log_encryption<\/a> system variable at server startup. Permitted values are NONE<\/strong> (no encryption; the default) and AES<\/strong> (AES-256-CBC cipher encryption).<\/p>\n
To set or get the encryption password, use these user-defined functions (UDFs):<\/p>\n