操作步骤

传统审计使用指导

root用户通过客户端ssh登录DN节点。
修改审计对象GUC参数audit_system_object的值为12，表示审计TABLE和USER对象的CREATE、DROP、ALTER操作。
```
gs_guc reload -Z datanode  -N all -I all -c "audit_system_object=12"
```

root用户登录客户端查看audit_system_object值返回12即修改成功。

gaussdb=# SHOW audit_system_object;
  audit_system_object
---------------------------
  12
(1 row)

root用户创建审计管理员auditadmin和普通用户audit_t1，auditadmin用于查询审计日志，创建普通用户audit_t1用于执行SQL操作。
```
gaussdb=# CREATE USER auditadmin WITH AUDITADMIN password *******';
CREATE ROLE
gaussdb=# CREATE USER audit_t1 password '*******';
CREATE ROLE
```

使用audit_t1登录数据库，创建数据表t1并插入数据然后添加一列name，再删除t1。

gaussdb=# CREATE TABLE t1 (id int);
CREATE TABLE
gaussdb=# INSERT INTO t1 values(123);
INSERT 0 1
gaussdb=# ALTER TABLE t1 ADD COLUMN name varchar(20);
ALTER TABLE
gaussdb=# DROP TABLE t1;
DROP TABLE

登录root用户删除普通用户audit_t1。
```
gaussdb=# DROP USER audit_t1;
DROP ROLE
```

使用auditadmin登录数据库查询审计日志。

按起止时间查询审计日志，并查询用户audit_t1执行的ddl_table操作，root用户的user操作，例如：

gaussdb=# SELECT * FROM gs_query_audit('2024-03-23 15:50:00','2024-03-23 16:30:00') WHERE username='audit_t1' AND type='ddl_table';
          time          |   type    | result | userid | username | database |  client_conninfo  | object_name |                 detail_info             
    | node_name |            thread_id            | local_port | remote_port 
------------------------+-----------+--------+--------+----------+----------+-------------------+-------------+-----------------------------------------
----+-----------+---------------------------------+------------+-------------
 2024-03-23 15:51:15+08 | ddl_table | ok     | 20839  | audit_t1 | security_db | gsql@192.168.0.99 | t1          | CREATE TABLE t1(id int);                
    | dn_6001   | 281431669195952@764495475555777 | 8000       | 36548
 2024-03-23 16:02:48+08 | ddl_table | ok     | 20839  | audit_t1 | gaussdb | gsql@192.168.0.99 | t1          | ALTER TABLE t1 ADD COLUMN NAME varchar(2
0); | dn_6001   | 281430353822896@764496168035630 | 8000       | 36628
 2024-03-23 16:03:23+08 | ddl_table | ok     | 20839  | audit_t1 | gaussdb | gsql@192.168.0.99 | t1          | DROP TABLE t1;                          
    | dn_6001   | 281430353822896@764496203155439 | 8000       | 36636
(3 rows)
gaussdb=#  SELECT * FROM gs_query_audit('2024-03-23 15:50:00','2024-03-23 16:30:00') WHERE username='root' and type='user' AND  object_name='audit_t1';
          time          |   type   | result | userid | username | database |  client_conninfo  | object_name |                detail_info               
 | node_name |            thread_id            | local_port | remote_port 
------------------------+----------+--------+--------+----------+----------+-------------------+-------------+------------------------------------------
-+-----------+---------------------------------+------------+-------------
 2024-03-23 15:50:47+08 | user | ok     | 16725  | root     | gaussdb | gsql@192.168.0.99 | audit_t1    | CREATE USER audit_t1 password '********';
 | dn_6001   | 281431669195952@764495447019065 | 8000       | 36482
 2024-03-23 16:04:57+08 | user | ok     | 16725  | root     | gaussdb | gsql@192.168.0.99 | audit_t1    | DROP USER audit_t1;                      
 | dn_6001   | 281431669195952@764496297236538 | 8000       | 36648
(2 rows)

恢复GUC参数audit_system_object。

gs_guc reload -Z datanode  -N all -I all -c "audit_system_object=67121159"

