Oracle教程FG398-PDB细粒度审计

本文档风哥主要介绍Oracle PDB细粒度审计（Fine-Grained Auditing，FGA）相关知识，包括PDB细粒度审计的概念、PDB细粒度审计的组件、PDB细粒度审计的配置、PDB细粒度审计策略创建、PDB细粒度审计验证、PDB细粒度审计故障处理等内容，由风哥教程参考Oracle官方文档Security内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。

Part01-基础概念与理论知识

1.1 PDB细粒度审计的概念

Oracle PDB细粒度审计（Fine-Grained Auditing，FGA）是一种基于条件的审计技术，用于审计特定表或视图上的特定操作。FGA允许DBA定义审计条件，只有满足条件的操作才会被审计。FGA适用于需要精确控制审计范围的企业环境。更多视频教程www.fgedu.net.cn

Oracle PDB细粒度审计的特点：

基于条件的审计
精确控制审计范围
支持审计条件
支持审计处理程序
减少审计数据量

1.2 PDB细粒度审计的组件

Oracle PDB细粒度审计的组件包括：

审计策略（Audit Policy）：定义审计规则
审计条件（Audit Condition）：定义审计触发条件
审计列（Audit Column）：定义审计的列
审计处理程序（Audit Handler）：定义审计处理逻辑
审计事件（Audit Event）：记录审计事件

1.3 PDB细粒度审计的优势

Oracle PDB细粒度审计的优势：

精确控制：精确控制审计范围
减少数据量：减少审计数据量
灵活配置：支持灵活的审计配置
条件审计：支持基于条件的审计
集中管理：集中管理审计策略

风哥提示：PDB细粒度审计是基于条件的审计技术，特别适合需要精确控制审计范围的企业环境。

Part02-生产环境规划与建议

2.1 PDB细粒度审计规划

Oracle PDB细粒度审计规划要点：

# FGA规划
– 敏感数据识别：识别需要审计的敏感数据
– 审计策略规划：定义审计策略
– 审计条件规划：定义审计条件
– 审计处理程序规划：定义审计处理程序

# 审计策略规划
– 策略名称：定义策略名称
– 审计对象：定义审计对象
– 审计操作：定义审计操作
– 审计选项：定义审计选项

# 审计条件规划
– 条件类型：定义条件类型
– 条件表达式：定义条件表达式
– 条件参数：定义条件参数
– 条件验证：验证条件有效性

# 审计处理程序规划
– 处理程序类型：定义处理程序类型
– 处理程序逻辑：定义处理程序逻辑
– 处理程序参数：定义处理程序参数
– 处理程序测试：测试处理程序

2.2 PDB细粒度审计安全策略

Oracle PDB细粒度审计安全策略：

最小审计原则：只审计必要的操作
职责分离：分离管理和审计职责
定期审计：审计FGA配置和使用
定期测试：定期测试审计策略
文档记录：记录FGA配置

# 安全配置建议
– 识别敏感数据
– 定义审计策略和条件
– 配置审计处理程序
– 定期审计和测试
– 文档记录配置

2.3 PDB细粒度审计最佳实践

Oracle PDB细粒度审计最佳实践：

数据分类：根据数据敏感程度分类
策略设计：合理设计审计策略
条件设计：合理设计审计条件
定期审计：审计FGA配置和使用
定期测试：定期测试审计策略

生产环境建议：细粒度审计是基于条件的审计技术，建议根据数据敏感程度合理设计审计策略和条件，并定期审计和测试。学习交流加群风哥微信: itpux-com

Part03-生产环境项目实施方案

3.1 PDB细粒度审计配置

3.1.1 创建测试表

# 1. 连接到PDB
$ sqlplus / as sysdba

SQL> alter session set container=SALESPDB;

Session altered.

# 2. 创建用户
SQL> create user fgfgfgsales_user identified by fgfgfgsales_password;

User created.

SQL> grant create session, connect, resource to fgfgfgsales_user;

Grant succeeded.

# 3. 创建测试表
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.

# 4. 插入测试数据
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.

# 5. 验证数据
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

3.1.2 创建FGA策略

