Audyt w MySQL

Aby przejrzeć dziennik zdarzeń bazy MySQL wydajemy polecenie:

[root@localhost ~]# journalctl -u mariadb

-- Logs begin at Thu 2017-04-06 09:06:41 CEST, end at Thu 2017-04-06 10:16:07 CEST. --
Apr 06 09:06:50 localhost.localdomain systemd[1]: Starting MariaDB database server...
Apr 06 09:06:50 localhost.localdomain mysqld[900]: 2017-04-06  9:06:50 139838324652288 [Note] /usr/sbin/mysqld (mysqld 10.1.22-MariaDB) starting as pr
Apr 06 09:06:51 localhost.localdomain mysqld[900]: 2017-04-06  9:06:51 139838324652288 [Note] InnoDB: Using mutexes to ref count buffer pool pages
Apr 06 09:06:51 localhost.localdomain mysqld[900]: 2017-04-06  9:06:51 139838324652288 [Note] InnoDB: The InnoDB memory heap is disabled
. . . 
Dumping buffer pool(s) not yet started
Apr 06 09:59:12 localhost.localdomain mysqld[2222]: 2017-04-06  9:59:12 140128322922752 [Note] Server socket created on IP: '::'.
Apr 06 09:59:12 localhost.localdomain mysqld[2222]: 2017-04-06  9:59:12 140128322922752 [Note] /usr/sbin/mysqld: ready for connections.
Apr 06 09:59:12 localhost.localdomain mysqld[2222]: Version: '10.1.22-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
Apr 06 09:59:12 localhost.localdomain systemd[1]: Started MariaDB database server.

Nic tam jednak nie znajdziemy na temat wykonywanych operacji na bazie.

Możliwe jest logowanie wykonywanych operacji na bazie jednak wymaga to pewnej korekty konfiguracji gdyż takie logowanie domyślnie jest wyłączone.

Aby to uruchomić należy parametr bazy GENERAL_LOG zmienić z wartości 0 na 1

MariaDB [(none)]> SHOW variables where Variable_name like 'GENERAL%';

+------------------+---------------+
| Variable_name    | Value         |
+------------------+---------------+
| general_log      | OFF           |
| general_log_file | localhost.log |
+------------------+---------------+
2 rows in set (0.00 sec)

Zmieniamy parametr. Możemy to zrobić na dwa sposoby
z systemu:
shell> ./mysqld_safe --GLOBAL general_log=0
z bazy:
MariaDB [(none)]> SET GLOBAL general_log=1;

Query OK, 0 rows affected (0.00 sec)

Sprawdzamy wartość parametru po zmianie:
MariaDB [(none)]> SHOW variables where Variable_name like 'GENERAL%';

+------------------+---------------+
| Variable_name    | Value         |
+------------------+---------------+
| general_log      | ON            |
| general_log_file | localhost.log |
+------------------+---------------+
2 rows in set (0.00 sec)

Plik z dziennikiem aktywności w bazie localhost.log znajduje się w folderze z danymi (parametr DATADIR) czyli
/var/lib/mysql/

[root@localhost mysql]# less localhost.log

/usr/sbin/mysqld, Version: 10.1.22-MariaDB (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
170406 10:59:27     5 Query     SHOW variables where Variable_name like 'GENERAL%'
170406 10:59:51     5 Query     select * from testtable1
170406 10:59:57     5 Query     SELECT DATABASE()
                    5 Init DB   testowadb1
                    5 Query     show databases
                    5 Query     show tables
                    5 Field List        testtable1
170406 10:59:59     5 Query     select * from testtable1
170406 11:00:05     5 Quit
170406 11:02:14     6 Connect   newuser1@localhost as anonymous on
                    6 Connect   Access denied for user 'newuser1'@'localhost' (using password: YES)
170406 11:02:20     7 Connect   newuser@localhost as anonymous on
                    7 Query     select @@version_comment limit 1
170406 11:02:37     7 Query     select * from testowadb1.testtable1
170406 11:02:41     7 Quit

Dane są zapisywane do pliku ale jest też możliwe zapisywanie historii zdarzeń do tabeli w bazie MYSQL. Należy pamiętać by ją systematycznie czyścić aby nie zapchać sobie dysków.

Sprawdzamy jak jest ustawiony zapis obecnie na bazie

MariaDB [(none)]> show variables where variable_name like 'log_out%';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.00 sec)

MariaDB [(none)]> SHOW variables where Variable_name like 'GENERAL_LOG%';

+------------------+---------------+
| Variable_name    | Value         |
+------------------+---------------+
| general_log      | ON            |
| general_log_file | localhost.log |
+------------------+---------------+
2 rows in set (0.00 sec)

