GBase 8a
运维管理
文章

GBase8a 审计管理

可口
发表于2024-11-28 16:33:23138次浏览0个评论

审计管理

1. 审计日志概述

审计日志用于记录用户的数据库操作，审计其行为，主要用于安全管理。审计日志将执行时间超过 long_query_time 值的SQL 记录下来，方便用户针对这些执行效率低下的SQL 语句进行分析、优化和改写，从而提高SQL 语句的执行效率。

2. 审计日志参数配置

操作场景

指导管理员开启或关闭审计日志。

操作方法

 执行如下命令，开启审计日志。可以通过配置文件或 global 级控制。

SET GLOBAL audit_log = 1;

执行如下命令，设定审计日志存放在系统表中。

SET GLOBAL log_output = 'table';

执行如下命令，关闭审计日志。默认为关闭。

SET GLOBAL audit_log = 0;

3. 设置审计策略

操作场景

审计策略用来控制审计日志记录的策略，可以设置审计日志只记录某些指定的操作

或者某些固定用户的操作。本节将指导管理员创建、修改、删除审计策略。

操作方法

创建审计策略

创建审计策略的语法规则如下所示：

CREATE AUDIT POLICY <audit_policy_name> [(<audit_policy_item> = <value>[,<audit_policy_item> = <value>])];

表 3-1 参数说明

参数名称 

描 述

audit_policy_name

为审计策略的名称，不区分大小写，存储时小写保存，可包

括大小写字符、数字和下划线，不包含特殊符号，审计名称

的首字符必须是英文字符。

audit_policy_item为审计策略项目，审计策略项目名称不区分大小写。

表 3-2 审计策略项目说明

项目名称

取值&含义

EnableY：启用，默认值
N：禁用
Hosts“”：不限制，默认值

<host>：严格匹配host， 支持空格“ ”分隔的host 列表， host 可使用“%”

和“_”做通配符

User“”：不限制，默认值
<user>：严格匹配 user，区分大小写
Db“”：不限制，默认值
<db>：严格匹配 db
Obj_type“”：不限制，默认值
TABLE(VIEW)：Object 为表（视图）
PROCEDURE：Object 为存储过程
FUNCTION：Object 为函数
Object“”：不限制，默认值
<object>：匹配 Obj_type 指定的 object

Sql_comman

ds

“”：不限制，默认值

INSERT, DELETE, UPDATE, LOAD, CREATE_USER, CREATE_D

B, CREATE_TABLE, CREATE_VIEW, CREATE_INDEX, CREATE_

PROCEDURE, CREATE_FUNCTION, RENAME_USER, ALTER_D

B, ALTER_TABLE, ALTER_PROCEDURE, ALTER_FUNCTION, A

LTER_EVENT, DROP_USER, DROP_DB, DROP_TABLE, DROP_V

IEW, DROP_INDEX, DROP_PROCEDURE, DROP_FUNCTION, DR

OP_EVENT, TRUNCATE, GRANT, REVOKE, SELECT，OTHERS：

其中的一种或多种类型，多个类型间以逗号‘,’连接，且不添加空格

Long_query_

time

<secs>：最小查询秒数，可带 6 位小数，精确到微秒，默认值 0，取

值范围为 0~31536000s

Status“”：不限制，默认不限制。
SUCCESS：执行成功
FAILED：执行失败

修改审计策略

修改审计策略的语法规则如下所示：

ALTER AUDIT POLICY <audit_policy_name> SET [(]<audit_policy_item> = <value>[,<audit_policy_item> = <value>][)];

表 3-3 参数说明

参数名称

描 述

audit_policy_name为审计策略的名称，不区分大小写
audit_policy_item为审计策略项目，取值内容与创建审计策略中描述相同。

删除审计策略

删除审计策略语法规则如下：

DROP AUDIT POLICY <audit_policy_name>;

4. 存储审计日志

审计日志的信息存储在系统表 gbase.audit_log 或日志gclusterd-audit.log 中，依赖于

