반응형
MariaDB는 10.0.10 및 5.5.37 버전에서 기본적으로 Audit Plugin이 패키지에 포함되어 있다.
server_audit 플러그인은 서버의 활동을 기록하며 클라이언트 세션에 대해 서버에 연결된 사용자 (예 : 사용자 이름 및 호스트), 실행 된 쿼리, 액세스 된 테이블 및 변경된 서버 변수를 기록한다.
이 정보는 로그파일에 기록되거나 local syslogd 에 저장되도록 설정이 가능하다.
server_audit 플러그인의 공유 라이브러리는 같은 MariaDB 패키지에 포함 된 server_audit.so또는 server_audit.dll이 구축 될 수있는 시스템에 라이브러리를 공유했다
참조 : https://mariadb.com/kb/en/mariadb-audit-plugin/
MariaDB Audit Plugin
mariadb.com
MariaDB Audit Plugin
mariadb.com
1. Audit 설정 확인
플러그인은 MariaDB 용 모든 플러그인 라이브러리가 포함 된 플러그인 디렉토리에 있어야 한다.
a. Plugin이 디렉토리의 위치를 확인
MariaDB [(none)]> show global variables like 'plugin_dir%';
--------------
show global variables like 'plugin_dir%'
--------------
+---------------+--------------------------+
| Variable_name | Value |
+---------------+--------------------------+
| plugin_dir | /usr/lib64/mysql/plugin/ |
+---------------+--------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]>
b. server_audit.so 라이브러리파일이 해당 디렉토리에 있어야 하며
만약 없으면 상위 버전을 설치하거나 상위버전의 라이브러리에서 해당 파일을 copy해 온다.
$ ls /usr/lib64/mysql/plugin/server_audit*
/usr/lib64/mysql/plugin/server_audit.so
c. Plugin 리스트를 통해 Audit Plugin이 설치되어 있는지 확인
MariaDB [(none)]> show plugins ;
--------------
show plugins
--------------
+-------------------------------+----------+--------------------+---------+---------+
| Name | Status | Type | Library | License |
+-------------------------------+----------+--------------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| wsrep | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CLIENT_STATISTICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INDEX_STATISTICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| TABLE_STATISTICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| USER_STATISTICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
… 생략
| INNODB_TABLESPACES_SCRUBBING | ACTIVE | INFORMATION SCHEMA | NULL | BSD |
| Aria | ACTIVE | STORAGE ENGINE | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| SEQUENCE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEEDBACK | DISABLED | INFORMATION SCHEMA | NULL | GPL |
| user_variables | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
+-------------------------------+----------+--------------------+---------+---------+
52 rows in set (0.00 sec)
d. audit 환경변수가 설정되어 있는지 확인
MariaDB [(none)]> show global variables like 'server_audit%';
--------------
show global variables like 'server_audit%'
--------------
Empty set (0.00 sec)
MariaDB [(none)]>2. Audit 설치
라이브러리 파일을 확인 했다면 Audit 설치는 매우 간단하게 할 수 있다.
a. Install plugin 명령으로 audit plugin 설치
MariaDB [(none)]> INSTALL SONAME 'server_audit' ;
Query OK, 0 rows affected (0.00 sec)b. server_audit Plugin이 설치되어 있는지 확인
MariaDB [(none)]> show plugins ;
--------------
show plugins
--------------
+-------------------------------+----------+--------------------+-----------------+---------+
| Name | Status | Type | Library | License |
+-------------------------------+----------+--------------------+-----------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| wsrep | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CLIENT_STATISTICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INDEX_STATISTICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| TABLE_STATISTICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| USER_STATISTICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
… 생략
| INNODB_TABLESPACES_SCRUBBING | ACTIVE | INFORMATION SCHEMA | NULL | BSD |
| Aria | ACTIVE | STORAGE ENGINE | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| SEQUENCE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEEDBACK | DISABLED | INFORMATION SCHEMA | NULL | GPL |
| user_variables | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| SERVER_AUDIT | ACTIVE | AUDIT | server_audit.so | GPL |
+-------------------------------+----------+--------------------+-----------------+---------+
53 rows in set (0.00 sec)c. audit 환경변수가 설정되어 있는지 확인
기본 구성은 아래와 같으며 아직 audit 기능은 꺼져있다.
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)
d. audit 설정은 DB접속으로 바로 가능하며 DB 재기동시 설정값이 없어지므로 my.cnf에 설정하도록 한다.
MariaDB [(none)]> set global server_audit_events='query_dml_no_select,query_ddl,query_dcl';
MariaDB [(none)]> set global server_audit_logging = 1 ;
e. my.cnf 설정은 mysqld 밑에 설정을 해주면 된다.
[mysqld]
server_audit_output_type = file
server_audit_query_log_limit = 1024
server_audit_syslog_ident = mysql-server_auditing
server_audit_logging = 1
server_audit_file_path = /var/lib/mysql/audit/server_audit.log
server_audit_events = query_ddl,query_dcl,query_dml_no_select
server_audit_file_rotate_size = 1000000
server_audit_file_rotations = 10
설정 옵션에 대한 상세한 사항은 아래 링크를 참조한다.
https://mariadb.com/kb/en/mariadb-audit-plugin-options-and-system-variables/#server_audit_events
MariaDB Audit Plugin Options and System Variables
Description of Server_Audit plugin options and system variables.
mariadb.com
MariaDB Audit Plugin Options and System Variables
Description of Server_Audit plugin options and system variables.
mariadb.com
3. Audit 로그 확인
audit log 경로는 server_audit_file_path 을 설정하거나 설정하지 않으면 기본적으로 경로 data 디렉토리에 파일이 생성된다.
a. DML, DCL Test
MariaDB [(none)]> use test
MariaDB [(none)]>
MariaDB [test]> create table test (aa varchar(10));
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]>
MariaDB [test]> insert into test values ('aa') ;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]>
MariaDB [test]> update test set aa = 'bb';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [(none)]>
MariaDB [test]> truncate table test ;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]>
MariaDB [mysql]> GRANT SELECT ON test.* TO 'roo_db_sel'@'172.31.32.10' ;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]>
MariaDB [mysql]> revoke SELECT ON test.* from 'roo_db_sel'@'172.31.32.10' ;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]>
MariaDB [test]> drop table test ;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]>
b. Audit log를 보면 설정한 event에 대해 어떤 구문이 실행되었는지 확인가능하다.
$ tail -f server_audit.log
20200825 11:26:09,192.168.0.100,root,localhost,8,1,QUERY,,'select @@version_comment limit 1',0
20200825 11:26:24,192.168.0.100,root,localhost,8,2,QUERY,,'SELECT DATABASE()',0
20200825 11:32:12,192.168.0.100,root,localhost,8,22,QUERY,test,'create table test (aa varchar(10))',0
20200825 11:32:48,192.168.0.100,root,localhost,8,23,QUERY,test,'insert into test values (\'aa\')',0
20200825 11:33:20,192.168.0.100,root,localhost,8,25,QUERY,test,'update test set aa = \'bb\'',0
20200825 11:33:26,192.168.0.100,root,localhost,8,26,QUERY,test,'truncate table test',0
20200825 11:38:28,192.168.0.100,root,localhost,8,65,QUERY,test,'GRANT SELECT ON test.* TO \'test_user\'@\'192.168.0.100\'',0
20200825 11:38:39,192.168.0.100,root,localhost,8,66,QUERY,test,'revoke SELECT ON test.* from \'test_user\'@\'192.168.0.100\'',0
20200825 11:39:43,192.168.0.100,root,localhost,8,67,QUERY,test,'drop table test',0C. Log Format 참조
[timestamp],[serverhost],[username],[host],[connectionid], [queryid],[operation],[database],[object],[retcode]
| Item logged | Description |
| timestamp | Time at which the event occurred. If syslog is used, the format is defined by syslogd. |
| serverhost | The MariaDB server host name. |
| username | Connected user. |
| host | Host from which the user connected. |
| connectionid | Connection ID number for the related operation. |
| queryid | Query ID number, which can be used for finding the relational table events and related queries. For TABLE events, multiple lines will be added. |
| operation | Recorded action type: CONNECT, QUERY, READ, WRITE, CREATE, ALTER, RENAME, DROP. |
| database | Active database (as set by USE). |
| object | Executed query for QUERY events, or the table name in the case of TABLE events. |
| retcode | Return code of the logged operation |
이상 MariaDB Audit 설정을 설명하였습니다.
반응형
'Database > Mysql' 카테고리의 다른 글
| Mysql / Mariadb에서 쉘명령어 사용하기 (0) | 2020.12.14 |
|---|---|
| MariaDB Password 복잡도 설정 (6) | 2020.12.14 |
| mysql / mariadb grant 설정 (0) | 2020.12.14 |
| EC2에서 Mariadb 설치하기 (0) | 2020.12.14 |
| mysql/mariadb Aborted connection 에러 (0) | 2020.12.14 |
댓글