# 1. 创建FGA策略
SQL> begin
dbms_fga.add_policy(
object_schema => ‘SALES_USER’,
object_name => ‘CUSTOMERS’,
policy_name => ‘CUSTOMER_FGA_POLICY’,
audit_condition => ‘balance > 15000’,
audit_column => ‘CREDIT_CARD,SSN,BALANCE’,
handler_schema => null,
handler_module => null,
enable => true,
statement_types => ‘SELECT,UPDATE,DELETE’
);
end;
/

PL/SQL procedure successfully completed.

# 2. 验证FGA策略创建
SQL> select object_schema, object_name, policy_name, enabled
from dba_audit_policies
where object_schema = ‘SALES_USER’ and object_name = ‘CUSTOMERS’;

OBJECT_SCHEMA OBJECT_NAME POLICY_NAME ENABLED
————- ———— ——————— ——–
SALES_USER CUSTOMERS CUSTOMER_FGA_POLICY YES

# 3. 查看FGA策略详情
SQL> select object_schema, object_name, policy_name,
audit_condition, audit_column, enabled, statement_types
from dba_audit_policies
where object_schema = ‘SALES_USER’ and object_name = ‘CUSTOMERS’;

OBJECT_SCHEMA OBJECT_NAME POLICY_NAME AUDIT_CONDITION AUDIT_COLUMN ENABLED STATEMENT_TYPES
————- ———— ——————— —————– ————- ——– —————-
SALES_USER CUSTOMERS CUSTOMER_FGA_POLICY balance > 15000 CREDIT_CARD,SSN,BALANCE YES SELECT,UPDATE,DELETE

# 4. 测试FGA策略
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

# 5. 查看审计记录
SQL> conn / as sysdba

SQL> alter session set container=SALESPDB;

Session altered.

SQL> select db_user, os_user, object_schema, object_name, policy_name, sql_text
from dba_fga_audit_trail
order by timestamp desc;

DB_USER OS_USER OBJECT_SCHEMA OBJECT_NAME POLICY_NAME SQL_TEXT
———- ———- ————- ———— ——————— ————————————————
SALES_USER oracle SALES_USER CUSTOMERS CUSTOMER_FGA_POLICY select * from customers where id = 2

3.2 PDB细粒度审计策略创建

3.2.1 创建基于条件的FGA策略

# 1. 创建基于条件的FGA策略
SQL> begin
dbms_fga.add_policy(
object_schema => ‘SALES_USER’,
object_name => ‘CUSTOMERS’,
policy_name => ‘HIGH_BALANCE_FGA_POLICY’,
audit_condition => ‘balance >= 20000’,
audit_column => ‘CREDIT_CARD,SSN,BALANCE’,
handler_schema => null,
handler_module => null,
enable => true,
statement_types => ‘SELECT’
);
end;
/

PL/SQL procedure successfully completed.

# 2. 验证FGA策略创建
SQL> select object_schema, object_name, policy_name, enabled
from dba_audit_policies
where object_schema = ‘SALES_USER’ and object_name = ‘CUSTOMERS’;

OBJECT_SCHEMA OBJECT_NAME POLICY_NAME ENABLED
————- ———— ———————– ——–
SALES_USER CUSTOMERS CUSTOMER_FGA_POLICY YES
SALES_USER CUSTOMERS HIGH_BALANCE_FGA_POLICY YES

# 3. 测试FGA策略
SQL> conn fgfgfgsales_user/fgfgfgsales_password@SALESPDB

Connected.

SQL> select * from customers where id = 1;

SQL> select * from customers where id = 2;

SQL> select * from customers where id = 3;

ID NAME EMAIL PHONE CREDIT_CARD SSN BALANCE
———- ———- ———————- ————— —————— ———- ———-
3 王五 wangwu@fgedu.net.cn 13700137000 3456789012345678 345678901 30000

# 4. 查看审计记录
SQL> conn / as sysdba

SQL> alter session set container=SALESPDB;

Session altered.

SQL> select db_user, object_schema, object_name, policy_name, sql_text
from dba_fga_audit_trail
order by timestamp desc;

DB_USER OBJECT_SCHEMA OBJECT_NAME POLICY_NAME SQL_TEXT
———- ————- ———— ———————– ————————————————
SALES_USER SALES_USER CUSTOMERS HIGH_BALANCE_FGA_POLICY select * from customers where id = 2
SALES_USER SALES_USER CUSTOMERS HIGH_BALANCE_FGA_POLICY select * from customers where id = 3