使用root用户登录数据库，删除用户auditadmin。
```
gaussdb=# DROP USER auditadmin;
DROP ROLE
```

统一审计使用指导

root用户执行以下命令开启统一审计开关。

gs_guc reload -Z datanode -N all -I all -c "enable_security_policy=on"
gs_guc reload -Z datanode -N all -I all -c "unified_audit_location=0"

操作系统root用户进行rsyslog配置。

在操作系统后台服务配置文件/etc/rsyslog.conf中添加：
```
local0.* /var/log/localmessages  
```
重启rsyslog服务使配置生效。
```
sudo systemctl restart rsyslog
```

初始用户登录数据库，配置资源标签，创建审计策略。配置资源标签及创建审计策略需要有安全策略管理员权限，对于安全策略管理员的相关操作请参见《开发指南》中“数据库安全 > 用户及权限 > 管理员”章节，审计策略参数请参见《参考》中“SQL参考 > SQL语法 > C > CREATE AUDIT POLICY”章节的相关描述。

-- 初始化资源
DROP TABLE IF EXISTS table_security_auditing;
CREATE TABLE table_security_auditing(id int,name char(10));
CREATE USER user001 PASSWORD '********';
CREATE USER user002 PASSWORD '********';
GRANT ALL PRIVILEGES TO user001;

-- 新建资源标签
DROP RESOURCE LABEL IF EXISTS rl_security_auditing;
CREATE RESOURCE LABEL rl_security_auditing ADD TABLE(table_security_auditing);

-- 创建审计策略，审计用户user001在资源标签rl_security_auditing上的DDL、DML操作
CREATE AUDIT POLICY audit_security_priall PRIVILEGES all on LABEL(rl_security_auditing) FILTER ON ROLES(user001);
CREATE AUDIT POLICY audit_security_accall ACCESS all on LABEL(rl_security_auditing) FILTER ON ROLES(user001);

使用用户user001登录数据库，执行如下操作，触发审计策略。

-- ACCESS
INSERT INTO table_security_auditing VALUES(1,'22');
UPDATE table_security_auditing SET name=234123 WHERE id=1;
DELETE FROM table_security_auditing WHERE id=1;
 TRUNCATE TABLE table_security_auditing;
-- PRIVILEGES
GRANT INSERT ON TABLE table_security_auditing TO user002;
REVOKE INSERT ON TABLE table_security_auditing FROM user002;

使用操作系统root用户查看审计日志/var/log/localmessages。

Oct  9 15:38:11 localhost PGAUDIT: AUDIT EVENT: user name: [user001], app_name: [gsql], client_ip: [local], access type: [INSERT], policy id: [16423], table: [public.table_security_auditing], result: [OK]
Oct  9 15:38:11 localhost PGAUDIT: AUDIT EVENT: user name: [user001], app_name: [gsql], client_ip: [local], access type: [UPDATE], policy id: [16423], table: [public.table_security_auditing], result: [OK]
Oct  9 15:38:11 localhost PGAUDIT: AUDIT EVENT: user name: [user001], app_name: [gsql], client_ip: [local], access type: [DELETE], policy id: [16423], table: [public.table_security_auditing], result: [OK]
Oct  9 15:38:12 localhost PGAUDIT: AUDIT EVENT: user name: [user001], app_name: [gsql], client_ip: [local], access type: [TRUNCATE], policy id: [16423], table: [public.table_security_auditing], result: [OK]
Oct  9 15:49:41 localhost PGAUDIT: AUDIT EVENT: user name: [user001], app_name: [gsql], client_ip: [local], privilege type: [GRANT ON TABLE postgres.public.table_security_auditing TO user002], poy id: [16408], result: [OK]
Oct  9 15:49:53 localhost PGAUDIT: AUDIT EVENT: user name: [user001], app_name: [gsql], client_ip: [local], privilege type: [REVOKE ON TABLE postgres.public.table_security_auditing FROM user002],licy id: [16408], result: [OK]

修改GUC参数unified_audit_location的值，将统一审计日志记录到审计日志文件。

gs_guc reload -Z datanode -N all -I all -c "unified_audit_location=1"
gs_guc reload -Z datanode -N all -I all -c "audit_enabled=on"

