審計日志概述??

? ? ? ?審計日志用于記錄用戶的數據庫操作，審計其行為，主要用于安全管理。 審計日志將執行時間超過 long_query_time 值的 SQL 記錄下來，方便用戶針對這些執行效率低下的 SQL 語句進行分析、 優化和改寫，從而提高 SQL 語句的執行效率。??

審計日志參數配置??

? ? ? ?操作場景
? ? ? ?指導管理員開啟或關閉審計日志。
? ? ? ?操作方法
? ? ? ?執行如下命令， 開啟審計日志?？梢酝ㄟ^配置文件或 session 級控制。

SET GLOBAL audit_log = 1;

? ? ? ?執行如下命令，設定審計日志存放在系統表中。

SET GLOBAL log_output = 'table';

? ? ? ?執行如下命令， 關閉審計日志。默認為關閉。

SET GLOBAL audit_log = 0;

設置審計策略

? ? ? ?操作場景
? ? ? ?審計策略用來控制審計日志記錄的策略，可以設置審計日志只記錄某些指定的操作或者某些固定用戶的操作。 本節將指導管理員創建、修改、刪除審計策略。

? ? ? ?操作方法

? ? ? ?創建審計策略
? ? ? ?創建審計策略的語法規則如下所示：??

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

? ? ? ?參數說明：

參數名稱	描 述
audit_policy_name	為審計策略的名稱，不區分大小寫，存儲時小寫保存，可包 括大小寫字符、數字和下劃線，不包含特殊符號，審計名稱 的首字符必須是英文字符。
audit_policy_item	為審計策略項目，審計策略項目名稱不區分大小寫。

? ? ? ?審計策略項目說明??

項目名稱	取值&含義
Enable	Y：啟用，默認值
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>][)];

參數名稱	描 述
audit_policy_name	為審計策略的名稱，不區分大小寫
audit_policy_item	為審計策略項目，取值內容與創建審計策略中描述相同。

? ? ? ?刪除審計策略??

? ? ? ?刪除審計策略語法規則如下：

DROP AUDIT POLICY <audit_policy_name>;

存儲審計日志??

? ? ? ?審計日志的信息存儲在系統表 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 可看到所有節點上發生的日志。??

審計日志高可用??

? ? ? ?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

使用示例??

? ? ? ?使用系統表查看審計日志。??

