개 요
- Mysql내에 접근기록을 사용할 수 있는 Audit 플러그인 설명
- 제약사항
- Mysql 5.6이상 설치가 가능하나, Mysql내에는 Enterprise Edition만 제공되는 것으로 파악
- MariaDB내에는 Audit Plugin이 제공되어 MariaDB에 저장된 플러그인을 통해 Audit 기능 활성화 가능
설치 방법
- Mysql 접속 후 플러그인 경로 확인1
mysql> show global variables like 'plugin_dir';2+---------------+------------------------+3| Variable_name | Value |4+---------------+------------------------+5| plugin_dir | //usr/local/mysql/lib/plugin/ |6+---------------+------------------------+71 row in set (0.00 sec)- 이경우 플러그인이 저장된 경로는 //usr/local/mysql/lib/plugin/ 경로로 설정되어 있음
- audit 플러그인 복사1
$> cp server_audit.so /usr/local/mysql/lib/plugin/ - Mysql에 Audit 플러그인 연동1
mysql> install plugin server_audit soname 'server_audit.so';2Query OK, 0 rows affected (0.00 sec)3mysql> show plugins;4+----------------------------+----------+--------------------+----------------------+---------+5| Name | Status | Type | Library | License |6+----------------------------+----------+--------------------+----------------------+---------+7| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |8| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |9| sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL |10| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |11| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |12| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |13| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |14| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |15| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |16| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |17| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |18| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |19| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |20| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |21| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |22| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |23| INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |24| INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |25| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |26| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |27| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |28| INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL |29| INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |30| INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL |31| INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |32| INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |33| INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL |34| INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |35| INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |36| INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |37| INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |38| INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |39| INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |40| INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |41| INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL |42| INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |43| INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |44| INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |45| INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL |46| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |47| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |48| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |49| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |50| ngram | ACTIVE | FTPARSER | NULL | GPL |51| validate_password | ACTIVE | VALIDATE PASSWORD | validate_password.so | GPL |52| SERVER_AUDIT | ACTIVE | AUDIT | server_audit.so | GPL |53+----------------------------+----------+--------------------+----------------------+---------+ - audit 플러그인 설치 후 맨 마지막 SERVER_AUDIT항목이 보이면 설치 완료
Audit 관련 설정 진행1. 이벤트 설정
1mysql> set global server_audit_events=connect;2Query OK, 0 rows affected (0.00 sec)- 로그 경로 설정1
mysql> set global server_audit_file_path='//usr/local/mysql/logs/server_audit.log';2Query OK, 0 rows affected (0.00 sec) - 로그 로테이트 술정1
mysql> set global server_audit_file_rotate_now=1;2Query OK, 0 rows affected (0.00 sec) - 파일당 최대 사이트 설정, 10M로 설정예시 (Format : Byte)1
mysql> set global server_audit_query_log_limit=10240000;
- 로그 경로 설정
- 보관주기 설정1
mysql> set global server_audit_file_rotations=10;2Query OK, 0 rows affected (0.00 sec)- 10개 보관 설정 (허용된 파일수에 도달하면 오래된 파일을 덮어씀)
- Audit 기능활성화1
mysql> set global server_audit_logging=1;2Query OK, 0 rows affected (0.00 sec) - 설정값 확인1
mysql> show global variables like '%audit%';2+-------------------------------+--------------------------------------------------------------------------------------------------------------+3| Variable_name | Value |4+-------------------------------+--------------------------------------------------------------------------------------------------------------+5| server_audit_events | CONNECT |6| server_audit_excl_users | |7| server_audit_file_path | //usr/local/mysql/logs/server_audit.log |8| server_audit_file_rotate_now | ON |9| server_audit_file_rotate_size | 1000000 |10| server_audit_file_rotations | 10 |11| server_audit_incl_users | |12| server_audit_loc_info | OOOO |13| server_audit_logging | ON |14| server_audit_mode | 1 |15| server_audit_output_type | file |16| server_audit_query_log_limit | 10240000 |17| server_audit_syslog_facility | LOG_USER |18| server_audit_syslog_ident | mysql-server_auditing |19| server_audit_syslog_info | |20| server_audit_syslog_priority | LOG_INFO |21+-------------------------------+--------------------------------------------------------------------------------------------------------------+2216 rows in set (0.01 sec) - 로그 생성 여부 확인1
$> ls -l //usr/local/mysql/logs/server_audit.log2-rw-r----- 1 stoauser stoauser 81 Apr 24 17:01 /usr/local/mysql/logs/server_audit.log34$> tail -f //usr/local/mysql/logs/server_audit.log520190424 17:01:06,DB-TEST,root,localhost,7,0,DISCONNECT,,,0620190424 17:01:35,DB-TEST,root,localhost,8,0,CONNECT,,,0720190424 17:01:46,DB-TEST,root,localhost,8,0,DISCONNECT,,,0 - 영구적용을 위해 설정값 적용1
$> vi /etc/my.cnf2...3######### Audit #############4plugin_load_add = server_audit5server_audit_logging = on6server_audit_events = connect7server_audit_output_type = file8server_audit_file_path = /usr/local/mysql/logs/server_audit.log9server_audit_file_rotate_now = ON10server_audit_file_rotate_size = 100000011server_audit_file_rotations = 102412...
기타사항
- Log output 을 syslog로 전달이 가능하며 server_audit_output_type 값을 syslog로 변경하면 된다.
- File 방식으로 사용할 경우 생성되는 로그는 격리된 공간에서 저장하는 것을 추천
출처 - http://igoni.kr/books/dbms/page/mysql-audit http://igoni.kr/books/dbms/page/mysql-audit