再次使用用户user001登录数据库，执行如下操作，触发审计策略。

-- ACCESS
INSERT INTO table_security_auditing VALUES(1,'22');
UPDATE table_security_auditing SET name=234123 WHERE id=1;
DELETE FROM table_security_auditing WHERE id=1;
TRUNCATE TABLE table_security_auditing;
-- PRIVILEGES
GRANT INSERT ON TABLE table_security_auditing TO user002;
REVOKE INSERT ON TABLE table_security_auditing FROM user002;

初始用户登录数据库，使用系统函数gs_query_unified_audit查询统一审计策略。查询审计策略需要有审计管理员权限，对于审计管理员的相关操作请参见《开发指南》中“数据库安全 > 用户及权限 > 管理员”章节，gs_query_unified_audit函数的相关信息请参见《参考》中“SQL参考 > 函数和操作符 > 安全函数”章节。

     gaussdb=# SELECT * FROM gs_query_unified_audit(sysdate-1,sysdate+1) WHERE type='audit_policy';

查询结果如下：

          time          |     type     | result | userid | username | database | client_conninfo |               object_name          
     |                         detail_info                          | node_name |            thread_id            | local_port | remot
e_port | policy_id | unified_audit_type |                         unified_audit_policy                         
------------------------+--------------+--------+--------+----------+----------+-----------------+------------------------------------
-----+--------------------------------------------------------------+-----------+---------------------------------+------------+------
-------+-----------+--------------------+----------------------------------------------------------------------
 2025-04-26 14:49:54+08 | audit_policy | ok     | 16761  | user001  | postgres | gsql@[local]    | table: [public.table_security_audit
ing] | insert into table_security_auditing values(1,'22');          | datanode  | 139686500628224@798965394386913 | 17778      | 46946 
       | 16785     | access             | INSERT
 2025-04-26 14:49:54+08 | audit_policy | ok     | 16761  | user001  | postgres | gsql@[local]    | table: [public.table_security_audit
ing] | update table_security_auditing set name=234123 where id=1;   | datanode  | 139686500628224@798965394390539 | 17778      | 46946 
       | 16785     | access             | UPDATE
 2025-04-26 14:49:54+08 | audit_policy | ok     | 16761  | user001  | postgres | gsql@[local]    | table: [public.table_security_audit
ing] | delete from table_security_auditing where id=1;              | datanode  | 139686500628224@798965394391655 | 17778      | 46946
       | 16785     | access             | DELETE
 2025-04-26 14:49:55+08 | audit_policy | ok     | 16761  | user001  | postgres | gsql@[local]    | table: [public.table_security_audit
ing] | truncate table table_security_auditing;                      | datanode  | 139686500628224@798965395709651 | 17778      | 46946
       | 16785     | access             | TRUNCATE
 2025-04-26 14:50:00+08 | audit_policy | ok     | 16761  | user001  | postgres | gsql@[local]    | public.table_security_auditing     
     | GRANT INSERT ON TABLE table_security_auditing TO user002;    | datanode  | 139686500628224@798965400073107 | 17778      | 46946 
       | 16770     | privilege          | GRANT ON TABLE postgres.public.table_security_auditing TO user002
 2025-04-26 14:50:01+08 | audit_policy | ok     | 16761  | user001  | postgres | gsql@[local]    | public.table_security_auditing     
     | revoke insert on table table_security_auditing from user002; | datanode  | 139686500628224@798965401697885 | 17778      | 46946
       | 16770     | privilege          | REVOKE ON TABLE postgres.public.table_security_auditing FROM user002
(6 rows)

上述查询记录展示了用户user001的详细执行SQL语句及执行的相关信息，其中policy_id表示审计策略ID，unified_audit_type表示审计策略是privilege或access类型，unified_audit_policy为审计策略的详细信息。

如不需要继续对特定资源进行审计，可移除审计策略。

drop audit policy audit_security_priall;  
drop audit policy audit_security_accall;

父主题：操作步骤

上一篇：流程总览

下一篇：结果验证

操作步骤

传统审计使用指导

统一审计使用指导

相关文档

您对此文档页面的整体评价？