设置统一审计策略

背景信息

传统审计会产生大量的审计日志，且不支持定制化的访问对象和访问来源配置，不方便数据库安全管理员对审计日志的分析。而统一审计策略支持绑定资源标签、配置数据来源输出审计日志，可以提升安全管理员对数据库监控的效率。

操作步骤

  1. 执行以下命令开启统一审计开关。

1. gs_guc reload -Z coordinator -N all -I all -c "enable_security_policy=on" 2. 操作系统root用户进行rsyslog配置。

在操作系统后台服务配置文件/etc/rsyslog.conf中添加：

1. local0.* /var/log/localmessages

重启rsyslog服务使配置生效。

1. sudo systemctl restart rsyslog 3. 安全策略管理员登录数据库，配置资源标签，对于安全策略管理员的相关操作参考管理员章节，审计策略参数参考SQL语法描述。

``` 1. -- 初始化资源 2. DROP TABLE IF EXISTS table_security_auditing; 3. CREATE TABLE table_security_auditing(id int,name char(10)); 4. create user user001 password '*'; 5. create user user002 password '*'; 6. grant all privileges to user001;

  1. -- 新建资源标签
  2. DROP RESOURCE LABEL IF EXISTS rl_security_auditing;

  3. CREATE RESOURCE LABEL rl_security_auditing ADD TABLE(table_security_auditing);

  4. -- 创建审计策略，审计用户user001在资源标签rl_security_auditing上的DDL、DML操作

  5. CREATE AUDIT POLICY audit_security_priall PRIVILEGES all on LABEL(rl_security_auditing) FILTER ON ROLES(user001);
  6. CREATE AUDIT POLICY audit_security_accall ACCESS all on LABEL(rl_security_auditing) FILTER ON ROLES(user001); ```
  7. 使用用户user001登录数据库，执行如下操作, 触发审计策略。

1. -- DML 2. insert into table_security_auditing values(1,'22'); 3. update table_security_auditing set name=234123 where id=1; 4. delete from table_security_auditing where id=1; 5. truncate table table_security_auditing; 6. -- DDL 7. GRANT INSERT ON TABLE table_security_auditing TO user002; 8. revoke insert on table table_security_auditing from user002; 5. 使用操作系统root用户查看审计日志/var/log/localmessage。

1. 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_auditi ng], result: [OK] 2. 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_auditi ng], result: [OK] 3. 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_auditi ng], result: [OK] 4. 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_audi ting], result: [OK] 5. 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] 6. 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] 6. 如不需要继续对特定资源进行审计，可移除审计策略。

1. drop audit policy audit_security_priall; 2. drop audit policy audit_security_accall;

统一审计日志字段说明

1. 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]

以如上TRUNCATE操作触发的审计日志为例，字段说明如下:

|时间戳|主机名|事件类型|用户名|触发客户端|客户端IP|操作类型|策略ID|列名称|执行结果|

设置统一审计策略 - 图1 注意： 在使用DATABASE LINK功能的场景下，客户端发起的DATABASE LINK请求，实际的发送方是服务端，发送端ip地址等相关的属性将是服务端的值。