准备试用人大金仓KingBase数据库的审计功能，首先要进行安装，安装步骤可参考安装KingbaseES。
这里我采用的是命令行安装，操作系统为Ubuntu。安装的时候会让你选择模式，选的是oracle模式安装。安装好后，查看数据目录，打开配置文件kingbase.conf，发现和PostgreSQL的配置文件基本是一样的，其Oracle兼容的一些特性部分是通过插件方式实现的。可以看到KingBase是基于PostgreSQL开发的一款关系型数据库。
插件
查看其安装的插件
kingbase=# \dx
List of installed extensions
Name | Version | Schema | Description
---------------------+---------+--------------+----------------------
dbms_ddl | 1.0 | sys | DBMS_DDL system package
dbms_output | 1.0 | sys | DBMS_OUTPUT system package
dbms_utility | 1.0 | sys | dbms_utility extension package
kdb_cast | 1.0 | sys | kdb_cast extension
kdb_license | 1.0 | pg_catalog | kdb_license extension
kdb_oracle_datatype | 1.7 | sys | kdb_oracle_datatype extension
kdb_tinyint | 1.0 | pg_catalog | Create a new data type tinyint and its functions operators and indexes
kingbase_version | 1.0 | pg_catalog | This is a utility that provides function related to version number, it is used to get the Kingbase version number.
owa_util | 1.0 | sys | owa_util system package
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
plsql | 1.0 | pg_catalog | PL/SQL procedural language
src_restrict | 1.0 | src_restrict | src restrict plugin
sys_anon | 1.0 | anon | provides data masking functionality
sys_freespacemap | 1.2 | sys | examine the free space map (FSM) sys_hm | 1.0 | pg_catalog | Kingbase Healthy Check
sys_stat_statements | 1.10 | public | track parsing, planning and execution statistics of all SQL statements executed
sysaudit | 1.0 | sysaudit | provides auditing functionality
sysmac | 1.0 | sysmac | Mac for Kingbase
xlog_record_read | 1.0 | pg_catalog | xlog_record_read functions
(19 rows)
复用了很多PostgreSQL的开源插件，以及开发了一些Oracle数据类型等兼容插件。审计功能插件sysaudit。
审计功能
我们关注其审计功能，
- 使用审计功能需要先配置插件
shared_preload_libraries = 'sysaudit' - kingbase.conf配置文件设置
sysaudit.enable = on。
人大金仓三权分立，审计功能的设置与使用需要审计管理员sao用户。
kingbase=# \du
List of roles
Role name | Attributes | Member of
-----------+--------------------------------+-----------
kcluster | Cannot login | {}
kingbase | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
sao | No inheritance | {}
sso | No inheritance | {}
后面就可以进行审计了，具体审计那些内容，需要用户根据实际的需要进行配置，审计并不是越细越好，如果对所有的内容都进行审计，是有性能代价的，并且在分析审计日志的时候，面对海量的审计日志，分析的时候也更为困难，也有存储等的代价，所以要结合实际情况进行审计。这也是审计功能中审计策略设置的一大原因，就是给用户自由度。
审计共分为事件级审计、语句级审计、对象级审计，下面就各自看一下。
审计规则
数据库在审计过程中，需要先读取审计规则，之后使用审计规则判断，才能生成审计记录，如果在读取审计规则前就出错，则可能无法生成审计记录。
审计规则的设置可分为语句审计与对象审计，其中系统级审计为默认的规则，不需要进行设置。而语句审计和对象审计，需要通过函数sysaudit.set_audit_stmt和sysaudit.set_audit_object进行设置审计规则。
事件级审计
设置相关的参数，配置审计是否审计某些事件：
kingbase@slpc:~/data$ cat data/kingbase.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
sysaudit.serverevent = 'on'
sysaudit.userevent = 'on'
- 以sao用户登录数据库，查看审计日志需要切换到security数据库
kingbase@slpc:~/data$ ksql -U sao -d kingbase
Type "help" for help.
kingbase=> show sysaudit.serverevent ;
sysaudit.serverevent
----------------------
on
(1 row)
kingbase=> \c security
You are now connected to database "security" as userName "sao".
security=> \d
List of relations
Schema | Name | Type | Owner
--------+-------------------------+------+----------
public | sys_stat_statements | view | kingbase
public | sys_stat_statements_all | view | kingbase
(2 rows)
-- 查看审计日志
security=> select * from sysaudit_record_sao;
more: /home/kingbase/data/KESRealPro/V008R006C008B0020/Server/lib/libtinfo.so.6: no version information available (required by more)
-[ RECORD 1 ]-+------------------------------
session_id | 66a8543d.29b8
proc_id | 10680
vxid | 8/0
xid | 0
user_id | 10
username | kingbase
remote_addr | [local]
db_id | 13543
db_name | kingbase
rule_id |
rule_type | EVENT
opr_type | USR_LOGOFF
obj_type |
schm_id |
schm_name |
obj_id |
obj_name |
sqltext |
params |
errcode |
errmsg |
audit_ts | 2024-07-30 10:50:45.763058+08
result | success
record_type | 200
aud_client | ksql
authen_method | trust
server_type | M
-[ RECORD 2 ]-+------------------------------
session_id | 66a85524.29fb
proc_id | 10747
vxid |
xid | 0
user_id |
username |
remote_addr |
db_id | 0
db_name |
rule_id |
rule_type | EVENT
opr_type | SERVER_START
obj_type |
schm_id |
schm_name |
obj_id |
obj_name |
sqltext |
params |
errcode |
errmsg | Server Start
audit_ts | 2024-07-30 10:51:16.371809+08
result | success
record_type | 200
aud_client |
authen_method |
server_type | M
-[ RECORD 3 ]-+------------------------------
session_id | 66a85524.29fb
proc_id | 10747
vxid |
xid | 0
user_id |
username |
remote_addr |
db_id | 0
db_name |
rule_id |
rule_type | EVENT
opr_type | SERVER_START
obj_type |
schm_id |
schm_name |
obj_id |
obj_name |
sqltext |
params |
errcode |
errmsg | startup recovery exit
audit_ts | 2024-07-30 10:51:16.393716+08
result | success
record_type | 200
aud_client |
authen_method |
server_type | M
-[ RECORD 4 ]-+------------------------------
session_id | 66a8558d.2a39
proc_id | 10809
vxid | 8/1
xid | 0
user_id | 10
username | kingbase
remote_addr | [local]
db_id | 0
db_name | kingbase
rule_id |
rule_type | EVENT
opr_type | USR_LOGIN
obj_type |
schm_id |
schm_name |
obj_id |
obj_name |
sqltext |
params |
errcode |
errmsg | connection authorized
audit_ts | 2024-07-30 10:53:01.732833+08
result | success
record_type | 200
aud_client | ksql
authen_method | trust
server_type | M
语句级审计
我们可以设置对DDL语句或者DML语句进行审计。或者也可设置为ALL，对所有语句进行审计，不建议这么使用。
其中语句审计规则可通过函数sysaudit.set_audit_stmt(audit_type text, audit_users text, audit_schema text audit_objs text)实现：
其中：
- audit_type: 语句级审计策略，可以指定审计某一种SQL命令，比如
CREATE TABLE等。 - audit_users: 审计的用户名，null表示审计所有用户，指定审计具体哪个用户，当audit_type设置为ALL时，必须指定审计的用户。
- audit_schema: 审计对象的模式名。可以为空，表示审计所有模式下的此类对象。
- audit_objs: 审计对象的名称，只有
select table、insert table、update table、delete table、truncate table、drop table这六种语句支持设置表名。
我们具体的举个例子说明：
-- 设置审计规则，审计kingbase用户的建表语句
kingbase=> select sysaudit.set_audit_stmt('CREATE TABLE','kingbase',null,null);
set_audit_stmt
----------------
(1 row)
-- 查看审计规则，
kingbase=> select * from sysaudit.all_audit_rules;
audit_id | audit_target | audit_type | audit_users | audit_schema | audit_objname | audit_objoid | creator_name
----------+--------------+--------------+-------------+--------------+---------------+--------------+-----
16392 | SQL | create table | kingbase | | | | sao
(1 rows)
-- kingbase用户建表
kingbase=# create table t3(a int);
CREATE TABLE
-- sao用户查看审计日志
-[ RECORD 9 ]-+------------------------------
session_id | 66a8613c.2ca8
proc_id | 11432
vxid | 8/26
xid | 1103
user_id | 10
username | kingbase
remote_addr | [local]
db_id | 13543
db_name | kingbase
rule_id | 16391
rule_type | create table
opr_type | CREATE TABLE
obj_type |
schm_id | 2200
schm_name |
obj_id | 16393
obj_name |
sqltext | create table t3(a int);
params |
errcode |
errmsg |
audit_ts | 2024-07-30 11:43:04.471619+08
result | success
record_type | 200
aud_client | ksql
authen_method | trust
server_type | M
对插入语句设置审计规则
kingbase=> select sysaudit.set_audit_stmt('INSERT TABLE','kingbase',null,null);
set_audit_stmt
----------------
(1 row)
测试用例
kingbase=# create table t4(a int primary key);
CREATE TABLE
kingbase=# insert into t4 values(1);
INSERT 0 1
kingbase=# insert into t4 values(1);
ERROR: duplicate key value violates unique constraint "t4_pkey"
DETAIL: Key (a)=(1) already exists.
查看审计日志：
-[ RECORD 14 ]+---------------------------------------------------------
session_id | 66a86435.2d68
proc_id | 11624
vxid | 8/34
xid | 1114
user_id | 10
username | kingbase
remote_addr | [local]
db_id | 13543
db_name | kingbase
rule_id | 16396
rule_type | insert table
opr_type | INSERT
obj_type |
schm_id | 2200
schm_name | public
obj_id | 16397
obj_name | t4
sqltext | insert into t4 values(1);
params |
errcode |
errmsg |
audit_ts | 2024-07-30 11:57:42.072349+08
result | success
record_type | 200
aud_client | ksql
authen_method | trust
server_type | M
-[ RECORD 15 ]+---------------------------------------------------------
session_id | 66a86435.2d68
proc_id | 11624
vxid | 8/35
xid | 1116
user_id | 10
username | kingbase
remote_addr | [local]
db_id | 13543
db_name | kingbase
rule_id | 16396
rule_type | insert table
opr_type | INSERT
obj_type |
schm_id | 2200
schm_name | public
obj_id | 16397
obj_name | t4
sqltext | insert into t4 values(1);
params |
errcode | 23505
errmsg | duplicate key value violates unique constraint "t4_pkey"
audit_ts | 2024-07-30 11:57:50.685801+08
result | failure
record_type | 200
aud_client | ksql
authen_method | trust
server_type | M
对象级审计
模式对象级审计发生在具体的对象上的DML/SELECT操作，需要指定模式名及其对象名。只有对相应对象执行正确的设置才会触发审计，比如：为函数对象设置一个insert的审计策略将无法生效，因为数据库不支持insert到一个函数。
可通过下面的函数sysaudit.set_audit_object(audit_type text, audit_users text, audit_schema text, audit_objs text)进行设置，其中参数说明如下：
- audit_type: 审计对象的类型，支持：
TABLE、VIEW、MATERIALIZED VIEW、PROCEDURE、FUNCTION。 - audit_users: 审计的用户名，null表示审计所有用户。
- audit_schema:审计对象的模式名，可以为空，表示审计所有模式下的此类对象。
- audit_objs:审计对象的名称，比如为表名、视图名、存储过程名。可以为空，表示审计指定模式下的所有此类对象。
-- sao审计管理员用户连接
kingbase=# \c kingbase sao
You are now connected to database "kingbase" as userName "sao".
-- 设置对象审计，审计kingbase用户，表 public.t4
kingbase=> select sysaudit.set_audit_object('table','kingbase','public','t4');
set_audit_object
------------------
(1 row)
-- 查看审计规则
kingbase=> select * from sysaudit.all_audit_rules;
audit_id | audit_target | audit_type | audit_users | audit_schema | audit_objname | audit_objoid | creator_name
----------+--------------+--------------+-------------+--------------+---------------+--------------+--------------
16391 | SQL | create table | | | | | sao
16392 | SQL | create table | kingbase | | | | sao
16396 | SQL | insert table | kingbase | | | | sao
16402 | Object | table | kingbase | public | t4 | 16397 | sao
(4 rows)
-- 对对象t4进行select操作
kingbase=> \c kingbase kingbase
You are now connected to database "kingbase" as userName "kingbase".
kingbase=# select * from t4;
a
---
1
(1 row)
-- 查看审计记录：
security=> select * from sysaudit_record_sao;
-[ RECORD 23 ]+---------------------------------------------------------
session_id | 66a9a5d3.19ff
proc_id | 6655
vxid | 9/3
xid | 0
user_id | 10
username | kingbase
remote_addr | [local]
db_id | 13543
db_name | kingbase
rule_id | 16402
rule_type | table
opr_type | SELECT
obj_type | TABLE
schm_id | 2200
schm_name | public
obj_id | 16397
obj_name | t4
sqltext | select * from t4;
params |
errcode |
errmsg |
audit_ts | 2024-07-31 10:47:52.088104+08
result | success
record_type | 200
aud_client | ksql
authen_method | trust
server_type | M
需要注意的是，对于对象级审计，只审计对象的SELECT/DML操作，对于TRUNCATE,DROP TABLE则不进行审计。
-- 对对象进行truncate，drop table 操作，不会记录审计日志
kingbase=# truncate table t4;
TRUNCATE TABLE
kingbase=# drop table t4;
DROP TABLE
-- 创建视图
kingbase=# create view vt1 as select * from t1;
CREATE VIEW
-- 添加对象级审计
kingbase=> select sysaudit.set_audit_object('VIEW','kingbase','public','vt1');
set_audit_object
------------------
(1 row)
kingbase=# select * from vt1;
a
---
1
2
(2 rows)
kingbase=# alter view vt1 rename to vt2; -- 发现一个bug，重命名后查询审计规则，规则表中的审计对象名没有同步修改
ALTER VIEW
kingbase=> select * from sysaudit.all_audit_rules;
audit_id | audit_target | audit_type | audit_users | audit_schema | audit_objname | audit_objoid | creator_name
----------+--------------+--------------+-------------+--------------+---------------+--------------+--------------
16391 | SQL | create table | | | | | sao
16392 | SQL | create table | kingbase | | | | sao
16396 | SQL | insert table | kingbase | | | | sao
16402 | Object | table | kingbase | public | t4 | 16397 | sao
16404 | SQL | insert table | kingbase | hangzhou | | | sao
16408 | SQL | insert table | kingbase | public | t5 | 16405 | sao
16409 | SQL | alter user | kingbase | | | | sao
16416 | Object | view | kingbase | public | vt1 | 16412 | sao
(8 rows)
kingbase=# select * from vt2;
a
---
1
2
(2 rows)
-- 查询审计日志
security=> select username,db_name,rule_id,rule_type,opr_type,obj_type,obj_name,sqltext from sysaudit_record_sao;
kingbase | kingbase | 16396 | insert table | INSERT | | t4 | insert into t4 values(2);
kingbase | kingbase | | EVENT | USR_LOGOFF | | |
kingbase | kingbase | | EVENT | USR_LOGIN | | |
kingbase | kingbase | 16402 | table | DELETE | TABLE | t4 | delete from t4;
kingbase | kingbase | 16396 | insert table | INSERT | | t4 | insert into t4 values(1);
kingbase | kingbase | | EVENT | USR_LOGIN | | |
kingbase | kingbase | | EVENT | USR_LOGOFF | | |
kingbase | kingbase | 16416 | view | SELECT | VIEW | vt1 | select * from vt1;
kingbase | kingbase | 16416 | view | SELECT | VIEW | vt2 | select * from vt2;
补充
- 系统表不进行审计
最后修改时间：2024-12-23 17:39:41
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
