南大通用GBase 8a 集群审计日志audit_log攻略和使用方法

GBase 8a数据库集群提供了审计日志audit_log,记录数据库执行的所有SQL。通过参数或审计策略,保留符合条件的日志到文件或表里。

作用

记录集群所有执行完成的SQL,包括用户登录失败,SQL语法错误等。

存储过程的审计日志

存储过程的创建是DDL,可以记录完整的SQL语句。

存储过程执行时,审计日志只记录call 的SQL,不记录内部的每个SQL。

注意

  • 默认的审计日志,以FILE形式,保存在audit_log_file参数指定的目录下,默认是是gcluster/log/gcluster/gclusterd-audit.log. 如果以TABLE方式记录时,是保存在gbase.audit_log表里面。
  • 审计日志只记录在当前节点运行的SQL,如果你要看整个集群的SQL,需要逐个访问每个节点。
  • 审计日志分管理节点(gcluster)和数据节点(gnode),其中管理节点是保存外部传来的SQL,而gnode保存的是内部(管理节点下发)的SQL。
  • 审计日志归档。由于gbase.audit_log表能保存的数据行数有限,一般超过千万行就经常报错,所以新版集群(862B33以后)都默认提供了一个审计日志归档功能(具体看后面的章节)。每个【管理节点】的审计日志会自动归档到一个集群表里面,默认是gclusterdb.audit_log_express表。所以如果查询历史审计日志,请从这个集群表里查询,当前的最近【归档周期】内的在gbase.audit_log里。 归档周期在event里由用户自行指定。

使用方法

注意:审计日志一般都是对外部SQL的审计,所以都只建议开启gcluster层的即可。

新版集群862B33+ 有审计策略功能,请看后续的章节。

只开gcluster层审计的方法

该方法适合后面所有操作,请参考执行。

1、最简单的是在管理节点配置审计日志参数,然后重启

2、如果不想重启,则需要如下操作

a、在集群层gccli打开所有审计日志,必须是global的。这样所有节点,包括管理和数据的都打开了。

b、挨个连接数据节点, gncli ,关闭审计日志 也是global的。

临时使用

使用集群客户端 gccli

gbase> set global audit_log=1;
gbase> set global log_output='table';
gbase> set global long_query_time=0;

如上操作同时打开了gcluster和gnode的审计日志,gnode日志会非常多,不建议使用。

第一行是打开审计日志功能。1是打开,0是关闭。

第二行是审计日志记录到数据库表里面。默认是文件。

第三行是记录SQL执行时间阈值,单位秒。大于等于这个值的才记录。0等于全部记录。建议性能排查时设定更高的值。默认10秒。

查询时从gbase.audit_log数据库查看。

长期使用

  请修改【管理节点gcluster】配置文件参数,放到[gbase]部分,并重启数据库服务。

audit_log=1

注意事项:

1、如需要长期记录,请更改配置文件,实现集群重启后依然生效。

2、请定时检查表行数,如超过千万行,建议删除掉。truncate self table audit_log。

3、审计日志分为管理层和计算层,如上是全部打开了。如清理要管理和计算都要清理。

4、审计日志是节点级的,只记录了连接到本节点执行的SQL,整个集群的SQL要查看每个节点。

审计策略

在8.6.2Build33等新版本集群里,引入了审计策略的配置,可以更灵活的配置需要审计并记录的内容。老版本没有这个功能。只有开启了审计策略,才会真正记录日志。其中的各个参数都可以在创建审计日志时配置,具体看产品《管理员手册》

只有audit_log开关参数,log_output保存类型是需要在集群参数设置的,其它的都在审计策略里。

审计策略可以创建多个,并同时生效。

CREATE AUDIT POLICY 审计日志名字[(参数=值,参数=值)];

参数列表如下,可以通过desc gbase.audit_policy看到。

