Довольно часто возникают задачи аудита разных событий в базе данных — это может помочь контролировать активность базы данных для обеспечения безопасности, создания разных отчетов, а так же устранения неполадок. Для MySQL существует несколько плагинов реализующих функционал аудита. Ниже мы познакомимся с этими плагинами и попробуем использовать один из них.
Зачем вообще проводить аудит ваших баз данных?
Аудит является важной задачей для мониторинга базы данных. Он позволяет отслеживать обращения пользователей к данным, что в дальнейшем может предотвратить множество инцидентов в безопасности, когда те или иные пользователи в силу неправильно выданных привилегий или иных обстоятельств получают доступ к той информации, которая им не предназначается.
Например, у нас есть ряд пользователей с различными уровнями доступа к данным, один из пользователей удаляет данные из таблицы, включив аудит администраторы могут проверить все подключения к базе данных, проверить какие команды выполнял нужный пользователь и выявить все удаления строк из таблицы.
Какие плагины аудита доступны для MySQL?
Начиная с версии 5.5.3 в MySQL стало доступно Audit Plugin API, которое можно использовать для создания плагинов аудита. Данное API предоставляет следующие события:
Все существующие на текущий момент плагины аудита предоставляют результат в виде лог-файла. Они отличаются форматом этого лога, возможностью фильтрации и уровнем логирования событий.
Перечислим основные доступные на текущий момент плагины аудита для MySQL:
Выбор конкретного плагина будет зависеть от того какой версией MySQL и от какого производителя (Oracle, Percona или MariaDB) Вы пользуетесь. Т.к. у меня используется MariaDB, то в первую очередь мой выбор пал на MariaDB Audit Plugin, т.к. он идет в составе MariaDB.
Итак,
Исходные данные: ОС Debian 9.4 (Stretch), MariaDB 10.2.13;
Задача: Установить MariaDB Audit Plugin с минимум телодвижений и произвести базовую настройку плагина;
1. Проверка наличия плагина:
Первым делом выясним где у нас находится каталог с плагинами:
# mysql -u root -p mysql MariaDB [mysql]> SHOW GLOBAL VARIABLES LIKE 'plugin_dir'; +---------------+------------------------+ | Variable_name | Value | +---------------+------------------------+ | plugin_dir | /usr/lib/mysql/plugin/ | +---------------+------------------------+ 1 row in set (0.00 sec)
Проверим наличие нужного плагина:
# ls -l /usr/lib/mysql/plugin | grep server_audit -rw-r--r-- 1 root root 59480 Feb 12 20:02 server_audit.so
Отлично, плагин есть.
2. Установка плагина:
Плагины можно устанавливать 2 способами:
1) При работающем сервере без его перезапуска с помощью команды INSTALL PLUGIN
2) При запуске mysql с помощью указания опции plugin_load= в секции [mysqld] файла my.cnf
Я воспользуюсь первым способом установки:
# mysql -u root -p mysql MariaDB [mysql]> install plugin server_audit soname 'server_audit.so';
Проверим список установленных плагинов:
MariaDB [mysql]> show plugins; +-------------------------------+----------+--------------------+--------------------+---------+ | Name | Status | Type | Library | License | +-------------------------------+----------+--------------------+--------------------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | ...... | SERVER_AUDIT | ACTIVE | AUDIT | server_audit.so | GPL | +-------------------------------+----------+--------------------+--------------------+---------+
И посмотрим полную информацию о плагине:
MariaDB [mysql]> select * from information_schema.plugins where plugin_name='SERVER_AUDIT'\G;
*************************** 1. row ***************************
PLUGIN_NAME: SERVER_AUDIT
PLUGIN_VERSION: 1.4
PLUGIN_STATUS: ACTIVE
PLUGIN_TYPE: AUDIT
PLUGIN_TYPE_VERSION: 3.2
PLUGIN_LIBRARY: server_audit.so
PLUGIN_LIBRARY_VERSION: 1.13
PLUGIN_AUTHOR: Alexey Botchkov (MariaDB Corporation)
PLUGIN_DESCRIPTION: Audit the server activity
PLUGIN_LICENSE: GPL
LOAD_OPTION: FORCE_PLUS_PERMANENT
PLUGIN_MATURITY: Stable
PLUGIN_AUTH_VERSION: 1.4.3
1 row in set (0.00 sec)
Плагин установлен. При перезапуске MariaDB плагин будет загружен автоматически, поэтому в my.cnf добавлять ничего не нужно, но если способ с установки помощью команды INSTALL PLUGIN Вам не подходит, то Вы можете прописать в секции [mysqld] файла my.cnf строку:
plugin-load=server_audit=server_audit.so
И тогда при перезапуске mysqld плагин будет загружен и готов к использованию.
Для того, чтобы защитить плагин от возможности его выгрузки (UNINSTALL PLUGIN) необходимо прописать в секции [mysqld] файла my.cnf строку:
server_audit=FORCE_PLUS_PERMANENT
И перезапустить mysqld:
/etc/init.d/mysql restart
Теперь выгрузить плагин не получиться:
MariaDB [mysql]> UNINSTALL PLUGIN server_audit; ERROR 1702 (HY000): Plugin 'server_audit' is force_plus_permanent and can not be unloaded
3. Настройка плагина:
Посмотрим список доступных настроек:
MariaDB [mysql]> show global variables like 'server_audit%'; +-------------------------------+-----------------------+ | Variable_name | Value | +-------------------------------+-----------------------+ | server_audit_events | | | server_audit_excl_users | | | server_audit_file_path | server_audit.log | | server_audit_file_rotate_now | OFF | | server_audit_file_rotate_size | 1000000 | | server_audit_file_rotations | 9 | | server_audit_incl_users | | | server_audit_logging | OFF | | server_audit_mode | 0 | | server_audit_output_type | file | | server_audit_query_log_limit | 1024 | | server_audit_syslog_facility | LOG_USER | | server_audit_syslog_ident | mysql-server_auditing | | server_audit_syslog_info | | | server_audit_syslog_priority | LOG_INFO | +-------------------------------+-----------------------+ 15 rows in set (0.00 sec)
По умолчанию плагин находится в отключенном состоянии, об этом нам говорит строка server_audit_logging = OFF
Включим его и проверим:
MariaDB [mysql]> set global server_audit_logging=on; Query OK, 0 rows affected (0.00 sec) MariaDB [mysql]> show global variables like 'server_audit_logging%'; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | server_audit_logging | ON | +----------------------+-------+ 1 row in set (0.00 sec)
Плагин начал вести аудит, каталог для хранения лога аудита определяется настройкой server_audit_file_path и по умолчанию лог лежит в каталоге определенном в директиве datadir, посмотрим и проверим это:
MariaDB [mysql]> show global variables like 'datadir'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | datadir | /var/lib/mysql/ | +---------------+-----------------+ 1 row in set (0.00 sec)
Проверим каталог /var/lib/mysql
# ls -la /var/lib/mysql | grep server_audit.log -rw-rw---- 1 mysql mysql 9943 Mar 20 18:02 server_audit.log
Файл присутствует и в него уже начали идти данные.
Теперь давайте проведем настройку некоторых параметров:
MariaDB [mysql]> set global server_audit_events='CONNECT,QUERY,QUERY_DDL'; Query OK, 0 rows affected (0.00 sec)
Данной настройкой мы определили для каких типов событий мы будем вести аудит.
Следующей настройкой мы исключим из аудита все действия пользователя root:
MariaDB [mysql]> set global server_audit_excl_users='root'; Query OK, 0 rows affected (0.00 sec)
Проверим все установленные параметры плагина:
MariaDB [mysql]> show global variables like 'server_audit_%'; +-------------------------------+-------------------------+ | Variable_name | Value | +-------------------------------+-------------------------+ | server_audit_events | CONNECT,QUERY,QUERY_DDL | | server_audit_excl_users | root | | server_audit_file_path | server_audit.log | | server_audit_file_rotate_now | OFF | | server_audit_file_rotate_size | 1000000 | | server_audit_file_rotations | 9 | | server_audit_incl_users | | | server_audit_logging | ON | | server_audit_mode | 0 | | server_audit_output_type | file | | server_audit_query_log_limit | 1024 | | server_audit_syslog_facility | LOG_USER | | server_audit_syslog_ident | mysql-server_auditing | | server_audit_syslog_info | | | server_audit_syslog_priority | LOG_INFO | +-------------------------------+-------------------------+ 15 rows in set (0.00 sec)
Следующим обязательным шагом необходимо прописать все наши настройки плагина в секцию [mysqld] файла my.cnf, чтобы при перезапуске mysqld наши настройки не потерялись:
# vi /etc/mysql/my.cnf server_audit_logging=ON server_audit_events='CONNECT,QUERY,QUERY_DDL' server_audit_excl_users='root' server_audit=FORCE_PLUS_PERMANENT
4. Проверка работы плагина:
Теперь давайте проверим работу плагина на реальных запросах к БД, для этого очистим лог аудита server_audit.log, далее создадим пользователя audit, создадим отдельную БД audit_test, создадим простую таблицу и выполним ряд запросов по вставке, обновлению, удалению и выборке данных, после этого посмотрим лог аудита:
# echo>/var/lib/mysql/server_audit.log
# mysql -u root -p mysql
MariaDB [mysql]> CREATE DATABASE audit_test;
Query OK, 1 row affected (0.31 sec)
MariaDB [mysql]> CREATE USER 'audit'@'localhost' IDENTIFIED BY 'auditpwd';
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> GRANT ALL PRIVILEGES ON audit_test.* TO 'audit'@'localhost';
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> exit
Bye
# mysql -u audit -pauditpwd audit_test
MariaDB [audit_test]>
CREATE TABLE `users` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` CHAR(30) NOT NULL,
`age` SMALLINT(6) NOT NULL,
PRIMARY KEY(`id`)
);
Query OK, 0 rows affected (0.30 sec)
MariaDB [audit_test]> INSERT INTO users (name,age) VALUES ('Vasua', 12),('Katya', 18),('Misha', 16),('Sasha', 20);
Query OK, 4 rows affected (0.30 sec)
Records: 4 Duplicates: 0 Warnings: 0
MariaDB [audit_test]> SELECT name,age FROM users WHERE age<18;
+-------+-----+
| name | age |
+-------+-----+
| Vasua | 12 |
| Misha | 16 |
+-------+-----+
2 rows in set (0.00 sec)
MariaDB [audit_test]> DELETE FROM users WHERE name='Vasua';
Query OK, 1 row affected (0.30 sec)
MariaDB [audit_test]> UPDATE users SET age=17 WHERE name='Misha';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [audit_test]> SELECT name,age FROM users WHERE age<18;
+-------+-----+
| name | age |
+-------+-----+
| Misha | 17 |
+-------+-----+
1 row in set (0.00 sec)
MariaDB [audit_test]> DROP TABLE users;
Query OK, 0 rows affected (0.27 sec)
MariaDB [mysql]> exit
Bye
Мы выполнили все операции, проверим лог аудита:
# cat /var/lib/mysql/server_audit.log 20180321 12:39:48,mariadb,root,localhost,16,0,CONNECT,mysql,,0 20180321 12:41:14,mariadb,root,localhost,16,0,DISCONNECT,mysql,,0 20180321 12:41:22,mariadb,audit,localhost,17,0,CONNECT,audit_test,,0 20180321 12:41:22,mariadb,audit,localhost,17,239,QUERY,audit_test,'show databases',0 20180321 12:41:22,mariadb,audit,localhost,17,240,QUERY,audit_test,'show tables',0 20180321 12:41:22,mariadb,audit,localhost,17,241,QUERY,audit_test,'select @@version_comment limit 1',0 20180321 12:41:39,mariadb,audit,localhost,17,242,QUERY,audit_test,'CREATE TABLE `users` (\n`id` INT(11) NOT NULL AUTO_INCREMENT,\n`name` CHAR(30) NOT NULL,\n`age` SMALLINT(6) NOT NULL,\nPRIMARY KEY(`id`)\n)',0 20180321 12:41:47,mariadb,audit,localhost,17,243,QUERY,audit_test,'INSERT INTO users (name,age) VALUES (\'Vasua\', 12),(\'Katya\', 18),(\'Misha\', 16),(\'Sasha\', 20)',0 20180321 12:41:53,mariadb,audit,localhost,17,244,QUERY,audit_test,'SELECT name,age FROM users WHERE age<18',0 20180321 12:41:59,mariadb,audit,localhost,17,245,QUERY,audit_test,'DELETE FROM users WHERE name=\'Vasua\'',0 20180321 12:42:04,mariadb,audit,localhost,17,246,QUERY,audit_test,'UPDATE users SET age=17 WHERE name=\'Misha\'',0 20180321 12:42:09,mariadb,audit,localhost,17,247,QUERY,audit_test,'SELECT name,age FROM users WHERE age<18',0 20180321 12:42:12,mariadb,audit,localhost,17,248,QUERY,audit_test,'DROP TABLE users',0 20180321 12:42:15,mariadb,audit,localhost,17,0,DISCONNECT,audit_test,,0
Как мы видим по логу у нас запротоколированы все операции производимые под пользователем audit в БД audit_test.
Формат лога описан в документации и выглядит так:
[timestamp],[serverhost],[username],[host],[connectionid],[queryid],[operation],[database],[object],[retcode]
Я думаю он не нуждается в объяснениях.
На этом все, до скорых встреч. Если у Вас возникли вопросы или Вы хотите чтобы я помог Вам, то Вы всегда можете связаться со мной разными доступными способами.
Профессионально занимаюсь системным администрированием Linux -серверов и баз данных (MySQL, PostgreSQL) на протяжении последних 24 лет.