3.2.2 创建基于用户的FGA策略

# 1. 创建多个用户
SQL> conn / as sysdba

SQL> alter session set container=SALESPDB;

Session altered.

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, select on fgfgfgsales_user.customers to manager_user;

Grant succeeded.

SQL> grant create session, select on fgfgfgsales_user.customers to clerk_user;

Grant succeeded.

# 2. 创建基于用户的FGA策略
SQL> begin
dbms_fga.add_policy(
object_schema => ‘SALES_USER’,
object_name => ‘CUSTOMERS’,
policy_name => ‘MANAGER_FGA_POLICY’,
audit_condition => ‘sys_context(”USERENV”, ”SESSION_USER”) = ”MANAGER_USER”’,
audit_column => ‘CREDIT_CARD,SSN,BALANCE’,
handler_schema => null,
handler_module => null,
enable => true,
statement_types => ‘SELECT’
);
end;
/

PL/SQL procedure successfully completed.

# 3. 验证FGA策略创建
SQL> select object_schema, object_name, policy_name, enabled
from dba_audit_policies
where object_schema = ‘SALES_USER’ and object_name = ‘CUSTOMERS’;

OBJECT_SCHEMA OBJECT_NAME POLICY_NAME ENABLED
————- ———— ———————– ——–
SALES_USER CUSTOMERS CUSTOMER_FGA_POLICY YES
SALES_USER CUSTOMERS HIGH_BALANCE_FGA_POLICY YES
SALES_USER CUSTOMERS MANAGER_FGA_POLICY YES

# 4. 测试FGA策略
SQL> conn manager_user/manager_password@SALESPDB

Connected.

SQL> select * from fgfgfgsales_user.customers;

SQL> conn clerk_user/clerk_password@SALESPDB

Connected.

SQL> select * from fgfgfgsales_user.customers;

# 5. 查看审计记录
SQL> conn / as sysdba

SQL> alter session set container=SALESPDB;

Session altered.

SQL> select db_user, object_schema, object_name, policy_name, sql_text
from dba_fga_audit_trail
where policy_name = ‘MANAGER_FGA_POLICY’
order by timestamp desc;

DB_USER OBJECT_SCHEMA OBJECT_NAME POLICY_NAME SQL_TEXT
———— ————- ———— ——————- ————————————————
MANAGER_USER SALES_USER CUSTOMERS MANAGER_FGA_POLICY select * from fgfgfgsales_user.customers

3.3 PDB细粒度审计验证

3.3.1 验证FGA配置

# 1. 查看FGA策略
SQL> select object_schema, object_name, policy_name, enabled
from dba_audit_policies
where object_schema = ‘SALES_USER’ and object_name = ‘CUSTOMERS’;

# 2. 查看FGA策略详情
SQL> select object_schema, object_name, policy_name,
audit_condition, audit_column, enabled, statement_types
from dba_audit_policies
where object_schema = ‘SALES_USER’ and object_name = ‘CUSTOMERS’;

OBJECT_SCHEMA OBJECT_NAME POLICY_NAME AUDIT_CONDITION AUDIT_COLUMN ENABLED STATEMENT_TYPES
————- ———— ———————– —————– ————- ——– —————-
SALES_USER CUSTOMERS CUSTOMER_FGA_POLICY balance > 15000 CREDIT_CARD,SSN,BALANCE YES SELECT,UPDATE,DELETE
SALES_USER CUSTOMERS HIGH_BALANCE_FGA_POLICY balance >= 20000 CREDIT_CARD,SSN,BALANCE YES SELECT
SALES_USER CUSTOMERS MANAGER_FGA_POLICY sys_context(‘USERENV’, ‘SESSION_USER’) = ‘MANAGER_USER’ CREDIT_CARD,SSN,BALANCE YES SELECT

# 3. 查看审计记录
SQL> select db_user, os_user, object_schema, object_name, policy_name, sql_text, timestamp
from dba_fga_audit_trail
order by timestamp desc;