项目名称取值&含义
EnableY:启用,默认值
N:禁用
Hosts'':不限制,默认不限制。
<host>:严格匹配 host,支持空格''分隔的 host 列表,host 可使用'%' 和'_'做通配符
User'':不限制,默认不限制。
<user>:严格匹配 user,区分大小写
Db'':不限制,默认不限制。
<db>:严格匹配
Obj_type'':不限制,默认不限制。
TABLE(VIEW):Object 为表(视图)
PROCEDURE:Object 为存储过程
FUNCTION:Object 为函数
Object'':不限制,默认不限制。
<object>:匹配 Obj_type 指定的 object 
Sql_commands
'':不限制,默认不限制。
INSERT, DELETE, UPDATE, LOAD,
CREATE_USER, CREATE_DB, CREATE_TABLE, CREATE_VIEW, CREATE_INDEX, CREATE_PROCEDURE, CREATE_FUNCTION,
RENAME_USER,
ALTER_DB, ALTER_TABLE, ALTER_PROCEDURE, ALTER_FUNCTION, ALTER_EVENT,
DROP_USER, DROP_DB, DROP_TABLE, DROP_VIEW, DROP_INDEX, DROP_PROCEDURE, DROP_FUNCTION, DROP_EVENT,
TRUNCATE, GRANT, REVOKE, SELECT,OTHERS:
其中的一种或多种类型,多个类型间以逗号‘,’连接,且不添加空格
Long_query_time
<secs>:最小查询秒数,可带 6 位小数,精确到微秒,默认值 0,取
值范围为 0~31536000s
Status'':不限制,默认不限制。
SUCCESS:执行成功
FAILED:执行失败

gbase> desc audit_policy;
+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+----------+-------+
| Field           | Type                                                                                                                                                                                                                                                                                                                                                                                                                | Null | Key | Default  | Extra |
+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+----------+-------+
| Name            | varchar(64)                                                                                                                                                                                                                                                                                                                                                                                                         | NO   | PRI |          |       |
| Enable          | enum('Y','N')                                                                                                                                                                                                                                                                                                                                                                                                       | NO   |     | Y        |       |
| Hosts           | varchar(512)                                                                                                                                                                                                                                                                                                                                                                                                        | NO   |     |          |       |
| User            | varchar(16)                                                                                                                                                                                                                                                                                                                                                                                                         | NO   |     |          |       |
| Db              | varchar(64)                                                                                                                                                                                                                                                                                                                                                                                                         | NO   |     |          |       |
| Obj_type        | enum('','TABLE(VIEW)','PROCEDURE','FUNCTION')                                                                                                                                                                                                                                                                                                                                                                       | NO   |     |          |       |
| Object          | varchar(64)                                                                                                                                                                                                                                                                                                                                                                                                         | NO   |     |          |       |
| Sql_commands    | set('','INSERT','DELETE','UPDATE','LOAD','CREATE_USER','CREATE_DB', 'CREATE_TABLE','CREATE_VIEW',
'CREATE_INDEX','CREATE_PROCEDURE','CREATE_FUNCTION', 'CREATE_EVENT','RENAME_USER','ALTER_DB','ALTER_TABLE','ALTER_PROCEDURE','ALTER_FUNCTION','ALTER_EVENT', 'DROP_USER','DROP_DB','DROP_TABLE','DROP_VIEW','DROP_INDEX','DROP_PROCEDURE','DROP_FUNCTION','DROP_EVENT','TRUNCATE','GRANT','REVOKE','SELECT','OTHERS') | NO   |     |          |       |
| Long_query_time | decimal(18,6)                                                                                                                                                                                                                                                                                                                                                                                                       | NO   |     | 0.000000 |       |
| Status          | enum('','SUCCESS','FAILED')                                                                                                                                                                                                                                                                                                                                                                                         | NO   |     |          |       |
+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+----------+-------+
10 rows in set (Elapsed: 00:00:00.00)

注意:

审计策略,默认只在管理节点有效,在gnode上是不会创建的,所以虽然gnode看参数audit_log是on,但其并不记录日志。如果你需要记录,请自行在各个gnode节点创建审计策略。

