GBase 8a记录用户执行sql

在使用Gbase 8a时，遇到需要记录用户执行SQL的场景时，可以通过打开审计日志的方式

审计日志概述

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

操作方法

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

SET GLOBAL audit_log = 1;

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

SET GLOBAL log_output = 'table';

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

SET GLOBAL audit_log = 0;

查看执行SQL记录

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> 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 id entified 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.094043 | 4    | DELETE FROM t1                | CAPI    |
+-----------------+------+-------------------------------+----------+