DB_USER OS_USER OBJECT_SCHEMA OBJECT_NAME POLICY_NAME SQL_TEXT TIMESTAMP
———— ——- ————- ———— ———————– ———————————————— —————–
MANAGER_USER oracle SALES_USER CUSTOMERS MANAGER_FGA_POLICY select * from fgfgfgsales_user.customers 31-MAR-26 10:00:00
SALES_USER oracle SALES_USER CUSTOMERS HIGH_BALANCE_FGA_POLICY select * from customers where id = 3 31-MAR-26 10:00:00
SALES_USER oracle SALES_USER CUSTOMERS HIGH_BALANCE_FGA_POLICY select * from customers where id = 2 31-MAR-26 10:00:00

3.3.2 管理FGA策略

# 1. 禁用FGA策略
SQL> begin
dbms_fga.enable_policy(
object_schema => ‘SALES_USER’,
object_name => ‘CUSTOMERS’,
policy_name => ‘CUSTOMER_FGA_POLICY’,
enable => false
);
end;
/

PL/SQL procedure successfully completed.

# 2. 验证策略已禁用
SQL> select object_schema, object_name, policy_name, enabled
from dba_audit_policies
where object_schema = ‘SALES_USER’ and object_name = ‘CUSTOMERS’;

OBJECT_SCHEMA OBJECT_NAME POLICY_NAME ENABLED
————- ———— ———————– ——–
SALES_USER CUSTOMERS CUSTOMER_FGA_POLICY NO
SALES_USER CUSTOMERS HIGH_BALANCE_FGA_POLICY YES
SALES_USER CUSTOMERS MANAGER_FGA_POLICY YES

# 3. 启用FGA策略
SQL> begin
dbms_fga.enable_policy(
object_schema => ‘SALES_USER’,
object_name => ‘CUSTOMERS’,
policy_name => ‘CUSTOMER_FGA_POLICY’,
enable => true
);
end;
/

PL/SQL procedure successfully completed.

# 4. 删除FGA策略
SQL> begin
dbms_fga.drop_policy(
object_schema => ‘SALES_USER’,
object_name => ‘CUSTOMERS’,
policy_name => ‘CUSTOMER_FGA_POLICY’
);
end;
/

PL/SQL procedure successfully completed.

# 5. 验证策略已删除
SQL> select object_schema, object_name, policy_name, enabled
from dba_audit_policies
where object_schema = ‘SALES_USER’ and object_name = ‘CUSTOMERS’;

OBJECT_SCHEMA OBJECT_NAME POLICY_NAME ENABLED
————- ———— ———————– ——–
SALES_USER CUSTOMERS HIGH_BALANCE_FGA_POLICY YES
SALES_USER CUSTOMERS MANAGER_FGA_POLICY YES

风哥提示：细粒度审计是基于条件的审计技术，建议根据数据敏感程度合理设计审计策略和条件，并定期审计和测试。学习交流加群风哥QQ113257174

Part04-生产案例与实战讲解

4.1 PDB细粒度审计配置案例

在生产环境中配置PDB细粒度审计的完整案例：

4.1.1 场景描述

某企业需要为销售PDB中的客户数据配置FGA，审计查看高余额客户信息的操作，保护客户敏感信息。

4.1.2 配置步骤

# 1. 创建测试表
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. 创建FGA策略
SQL> begin
dbms_fga.add_policy(
object_schema => ‘SALES_ADMIN’,
object_name => ‘CUSTOMERS’,
policy_name => ‘HIGH_BALANCE_FGA_POLICY’,
audit_condition => ‘balance >= 30000’,
audit_column => ‘CREDIT_CARD,SSN,BALANCE’,
handler_schema => null,
handler_module => null,
enable => true,
statement_types => ‘SELECT’
);
end;
/

PL/SQL procedure successfully completed.

# 4. 验证FGA策略创建
SQL> select object_schema, object_name, policy_name, enabled
from dba_audit_policies
where object_schema = ‘SALES_ADMIN’ and object_name = ‘CUSTOMERS’;

OBJECT_SCHEMA OBJECT_NAME POLICY_NAME ENABLED
————- ———— ———————– ——–
SALES_ADMIN CUSTOMERS HIGH_BALANCE_FGA_POLICY YES