sql_commands里的OTHERS,包含了除了如上列出的,所有其它的,比如connect,quit,,set,kill,show等等。

使用样例

打开审计日志

gbase> set global audit_log=1;
Query OK, 0 rows affected (Elapsed: 00:00:00.31)

-- 确认审计日志输出方式,少量的建议用TABLE,大量的(超过千万行)建议用FILE。

gbase> show variables like '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (Elapsed: 00:00:00.00)

创建记录所有操作的审计策略

gbase> create audit policy audit_ALL;
Query OK, 0 rows affected (Elapsed: 00:00:00.03)
gbase> select * from gbase.audit_policy;
+-----------+--------+-------+------+----+----------+--------+--------------+-----------------+--------+
| Name      | Enable | Hosts | User | Db | Obj_type | Object | Sql_commands | Long_query_time | Status |
+-----------+--------+-------+------+----+----------+--------+--------------+-----------------+--------+
| audit_all | Y      |       |      |    |          |        |              |        0.000000 |        |
+-----------+--------+-------+------+----+----------+--------+--------------+-----------------+--------+
1 row in set (Elapsed: 00:00:00.01)

创建除了SELECT和OTHER之外的DML和DDL的审计策略

create audit policy p_dmlddl(sql_commands='INSERT,DELETE,UPDATE,LOAD,CREATE_USER,CREATE_DB,CREATE_TABLE,CREATE_VIEW,CREATE_INDEX,CREATE_PROCEDURE,CREATE_FUNCTION,CREATE_EVENT,RENAME_USER,ALTER_DB,ALTER_TABLE,ALTER_PROCEDURE,ALTER_FUNCTION,ALTER_EVENT,DROP_USER,DROP_DB,DROP_TABLE,DROP_VIEW,DROP_INDEX,DROP_PROCEDURE,DROP_FUNCTION,DROP_EVENT,TRUNCATE,GRANT,REVOKE')

创建记录特定SQL的审计策略

本例只记录某些DDL操作

gbase> create audit policy ddl_dml_policy(sql_commands='update,delete,drop_table,alter_table');
Query OK, 0 rows affected (Elapsed: 00:00:00.03)
gbase> select * from audit_policy;
+----------------+--------+-------+------+----+----------+--------+--------------------------------------+-----------------+--------+
| Name           | Enable | Hosts | User | Db | Obj_type | Object | Sql_commands                         | Long_query_time | Status |
+----------------+--------+-------+------+----+----------+--------+--------------------------------------+-----------------+--------+
| ddl_dml_policy | Y      |       |      |    |          |        | DELETE,UPDATE,ALTER_TABLE,DROP_TABLE |        0.000000 |        |
+----------------+--------+-------+------+----+----------+--------+--------------------------------------+-----------------+--------+
1 row in set (Elapsed: 00:00:00.00)

针对特定表的审计策略

gbase> alter audit policy ap_all set(Obj_type='TABLE(VIEW)',object='t1');
Query OK, 0 rows affected (Elapsed: 00:00:00.01)

gbase> select * from gbase.audit_policy;
+--------+--------+-------+------+----+-------------+--------+--------------+-----------------+--------+
| Name   | Enable | Hosts | User | Db | Obj_type    | Object | Sql_commands | Long_query_time | Status |
+--------+--------+-------+------+----+-------------+--------+--------------+-----------------+--------+
| ap_all | Y      |       |      |    | TABLE(VIEW) | t1     |              |        0.000000 |        |
+--------+--------+-------+------+----+-------------+--------+--------------+-----------------+--------+
1 row in set (Elapsed: 00:00:00.00)

审计策略参数修改

gbase> alter audit policy ddl_dml_policy set(sql_commands='update,delete,create_table,truncate,drop_table,alter_table');
Query OK, 0 rows affected (Elapsed: 00:00:00.00)