$ 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> DROP USER tzt; Query OK, 0 rows affected gbase> SET GLOBAL log_output = 'table'; Query OK, 0 rows affected gbase> DROP DATABASE test; Query OK, 1 row affected gbase> CREATE USER tzt identified by 'tzt'; Query OK, 0 rows affected gbase> GRANT ALL ON *.* TO tzt@'%'; Query OK, 0 rows affected gbase> CREATE DATABASE test; Query OK, 1 row affected gbase> USE test; 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 | query_time | rows | LEFT(sql_text, 30) | conn_type | +---------------------+----------------------------------+-----------------+---- --+ | 2019-09-02 08:47:27 | root[root] @ [192.168.105.61] | 00:00:00.001129 | 0 | Connect | CAPI | | 2019-09-02 08:47:27 | root[root] @ [192.168.105.61] | 00:00:00.013673 | 0 | set gcluster_lock_timeout=0,g | CAPI | | 2019-09-02 08:47:27 | root[root] @ [192.168.105.61] | 00:00:00.000559 | 0 | set self log_output='table' | CAPI | | 2019-09-02 08:47:27 | root[root] @ [192.168.105.61] | 00:00:00.000363 | 0 | SET NAMES utf8mb4 | CAPI | | 2019-09-02 08:47:27 | root[root] @ [192.168.105.61] | 00:00:00.000325 | 0 | SET SELF GLOBAL log_output = | CAPI | | 2019-09-02 08:47:27 | root[root] @ localhost [] | 00:00:00.032217 | 0 | SET GLOBAL log_output = 'table | CAPI | | 2019-09-02 08:48:05 | root[root] @ localhost [] | 00:00:00.000530 | 9 | SELECT start_time,user_host,qu | CAPI | | 2019-09-02 08:48:50 | root[root] @ localhost [] | 00:00:00.000977 | 10 | SELECT start_time,user_host,qu | CAPI | | 2019-09-02 08:49:06 | [gbase] @ localhost [] | 00:00:00.000509 | 0 | Connect | CAPI | | 2019-09-02 08:49:15 | [gbase] @ localhost [] | 00:00:00.000461 | 0 | Connect | CAPI | | 2019-09-02 08:49:22 | gbase[gbase] @ localhost [] | 00:00:00.000556 | 0 | Connect | CAPI | | 2019-09-02 08:49:27 | gbase[gbase] @ localhost [] | 00:00:00.010542 | 0 | use hb | CAPI | | 2019-09-02 08:49:52 | gbase[gbase] @ localhost [] | 00:00:00.007598 | 0 | drop function if exists func_g | CAPI | | 2019-09-02 08:50:05 | gbase[gbase] @ [192.168.105.61] | 00:00:00.000421 | 0 | Connect | CAPI | | 2019-09-02 08:50:05 | gbase[gbase] @ [192.168.105.61] | 00:00:00.013944 | 0 | set gcluster_lock_timeout=0,g | CAPI | | 2019-09-02 08:50:06 | gbase[gbase] @ [192.168.105.61] | 00:00:00.000858 | 0 | SET character_set_results = ut | CAPI | | 2019-09-02 08:50:06 | gbase[gbase] @ [192.168.105.61] | 00:00:00.000098 | 0 | Init DB | CAPI | | 2019-09-02 08:50:06 | gbase[gbase] @ [192.168.105.61] | 00:00:00.001789 | 0 | CREATE GCLUSTER_LOCAL DEFINER= | CAPI | | 2019-09-02 08:50:06 | gbase[gbase] @ [192.168.105.61] | 00:00:00.000369 | 0 | show /*+ sub_step */ warnings | CAPI | | 2019-09-02 08:50:05 | gbase[gbase] @ localhost [] | 00:00:00.102018 | 0 | CREATE FUNCTION "func_getstand | CAPI | | 2019-09-02 08:50:18 | gbase[gbase] @ localhost [] | 00:00:00.000012 | 0 | Quit | CAPI | | 2019-09-02 08:50:18 | gbase[gbase] @ [192.168.105.61] | 00:00:00.000005 | 0 | Quit | CAPI | | 2019-09-02 08:50:25 | gbase[gbase] @ localhost [] | 00:00:00.000505 | 0 | Connect | CAPI | | 2019-09-02 08:50:32 | gbase[gbase] @ [192.168.105.61] | 00:00:00.000275 | 0 | Connect | CAPI | | 2019-09-02 08:50:32 | gbase[gbase] @ [192.168.105.61] | 00:00:00.006585 | 0 | select table_id from informati | CAPI | | 2019-09-02 08:50:32 | gbase[gbase] @ [192.168.105.61] | 00:00:00.000002 | 0 | Quit | CAPI | | 2019-09-02 08:50:32 | gbase[gbase] @ [192.168.105.61] | 00:00:00.000209 | 0 | Connect | CAPI | | 2019-09-02 08:50:32 | gbase[gbase] @ [192.168.105.61] | 00:00:00.005802 | 0 | select table_id from informati | CAPI | | 2019-09-02 08:50:32 | gbase[gbase] @ [192.168.105.61] | 00:00:00.000003 | 0 | Quit | CAPI | | 2019-09-02 08:50:32 | gbase[gbase] @ [192.168.105.61] | 00:00:00.000130 | 0 | Connect | CAPI | | 2019-09-02 08:50:33 | gbase[gbase] @ localhost [] | 00:00:00.010871 | 33 | select * from gbase.audit_log | CAPI | | 2019-09-02 08:50:44 | root[root] @ [192.168.3.128] | 00:00:00.000658 | 0 | SELECT SCHEMA_NAME AS `Databas | STUDIO | +---------------------+----------------------------------+-----------------+---- --+ 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 | +---------------------+----------------------------------+ | 2013-10-09 17:21:08 | root[root] @ localhost [] | | 2013-10-09 17:21:22 | root[root] @ [192.168.10.116] | | 2013-10-09 17:21:22 | root[root] @ localhost [] | | 2013-10-09 17:21:32 | gbase[gbase] @ [192.168.10.116] | | 2013-10-09 17:21:32 | root[root] @ localhost [] | | 2013-10-09 17:21:32 | root[root] @ localhost [] | | 2013-10-09 17:21:45 | root[root] @ localhost [] | | 2013-10-09 17:21:52 | root[root] @ localhost [] | | 2013-10-09 17:21:58 | root[root] @ localhost [] | | 2013-10-09 17:22:05 | root[root] @ localhost [] | | 2013-10-09 17:22:10 | gbase[gbase] @ [192.168.10.116] | | 2013-10-09 17:22:10 | root[root] @ localhost [] | | 2013-10-09 17:22:17 | root[root] @ localhost [] | | 2013-10-09 17:22:25 | root[root] @ localhost [] | | 2013-10-09 17:23:13 | root[root] @ localhost [] | | 2013-10-09 17:23:20 | root[root] @ localhost [] | | 2013-10-09 17:23:27 | root[root] @ localhost [] | +---------------------+----------------------------------+ +-----------------+------+-------------------------------+----------+ | query_time | rows | LEFT(sql_text, 30) | conn_type| +-----------------+------+-------------------------------+----------+ | 00:00:00.006397 | 0 | SET GLOBAL log_output = 'table| CAPI | | 00:00:00.000282 | 0 | Connect | CAPI | | 00:00:00.025018 | 0 | DROP USER tzt | CAPI | | 00:00:00.000054 | 0 | Connect | CAPI | | 00:00:00.000175 | 0 | DROP DATABASE test | CAPI | | 00:00:00.111946 | 1 | SELECT DATABASE() | CAPI | | 00:00:00.000086 | 0 | CREATE USER tzt identified by | CAPI | | 00:00:00.439480 | 0 | GRANT ALL ON *.* TO tzt@'%' | CAPI | | 00:00:00.000387 | 0 | CREATE DATABASE test | CAPI | | 00:00:00.000025 | 0 | USE test | CAPI | | 00:00:00.000384 | 0 | Connect | CAPI | | 00:00:00.000144 | 0 | CREATE TABLE t1(i int) | CAPI | | 00:00:00.004527 | 2 | INSERT INTO t1 VALUES (1),(2) | CAPI | | 00:00:00.000035 | 13 | SELECT start_time,user_host,qu| CAPI | | 00:00:00.000191 | 2 | INSERT INTO t1 SELECT * FROM t| CAPI | | 00:00:00.000060 | 4 | UPDATE t1 SET i = 3 | CAPI | | 00:00:00.094043 | 4 | DELETE FROM t1 | CAPI | +-----------------+------+-------------------------------+----------+ 17 rows in set

總結

以上是生活随笔為你收集整理的GBase 8a 的审计管理的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。