【审计】细粒度审计

最新推荐文章于 2024-01-21 10:04:47 发布

congjiu2607

最新推荐文章于 2024-01-21 10:04:47 发布

阅读量277

点赞数

文章标签：数据库

从Oracle9i开始，通过引入细粒度的对象审计，或称为FGA，审计变得更为关注某个方面，并且更为精确，
由称为DBMS_FGA的PL/SQL程序包实现FGA。

使用标准的审计，可以轻松发现访问了哪些对象以及由谁访问，但无法知道访问了哪些行或列。细粒度的审计可解决这个问题，它不仅为需要访问的行指定谓词(或where子句)，还指定了表中访问的列。通过只在访问某些行和列时审计对表的访问，可以极大地减少审计表条目的数量。

程序包DBMS_FGA具有4个过程：

ADD_POLICY

添加使用谓词和审计列的审计策略

DROP_POLICY

删除审计策略

DISABLE_POLICY

禁用审计策略，但保留与表或视图关联的策略

ENABLE_POLICY

启用策略

详细参阅http://blog.chinaunix.net/u2/66903/showart_2082884.html

细粒度审计（审计线索记录在表 fga_log$ 中）
1) 查看细粒度审计相关包

SYS@ORA11GR2>desc dbms_fga

PROCEDURE ADD_POLICY

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

OBJECT_SCHEMA VARCHAR2 IN DEFAULT

OBJECT_NAME VARCHAR2 IN

POLICY_NAME VARCHAR2 IN

AUDIT_CONDITION VARCHAR2 IN DEFAULT

AUDIT_COLUMN VARCHAR2 IN DEFAULT

HANDLER_SCHEMA VARCHAR2 IN DEFAULT

HANDLER_MODULE VARCHAR2 IN DEFAULT

ENABLE BOOLEAN IN DEFAULT

STATEMENT_TYPES VARCHAR2 IN DEFAULT

AUDIT_TRAIL BINARY_INTEGER IN DEFAULT

AUDIT_COLUMN_OPTS BINARY_INTEGER IN DEFAULT

POLICY_OWNER VARCHAR2 IN DEFAULT

PROCEDURE DISABLE_POLICY

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

OBJECT_SCHEMA VARCHAR2 IN DEFAULT

OBJECT_NAME VARCHAR2 IN

POLICY_NAME VARCHAR2 IN

PROCEDURE DROP_POLICY

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

OBJECT_SCHEMA VARCHAR2 IN DEFAULT

OBJECT_NAME VARCHAR2 IN

POLICY_NAME VARCHAR2 IN

PROCEDURE ENABLE_POLICY

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

OBJECT_SCHEMA VARCHAR2 IN DEFAULT

OBJECT_NAME VARCHAR2 IN

POLICY_NAME VARCHAR2 IN

ENABLE BOOLEAN IN DEFAULT

2) 创建细粒度审计策略

SYS@ORA11GR2>begin

2 dbms_fga.add_policy(

3 object_schema => 'scott'

4 ,object_name => 'emp'

5 ,policy_name => 'fga_emp'

6 ,audit_condition => 'sal > 100'

7 ,audit_column => 'ename'

8 ,statement_types => 'select');

9 end;

10 /

PL/SQL procedure successfully completed.

SYS@ORA11GR2>select obj$schema,obj$name,osuid,oshst,lsqltext from fga_log$;

no rows selected

3) 执行 sql 语句，测试 FGA

SYS@ORA11GR2>conn scott/tiger

Connected.

SCOTT@ORA11GR2>select * from scott.emp where rownum=1;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- ------------------- ---------------------------------------------

7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20

SCOTT@ORA11GR2>select ename from emp;

ENAME

----------

SMITH

ALLEN

WARD

JONES

MARTIN

BLAKE

CLARK

SCOTT

KING

TURNER

ADAMS

ENAME

----------

JAMES

FORD

MILLER

14 rows selected.

SCOTT@ORA11GR2>select count(*) from emp;

COUNT(*)

----------

SCOTT@ORA11GR2>select ename from emp where sal>1000;

ENAME

----------

ALLEN

WARD