gbase> select * from audit_policy;
ERROR 1146 (42S02): Table 'testdb.audit_policy' doesn't exist
gbase> select * from gbase.audit_policy;
+----------------+--------+-------+------+----+----------+--------+------------------------------------------------------------+-----------------+--------+
| Name           | Enable | Hosts | User | Db | Obj_type | Object | Sql_commands                                               | Long_query_time | Status |
+----------------+--------+-------+------+----+----------+--------+------------------------------------------------------------+-----------------+--------+
| ddl_dml_policy | Y      |       |      |    |          |        | DELETE,UPDATE,CREATE_TABLE,ALTER_TABLE,DROP_TABLE,TRUNCATE |        0.000000 |        |
+----------------+--------+-------+------+----+----------+--------+------------------------------------------------------------+-----------------+--------+
1 row in set (Elapsed: 00:00:00.00)

审计策略删除

gbase> select * from gbase.audit_policy;
+---------+--------+-------+------+----+----------+--------+--------------+-----------------+--------+
| Name    | Enable | Hosts | User | Db | Obj_type | Object | Sql_commands | Long_query_time | Status |
+---------+--------+-------+------+----+----------+--------+--------------+-----------------+--------+
| p_all   | Y      |       |      |    |          |        |              |        0.000000 |        |
| p_all_2 | Y      |       |      |    |          |        |              |        0.000000 |        |
+---------+--------+-------+------+----+----------+--------+--------------+-----------------+--------+
2 rows in set (Elapsed: 00:00:00.00)

gbase> drop audit policy p_all_2;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)

gbase> select * from gbase.audit_policy;
+-------+--------+-------+------+----+----------+--------+--------------+-----------------+--------+
| Name  | Enable | Hosts | User | Db | Obj_type | Object | Sql_commands | Long_query_time | Status |
+-------+--------+-------+------+----+----------+--------+--------------+-----------------+--------+
| p_all | Y      |       |      |    |          |        |              |        0.000000 |        |
+-------+--------+-------+------+----+----------+--------+--------------+-----------------+--------+
1 row in set (Elapsed: 00:00:00.00)

审计日志TABLE格式的样例

gbase> select * from gbase.audit_log;
+-----------+---------+---------------------+---------------------+----------------------------+-----+------+------------+-----------------+------+--------+--------------------------------------------------+-------------------------------------+----------+-------------+-----------+---------+-----------+
| thread_id | taskid  | start_time          | end_time            | user_host                  | uid | user | host_ip    | query_time      | rows | db     | table_list                                       | sql_text                            | sql_type | sql_command | operators | status  | conn_type |
+-----------+---------+---------------------+---------------------+----------------------------+-----+------+------------+-----------------+------+--------+--------------------------------------------------+-------------------------------------+----------+-------------+-----------+---------+-----------+
|         7 |       0 | 2020-11-30 09:27:22 | 2020-11-30 09:27:22 | root[root] @  [10.0.2.201] |   1 | root | 10.0.2.201 | 00:00:00.000036 |    0 | testdb |                                                  | Ping                                | OTHERS   | Ping        |           | SUCCESS | CAPI      |
|         7 | 3276834 | 2020-11-30 09:27:22 | 2020-11-30 09:27:22 | root[root] @  [10.0.2.201] |   1 | root | 10.0.2.201 | 00:00:00.000197 |    0 | testdb | WRITE: ; READ: ; OTHER: ;                        | SET NAMES utf8mb4                   | OTHERS   | OTHERS      |           | SUCCESS | CAPI      |
|         7 |       0 | 2020-11-30 09:27:22 | 2020-11-30 09:27:22 | root[root] @  [10.0.2.201] |   1 | root | 10.0.2.201 | 00:00:00.000090 |    0 | testdb | WRITE: ; READ: ; OTHER: ;                        | set  SELF global log_output='table' | OTHERS   | OTHERS      |           | SUCCESS | CAPI      |
|         5 | 3276833 | 2020-11-30 09:27:22 | 2020-11-30 09:27:22 | root[root] @ localhost []  |   1 | root |            | 00:00:00.001803 |    0 | testdb | WRITE: ; READ: ; OTHER: ;                        | set global log_output='table'       | OTHERS   | OTHERS      |           | SUCCESS | CAPI      |
|         5 | 3276835 | 2020-11-30 09:27:27 | 2020-11-30 09:27:27 | root[root] @ localhost []  |   1 | root |            | 00:00:00.000249 |    2 | testdb | WRITE: ; READ: `gbase`.`audit_policy`; OTHER: ;  | select * from gbase.audit_policy    | DQL      | SELECT      |           | SUCCESS | CAPI      |
|         5 | 3276836 | 2020-11-30 09:27:40 | 2020-11-30 09:27:40 | root[root] @ localhost []  |   1 | root |            | 00:00:00.000254 |    2 | testdb | WRITE: ; READ: `gbase`.`audit_policy`; OTHER: ;  | select * from gbase.audit_policy    | DQL      | SELECT      |           | SUCCESS | CAPI      |
+-----------+---------+---------------------+---------------------+----------------------------+-----+------+------------+-----------------+------+--------+--------------------------------------------------+-------------------------------------+----------+-------------+-----------+---------+-----------+
6 rows in set (Elapsed: 00:00:00.00)