# 5. 测试FGA策略
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

# 6. 查看审计记录
SQL> conn / as sysdba

SQL> alter session set container=SALESPDB;

Session altered.

SQL> select db_user, object_schema, object_name, policy_name, sql_text, timestamp
from dba_fga_audit_trail
order by timestamp desc;

DB_USER OBJECT_SCHEMA OBJECT_NAME POLICY_NAME SQL_TEXT TIMESTAMP
———— ————- ———— ———————– ———————————————— —————–
SALES_ADMIN SALES_ADMIN CUSTOMERS HIGH_BALANCE_FGA_POLICY select * from customers where id = 5 31-MAR-26 10:00:00
SALES_ADMIN SALES_ADMIN CUSTOMERS HIGH_BALANCE_FGA_POLICY 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;

SQL> conn / as sysdba

SQL> alter session set container=SALESPDB;

Session altered.

SQL> select count(*) as audit_count from dba_fga_audit_trail;

AUDIT_COUNT
———–
0

# 分析步骤

# 1. 检查FGA策略
SQL> select object_schema, object_name, policy_name, enabled
from dba_audit_policies
where object_schema = ‘SALES_ADMIN’ and object_name = ‘CUSTOMERS’;

OBJECT_SCHEMA OBJECT_NAME POLICY_NAME ENABLED
————- ———— ———————– ——–
SALES_ADMIN CUSTOMERS HIGH_BALANCE_FGA_POLICY YES

# 2. 检查FGA策略条件
SQL> select object_schema, object_name, policy_name, audit_condition
from dba_audit_policies
where object_schema = ‘SALES_ADMIN’ and object_name = ‘CUSTOMERS’;

OBJECT_SCHEMA OBJECT_NAME POLICY_NAME AUDIT_CONDITION
————- ———— ———————– —————–
SALES_ADMIN CUSTOMERS HIGH_BALANCE_FGA_POLICY balance >= 30000

# 3. 检查数据
SQL> select id, name, balance from fgfgfgsales_admin.customers where id = 3;

ID NAME BALANCE
———- ———- ———-
3 王五 30000

# 4. 发现数据满足审计条件

# 5. 检查审计表空间
SQL> select tablespace_name, status from dba_tablespaces where tablespace_name = ‘SYSAUX’;

TABLESPACE_NAME STATUS
————— ———-
SYSAUX ONLINE

# 6. 解决方案：检查FGA表空间
SQL> select table_name, tablespace_name from dba_tables where table_name = ‘FGA_LOG$’;

TABLE_NAME TABLESPACE_NAME
———- —————-
FGA_LOG$ SYSAUX

# 7. 测试审计功能
SQL> conn fgfgfgsales_admin/fgfgfgsales_password@SALESPDB

Connected.

SQL> select * from customers where id = 5;

SQL> conn / as sysdba

SQL> alter session set container=SALESPDB;

Session altered.

SQL> select db_user, object_schema, object_name, policy_name, sql_text
from dba_fga_audit_trail
order by timestamp desc;

DB_USER OBJECT_SCHEMA OBJECT_NAME POLICY_NAME SQL_TEXT
———— ————- ———— ———————– ————————————————
SALES_ADMIN SALES_ADMIN CUSTOMERS HIGH_BALANCE_FGA_POLICY select * from customers where id = 5

# 8. 预防措施
# – 定期检查FGA策略状态
# – 监控审计记录生成
# – 定期清理审计记录

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, policy_name, sql_text
from dba_fga_audit_trail
order by timestamp desc;

# 分析步骤

# 1. 检查FGA策略条件
SQL> select object_schema, object_name, policy_name, audit_condition
from dba_audit_policies
where object_schema = ‘SALES_ADMIN’ and object_name = ‘CUSTOMERS’;

OBJECT_SCHEMA OBJECT_NAME POLICY_NAME AUDIT_CONDITION
————- ———— ———————– —————–
SALES_ADMIN CUSTOMERS HIGH_BALANCE_FGA_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. 解决方案：检查是否有其他FGA策略 SQL> select object_schema, object_name, policy_name, audit_condition
from dba_audit_policies
where object_schema = ‘SALES_ADMIN’ and object_name = ‘CUSTOMERS’;