全局级变量 log_output 的配置；gbased-audit.log 日志若不配置默认存储在

$GCLUSTER_BASE/log/gcluster/目录下。

使用约束

GBase 8a MPP Cluster 审计日志使用的约束条件：

 审计日志用于记录所有的SQL 操作。对于包含结果集行数的统计操作，例如 SELECT、DELETE、INSERT、UPDATE、MERGE 和ALTER；

 清空audit_log 时，需要使用TRUNCATE SELF audit_log 语句。

 查看gclusterdb 下的audit_log_express 可看到所有节点上发生的日志。

5. 审计日志高可用

GBase 8a MPP Cluster 具有审计日志高可用机制，为实现审计日志的高可用机制，集群安装或升级时自动在gclusterdb 库下创建 EXPRESS 引擎随机分布表audit_log_express，并自动创建定时导出事件，将gbase 库中的audit_log 表内容定时导出到gclusterdb 库中的audit_log_express 表。

在多 VC 版本下，内置自动导出 event 功能失效，需要用户手动删除后重新创建 event。

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

6. 使用示例

示例

使用系统表查看审计日志。

$ gccli -uroot -p

Enter password:

GBase client 9.5.3.17.117651. Copyright (c) 2004-2020, GBase. All Rights

Reserved.

gbase> SET long_query_time = 0;
Query OK, 0 rows affected

gbase> SET GLOBAL audit_log = 1;
Query OK, 0 rows affected

gbase>CREATE AUDIT POLICY audit_policy_1 ( Enable = 'Y' );
Query OK, 0 rows affected

gbase> SET GLOBAL log_output = 'table';
Query OK, 0 rows affected

gbase> CREATE USER u_sj identified by 'u_sj';
Query OK, 0 rows affected

gbase> GRANT ALL ON *.* TO u_sj;
Query OK, 0 rows affected

gbase> CREATE DATABASE testSJ;
Query OK, 1 row affected

gbase> USE testSJ;
Query OK, 0 rows affected

gbase> CREATE TABLE t1(i int);
Query OK, 0 rows affected

gbase> INSERT INTO t1 VALUES (1),(2);
Query OK, 2 rows affected

gbase> SELECT start_time,user_host,query_time,rows,LEFT(sql_text, 30),
conn_type FROM gbase.audit_log;

+---------------------+----------------------------------+
|       start_time    |   user_host                     |
+---------------------+----------------------------------+
| 2021-06-16 10:40:24 | root[root] @ [192.168.146.24]   |
| 2021-06-16 10:40:24 | root[root] @ [192.168.146.24]   |
| 2021-06-16 10:40:24 | root[root] @ [192.168.146.24]   |
| 2021-06-16 10:40:24 | root[root] @ [192.168.146.24]   |
| 2021-06-16 10:40:24 | root[root] @ localhost []       |
| 2021-06-16 10:40:33 | root[root] @ [192.168.146.24]   |
| 2021-06-16 10:40:33 | root[root] @ [192.168.146.24]   |
| 2021-06-16 10:40:33 | root[root] @ [192.168.146.24]   |
| 2021-06-16 10:40:32 | root[root] @ localhost []       |
| 2021-06-16 10:40:44 | root[root] @ [192.168.146.24]   |
| 2021-06-16 10:40:44 | root[root] @ [192.168.146.24]   |
| 2021-06-16 10:40:44 | root[root] @ [192.168.146.24]   |
| 2021-06-16 10:40:44 | root[root] @ localhost []       |
| 2021-06-16 10:40:48 | gbase[gbase] @ [192.168.146.24] |
| 2021-06-16 10:40:49 | gbase[gbase] @ [192.168.146.24] |
| 2021-06-16 10:40:49 | gbase[gbase] @ [192.168.146.24] |
| 2021-06-16 10:40:57 | root[root] @ [192.168.146.24]   |
| 2021-06-16 10:40:57 | root[root] @ [192.168.146.24]   |
| 2021-06-16 10:40:57 | root[root] @ [192.168.146.24]   |
| 2021-06-16 10:40:57 | root[root] @ [192.168.146.24]   |
| 2021-06-16 10:40:57 | root[root] @ localhost []       |
| 2021-06-16 10:41:05 | root[root] @ [192.168.146.24]   |
| 2021-06-16 10:41:05 | root[root] @ [192.168.146.24]   |
| 2021-06-16 10:41:05 | root[root] @ [192.168.146.24]   |
| 2021-06-16 10:41:05 | root[root] @ localhost []       |
| 2021-06-16 10:41:17 | gbase[gbase] @ [192.168.146.24] |
| 2021-06-16 10:41:17 | gbase[gbase] @ [192.168.146.24] |
| 2021-06-16 10:41:17 | gbase[gbase] @ [192.168.146.24] |
| 2021-06-16 10:41:17 | root[root] @ [192.168.146.24]   |
| 2021-06-16 10:41:17 | root[root] @ [192.168.146.24]   |
| 2021-06-16 10:41:17 | root[root] @ [192.168.146.24]   |
| 2021-06-16 10:41:17 | root[root] @ [192.168.146.24]   |
| 2021-06-16 10:41:17 | root[root] @ [192.168.146.24]   |
| 2021-06-16 10:41:17 | root[root] @ localhost []       |
| 2021-06-16 10:41:29 | root[root] @ [192.168.146.24]   |
| 2021-06-16 10:41:29 | root[root] @ [192.168.146.24]   |
| 2021-06-16 10:41:29 | root[root] @ [192.168.146.24]   |
| 2021-06-16 10:41:29 | root[root] @ localhost []       |
| 2021-06-16 10:41:43 | root[root] @ localhost []       |
+---------------------+----------------------------------+

