[MariaDB] Audit 설정

MariaDB에서는 서버 활동의 기록을 위해 서버에 접속한 사람, 실행된 쿼리, 액세스 된 테이블 및 변경된 서버 변수등을 기록하는 감사 플러그인이 존재한다. 이 감사 플러그인은 MariaDB, Mysql에서 작동이 가능하며 MariaDB는  버전 10.0.10 및 5.5.37에 감사 플러그인을 포함하기 시작했다.

 

참고: https://mariadb.com/kb/en/mariadb-audit-plugin/

MariaDB Audit Plugin

 

📌 플러그인 설치

플러그인 설치는 수동으로 진행해야하는데 DB의 재시작 없이도 가능하다.

MariaDB [(none)]> show global variables like 'server_audit%';
Empty set (0.00 sec)

 

기존에는 아무 설치도 되어있지 않는 상태에서 아래와 같이 install soname 'server_audit' 로 설치가 가능하다.

MariaDB [(none)]> install soname 'server_audit';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> 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_events 추가와 logging만 ON으로 돌리고 테스트를 진행했다.

MariaDB [(none)]> 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)

MariaDB [(none)]> set global server_audit_events='connect,query,table';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> set global server_audit_logging=1;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show global variables like '%server_audit%';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| server_audit_events           | CONNECT,QUERY,TABLE   |
| 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          | 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)

📌 테스트

server_audit_file_path를 따로 지정하지 않으면 기본적으로 DB 디렉토리/data/server_audit.log라는 이름으로 로그 파일이 아래와 같은 내용으로 생성된다.

20240527 17:11:14,localhost.localdomain,root,localhost,10,12,QUERY,,'set global server_audit_logging=1',0
20240527 17:11:16,localhost.localdomain,root,localhost,10,13,QUERY,,'show global variables like \'%server_audit%\'',0
20240527 17:13:14,localhost.localdomain,root,localhost,10,0,DISCONNECT,,,0
20240527 17:13:49,localhost.localdomain,root,localhost,11,0,CONNECT,,,0
20240527 17:13:49,localhost.localdomain,root,localhost,11,15,QUERY,,'select @@version_comment limit 1',0
20240527 17:13:56,localhost.localdomain,root,localhost,11,16,QUERY,,'SELECT DATABASE()',0
20240527 17:13:56,localhost.localdomain,root,localhost,11,18,QUERY,test,'show databases',0
20240527 17:13:56,localhost.localdomain,root,localhost,11,19,QUERY,test,'show tables',0
20240527 17:14:02,localhost.localdomain,root,localhost,11,20,QUERY,test,'show tables',0
20240527 17:14:04,localhost.localdomain,root,localhost,11,0,DISCONNECT,test,,0

 

나는 events로 CONNECT, QUERY, TABLE 을 해서 이렇게 실행 쿼리와 연결 상태 등이 나오는데 필요한 정보에 따라서 맞춰서 설정을 하면 될 것 같다.

좀 더 자세한 설정은 아래 링크 참고.

https://mariadb.com/kb/en/mariadb-audit-plugin-options-and-system-variables/

MariaDB Audit Plugin Options and System Variables