FGA细粒度审计
1. 前言
- ORACLE的标准审计默认级别是DB,这个级别是不会记录sql语句的,如果需要记录sql语句,则需要将audit_trail参数设置为audit_trail=db,extend。
AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }
SYS@hfdr>show parameter audit_trail
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string DB
可是更改audit_trail参数需要重启数据库,就可以使用细粒度审计(FGA:Fine Grained Auditing)来进行实现
2. 细粒度审计(FGA:Fine Grained Auditing)
细粒度审计(FGA:Fine Grained Auditing):实现特定行和列的自定义审计
DBMS_FGA.ADD_POLICY(
object_schema IN VARCHAR2 DEFAULT NULL
object_name IN VARCHAR2,
policy_name IN VARCHAR2,
audit_condition IN VARCHAR2 DEFAULT NULL,
audit_column IN VARCHAR2 DEFAULT NULL
handler_schema IN VARCHAR2 DEFAULT NULL,
handler_module IN VARCHAR2 DEFAULT NULL,
enable IN BOOLEAN DEFAULT TRUE,
statement IN SELECT,
audit_trail IN BINARY_INTEGER DEFAULT NULL,
audit_column_opts IN BINARY_INTEGER DEFAULT ANY_COLUMNS,
policy_owner IN VARCHAR2 DEFAULT NULL);
/
object_schema:指定要审计的对象的模式。(如果NULL,则假定当前登录用户。)
object_name:指定要审计的对象的名称。
policy_name:指定要创建的策略的名称。确保此名称是唯一的。
audit_condition:审计的相关条件,如果为null或未指定,则对表执行的任何审计都会被记录
audit_column:指定要审核的一列或多列,包括隐藏列。如果设置为NULL或省略
Enable:使用 true 或 false 启用或禁用策略。
statement_types:指定要审计的 SQL 语句:INSERT、UPDATE、DELETE或SELECT
BEGIN
DBMS_FGA.ADD_POLICY(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'chk_hr_employees',
audit_column => 'SALARY',
enable => TRUE,
statement_types => 'INSERT, UPDATE, SELECT, DELETE');
END;
/
3. 使用FGA细粒度审计
ZHANGYUN@hfdr>select * from zy;
ID NAME ADDR
---------- -------------------- --------------------
1 zhangyu6 hefei
2 zhangyu4 hefei
3 zhangyu4444 hefei
4 zhangyu3 hefei
5 zhangyu2 hefei
6 zhangyu1 hefei
BEGIN
DBMS_FGA.ADD_POLICY(
object_schema => 'ZHANGYUN',
object_name => 'ZY',
policy_name => 'chk_zhangyun_zy',
audit_column => 'name',
enable => TRUE,
statement_types => 'INSERT, UPDATE, SELECT, DELETE');
END;
/
ZHANGYUN@hfdr>select policy_name from dba_audit_policies;
POLICY_NAME
------------------------------
CHK_ZHANGYUN_ZY
4.查看审计是否有效
ZHANGYUN@hfdr>select sql_text from dba_fga_audit_trail;
ZHANGYUN@hfdr>select * from zy where name='zhangyu1';
ID NAME ADDR
---------- -------------------- --------------------
6 zhangyu1 hefei
ZHANGYUN@hfdr>select * from zy;
ID NAME ADDR
---------- -------------------- --------------------
1 zhangyu6 hefei
2 zhangyu4 hefei
3 zhangyu4444 hefei
4 zhangyu3 hefei
5 zhangyu2 hefei
6 zhangyu1 hefei
ZHANGYUN@hfdr>select id from zy;
ID
----------
1
2
3
4
5
6
ZHANGYUN@hfdr>select id from zy where name='zhangyu1';
ID
----------
6
ZHANGYUN@hfdr>select sql_text from dba_fga_audit_trail;
SQL_TEXT
--------------------------------------------------------------------------------
select * from zy where name='zhangyu1'
select * from zy
select id from zy where name='zhangyu1'
可以发现只要涉及到name列的查询语句都被记录了下来,配置成功。
5. 删除细粒度审计
DBMS_FGA.DROP_POLICY(
object_schema VARCHAR2,
object_name VARCHAR2,
policy_name IVARCHAR2);
BEGIN
DBMS_FGA.DROP_POLICY(
object_schema => 'zhangyun',
object_name => 'zy',
policy_name => 'chk_zhangyun_zy');
END;
/
select policy_name from dba_audit_policies;
ZHANGYUN@hfdr>select sql_text from dba_fga_audit_trail;
SQL_TEXT
--------------------------------------------------------------------------------
select * from zy where name='zhangyu1'
select * from zy
select id from zy where name='zhangyu1'
即使删除了创建的FGA审计策略,已经记录下的语句不会删除
6. 常用视图
SYS.FGA_LOG$:基表。如果audit_trail参数包含DB,审计记录会被记录在FGA_LOG$表中。
V$XML_AUDIT_TRAIL:如果audit_trail参数包含XML,审计记录会记录在AUDIT_FILE_DEST初始化参数指定的目的地下的XML文件中,Oracle会读取这些XML文件,生成V$XML_AUDIT_TRAIL动态性能视图,方便DBA查看审计详细信息。
DBA_AUDIT_POLICIES:详细记录了审计配置的策略信息。
DBA_FGA_AUDIT_TRAIL:查看到审计的SQL语句和绑定变量。
DBA_COMMON_AUDIT_TRAIL:包含V$XML_AUDIT_TRAIL动态性能视图的内容,是标准和细粒度审计记录。