JONES

MARTIN

BLAKE

CLARK

SCOTT

KING

TURNER

ADAMS

FORD

ENAME

----------

MILLER

12 rows selected.

SCOTT@ORA11GR2>select distinct job from emp;

JOB

---------

CLERK

SALESMAN

PRESIDENT

MANAGER

ANALYST

4)查看审计结果：

SYS@ORA11GR2>col obj$schema for a10

SYS@ORA11GR2>col obj$name for a10

SYS@ORA11GR2>col osuid for a6

SYS@ORA11GR2>col oshst for a6

SYS@ORA11GR2>col lsqltext for a40

SYS@ORA11GR2>select obj$schema,obj$name,osuid,oshst,lsqltext from fga_log$;

OBJ$SCHEMA OBJ$NAME OSUID OSHST LSQLTEXT

---------- ---------- ------ ------ ----------------------------------------

SCOTT EMP oracle wang select * from scott.emp where rownum=1

SCOTT EMP oracle wang select ename from emp

SCOTT EMP oracle wang select count(*) from emp

SCOTT EMP oracle wang select ename from emp where sal>1000

（audit_condition => 'sal > 100' 和audit_column => 'ename'， 细粒度审计首要满足的是 sal > 100，然后在此基础上满足其他过审计滤条件；总之audit_condition和audit_column必须同时满足才执行审计）

5） 查看 FGA 策略

SYS@ORA11GR2>col policy_name for a12;

SYS@ORA11GR2>col policy_text for a12

SYS@ORA11GR2>col enabled for a7

SYS@ORA11GR2>select policy_name,policy_text,enabled from dba_audit_policies;

POLICY_NAME POLICY_TEXT ENABLED

------------ ------------ -------

FGA_EMP sal > 100 YES

SYS@ORA11GR2>

6） 修改 FGA使其失效

SYS@ORA11GR2>exec dbms_fga.disable_policy(object_schema =>'scott',object_name => 'emp',policy_name => 'fga_emp');

PL/SQL procedure successfully completed.

——验证：

SYS@ORA11GR2>select policy_name,policy_text,enabled from dba_audit_policies;

POLICY_NAME POLICY_TEXT ENABLED

------------ ------------ -------

FGA_EMP sal > 100 NO

1) 修改 FGA 使其生效

SYS@ORA11GR2>exec dbms_fga.enable_policy(object_schema =>'scott',object_name => 'emp',policy_name => 'fga_emp',enable => true);

PL/SQL procedure successfully completed.

SYS@ORA11GR2>

2) 删除 FGA 策略

SYS@ORA11GR2>exec dbms_fga.drop_policy(object_schema =>'scott',object_name => 'emp',policy_name => 'fga_emp');

PL/SQL procedure successfully completed.

注：下列是与审计相关的数据字典视图

下表包含了与审计相关的数据字典视图。

数据字典视图	说明
AUDIT_ACTIONS	包含审计跟踪动作类型代码的描述，例如INSERT、DROP VIEW、DELETE、LOGON和LOCK
DBA_AUDIT_OBJECT	与数据库中对象相关的审计跟踪记录
DBA_AUDIT_POLICIES	数据库中的细粒度审计策略
DBA_AUDIT_SESSION	与CONNECT和DISCONNECT相关的所有审计跟踪记录
DBA_AUDIT_STATEMENT	与GRANT、REVOKE、AUDIT、NOAUDIT和ALTER SYSTEM命令相关的审计跟踪条目
DBA_AUDIT_TRAIL	包含标准审计跟踪条目。 USER_AUDIT_TRAILUSER_TRAIL_AUDIT 只包含已连接用户的审计行
DBA_FGA_AUDIT_TRAIL	细粒度审计策略的审计跟踪条目

数据字典视图	说明
DBA_COMMON_AUDIT_TRAIL	将标准的审计行和细粒度的审计行结合在一个视图中
DBA_OBJ_AUDIT_OPTS	对数据库对象生效的审计选项
DBA_PRIV_AUDIT_OPTS	对系统权限生效的审计选项
DBA_STMT_AUDIT_OPTS	对语句生效的审计选项