OBJECT_SCHEMA OBJECT_NAME POLICY_NAME AUDIT_CONDITION
————- ———— ———————– —————–
SALES_ADMIN CUSTOMERS HIGH_BALANCE_FGA_POLICY balance >= 30000
SALES_ADMIN CUSTOMERS ALL_ACCESS_FGA_POLICY 1=1

# 6. 发现有另一个FGA策略审计所有访问

# 7. 解决方案：删除或禁用不需要的FGA策略
SQL> begin
dbms_fga.drop_policy(
object_schema => ‘SALES_ADMIN’,
object_name => ‘CUSTOMERS’,
policy_name => ‘ALL_ACCESS_FGA_POLICY’
);
end;
/

PL/SQL procedure successfully completed.

# 8. 验证策略已删除
SQL> select object_schema, object_name, policy_name, audit_condition
from dba_audit_policies
where object_schema = ‘SALES_ADMIN’ and object_name = ‘CUSTOMERS’;

OBJECT_SCHEMA OBJECT_NAME POLICY_NAME AUDIT_CONDITION
————- ———— ———————– —————–
SALES_ADMIN CUSTOMERS HIGH_BALANCE_FGA_POLICY balance >= 30000

# 9. 再次测试
SQL> conn fgfgfgsales_admin/fgfgfgsales_password@SALESPDB

Connected.

SQL> select * from customers where id = 2;

SQL> conn / as sysdba

SQL> alter session set container=SALESPDB;

Session altered.

SQL> select db_user, object_schema, object_name, policy_name, sql_text
from dba_fga_audit_trail
order by timestamp desc;

# 10. 预防措施
# – 定期检查FGA策略配置
# – 避免创建冲突的FGA策略
# – 文档记录FGA策略用途

4.3 PDB细粒度审计优化

优化PDB细粒度审计配置的最佳实践：

4.3.1 优化FGA配置

# 1. 创建多个FGA策略
SQL> begin
dbms_fga.add_policy(
object_schema => ‘SALES_ADMIN’,
object_name => ‘CUSTOMERS’,
policy_name => ‘VERY_HIGH_BALANCE_FGA_POLICY’,
audit_condition => ‘balance >= 50000’,
audit_column => ‘CREDIT_CARD,SSN,BALANCE’,
handler_schema => null,
handler_module => null,
enable => true,
statement_types => ‘SELECT’
);
end;
/

PL/SQL procedure successfully completed.

SQL> begin
dbms_fga.add_policy(
object_schema => ‘SALES_ADMIN’,
object_name => ‘CUSTOMERS’,
policy_name => ‘INACTIVE_CUSTOMER_FGA_POLICY’,
audit_condition => ‘status = ”INACTIVE”’,
audit_column => ‘CREDIT_CARD,SSN,BALANCE,STATUS’,
handler_schema => null,
handler_module => null,
enable => true,
statement_types => ‘SELECT,UPDATE’
);
end;
/

PL/SQL procedure successfully completed.

# 2. 验证多个FGA策略
SQL> select object_schema, object_name, policy_name, audit_condition
from dba_audit_policies
where object_schema = ‘SALES_ADMIN’ and object_name = ‘CUSTOMERS’;

OBJECT_SCHEMA OBJECT_NAME POLICY_NAME AUDIT_CONDITION
————- ———— —————————– —————–
SALES_ADMIN CUSTOMERS HIGH_BALANCE_FGA_POLICY balance >= 30000
SALES_ADMIN CUSTOMERS VERY_HIGH_BALANCE_FGA_POLICY balance >= 50000
SALES_ADMIN CUSTOMERS INACTIVE_CUSTOMER_FGA_POLICY status = ‘INACTIVE’

# 3. 配置FGA审计
SQL> audit policy oracle_secure_config;

Audit succeeded.

# 4. 监控FGA策略
SQL> select object_schema, object_name, policy_name, enabled
from dba_audit_policies
where object_schema = ‘SALES_ADMIN’;

OBJECT_SCHEMA OBJECT_NAME POLICY_NAME ENABLED
————- ———— —————————– ——–
SALES_ADMIN CUSTOMERS HIGH_BALANCE_FGA_POLICY YES
SALES_ADMIN CUSTOMERS VERY_HIGH_BALANCE_FGA_POLICY YES
SALES_ADMIN CUSTOMERS INACTIVE_CUSTOMER_FGA_POLICY YES