+-----------------+------+--------------------------------+-----------+
|    query_time   | rows |        LEFT(sql_text, 30)      | conn_type |
+-----------------+------+--------------------------------+-----------+
| 00:00:00.000012 |   0  | Ping                           |   CAPI    |
| 00:00:00.000115 |   0  | SET NAMES utf8mb4              |   CAPI    |
| 00:00:00.000164 |   0  | SET SELF SCN = 0               |   CAPI    |
| 00:00:00.000094 |   0  | SET SELF GLOBAL log_output =   |   CAPI    |
| 00:00:00.239863 |   0  | SET GLOBAL log_output = 'table |   ODBC    |
| 00:00:00.000009 |   0  | Ping                           |   CAPI    |
| 00:00:00.000143 |   0  | SET SELF SCN = 0               |   CAPI    |
| 00:00:01.149579 |   0  | CREATE GCLUSTER_LOCAL USER "u_ |   CAPI    |
| 00:00:04.376179 |   0  | CREATE USER u_sj identified by |   ODBC    |
| 00:00:00.000019 |   0  | Ping                           |   CAPI    |
| 00:00:00.000207 |   0  | SET SELF SCN = 0               |   CAPI    |
| 00:00:00.057846 |   0  | GRANT /*+vcid*/ GCLUSTER_LOCAL |   CAPI    |
| 00:00:00.266514 |   0  | GRANT ALL ON *.* TO u_sj       |   ODBC    |
| 00:00:00.001372 |   0  | Connect                        |   CAPI    |
| 00:00:00.000005 |   0  | Ping                           |   CAPI    |
| 00:00:00.000002 |   0  | Quit                           |   CAPI    |
| 00:00:00.000008 |   0  | Ping                           |   CAPI    |
| 00:00:00.000090 |   0  | SET NAMES utf8                 |   CAPI    |
| 00:00:00.000147 |   0  | SET SELF SCN = 9259            |   CAPI    |
| 00:00:00.261615 |   0  | CREATE GCLUSTER_LOCAL DATABASE |   CAPI    |
| 00:00:00.965256 |   0  | CREATE DATABASE testSJ         |   ODBC    |
| 00:00:00.000018 |   0  | Ping                           |   CAPI    |
| 00:00:00.000042 |   0  | Init VC Name                   |   CAPI    |
| 00:00:00.000058 |   0  | Init DB                        |   CAPI    |
| 00:00:00.014822 |   0  | USE testSJ                     |   ODBC    |
| 00:00:00.004341 |   0  | Connect                        |   CAPI    |
| 00:00:00.032791 |   0  | select table_id from informati |   CAPI    |
| 00:00:00.000003 |   0  | Quit                           |   CAPI    |
| 00:00:00.000038 |   0  | Ping                           |   CAPI    |
| 00:00:00.000046 |   0  | Init VC Name                   |   CAPI    |
| 00:00:00.000070 |   0  | Init DB                        |   CAPI    |
| 00:00:00.000547 |   0  | SET SELF SCN = 9260            |   CAPI    |
| 00:00:00.781898 |   0  | CREATE GCLUSTER_LOCAL TABLE    |   CAPI    |
| 00:00:02.731924 |   0  | CREATE TABLE t1(i int)         |   ODBC    |
| 00:00:00.000132 |   0  | commit                         |   CAPI    |
| 00:00:00.000614 |   0  | set autocommit=1               |   CAPI    |
| 00:00:00.000175 |   0  | set autocommit=1               |   CAPI    |
| 00:00:00.200714 |   2  | INSERT INTO t1 VALUES (1),(2)  |   ODBC    |
| 00:00:00.000530 |   38 | SELECT start_time,user_host,qu |   ODBC    |
+-----------------+------+--------------------------------+-----------+

