Oracle审计实验(二)
OTT oracle.somnus.com
2011-09-22 19:33:32 select * from emp
SCOTT oracle.somnus.com
2011-09-22 19:33:48 delete from emp where ename='M
ILLER'
SCOTT oracle.somnus.com
2011-09-22 19:40:49 select * from emp
GZ oracle.somnus.com
2011-09-22 19:55:15
可以发现，虽然查询没有成功，但还是被审计了下来。
SQL>audit all on scott.emp whenever successful;
SQL> select * from scott.emp;
select * from scott.emp
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> grant select on scott.emp to gz;
Grant succeeded.
SQL> select USERNAME,USERHOST,TIMESTAMP,SQL_TEXT FROM dba_audit_trail;
GZ oracle.somnus.com
2011-09-22 19:57:38 select * from scott.emp
GZ oracle.somnus.com
2011-09-22 19:58:39 select * from scott.emp
可以发现只记录了查询成功的记录！！！！
三、更细微的审计
SQL> desc dbms_fga 这是增加策略的参数视图
PROCEDURE ADD_POLICY
我们增加一个策略
SQL> exec dbms_fga.add_policy(OBJECT_SCHEMA=>'scott',OBJECT_NAME=>'emp',
POLICY_
NAME=>'demo_emp');
POLICY_
NAME=>'demo_emp');
PL/SQL procedure successfully completed.
这语句的作用是增加一个方案对象为scott，方案名称为emp，策略名为demo_emp的策
略，
就是审计scott用户下的emp表的demo_emp策略
就是审计scott用户下的emp表的demo_emp策略
SQL> desc dba_fga_audit_trail;这是审计内容的视图
SQL> select timestamp,db_user,userhost,sql_text from dba_fga_audit_trail;
no rows selected
现在没有查询记录
我们在另一个会话对emp表做查询
SQL> select * from scott.emp;
再次查询审计记录
SQL> select timestamp,db_user,userhost,sql_text from dba_fga_audit_trail;
TIMESTAMP DB_USER
------------------------------ ------------------------------
USERHOST SQL_TEXT
------------------------------ ------------------------------
2011-09-22 20:16:09 GZ
oracle.somnus.com select * from scott.emp
发现已经审计下来了
我们发现现在跟一般的审计是一样的，下面我们来做更细微的审计：
SQL> exec dbms_fga.add_policy(OBJECT_SCHEMA=>'scott',OBJECT_NAME=>'emp',
POLICY_NAME
=>'demo2_emp',AUDIT_COLUMN=>'sal,comm',STATEMENT_TYPES=>'select,insert,
update,delete');
POLICY_NAME
=>'demo2_emp',AUDIT_COLUMN=>'sal,comm',STATEMENT_TYPES=>'select,insert,
PL/SQL procedure successfully completed.
这是在上一条的基础上加入了这样一条规则，即只审计sal，comm这两个列的select,insert,
update,delete操作
update,delete操作
因为我们已经增加了demo_emp这样一条策略，这跟我们新建的有冲突，我们先禁止第
一条
SQL> exec
dbms_fga.disable_policy(OBJECT_SCHEMA=>'scott',OBJECT_NAME=
>'emp',POLICY_ NAME=>'demo_emp')这是禁止策略
>'emp',POLICY_ NAME=>'demo_emp')这是禁止策略
SQL> exec
dbms_fga.able_policy(OBJECT_SCHEMA=>'scott',OBJECT_NAME=
>'emp',POLICY_ NAME=>'demo_emp')这是开启策略
>'emp',POLICY_ NAME=>'demo_emp')这是开启策略
SQL> exec
dbms_fga.drop_policy(OBJECT_SCHEMA=>'scott',OBJECT_NAME=
>'emp',POLICY_ NAME=>'demo_emp')这是删除策略
>'emp',POLICY_ NAME=>'demo_emp')这是删除策略
我们在另一个会话上操作： www.2cto.com
SQL> select deptno from scott.emp;
DEPTNO
----------
20
30
SQL> select comm from scott.emp;
COMM
----------
300
500
1400
我们再看审计记录：
SQL> select timestamp,db_user,userhost,sql_text from dba_fga_audit_trail;
TIMESTAMP DB_USER
------------------------------ ------------------------------
USERHOST SQL_TEXT
2011-09-22 20:40:04 GZ
oracle.somnus.com select comm from scott.emp
我们发现只记录了查询comm列的记录，至些，发现细微审计的功能已经发挥出来了！
大功告成！！