审计日志文件格式的样例

# Threadid=11;
# Taskid=3276831;
# End_time: 201130  9:00:00
# User@Host: root[root] @ % [%]
# UID: 10584827
# Query_time: 0.000137 Rows: 0
# Tables: WRITE: `gbase`.`audit_log_bak1`; READ: ; OTHER: ; ;
# SET timestamp=1606698000;
# Sql_text: drop self table gbase.audit_log_bak1;
# Sql_type: DDL;
# Sql_command: DROP_TABLE;
# Status: SUCCESS;
# Connect Type: ;

# Threadid=5;
# Taskid=3276832;
# Time: 201130  9:00:40
# End_time: 201130  9:00:40
# User@Host: root[root] @ localhost []
# UID: 1
# Query_time: 0.001134 Rows: 1
# use testdb;
# Tables: WRITE: ; READ: `gbase`.`audit_policy`; OTHER: ; ;
# SET timestamp=1606698040;
# Sql_text: select * from gbase.audit_policy where name='audit_all';
# Sql_type: DQL;
# Sql_command: SELECT;
# Algorithms: WHERE;
# Status: SUCCESS;
# Connect Type: CAPI;

审计日志归档

862B33版本以后,在集群安装后,会自动在gclusterdb下创建一个import_audit_log的event,该定时任务自动创建gclusterdb.audit_log_express表,并定时将gbase.audit_log的SQL转移到gclusterdb.audit_log_express表里面。

具体的event可以通过show create event gclustedb.import_audit_log查看。

注意:V95版本的审计日志归档功能event, 由于引入了vc,所以默认是不生效的,用户可以删掉现有的event然后再重新创建,或者手工做修改。 参考 GBase 8a数据库集群V95版本相对V8版本变动内容汇总 其中的【重大变动部分】的【审计日志归档的event默认不可用

由于event是节点级别的,在每个管理节点都创建了。如果你更改这个event,请将每个管理节点都更新一下。

该event对数据节点(gnode)的审计日志无效

目前归档表没有老化功能。

862Build20的审计日志

set sql_mode='';
create self table  audit_log_1  (
   thread_id  int(10) unsigned NOT NULL,
   taskid  bigint(20) NOT NULL,
   start_time  timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   end_time  timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
   user_host  mediumtext NOT NULL,
   user  mediumtext NOT NULL,
   host_ip  mediumtext NOT NULL,
   query_time  time NOT NULL,
   rows  bigint(20) NOT NULL,
   db  varchar(512) NOT NULL,
   table_list  mediumtext NOT NULL,
   sql_text  mediumtext NOT NULL,
   sql_type  mediumtext NOT NULL,
   sql_command  mediumtext NOT NULL,
   algorithms  mediumtext NOT NULL,
   status  mediumtext NOT NULL,
   conn_type  mediumtext NOT NULL
) ENGINE=GsSYS 

