MySQL Community Edition:
MySQL Community Edition having feature of auditing but I cannot having feature filter the users
To activate audit log:
|
1 |
Set Global General_Log=1 |
To disable audit log:
|
1 |
Set Global General_Log=0 |
You can also do these setting in my.cnf
MySQL Enterprise Edition:
MySQL Community Edition having feature of auditing and it have several extended features
Check out the audit_log.so in the server. It must be there after the installation of MySQL Enterprise Edition
|
1 |
mysql> INSTALL PLUGIN audit_log SONAME ‘<span class="skimlinks-unlinked">audit_log.so</span>’; |
After the successful installation of .so on mysql configure and activate the auditing as per your requirement by the following commands
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> SHOW VARIABLES LIKE ‘audit_log%’; +---------------------------+--------------+ | Variable_name | Value | +---------------------------+--------------+ | audit_log_buffer_size | 1048576 | | audit_log_file | <span class="skimlinks-unlinked">audit.log</span> | | audit_log_flush | OFF | | audit_log_format | OLD | | audit_log_policy | ALL | | audit_log_rotate_on_size | 0 | | audit_log_strategy | ASYNCHRONOUS | +---------------------------+--------------+ 7 rows in set (0.00 sec) |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
OR mysql> SELECT * FROM information_schema.PLUGINS where PLUGIN_NAME = ‘audit_log’\G *************************** 1. row *************************** PLUGIN_NAME: audit_log PLUGIN_VERSION: 0.1 PLUGIN_STATUS: ACTIVE PLUGIN_TYPE: AUDIT PLUGIN_TYPE_VERSION: 3.1 PLUGIN_LIBRARY: <span class="skimlinks-unlinked">audit_log.so</span> PLUGIN_LIBRARY_VERSION: 1.4 PLUGIN_AUTHOR: Oracle PLUGIN_DESCRIPTION: Auditing events logger PLUGIN_LICENSE: PROPRIETARY LOAD_OPTION: ON 1 row in set (0.02 sec) |
To Enable Auditing for specific Account do the following addition to My.CNF file of MySQL Instance
|
1 2 3 4 5 6 |
audit_log_file = audit.log (This file will be created in the base_dir of mysql home) audit_log_rotate_on_size = 200MB (This will rotate the audit file if the size exceeds from this mentioned value) audit-log = ON (to enable auditing) audit_log_include_accounts = 'root_dba@xxxxx,root_dba@xxxxx,root@localhost' (to mention specific accounts only) log_timestamps = SYSTEM |
You can enable several filtration on auditing like Specific Tables,SQL Queries , User Accounts, Hosts etc. One of the example are as follows i.e. enable auditing for specific user accounts only.
