​

案例说明：

对于KingbaseES V8R3数据库，默认用户无权限删除审计记录，只有对审计记录做了转储以后会自动清理审计记录。

适用版本：

KingbaseES V8R3

本案例数据库版本：

SECURITY=> select version();
                                                         VERSION
-------------------------------------------------------------------------------------------------------------------------
 Kingbase V008R003C002B0290 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit
(1 row)

1、数据库配置审计参数

[kingbase@node101 data]$ cat kingbase.conf |grep audit
shared_preload_libraries = 'passwordcheck,sysaudit'             # (change requires restart)
sysaudit.enable = on

2、配置和启用审计

# 查看审计参数
[kingbase@node101 bin]$ ./ksql -U SYSSAO -W 12345678ab TEST
ksql (V008R003C002B0290)
Type "help" for help.

TEST=> \c SECURITY
You are now connected to database "SECURITY" as user "SYSSAO".

SECURITY=> show sysaudit.enable;
 sysaudit.enable
-----------------
 on
(1 row)

# 配置审计规则：

SECURITY=> SELECT sysaudit.set_audit_stmt('select table', 'system', null, null);
 SET_AUDIT_STMT
----------------

(1 row)

# 查看审计规则
SECURITY=> select * from sysaudit.all_audit_rules;
 AUDIT_ID | AUDIT_TARGET |  AUDIT_TYPE  | AUDIT_USERS | AUDIT_SCHEMA | AUDIT_OBJNAME | AUDIT_OBJOID | CREATOR_NAME
----------+--------------+--------------+-------------+--------------+---------------+--------------+--------------
    16530 | SQL          | select table | SYSTEM      |              |               |              | SYSSAO
(1 row)

3、查看审计记录（SYSSSO用户）

[kingbase@node101 bin]$ ./ksql -U SYSSSO -W 12345678ab TEST
ksql (V008R003C002B0290)
Type "help" for help.

TEST=> \c SECURITY
You are now connected to database "SECURITY" as user "SYSSSO".
SECURITY=> select * from sysaudit_record_sso;
  SESSION_ID   | PROC_ID | VXID  | XID | USER_ID | USERNAME | REMOTE_ADDR | DB_ID | DB_NAME  | RULE_ID | RULE_TYPE | OPR_TYPE | OBJ_TYPE | SCHM_ID | SCHM_NAME | OBJ_ID | OBJ_NAME |                SQLTEXT                 | PARAMS | ERRCODE |                  ERRMSG                   |           AUDIT_TS            | RESULT  | RECORD_TYPE | AUD_CLIENT | SERVER_TYPE
---------------+---------+-------+-----+---------+----------+-------------+-------+----------+---------+-----------+----------+-----
 62b585a3.29c9 |   10697 | 5/457 |   0 |    9202 | SYSSAO   | [local]     |     0 | TEST     |         | EVENT     | LOGIN    |     |         |           |        |          |                                        |        |         | connection authorized                   | 2022-06-24 17:36:35.256000+08 | success |         600 | ksql       | M
 62b585ab.29e4 |   10724 | 4/146 |   0 |    9202 | SYSSAO   | [local]     |     0 | SECURITY |         | EVENT     | LOGIN    |     |         |           |        |          |                                        |        |         | connection authorized                   | 2022-06-24 17:36:43.439000+08 | success |         600 | ksql       | M
 62b585a3.29c9 |   10697 | 5/0   |   0 |    9202 | SYSSAO   | [local]     | 14928 | TEST     |         | EVENT     | LOGOFF   |     |         |           |        |          |                                        |        |         |                   | 2022-06-24 17:36:43.440000+08 | success |         600 | ksql       | M
 62b585ab.29e4 |   10724 | 4/151 |   0 |    9202 | SYSSAO   | [local]     | 15371 | SECURITY |         | EVENT     | SEMANTIC |     |         |           |        |          | delete from  sysaudit.all_audit_rules; |        | 55000   | cannot delete from view "ALL_AUDIT_RULES" | 2022-06-24 17:40:01.111000+08 | failure |         600 | ksql       | M
 62b585ab.29e4 |   10724 | 4/0   |   0 |    9202 | SYSSAO   | [local]     | 15371 | SECURITY |         | EVENT     | LOGOFF   |     |         |           |        |          |                                        |        |         |                   | 2022-06-24 17:43:53.438000+08 | success |         600 | ksql       | M
(5 rows)

4、删除审计记录（无权限包括syssao用户）

SECURITY=> delete from sysaudit_record_sso;
ERROR:  permission denied for relation SYSAUDIT_RECORD

5、配置审计转储auditlog_dump_dir（目录需手工创建）

参考:https://help.kingbase.com.cn/stage-api/profile/document/kes/v8r3/html/safety/safety-guide/safety-audit.html#id12 《kingbaseES 官方文档》

[kingbase@node101 bin]$ cat ../data/kingbase.conf|grep audit
shared_preload_libraries = 'passwordcheck,sysaudit'
sysaudit.enable = on
sysaudit.auditlog_dump_dir='/home/kingbase/audit_dump'

6、手工执行审计转储

[kingbase@node101 bin]$ ./ksql -U SYSSAO -W 123456ab TEST
ksql (V008R003C002B0290)
Type "help" for help.

TEST=> show sysaudit.auditlog_dump_dir ;
 sysaudit.auditlog_dump_dir
----------------------------
 /home/kingbase/audit_dump
(1 row)

TEST=> \c SECURITY;
You are now connected to database "SECURITY" as user "SYSSAO".

SECURITY=> SELECT sysaudit.dump_auditlog(0);
 DUMP_AUDITLOG
---------------

(1 row)

SECURITY=> SELECT sysaudit.show_audlog_dump_file();
               SHOW_AUDLOG_DUMP_FILE
----------------------------------------------------
 (AUDIT_DUMP_FILE-2022-06-24_175144,"665564 bytes")
(1 row)

# 查看转储文件：
[kingbase@node101 bin]$ cd ~/audit_dump/

[kingbase@node101 audit_dump]$ ls -lh
total 652K
-rw-r--r-- 1 kingbase kingbase 650K Jun 24 17:51 AUDIT_DUMP_FILE-2022-06-24_175144

7、查看审计记录（原审计记录已被清理）

SECURITY=> select * from sysaudit_record_sso;
  SESSION_ID   | PROC_ID | VXID | XID | USER_ID | USERNAME | REMOTE_ADDR | DB_ID | DB_NAME  | RULE_ID | RULE_TYPE | OPR_TYPE | OBJ_TYPE | SCHM_ID | SCHM_NAME | OBJ_ID | OBJ_NAME | SQLTEXT | PARAMS | ERRCODE | ERRMSG |           AUDIT_TS            | RESULT  | RECORD_TYPE | AUD_CLIENT | SERVER_TYPE
---------------+---------+------+-----+---------+----------+-------------+-------+----------+---------+-----------+----------+------
 62b58921.5255 |   21077 | 6/0  |   0 |    9202 | SYSSAO   | [local]     | 15371 | SECURITY |         | EVENT     | LOGOFF   |    |         |           |        |          |         |        |         |        | 2022-06-24 17:52:44.144000+08 | success |    600 | ksql       | M
(1 row)

SECURITY=> select now();
              NOW
-------------------------------
 2022-06-24 17:53:33.509074+08
(1 row)