8.6.2.43-R28的例子,支持utf8mb4

CREATE EVENT "import_audit_log" 
    ON SCHEDULE EVERY 60 MINUTE STARTS '2017-12-01 00:00:00' 
	ON COMPLETION NOT PRESERVE 
	ENABLE LOCAL 
DO begin 
    declare errno int;
    declare msg text;
    declare exit handler for sqlexception 
    begin 
        get diagnostics condition 1 errno = gbase_errno, msg = message_text; 
        create table if not exists import_audit_log_errors(
            err_time datetime, 
            hostname varchar(64), 
            err_no int, 
            msg_txt varchar(1024)
        ) CHARSET=utf8mb4; 
        insert into import_audit_log_errors values (now(), @@hostname, errno, substr(msg, 0, 1024));
    end; 
    create table if not exists audit_log_express (
        hostname varchar(64), 
        thread_id int, 
        taskid bigint, 
        start_time datetime, 
        uid bigint, 
        user varchar(16), 
        host_ip varchar(32), 
        query_time time, 
        rows bigint, 
        table_list varchar(4096), 
        sql_text varchar(8191), 
        sql_type varchar(16), 
        sql_command varchar(32), 
        operators varchar(256), 
        status varchar(16), 
        conn_type varchar(16)
     ) CHARSET=utf8mb4; 
     set self sql_mode = ''; 
     create self table gbase.audit_log_bak2 like gbase.audit_log; 
     set self sql_mode = default; 
     rename self table gbase.audit_log to gbase.audit_log_bak1, gbase.audit_log_bak2 to gbase.audit_log; 
     set _gbase_query_path = on; 
     insert into audit_log_express select 
        @@hostname as hostname, 
        thread_id, 
        taskid, 
        start_time, 
        uid, 
        user, 
        host_ip, 
        query_time, 
        rows, 
        substr(table_list, 0, 4096), 
        substr(sql_text, 0, 8191), 
        sql_type, 
        sql_command, 
        operators, 
        status, 
        conn_type 
    from gbase.audit_log_bak1; 
    drop self table gbase.audit_log_bak1; 
end 

登录密码错误记录

登录数据库密码错误时,审计日志会有记录。

如下登录

[gbase@862b43 ~]$ gccli -uroot -p1111
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

审计日志,登录的sql_command是connect, status为FAILED。

gbase> select * from gbase.audit_log where sql_command='Connect' \G;
*************************** 1. row ***************************
  thread_id: 21
     taskid: 0
 start_time: 2021-03-09 15:35:59
   end_time: 2021-03-09 15:35:59
  user_host: [root] @ localhost []
        uid: 0
       user: root
    host_ip:
 query_time: 00:00:00.000124
       rows: 0
         db:
 table_list:
   sql_text: Connect
   sql_type: OTHERS
sql_command: Connect
  operators:
     status: FAILED
  conn_type: CAPI
1 row in set (Elapsed: 00:00:00.00)

执行语法错误的SQL

如下随意写了个字符串

[gbase@862b43 ~]$ gccli -uroot -e"ABCDE"
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your GBase server version for the right syntax to use near 'ABCDE' at line 1
[gbase@862b43 ~]$ 

审计日志记录。

  thread_id: 22
     taskid: 0
 start_time: 2021-03-09 15:37:41
   end_time: 2021-03-09 15:37:41
  user_host: root[root] @ localhost []
        uid: 1
       user: root
    host_ip:
 query_time: 00:00:00.000046
       rows: 0
         db:
 table_list:
   sql_text: ABCDE
   sql_type: OTHERS
sql_command: OTHERS
  operators:
     status: FAILED
  conn_type: CAPI

南大通用GBase 8a 集群审计日志audit_log攻略和使用方法》有2条评论

评论已关闭。