本文档风哥主要介绍Oracle PDB统一审计（Unified Auditing）相关知识，包括PDB统一审计的概念、PDB统一审计的组件、PDB统一审计的配置、PDB统一审计策略创建、PDB统一审计验证、PDB统一审计故障处理等内容，由风哥教程参考Oracle官方文档Security内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。
Part01-基础概念与理论知识
1.1 PDB统一审计的概念
Oracle PDB统一审计（Unified Auditing）是一种集中式审计技术，用于统一管理所有审计类型。统一审计将传统审计、细粒度审计（FGA）、数据库保险库审计等审计类型整合到一个统一的审计框架中。统一审计适用于需要集中管理审计的企业环境。更多视频教程www.fgedu.net.cn
- 集中式审计管理
- 统一审计记录
- 高性能审计
- 灵活审计策略
- 减少审计开销
1.2 PDB统一审计的组件
Oracle PDB统一审计的组件包括：
- 审计策略（Audit Policy）：定义审计规则
- 审计条件（Audit Condition）：定义审计触发条件
- 审计操作（Audit Action）：定义审计的操作
- 审计记录（Audit Record）：记录审计事件
- 审计视图（Audit View）：查看审计记录
1.3 PDB统一审计的优势
Oracle PDB统一审计的优势：
- 集中管理：集中管理所有审计类型
- 高性能：减少审计开销
- 灵活配置：支持灵活的审计配置
- 统一记录：统一审计记录格式
- 简化管理：简化审计管理
Part02-生产环境规划与建议
2.1 PDB统一审计规划
Oracle PDB统一审计规划要点：
– 审计需求分析：分析审计需求
– 审计策略规划：定义审计策略
– 审计条件规划：定义审计条件
– 审计操作规划：定义审计操作
# 审计策略规划
– 策略名称：定义策略名称
– 审计对象：定义审计对象
– 审计操作：定义审计操作
– 审计选项：定义审计选项
# 审计条件规划
– 条件类型：定义条件类型
– 条件表达式：定义条件表达式
– 条件参数：定义条件参数
– 条件验证：验证条件有效性
# 审计操作规划
– 操作类型：定义操作类型
– 操作对象：定义操作对象
– 操作参数：定义操作参数
– 操作测试：测试操作
2.2 PDB统一审计安全策略
Oracle PDB统一审计安全策略：
- 最小审计原则：只审计必要的操作
- 职责分离：分离管理和审计职责
- 定期审计：审计统一审计配置和使用
- 定期测试：定期测试审计策略
- 文档记录：记录统一审计配置
– 分析审计需求
– 定义审计策略和条件
– 配置审计操作
– 定期审计和测试
– 文档记录配置
2.3 PDB统一审计最佳实践
Oracle PDB统一审计最佳实践：
- 数据分类：根据数据敏感程度分类
- 策略设计：合理设计审计策略
- 条件设计：合理设计审计条件
- 定期审计：审计统一审计配置和使用
- 定期测试：定期测试审计策略
Part03-生产环境项目实施方案
3.1 PDB统一审计配置
3.1.1 启用统一审计
$ sqlplus / as sysdba
SQL> select value from v$option where parameter = ‘Unified Auditing’;
VALUE
——-
TRUE
SQL> select value from v$parameter where name = ‘unified_auditing’;
VALUE
——-
FALSE
# 2. 启用统一审计（需要重启数据库）
$ sqlplus / as sysdba
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 838860800 bytes
Fixed Size 8798312 bytes
Variable Size 503316480 bytes
Database Buffers 318767104 bytes
Redo Buffers 7978904 bytes
Database mounted.
Database opened.
SQL> alter system set unified_auditing = true scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 838860800 bytes
Fixed Size 8798312 bytes
Variable Size 503316480 bytes
Database Buffers 318767104 bytes
Redo Buffers 7978904 bytes
Database mounted.
Database opened.
SQL> select value from v$parameter where name = ‘unified_auditing’;
VALUE
——-
TRUE
# 3. 连接到PDB
SQL> alter session set container=SALESPDB;
Session altered.
# 4. 创建测试用户
SQL> create user fgfgfgsales_user identified by fgfgfgsales_password;
User created.
SQL> grant create session, connect, resource to fgfgfgsales_user;
Grant succeeded.
# 5. 创建测试表
SQL> create table fgfgfgsales_user.customers (
id number primary key,
name varchar2(100),
email varchar2(100),
phone varchar2(20),
credit_card varchar2(20),
ssn varchar2(20),
balance number
);
Table created.
# 6. 插入测试数据
SQL> insert into fgfgfgsales_user.customers values (1, ‘风哥1号’, ‘zhangsan@fgedu.net.cn’, ‘13800138000’, ‘1234567890123456’, ‘123456789’, 10000);
1 row created.
SQL> insert into fgfgfgsales_user.customers values (2, ‘风哥2号’, ‘lisi@fgedu.net.cn’, ‘13900139000’, ‘2345678901234567’, ‘234567890’, 20000);
1 row created.
SQL> insert into fgfgfgsales_user.customers values (3, ‘王五’, ‘wangwu@fgedu.net.cn’, ‘13700137000’, ‘3456789012345678’, ‘345678901’, 30000);
1 row created.
SQL> commit;
Commit complete.
3.1.2 创建统一审计策略
SQL> create audit policy customer_access_policy
actions select on fgfgfgsales_user.customers,
update on fgfgfgsales_user.customers,
delete on fgfgfgsales_user.customers
when ‘sys_context(”USERENV”, ”SESSION_USER”) = ”SALES_USER”’
evaluate per session;
Audit policy created.
# 2. 验证审计策略创建
SQL> select policy_name, policy_type, enabled
from audit_unified_policies
where policy_name = ‘CUSTOMER_ACCESS_POLICY’;
POLICY_NAME POLICY_TYPE ENABLED
————————- ———— ——–
CUSTOMER_ACCESS_POLICY OBJECT YES
# 3. 启用审计策略
SQL> audit policy customer_access_policy;
Audit succeeded.
# 4. 测试审计策略
SQL> conn fgfgfgsales_user/fgfgfgsales_password@SALESPDB
Connected.
SQL> select * from customers;
ID NAME EMAIL PHONE CREDIT_CARD SSN BALANCE
———- ———- ———————- ————— —————— ———- ———-
1 风哥1号 zhangsan@fgedu.net.cn 13800138000 1234567890123456 123456789 10000
2 风哥2号 lisi@fgedu.net.cn 13900139000 2345678901234567 234567890 20000
3 王五 wangwu@fgedu.net.cn 13700137000 3456789012345678 345678901 30000
# 5. 查看审计记录
SQL> conn / as sysdba
SQL> alter session set container=SALESPDB;
Session altered.
SQL> select db_user, object_schema, object_name, action_name, sql_text
from unified_audit_trail
where object_schema = ‘SALES_USER’ and object_name = ‘CUSTOMERS’
order by event_timestamp desc;
DB_USER OBJECT_SCHEMA OBJECT_NAME ACTION_NAME SQL_TEXT
———- ————- ———— ———– ————————————————
SALES_USER SALES_USER CUSTOMERS SELECT select * from customers
3.2 PDB统一审计策略创建
3.2.1 创建基于条件的统一审计策略
SQL> create audit policy high_balance_policy
actions select on fgfgfgsales_user.customers
when ‘balance >= 20000’
evaluate per statement;
Audit policy created.
# 2. 验证审计策略创建
SQL> select policy_name, policy_type, enabled
from audit_unified_policies
where policy_name = ‘HIGH_BALANCE_POLICY’;
POLICY_NAME POLICY_TYPE ENABLED
——————— ———— ——–
HIGH_BALANCE_POLICY OBJECT YES
# 3. 启用审计策略
SQL> audit policy high_balance_policy;
Audit succeeded.
# 4. 测试审计策略
SQL> conn fgfgfgsales_user/fgfgfgsales_password@SALESPDB
Connected.
SQL> select * from customers where id = 1;
ID NAME EMAIL PHONE CREDIT_CARD SSN BALANCE
———- ———- ———————- ————— —————— ———- ———-
1 风哥1号 zhangsan@fgedu.net.cn 13800138000 1234567890123456 123456789 10000
SQL> select * from customers where id = 2;
ID NAME EMAIL PHONE CREDIT_CARD SSN BALANCE
———- ———- ———————- ————— —————— ———- ———-
2 风哥2号 lisi@fgedu.net.cn 13900139000 2345678901234567 234567890 20000
SQL> select * from customers where id = 3;
ID NAME EMAIL PHONE CREDIT_CARD SSN BALANCE
———- ———- ———————- ————— —————— ———- ———-
3 王五 wangwu@fgedu.net.cn 13700137000 3456789012345678 345678901 30000
# 5. 查看审计记录
SQL> conn / as sysdba
SQL> alter session set container=SALESPDB;
Session altered.
SQL> select db_user, object_schema, object_name, action_name, sql_text
from unified_audit_trail
where policy_name = ‘HIGH_BALANCE_POLICY’
order by event_timestamp desc;
DB_USER OBJECT_SCHEMA OBJECT_NAME ACTION_NAME SQL_TEXT
———- ————- ———— ———– ————————————————
SALES_USER SALES_USER CUSTOMERS SELECT select * from customers where id = 3
SALES_USER SALES_USER CUSTOMERS SELECT select * from customers where id = 2
3.2.2 创建基于角色的统一审计策略
SQL> conn / as sysdba
SQL> alter session set container=SALESPDB;
Session altered.
SQL> create role fgfgfgsales_manager_role;
Role created.
SQL> create role fgfgfgsales_clerk_role;
Role created.
# 2. 授予权限
SQL> grant select, insert, update, delete on fgfgfgsales_user.customers to fgfgfgsales_manager_role;
Grant succeeded.
SQL> grant select on fgfgfgsales_user.customers to fgfgfgsales_clerk_role;
Grant succeeded.
# 3. 创建用户
SQL> create user manager_user identified by manager_password;
User created.
SQL> create user clerk_user identified by clerk_password;
User created.
SQL> grant create session, fgfgfgsales_manager_role to manager_user;
Grant succeeded.
SQL> grant create session, fgfgfgsales_clerk_role to clerk_user;
Grant succeeded.
# 4. 创建基于角色的统一审计策略
SQL> create audit policy manager_audit_policy
actions all on fgfgfgsales_user.customers
when ‘sys_context(”USERENV”, ”SESSION_USER”) in (select grantee from dba_role_privs where granted_role = ”SALES_MANAGER_ROLE”)’
evaluate per session;
Audit policy created.
# 5. 验证审计策略创建
SQL> select policy_name, policy_type, enabled
from audit_unified_policies
where policy_name = ‘MANAGER_AUDIT_POLICY’;
POLICY_NAME POLICY_TYPE ENABLED
———————— ———— ——–
MANAGER_AUDIT_POLICY OBJECT YES
# 6. 启用审计策略
SQL> audit policy manager_audit_policy;
Audit succeeded.
# 7. 测试审计策略
SQL> conn manager_user/manager_password@SALESPDB
Connected.
SQL> select * from fgfgfgsales_user.customers;
ID NAME EMAIL PHONE CREDIT_CARD SSN BALANCE
———- ———- ———————- ————— —————— ———- ———-
1 风哥1号 zhangsan@fgedu.net.cn 13800138000 1234567890123456 123456789 10000
2 风哥2号 lisi@fgedu.net.cn 13900139000 2345678901234567 234567890 20000
3 王五 wangwu@fgedu.net.cn 13700137000 3456789012345678 345678901 30000
SQL> conn clerk_user/clerk_password@SALESPDB
Connected.
SQL> select * from fgfgfgsales_user.customers;
ID NAME EMAIL PHONE CREDIT_CARD SSN BALANCE
———- ———- ———————- ————— —————— ———- ———-
1 风哥1号 zhangsan@fgedu.net.cn 13800138000 1234567890123456 123456789 10000
2 风哥2号 lisi@fgedu.net.cn 13900139000 2345678901234567 234567890 20000
3 王五 wangwu@fgedu.net.cn 13700137000 3456789012345678 345678901 30000
# 8. 查看审计记录
SQL> conn / as sysdba
SQL> alter session set container=SALESPDB;
Session altered.
SQL> select db_user, object_schema, object_name, action_name, sql_text
from unified_audit_trail
where policy_name = ‘MANAGER_AUDIT_POLICY’
order by event_timestamp desc;
DB_USER OBJECT_SCHEMA OBJECT_NAME ACTION_NAME SQL_TEXT
———— ————- ———— ———– ————————————————
MANAGER_USER SALES_USER CUSTOMERS SELECT select * from fgfgfgsales_user.customers
3.3 PDB统一审计验证
3.3.1 验证统一审计配置
SQL> select policy_name, policy_type, enabled
from audit_unified_policies
where object_schema = ‘SALES_USER’;
POLICY_NAME POLICY_TYPE ENABLED
————————- ———— ——–
CUSTOMER_ACCESS_POLICY OBJECT YES
HIGH_BALANCE_POLICY OBJECT YES
MANAGER_AUDIT_POLICY OBJECT YES
# 2. 查看统一审计策略详情
SQL> select policy_name, object_schema, object_name, action_name, enabled
from audit_unified_policies
where object_schema = ‘SALES_USER’;
POLICY_NAME OBJECT_SCHEMA OBJECT_NAME ACTION_NAME ENABLED
————————- ————- ———— ———– ——–
CUSTOMER_ACCESS_POLICY SALES_USER CUSTOMERS SELECT YES
CUSTOMER_ACCESS_POLICY SALES_USER CUSTOMERS UPDATE YES
CUSTOMER_ACCESS_POLICY SALES_USER CUSTOMERS DELETE YES
HIGH_BALANCE_POLICY SALES_USER CUSTOMERS SELECT YES
MANAGER_AUDIT_POLICY SALES_USER CUSTOMERS ALL YES
# 3. 查看审计记录
SQL> select db_user, object_schema, object_name, action_name, sql_text, event_timestamp
from unified_audit_trail
where object_schema = ‘SALES_USER’ and object_name = ‘CUSTOMERS’
order by event_timestamp desc;
DB_USER OBJECT_SCHEMA OBJECT_NAME ACTION_NAME SQL_TEXT EVENT_TIMESTAMP
———— ————- ———— ———– ———————————————— —————–
MANAGER_USER SALES_USER CUSTOMERS SELECT select * from fgfgfgsales_user.customers 31-MAR-26 10:00:00
SALES_USER SALES_USER CUSTOMERS SELECT select * from customers where id = 3 31-MAR-26 10:00:00
SALES_USER SALES_USER CUSTOMERS SELECT select * from customers where id = 2 31-MAR-26 10:00:00
3.3.2 管理统一审计策略
SQL> noaudit policy customer_access_policy;
Audit succeeded.
# 2. 验证策略已禁用
SQL> select policy_name, policy_type, enabled
from audit_unified_policies
where policy_name = ‘CUSTOMER_ACCESS_POLICY’;
POLICY_NAME POLICY_TYPE ENABLED
————————- ———— ——–
CUSTOMER_ACCESS_POLICY OBJECT NO
# 3. 启用审计策略
SQL> audit policy customer_access_policy;
Audit succeeded.
# 4. 删除审计策略
SQL> drop audit policy customer_access_policy;
Audit policy dropped.
# 5. 验证策略已删除
SQL> select policy_name, policy_type, enabled
from audit_unified_policies
where policy_name = ‘CUSTOMER_ACCESS_POLICY’;
no rows selected
# 6. 清理审计记录
SQL> begin
dbms_audit_mgmt.clean_audit_trail(
audit_trail_type => dbms_audit_mgmt.audit_trail_unified,
use_last_arch_timestamp => false
);
end;
/
PL/SQL procedure successfully completed.
Part04-生产案例与实战讲解
4.1 PDB统一审计配置案例
在生产环境中配置PDB统一审计的完整案例：
4.1.1 场景描述
某企业需要为销售PDB中的客户数据配置统一审计，审计查看高余额客户信息的操作，保护客户敏感信息。
4.1.2 配置步骤
SQL> conn / as sysdba
SQL> alter session set container=SALESPDB;
Session altered.
SQL> create user fgfgfgsales_admin identified by fgfgfgsales_password;
User created.
SQL> grant create session, connect, resource to fgfgfgsales_admin;
Grant succeeded.
SQL> create table fgfgfgsales_admin.customers (
id number primary key,
name varchar2(100),
email varchar2(100),
phone varchar2(20),
credit_card varchar2(20),
ssn varchar2(20),
balance number,
status varchar2(20)
);
Table created.
# 2. 插入测试数据
SQL> insert into fgfgfgsales_admin.customers values (1, ‘风哥1号’, ‘zhangsan@fgedu.net.cn’, ‘13800138000’, ‘1234567890123456’, ‘123456789’, 10000, ‘ACTIVE’);
1 row created.
SQL> insert into fgfgfgsales_admin.customers values (2, ‘风哥2号’, ‘lisi@fgedu.net.cn’, ‘13900139000’, ‘2345678901234567’, ‘234567890’, 20000, ‘ACTIVE’);
1 row created.
SQL> insert into fgfgfgsales_admin.customers values (3, ‘王五’, ‘wangwu@fgedu.net.cn’, ‘13700137000’, ‘3456789012345678’, ‘345678901’, 30000, ‘ACTIVE’);
1 row created.
SQL> insert into fgfgfgsales_admin.customers values (4, ‘赵六’, ‘zhaoliu@fgedu.net.cn’, ‘13600136000’, ‘4567890123456789’, ‘456789012’, 40000, ‘INACTIVE’);
1 row created.
SQL> insert into fgfgfgsales_admin.customers values (5, ‘钱七’, ‘qianqi@fgedu.net.cn’, ‘13500135000’, ‘5678901234567890’, ‘567890123’, 50000, ‘ACTIVE’);
1 row created.
SQL> commit;
Commit complete.
# 3. 创建统一审计策略
SQL> create audit policy high_balance_audit_policy
actions select on fgfgfgsales_admin.customers
when ‘balance >= 30000’
evaluate per statement;
Audit policy created.
# 4. 验证审计策略创建
SQL> select policy_name, policy_type, enabled
from audit_unified_policies
where policy_name = ‘HIGH_BALANCE_AUDIT_POLICY’;
POLICY_NAME POLICY_TYPE ENABLED
————————— ———— ——–
HIGH_BALANCE_AUDIT_POLICY OBJECT YES
# 5. 启用审计策略
SQL> audit policy high_balance_audit_policy;
Audit succeeded.
# 6. 测试审计策略
SQL> conn fgfgfgsales_admin/fgfgfgsales_password@SALESPDB
Connected.
SQL> select * from customers where id = 1;
ID NAME EMAIL PHONE CREDIT_CARD SSN BALANCE STATUS
———- ———- ———————- ————— —————— ———- ———- ———-
1 风哥1号 zhangsan@fgedu.net.cn 13800138000 1234567890123456 123456789 10000 ACTIVE
SQL> select * from customers where id = 3;
ID NAME EMAIL PHONE CREDIT_CARD SSN BALANCE STATUS
———- ———- ———————- ————— —————— ———- ———- ———-
3 王五 wangwu@fgedu.net.cn 13700137000 3456789012345678 345678901 30000 ACTIVE
SQL> select * from customers where id = 5;
ID NAME EMAIL PHONE CREDIT_CARD SSN BALANCE STATUS
———- ———- ———————- ————— —————— ———- ———- ———-
5 钱七 qianqi@fgedu.net.cn 13500135000 5678901234567890 567890123 50000 ACTIVE
# 7. 查看审计记录
SQL> conn / as sysdba
SQL> alter session set container=SALESPDB;
Session altered.
SQL> select db_user, object_schema, object_name, action_name, sql_text, event_timestamp
from unified_audit_trail
where policy_name = ‘HIGH_BALANCE_AUDIT_POLICY’
order by event_timestamp desc;
DB_USER OBJECT_SCHEMA OBJECT_NAME ACTION_NAME SQL_TEXT EVENT_TIMESTAMP
———— ————- ———— ———– ———————————————— —————–
SALES_ADMIN SALES_ADMIN CUSTOMERS SELECT select * from customers where id = 5 31-MAR-26 10:00:00
SALES_ADMIN SALES_ADMIN CUSTOMERS SELECT select * from customers where id = 3 31-MAR-26 10:00:00
4.2 PDB统一审计故障处理
在PDB统一审计过程中可能遇到的故障及处理方法：
4.2.1 故障现象：审计记录未生成
SQL> conn fgfgfgsales_admin/fgfgfgsales_password@SALESPDB
Connected.
SQL> select * from customers where id = 3;
ID NAME EMAIL PHONE CREDIT_CARD SSN BALANCE STATUS
———- ———- ———————- ————— —————— ———- ———- ———-
3 王五 wangwu@fgedu.net.cn 13700137000 3456789012345678 345678901 30000 ACTIVE
SQL> conn / as sysdba
SQL> alter session set container=SALESPDB;
Session altered.
SQL> select count(*) as audit_count from unified_audit_trail;
AUDIT_COUNT
———–
0
# 分析步骤
# 1. 检查统一审计状态
SQL> select value from v$parameter where name = ‘unified_auditing’;
VALUE
——-
TRUE
# 2. 检查审计策略
SQL> select policy_name, policy_type, enabled
from audit_unified_policies
where policy_name = ‘HIGH_BALANCE_AUDIT_POLICY’;
POLICY_NAME POLICY_TYPE ENABLED
————————— ———— ——–
HIGH_BALANCE_AUDIT_POLICY OBJECT YES
# 3. 检查审计策略是否启用
SQL> select policy_name, audit_condition
from audit_unified_policies
where policy_name = ‘HIGH_BALANCE_AUDIT_POLICY’;
POLICY_NAME AUDIT_CONDITION
————————— —————–
HIGH_BALANCE_AUDIT_POLICY balance >= 30000
# 4. 检查数据
SQL> select id, name, balance from fgfgfgsales_admin.customers where id = 3;
ID NAME BALANCE
———- ———- ———-
3 王五 30000
# 5. 发现数据满足审计条件
# 6. 解决方案：检查审计策略是否被启用
SQL> select policy_name, enabled
from audit_unified_enabled_policies
where policy_name = ‘HIGH_BALANCE_AUDIT_POLICY’;
POLICY_NAME ENABLED
————————— ——–
HIGH_BALANCE_AUDIT_POLICY YES
# 7. 测试审计功能
SQL> conn fgfgfgsales_admin/fgfgfgsales_password@SALESPDB
Connected.
SQL> select * from customers where id = 5;
ID NAME EMAIL PHONE CREDIT_CARD SSN BALANCE STATUS
———- ———- ———————- ————— —————— ———- ———- ———-
5 钱七 qianqi@fgedu.net.cn 13500135000 5678901234567890 567890123 50000 ACTIVE
SQL> conn / as sysdba
SQL> alter session set container=SALESPDB;
Session altered.
SQL> select db_user, object_schema, object_name, action_name, sql_text
from unified_audit_trail
where policy_name = ‘HIGH_BALANCE_AUDIT_POLICY’
order by event_timestamp desc;
DB_USER OBJECT_SCHEMA OBJECT_NAME ACTION_NAME SQL_TEXT
———— ————- ———— ———– ————————————————
SALES_ADMIN SALES_ADMIN CUSTOMERS SELECT select * from customers where id = 5
# 8. 预防措施
# – 定期检查统一审计状态
# – 监控审计记录生成
# – 定期清理审计记录
4.2.2 故障现象：审计条件不生效
SQL> conn fgfgfgsales_admin/fgfgfgsales_password@SALESPDB
Connected.
SQL> select * from customers where id = 2;
ID NAME EMAIL PHONE CREDIT_CARD SSN BALANCE STATUS
———- ———- ———————- ————— —————— ———- ———- ———-
2 风哥2号 lisi@fgedu.net.cn 13900139000 2345678901234567 234567890 20000 ACTIVE
SQL> conn / as sysdba
SQL> alter session set container=SALESPDB;
Session altered.
SQL> select db_user, object_schema, object_name, action_name, sql_text
from unified_audit_trail
where policy_name = ‘HIGH_BALANCE_AUDIT_POLICY’
order by event_timestamp desc;
DB_USER OBJECT_SCHEMA OBJECT_NAME ACTION_NAME SQL_TEXT
———— ————- ———— ———– ————————————————
SALES_ADMIN SALES_ADMIN CUSTOMERS SELECT select * from customers where id = 2
# 分析步骤
# 1. 检查审计策略条件
SQL> select policy_name, audit_condition
from audit_unified_policies
where policy_name = ‘HIGH_BALANCE_AUDIT_POLICY’;
POLICY_NAME AUDIT_CONDITION
————————— —————–
HIGH_BALANCE_AUDIT_POLICY balance >= 30000
# 2. 检查数据
SQL> select id, name, balance from fgfgfgsales_admin.customers where id = 2;
ID NAME BALANCE
———- ———- ———-
2 风哥2号 20000
# 3. 发现数据不满足审计条件（20000 < 30000）
# 4. 但审计记录仍然生成
# 5. 解决方案：检查是否有其他审计策略
SQL> select policy_name, audit_condition
from audit_unified_policies
where object_schema = ‘SALES_ADMIN’ and object_name = ‘CUSTOMERS’;
POLICY_NAME AUDIT_CONDITION
————————— —————–
HIGH_BALANCE_AUDIT_POLICY balance >= 30000
ALL_ACCESS_AUDIT_POLICY 1=1
# 6. 发现有另一个审计策略审计所有访问
# 7. 解决方案：删除或禁用不需要的审计策略
SQL> noaudit policy all_access_audit_policy;
Audit succeeded.
# 8. 验证策略已禁用
SQL> select policy_name, enabled
from audit_unified_enabled_policies
where policy_name = ‘ALL_ACCESS_AUDIT_POLICY’;
no rows selected
# 9. 再次测试
SQL> conn fgfgfgsales_admin/fgfgfgsales_password@SALESPDB
Connected.
SQL> select * from customers where id = 2;
ID NAME EMAIL PHONE CREDIT_CARD SSN BALANCE STATUS
———- ———- ———————- ————— —————— ———- ———- ———-
2 风哥2号 lisi@fgedu.net.cn 13900139000 2345678901234567 234567890 20000 ACTIVE
SQL> conn / as sysdba
SQL> alter session set container=SALESPDB;
Session altered.
SQL> select db_user, object_schema, object_name, action_name, sql_text
from unified_audit_trail
where policy_name = ‘HIGH_BALANCE_AUDIT_POLICY’
order by event_timestamp desc;
DB_USER OBJECT_SCHEMA OBJECT_NAME ACTION_NAME SQL_TEXT
———— ————- ———— ———– ————————————————
SALES_ADMIN SALES_ADMIN CUSTOMERS SELECT select * from customers where id = 5
# 10. 预防措施
# – 定期检查统一审计策略配置
# – 避免创建冲突的审计策略
# – 文档记录审计策略用途
4.3 PDB统一审计优化
优化PDB统一审计配置的最佳实践：
4.3.1 优化统一审计配置
SQL> create audit policy very_high_balance_audit_policy
actions select on fgfgfgsales_admin.customers
when ‘balance >= 50000’
evaluate per statement;
Audit policy created.
SQL> create audit policy inactive_customer_audit_policy
actions select, update on fgfgfgsales_admin.customers
when ‘status = ”INACTIVE”’
evaluate per statement;
Audit policy created.
# 2. 验证多个审计策略
SQL> select policy_name, audit_condition
from audit_unified_policies
where object_schema = ‘SALES_ADMIN’ and object_name = ‘CUSTOMERS’;
POLICY_NAME AUDIT_CONDITION
—————————– —————–
HIGH_BALANCE_AUDIT_POLICY balance >= 30000
VERY_HIGH_BALANCE_AUDIT_POLICY balance >= 50000
INACTIVE_CUSTOMER_AUDIT_POLICY status = ‘INACTIVE’
# 3. 启用审计策略
SQL> audit policy very_high_balance_audit_policy;
Audit succeeded.
SQL> audit policy inactive_customer_audit_policy;
Audit succeeded.
# 4. 配置审计清理
SQL> begin
dbms_audit_mgmt.init_cleanup(
audit_trail_type => dbms_audit_mgmt.audit_trail_unified,
default_cleanup_interval => 24
);
end;
/
PL/SQL procedure successfully completed.
# 5. 设置审计记录保留时间
SQL> begin
dbms_audit_mgmt.set_last_archive_timestamp(
audit_trail_type => dbms_audit_mgmt.audit_trail_unified,
last_archive_time => systimestamp – 30
);
end;
/
PL/SQL procedure successfully completed.
# 6. 创建审计监控脚本
$ vi /home/oracle/scripts/monitor_ua.sh
#!/bin/bash
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# 统一审计监控脚本
export ORACLE_HOME=/oracle/app/oracle/product/19c/dbhome_1
export ORACLE_SID=FGEDUDB
export PATH=$ORACLE_HOME/bin:$PATH
# 检查统一审计状态
UA_STATUS=$(sqlplus -s / as sysdba << EOF
set heading off feedback off pagesize 0
alter session set container=SALESPDB;
select value from v$parameter where name = ‘unified_auditing’;
exit;
EOF
)
if [ “$UA_STATUS” != “TRUE” ]; then
echo “WARNING: Unified Auditing is not enabled”
# 发送告警
echo “Unified Auditing is not enabled” | mail -s “Unified Auditing Alert” admin@fgedu.net.cn
fi
# 检查审计策略状态
POLICY_COUNT=$(sqlplus -s / as sysdba << EOF
set heading off feedback off pagesize 0
alter session set container=SALESPDB;
select count(*) from audit_unified_policies where enabled = ‘NO’;
exit;
EOF
)
if [ “$POLICY_COUNT” -gt 0 ]; then
echo “WARNING: $POLICY_COUNT audit policies are disabled”
# 发送告警
echo “$POLICY_COUNT audit policies are disabled” | mail -s “Audit Policy Alert” admin@fgedu.net.cn
fi
# 检查审计记录数量
AUDIT_COUNT=$(sqlplus -s / as sysdba << EOF
set heading off feedback off pagesize 0
alter session set container=SALESPDB;
select count(*) from unified_audit_trail where event_timestamp > sysdate – 1;
exit;
EOF
)
echo “Unified audit records in last 24 hours: $AUDIT_COUNT”
echo “Unified audit monitoring completed at $(date)”
# 7. 设置定期监控
$ crontab -e
# 每小时检查统一审计状态
0 * * * * /home/oracle/scripts/monitor_ua.sh >> /home/oracle/scripts/monitor_ua.log 2>&1
Part05-风哥经验总结与分享
5.1 PDB统一审计总结
Oracle PDB统一审计是集中式审计技术，具有以下特点：
- 集中管理：集中管理所有审计类型
- 高性能：减少审计开销
- 灵活配置：支持灵活的审计配置
- 统一记录：统一审计记录格式
- 简化管理：简化审计管理
5.2 PDB统一审计检查清单
Oracle PDB统一审计检查清单：
- 配置检查：检查统一审计配置
- 状态检查：检查统一审计状态
- 策略检查：检查审计策略
- 记录检查：检查审计记录
- 审计检查：检查统一审计
- 监控检查：检查统一审计监控
5.3 PDB统一审计工具推荐
Oracle PDB统一审计工具推荐：
- DBMS_AUDIT_MGMT包：审计管理包
- SQL*Plus：命令行工具，用于统一审计管理
- SQL Developer：图形化工具，用于统一审计管理
- Enterprise Manager：企业级管理工具
- Oracle Audit Vault：审计管理工具
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息
