# 操作步骤 #### 传统审计使用指导 1. root用户通过客户端登录CN节点。 2. 修改审计对象GUC参数audit_system_object的值为12，表示审计TABLE和USER对象的CREATE、DROP、ALTER操作。 ``` gs_guc reload -Z coordinator -Z datanode -N all -I all -c "audit_system_object=12" ``` 3. root用户登录客户端查看audit_system_object值返回12即修改成功。 ``` gaussdb=# SHOW audit_system_object; audit_system_object --------------------------- 12 (1 row) ``` 4. 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 ``` 5. 使用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 ``` 6. 登录root用户删除普通用户audit_t1。 ``` gaussdb=# DROP USER audit_t1; DROP ROLE ``` 7. 使用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 | gaussdb | 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) ``` 8. 恢复GUC参数audit_system_object。 ``` gs_guc reload -Z coordinator -Z datanode -N all -I all -c "audit_system_object=67121159" ``` 9. 使用root用户登录数据库，删除用户auditadmin。 ``` gaussdb=# DROP USER auditadmin; DROP ROLE ``` #### 统一审计使用指导 1. root用户执行以下命令开启统一审计开关。 ``` gs_guc reload -Z coordinator -Z datanode -N all -I all -c "enable_security_policy=on" gs_guc reload -Z coordinator -Z datanode -N all -I all -c "unified_audit_location=0" ``` 2. 操作系统root用户进行rsyslog配置。 在操作系统后台服务配置文件/etc/rsyslog.conf中添加： ``` local0.* /var/log/localmessages ``` 重启rsyslog服务使配置生效。 ``` sudo systemctl restart rsyslog ``` 3. 初始用户登录数据库，配置资源标签，创建审计策略。配置资源标签及创建审计策略需要有安全策略管理员权限，对于安全策略管理员的相关操作请参见《开发指南》中"数据库安全 \> 用户及权限 \> 管理员"章节，审计策略参数请参见《参考》中"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); ``` 4. 使用用户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; ``` 5. 使用操作系统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], policy 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],policy id: [16408], result: [OK] ``` 6. 修改GUC参数unified_audit_location的值，将统一审计日志记录到审计日志文件。 ``` gs_guc reload -Z coordinator -Z datanode -N all -I all -c "unified_audit_location=1" gs_guc reload -Z coordinator -Z datanode -N all -I all -c "audit_enabled=on" ``` 7. 再次使用用户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; ``` 8. 初始用户登录数据库，使用系统函数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为审计策略的详细信息。 9. 如不需要继续对特定资源进行审计，可移除审计策略。 ``` drop audit policy audit_security_priall; drop audit policy audit_security_accall; ```