Zmienamy FILE (plik) na TABLE (tabela)

MariaDB [(none)]> SET GLOBAL log_output='TABLE';

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show variables where variable_name like 'log_out%';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | TABLE |
+---------------+-------+
1 row in set (0.00 sec)

Wykonujemy polecenie (jakiekolwiek)

MariaDB [(none)]> select * from testowadb1.testtable1;
z użytkownika ROOT i NEWUSER.

A teraz logujemy się na ROOT’a i sprawdzamy czy się zapisało do naszej tabeli

MariaDB [(none)]> select * from mysql.general_log;

+----------------------------+---------------------------------+-----------+-----------+--------------+--------------------------------------------------------+
| event_time                 | user_host                       | thread_id | server_id | command_type | argument                                               |
+----------------------------+---------------------------------+-----------+-----------+--------------+--------------------------------------------------------+
| 2017-04-06 11:28:22.109494 | root[root] @ localhost []       |         9 |         0 | Query        | show variables where variable_name like 'log_out%'     |
| 2017-04-06 11:28:29.351519 | root[root] @ localhost []       |         9 |         0 | Query        | select * from testowadb1.testtable1                    |
| 2017-04-06 11:28:34.782370 | root[root] @ localhost []       |         9 |         0 | Query        | select * from mysql.general_log                        |
| 2017-04-06 11:32:29.073839 | root[root] @ localhost []       |         9 |         0 | Query        | show variables where variable_name like 'log_file%'    |
| 2017-04-06 11:32:38.601735 | root[root] @ localhost []       |         9 |         0 | Query        | show variables where variable_name like '%log_file%'   |
| 2017-04-06 11:32:57.712631 | root[root] @ localhost []       |         9 |         0 | Query        | show variables where variable_name like 'generel_log%' |
| 2017-04-06 11:33:05.875826 | root[root] @ localhost []       |         9 |         0 | Query        | show variables where variable_name like 'general_log%' |
| 2017-04-06 11:35:51.019294 | root[root] @ localhost []       |         9 |         0 | Query        | select * from mysql.general_log                        |
| 2017-04-06 11:36:00.201369 | root[root] @ localhost []       |         9 |         0 | Quit         |                                                        |
| 2017-04-06 11:36:10.484673 | [newuser] @ localhost []        |        10 |         0 | Connect      | newuser@localhost as anonymous on                      |
| 2017-04-06 11:36:10.485163 | newuser[newuser] @ localhost [] |        10 |         0 | Query        | select @@version_comment limit 1                       |
| 2017-04-06 11:36:20.618065 | newuser[newuser] @ localhost [] |        10 |         0 | Query        | select * from testowadb1.testtable1                    |
| 2017-04-06 11:36:29.589132 | newuser[newuser] @ localhost [] |        10 |         0 | Query        | show variables                                         |
| 2017-04-06 11:36:32.870548 | newuser[newuser] @ localhost [] |        10 |         0 | Quit         |                                                        |
| 2017-04-06 11:36:45.320248 | [root] @ localhost []           |        11 |         0 | Connect      | root@localhost as anonymous on                         |
| 2017-04-06 11:36:45.320700 | root[root] @ localhost []       |        11 |         0 | Query        | select @@version_comment limit 1                       |
| 2017-04-06 11:36:49.299322 | root[root] @ localhost []       |        11 |         0 | Query        | select * from mysql.general_log                        |
+----------------------------+---------------------------------+-----------+-----------+--------------+--------------------------------------------------------+
17 rows in set (0.01 sec)

Możemy oczywiście sobie ustawiać warunki na zapytaniu np żeby sprawdzić tylko logowania do bazy

MariaDB [(none)]> select event_time,user_host,command_type,argument from mysql.general_log where command_type like 'CONNECT';

+----------------------------+--------------------------+--------------+------------------------------------+
| event_time                 | user_host                | command_type | argument                           |
+----------------------------+--------------------------+--------------+------------------------------------+
| 2017-04-06 11:36:10.484673 | [newuser] @ localhost [] | Connect      | newuser@localhost as anonymous on  |
| 2017-04-06 11:36:45.320248 | [root] @ localhost []    | Connect      | root@localhost as anonymous on     |
+----------------------------+--------------------------+--------------+------------------------------------+
2 rows in set (0.00 sec)

Na koniec prac jeśli już nie potrzebujemy to wyłączamy logowanie:
MariaDB [(none)]> SHOW variables where Variable_name like 'general_log';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log   | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

Ten wpis został opublikowany w kategorii MySQL (MariaDB) i oznaczony tagami . Dodaj zakładkę do bezpośredniego odnośnika.

Dodaj komentarz