39 rows in set (Elapsed: 00:00:00.00)

gbase> INSERT INTO t1 SELECT * FROM t1;
Query OK, 2 rows affected

gbase> UPDATE t1 SET i = 3;
Query OK, 4 rows affected

gbase> DELETE FROM t1;
Query OK, 4 rows affected

gbase> SELECT start_time,user_host,query_time,rows, LEFT(sql_text, 30),
conn_type FROM gbase.audit_log;

+---------------------+--------------------------------+
| start_time          | user_host                      |
+---------------------+--------------------------------+
| 2021-06-16 11:17:18 | root[root] @ localhost []      |
| 2021-06-16 11:18:09 | root[root] @ localhost []      |
| 2021-06-16 11:18:21 | root[root] @ [192.168.146.24]  |
| 2021-06-16 11:18:21 | root[root] @ [192.168.146.24]  |
| 2021-06-16 11:18:21 | root[root] @ [192.168.146.24]  |
| 2021-06-16 11:18:20 | root[root] @ localhost []      |
| 2021-06-16 11:18:29 | root[root] @ [192.168.146.24]  |
| 2021-06-16 11:18:29 | root[root] @ [192.168.146.24]  |
| 2021-06-16 11:18:29 | root[root] @ [192.168.146.24]  |
| 2021-06-16 11:18:28 | root[root] @ localhost []      |
+---------------------+--------------------------------+

+-----------------+------+---------------------------------+-----------+
|    query_time   | rows |        LEFT(sql_text, 30)      | conn_type |
+-----------------+------+---------------------------------+-----------+
| 00:00:00.003119 |   0  |  truncate self table gbase.audi |    ODBC   |
| 00:00:02.090188 |   2  | INSERT INTO t1 SELECT * FROM t  |    ODBC   | 
| 00:00:00.000095 |   0  | commit                          |    CAPI   |     
| 00:00:00.000272 |   0  | set autocommit=1                |    CAPI   |
| 00:00:00.000416 |   0  | set autocommit=1                |    CAPI   |
| 00:00:00.000057 |   0  | commit                          |    CAPI   |
| 00:00:00.000198 |   0  | set autocommit=1                |    CAPI   |
| 00:00:00.000899 |   0  | set autocommit=1                |    CAPI   |
| 00:00:00.396723 |   4  | DELETE FROM t1                  |    ODBC   |
+-----------------+------+---------------------------------+-----------+

10 rows in set (Elapsed: 00:00:00.00)

 

评论

登录后才可以发表评论

热门帖子