# 5. 创建FGA监控脚本
$ vi /home/oracle/scripts/monitor_fga.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
# FGA监控脚本

export ORACLE_HOME=/oracle/app/oracle/product/19c/dbhome_1
export ORACLE_SID=FGEDUDB
export PATH=$ORACLE_HOME/bin:$PATH

# 检查FGA策略状态
POLICY_COUNT=$(sqlplus -s / as sysdba << EOF set heading off feedback off pagesize 0 alter session set container=SALESPDB;
select count(*) from dba_audit_policies where enabled = ‘NO’;
exit;
EOF
)

if [ “$POLICY_COUNT” -gt 0 ]; then
echo “WARNING: $POLICY_COUNT FGA policies are disabled”
# 发送告警
echo “$POLICY_COUNT FGA policies are disabled” | mail -s “FGA 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 dba_fga_audit_trail where timestamp > sysdate – 1;
exit;
EOF
)

echo “FGA audit records in last 24 hours: $AUDIT_COUNT”

echo “FGA monitoring completed at $(date)”

# 6. 设置定期监控
$ crontab -e

# 每小时检查FGA状态
0 * * * * /home/oracle/scripts/monitor_fga.sh >> /home/oracle/scripts/monitor_fga.log 2>&1

生产环境建议：细粒度审计优化需要综合考虑安全性、可用性和管理效率。建议合理设计审计策略和条件，定期审计和测试。更多学习教程公众号风哥教程itpux_com

Part05-风哥经验总结与分享

5.1 PDB细粒度审计总结

Oracle PDB细粒度审计是基于条件的审计技术，具有以下特点：

精确控制：精确控制审计范围
减少数据量：减少审计数据量
灵活配置：支持灵活的审计配置
条件审计：支持基于条件的审计
集中管理：集中管理审计策略

5.2 PDB细粒度审计检查清单

Oracle PDB细粒度审计检查清单：

配置检查：检查FGA策略配置
状态检查：检查FGA策略状态
条件检查：检查审计条件
记录检查：检查审计记录
审计检查：检查FGA审计
监控检查：检查FGA监控

5.3 PDB细粒度审计工具推荐

Oracle PDB细粒度审计工具推荐：

DBMS_FGA包：FGA管理包
SQL*Plus：命令行工具，用于FGA管理
SQL Developer：图形化工具，用于FGA管理
Enterprise Manager：企业级管理工具
Oracle Audit Vault：审计管理工具

风哥提示：PDB细粒度审计是基于条件的审计技术，特别适合需要精确控制审计范围的企业环境。建议根据数据敏感程度合理设计审计策略和条件，并定期审计和测试。from:www.itpux.com www.fgedu.net.cn

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

Oracle教程FG398-PDB细粒度审计

Part01-基础概念与理论知识

1.1 PDB细粒度审计的概念

1.2 PDB细粒度审计的组件

1.3 PDB细粒度审计的优势

Part02-生产环境规划与建议

2.1 PDB细粒度审计规划

2.2 PDB细粒度审计安全策略

2.3 PDB细粒度审计最佳实践

Part03-生产环境项目实施方案

3.1 PDB细粒度审计配置

3.1.1 创建测试表

3.1.2 创建FGA策略

3.2 PDB细粒度审计策略创建

3.2.1 创建基于条件的FGA策略

3.2.2 创建基于用户的FGA策略

3.3 PDB细粒度审计验证

3.3.1 验证FGA配置

3.3.2 管理FGA策略

Part04-生产案例与实战讲解

4.1 PDB细粒度审计配置案例

4.1.1 场景描述

4.1.2 配置步骤

4.2 PDB细粒度审计故障处理

4.2.1 故障现象：审计记录未生成

4.2.2 故障现象：审计条件不生效

4.3 PDB细粒度审计优化

4.3.1 优化FGA配置

Part05-风哥经验总结与分享

5.1 PDB细粒度审计总结

5.2 PDB细粒度审计检查清单

5.3 PDB细粒度审计工具推荐

相关推荐

联系我们