Last Updated on 1월 20, 2023 by Jade(정현호)
안녕하세요
이번 포스팅에서는 MySQL 8.0 Community Edition 에서 Audit 기능 사용에 대해서 확인 해보도록 하겠습니다.
Contents
MySQL Audit
Audit 은 MySQL 5.6 버전에 추가 된 기능으로 MySQL Enterprise Edition 에서 audit_log 라는 서버 플러그인을 사용하여 구현된 MySQL Enterprise Audit 기능 입니다.
MySQL Enterprise Audit 은 개방형 MySQL Audit API를 사용하여 특정 MySQL 서버에서 실행되는 연결 및 쿼리 활동에 대한 표준, 정책 기반 모니터링 및 로깅을 가능 하게 합니다
MySQL 에서는 위에서 설명과 같이 MySQL Enterprise Audit 은 상용 제품인 MySQL Enterprise Edition에 포함된 확장 기능입니다
MySQL Community Edition 5.6, 5.7 버전에서는 MariaDB Audit Plugin ,Percona Audit Plugin, Mcafee MySQL Audit Plugin 3개의 다른 곳에서 제작된 플러그인을 통해서 MySQL Community Edition 에서도 Audit 기능을 사용할 수 있었습니다.
MySQL Community Edition 8.0 에서 Audit 기능을 사용하기 위해서 유사한 방법을 통해서 사용할 수 있습니다.
다만 5.7 버전과 달리 현재 사용 가능한 3rd Party Plugin 의 경우 Percona Audit Plugin 가 확인 되고 있습니다
포스팅에서는 Percona Audit Plugin 를 사용하였으며, MySQL 버전은 8.0.23 을 사용하였습니다.
Note
AWS의 RDS for MySQL 에서는 MariaDB Audit Plugin 을 통해서 Audit 기능을 사용할 수 있습니다.
Percona Audit Plugin
Percona Audit Plugin 을 이용하기 위해서는 현재 사용중인 MySQL 버전과 맞는 Percona Server for MySQL 을 통해서 audit 플러그인 파일을 복사가 필요 합니다.
다운로드는 아래 링크에서 받으시면 됩니다.
버전은 맞는 버전을 선택하시면 되며, Linux - Generic 으로 다운로드 받으시면 됩니다.
파일을 리눅스 서버에서 wget 으로 직접 다운로드 받거나, 다운로드 파일을 Linux 에 업로드 한 뒤에 아래와 같은 절차로 파일을 사용중인 MySQL 로 복사를 합니다.
User> tar zxvf Percona-Server-8.0.23-14-Linux.x86_64.glibc2.12.tar.gz User> cd Percona-Server-8.0.23-14-Linux.x86_64.glibc2.12/lib/plugin User> ls -al | grep audit_log -rwxr-xr-x 1 jade jade 1127808 5월 7 2021 audit_log.so User> cp audit_log.so ~/mysql/mysql8.0.23/lib/plugin/
압축을 해제 한 Percona Server for MySQL 디렉토리 내 lib/plugin 안에 audit 플러그인을 확인 할 수 있습니다.
(파일명 : audit_log.so)
해당 파일을 사용중인 MySQL Server 의 lib/plugin 디렉토리로 복사 하시면 됩니다.
작업 방식에 따라서 plugin 의 소유권 변경도 필요 할수도 있습니다.
root#> chown mysql:mysql audit_log.so
Audit Plugin 활성화 및 설정
전 단계에서 Audit 플러그인 파일을 복사를 완료 하였다면 이번 단계에서는 사용중인 MySQL Server 로 접속하여 플러그인 설치를 진행 합니다.
플러그인 설치
먼저 설치 전에 plugin 설치 여부를 확인 해보면 결과가 없음을 알 수 있습니다.
mysql> select * from information_schema.PLUGINS where PLUGIN_NAME like '%audit%'\G Empty set (0.00 sec) mysql> show variables like 'audit%'; Empty set (0.01 sec)
플러그인 인스톨 후 다시 조회해 보겠습니다.
mysql> INSTALL PLUGIN audit_log SONAME 'audit_log.so';
Query OK, 0 rows affected (0.00 sec)
mysql> select *
from information_schema.PLUGINS
where PLUGIN_NAME like '%audit%'\G
*************************** 1. row ***************************
PLUGIN_NAME: audit_log
PLUGIN_VERSION: 0.2
PLUGIN_STATUS: ACTIVE
PLUGIN_TYPE: AUDIT
PLUGIN_TYPE_VERSION: 4.1
PLUGIN_LIBRARY: audit_log.so
PLUGIN_LIBRARY_VERSION: 1.10
PLUGIN_AUTHOR: Percona LLC and/or its affiliates.
PLUGIN_DESCRIPTION: Audit log
PLUGIN_LICENSE: GPL
LOAD_OPTION: ON
1 row in set (0.00 sec)
mysql> show variables like 'audit%';
+-----------------------------+---------------+
| Variable_name | Value |
+-----------------------------+---------------+
| audit_log_buffer_size | 1048576 |
| audit_log_exclude_accounts | |
| audit_log_exclude_commands | |
| audit_log_exclude_databases | |
| audit_log_file | audit.log |
| audit_log_flush | OFF |
| audit_log_format | OLD |
| audit_log_handler | FILE |
| audit_log_include_accounts | |
| audit_log_include_commands | |
| audit_log_include_databases | |
| audit_log_policy | ALL |
| audit_log_rotate_on_size | 0 |
| audit_log_rotations | 0 |
| audit_log_strategy | ASYNCHRONOUS |
| audit_log_syslog_facility | LOG_USER |
| audit_log_syslog_ident | percona-audit |
| audit_log_syslog_priority | LOG_INFO |
+-----------------------------+---------------+
18 rows in set (0.01 sec)
설치 완료 후에 information_schema.pluigins 에서도 플러그인에 정보를 확인 할 수 있으며 audit 관련된 시스템 변수도 추가 된 것을 확인 할 수 있습니다.
audit 관련 설정
플러그인을 설치 후에는 Audit이 활성화 되게 됩니다.
그래서 모든 DB, 모든 유저, 접속 및 수행 명령어 등 모든 대상에 대해서 Audit이 기록이 되게 됩니다.
audit 관련 시스템 변수 종류에서 확인 할 수 있는 것처럼, 다양한 형태로의 Audit 에 대한 설정이나 정책 설정이 가능 합니다.
포스팅에서는 아래와 같이 설정을 진행하였으며 사용하는 환경에 따라서 값은 변경해서 사용하시면 됩니다.
# my.cnf 설정 audit_log_rotations = 5 audit_log_buffer_size = 15728640 audit_log_rotate_on_size = 20971520 audit_log_policy = QUERIES audit_log_include_accounts = 'test@localhost,root@localhost' 또는 set global 으로 설정이 가능 합니다.
몇 가지에 Audit 설정 관련 시스템 변수에 대해서 설명을 드리면
- audit_log_rotate_on_size : Audit 내용이 기록되는 audit.log 의 Rotation 할때의 파일 사이즈를 의미 합니다.
- audit_log_rotations : rotation 된 이전 audit.log 파일을 몇 개까지 유지 할 것이지를 설정 합니다.
- audit_log_buffer_size : 로깅에 사용되는 메모리 버퍼 사이즈 입니다.
audit_log_strategy 시스템 변수가 ASYNCHRONOUS 나 PERFORMANCE 일 경우 사용 됩니다.
- audit_log_policy : 기록될 이벤트 종류에 대한 설정으로 ALL, LOGINS, QUERIES, NONE 을 설정 할 수 있습니다.
각각의 값에 대한 내용은 아래와 같습니다.
ALL - all events will be logged
LOGINS - only logins will be logged
QUERIES - only queries will be logged
NONE - no events will be logged
- audit_log_include_accounts : 특정 유저에 대해서만 로깅 기록을 남기는 설정 입니다.
아이디와 호스트명을 포함하여 입력하면 되며, 콤마(,) 를 통해서 여러 유저를 설정할 수 있습니다.
반대의 의미인 특정 유저를 로깅을 남기지 않겠다는 의미로 audit_log_exclude_accounts 가 있습니다.
audit_log_include_accounts 와 audit_log_exclude_accounts 의 변경 사항은 이미 접속된 세션에 대해서는 적용되지 않습니다.
참고로 MariaDB의 audit plugin 은 유저명만 사용하며 호스트는 사용하지 않습니다.
server_audit_excl_users='user_foo, user_bar'
그외 설정과 관련 된 내용은 아래 도큐먼트를 참고하시면 됩니다.
Audit 로그 확인
audit 내용이 남는지, 또는 어떤 내용으로 기록되는지 간략하게 확인 해 보도록 하겠습니다.
audit.log 파일의 파일명이나 경로에 대한 시스템 변수는 audit_log_file 이며 기본값은 audit.log 이며 기본 경로는 datadir 경로에 파일은 생성 됩니다.
먼저 db 생성 및 일반 유저 생성, 권한 부여를 진행 해보도록 하겠습니다.
mysql> create database tdb; Query OK, 1 row affected (0.00 sec) mysql> create user test@'localhost' identified by 'test'; Query OK, 0 rows affected (0.00 sec) mysql> grant all privileges on tdb.* to test@'localhost'; Query OK, 0 rows affected, 1 warning (0.00 sec)
위의 작업이 기록된 audit 로그 내용은 아래와 같습니다.
<AUDIT_RECORD NAME="Query" RECORD="22603_2022-03-01T12:38:35" TIMESTAMP="2022-03-01T12:40:13Z" COMMAND_CLASS="create_db" CONNECTION_ID="9" STATUS="0" SQLTEXT="create database tdb" USER="root[root] @ localhost []" HOST="localhost" OS_USER="" IP="" DB="" /> <AUDIT_RECORD NAME="Query" RECORD="22604_2022-03-01T12:38:35" TIMESTAMP="2022-03-01T12:40:23Z" COMMAND_CLASS="create_user" CONNECTION_ID="9" STATUS="0" SQLTEXT="CREATE USER 'test'@'localhost' IDENTIFIED BY <secret>" USER="root[root] @ localhost []" HOST="localhost" OS_USER="" IP="" DB="" /> <AUDIT_RECORD NAME="Query" RECORD="22605_2022-03-01T12:38:35" TIMESTAMP="2022-03-01T12:40:40Z" COMMAND_CLASS="grant" CONNECTION_ID="9" STATUS="0" SQLTEXT="GRANT ALL PRIVILEGES ON `tdb`.* TO 'test'@'localhost'" USER="root[root] @ localhost []" HOST="localhost" OS_USER="" IP="" DB="" />
이번에는 위에서 생성한 일반 유저로 접속하여 테이블 생성 , 데이터 입력, 조회를 해보도록 하겠습니다.
mysql> use tdb; Database changed mysql> create table tb_test(id int); Query OK, 0 rows affected (0.01 sec) mysql> insert into tb_test values(1); Query OK, 1 row affected (0.01 sec) mysql> select * from tb_test; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.00 sec)
위의 작업이 기록된 audit 은 아래와 같습니다.
<AUDIT_RECORD NAME="Query" RECORD="22610_2022-03-01T12:38:35" TIMESTAMP="2022-03-01T12:42:18Z" COMMAND_CLASS="select" CONNECTION_ID="10" STATUS="0" SQLTEXT="SELECT DATABASE()" USER="test[test] @ localhost []" HOST="localhost" OS_USER="" IP="" DB="" /> <AUDIT_RECORD NAME="Query" RECORD="22614_2022-03-01T12:38:35" TIMESTAMP="2022-03-01T12:42:30Z" COMMAND_CLASS="create_table" CONNECTION_ID="10" STATUS="0" SQLTEXT="create table tb_test(id int)" USER="test[test] @ localhost []" HOST="localhost" OS_USER="" IP="" DB="" /> <AUDIT_RECORD NAME="Query" RECORD="22615_2022-03-01T12:38:35" TIMESTAMP="2022-03-01T12:42:42Z" COMMAND_CLASS="insert" CONNECTION_ID="10" STATUS="0" SQLTEXT="insert into tb_test values(1)" USER="test[test] @ localhost []" HOST="localhost" OS_USER="" IP="" DB="" /> <AUDIT_RECORD NAME="Query" RECORD="22626_2022-03-01T12:38:35" TIMESTAMP="2022-03-01T13:55:47Z" COMMAND_CLASS="select" CONNECTION_ID="13" STATUS="0" SQLTEXT="select * from tb_test" USER="test[test] @ localhost []" HOST="localhost" OS_USER="" IP="" DB="" />
위와 같이 시간 등의 정보를 포함하여 상세한 정보가 기록되는 것을 확인할 수 있습니다.
MariaDB Plugin 관련
MySQL은 8.0.23 을 사용하였고, MariaDB는 몇가지 버전에 사용하여 테스트를 하였으나 테스트시 사용한 모든 버전에서 아래와 같은 에러가 발생되어 MariaDB Plugin 은 사용이 불가한 것으로 확인하였습니다.
• INSTALL PLUGIN 시 에러 발생된 내역
# 플러그인 인스톨 시도 mysql> INSTALL PLUGIN audit_log SONAME 'server_audit.so'; # 발생된 에러 내역 ERROR 1126 (HY000): Can't open shared library '.../lib/plugin/server_audit.so' (errno: 11 .../lib/plugin/server_audit.so: undefined symbol: PSI_server) (errno: 11 .../lib/plugin/server_audit.so: undefined symbol: my_printf_error)
테스트로 사용한 MariaDB의 버전은 아래와 같고 모두 에러가 발생되었습니다.
-
10.6.7
-
10.5.15
-
10.0.38
-
5.5.68
잠정적으로 설치형 MySQL 8버전대에서는 MariaDB Audit Plugin 사용이 불가능한 것으로 확인 되고 있습니다.
(관련하여 내용이 추가로 더 확인 되는 점이 있다면 메일이나 코멘트로 추가 정보 주시면 감사하겠습니다.)
MySQL Community Edition 8.0 버전에서는 확인 한 내용과 같이 Percona Audit Plugin 을 이용해서 Audit 기능을 사용할 수 있으며 포스팅 내용외 다양한 설정이 있으므로 Percona 문서를 통해서 더 많은 내용을 확인 하시면 더욱더 도움이 되실 서라고 생각 합니다
이번 포스팅은 여기서 마무리 하도록 하겠습니다.
Reference
Reference
• mysql.com/audit-log
• percona.com/audit_log_plugin
관련된 다른 글
Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중
Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io
