kingbaseES V8R3数据安全案例之---审计记录清除案例
案例说明：
对于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)
KINGBASE研究院
