第十九章： 审计 （Audit）
1、审计的功能：监控用户在database 的 action （操作）
2、审计分类：
1） session ：在同一个session，相同的语句只产生一个审计结果（默认）
2） access ： 在同一个session，每一个语句产生一个审计结果
3、启用审计（默认不启用）
09:54:18 SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/prod/adu
mp
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string NONE （不启用）
09:54:56 SQL> alter system set audit_trail=db scope=spfile;
System altered.
09:55:02 SQL> startup force;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 83888372 bytes
Database Buffers 79691776 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
09:55:23 SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/prod/adump
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string DB
09:55:29 SQL>
-------audit_trail
1）none 不启用audit
2）db 将审计结果放在数据字典里（database），只有dba 可以访问
3）os 将审计结果存放到操作系统的文件里（由audit_file_dest指定的位置）
----启用audit ，默认不审计sys用户的action
audit_sys_operations=true ，启用对于sys 用户的审计
4、审计的对象：（默认情况：session ，对成功和不成功的同时审计)
1）语句审计
10:02:39 SQL> audit table;
Audit succeeded.
10:02:43 SQL> audit table by tom ;
Audit succeeded.
10:02:52 SQL> audit table by tom whenever successful;
Audit succeeded.
----------查看审计设置
11:08:29 SQL> select user_name,audit_option from dba_stmt_audit_opts;
USER_NAME AUDIT_OPTION
------------------------------ ----------------------------------------
TABLE
11:08:54 SQL> conn scott/tiger
Connected.
11:09:02 SQL> drop table dept1 purge;
drop table dept1 purge
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys
11:09:12 SQL> drop table dept1 cascade purge;
drop table dept1 cascade purge
*
ERROR at line 1:
ORA-00905: missing keyword
11:09:28 SQL> drop table dept1 cascade;
drop table dept1 cascade
*
ERROR at line 1:
ORA-00905: missing keyword
11:09:31 SQL> drop table dept1 cascade constraint purge;
Table dropped.
11:09:38 SQL> drop table emp1 purge;
Table dropped.
11:09:46 SQL> create table emp1 as select * from emp;
Table created.
11:11:50 SQL> conn tom/tom
Connected.
11:12:52 SQL> create table t01 (id int);
Table created.
11:13:07 SQL> drop table t01 purge;
Table dropped.
11:13:11 SQL> conn /as sysdba
Connected.
11:13:29 SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
11:14:31 SQL> col username for a10
11:14:35 SQL> col obj_name for a10
11:14:42 SQL>
1* select USERNAME,TIMESTAMP,OBJ_NAME,ACTION_NAME from dba_audit_trail
USERNAME TIMESTAMP OBJ_NAME ACTION_NAME
---------- ------------------- ---------- ----------------------------
SCOTT 2011-08-11 11:09:12 DEPT1 DROP TABLE
SCOTT 2011-08-11 11:09:26 DEPT1 DROP TABLE
SCOTT 2011-08-11 11:09:31 DEPT1 DROP TABLE
SCOTT 2011-08-11 11:09:39 DEPT1 DROP TABLE
SCOTT 2011-08-11 11:09:47 EMP1 DROP TABLE
SCOTT 2011-08-11 11:09:59 EMP1 CREATE TABLE
TOM 2011-08-11 11:13:07 T01 CREATE TAB
---------审计结果存放到aud$的基表里，通过dba_audit_trail 视图查看
11:14:42 SQL> select count(*) from aud$;
COUNT(*)
----------
8
----------删除审计结果
11:17:24 SQL> delete from aud$;
8 rows deleted.
--------关闭审计
11:17:35 SQL> noaudit table
11:18:11 2 ;
Noaudit succeeded.
2）权限审计
11:18:12 SQL> audit create table;
Audit succeeded.
11:19:42 SQL> conn scott/tiger
Connected.
11:20:02 SQL> create table dept1 as select * from dept;
Table created.
11:20:10 SQL> drop table dept1 purge;
Table dropped.
11:20:17 SQL> conn /as sysdba
Connected.
11:20:20 SQL>
11:20:20 SQL> select USERNAME,TIMESTAMP,OBJ_NAME,ACTION_NAME from dba_audit_trail;
USERNAME TIMESTAMP OBJ_NAME ACTION_NAME
---------- --------- ---------- ----------------------------
SCOTT 11-AUG-11 DEPT1 CREATE TABLE
11:20:26 SQL>
3）对象审计
11:21:13 SQL> audit all on scott.emp1;
Audit succeeded.
11:21:25 SQL> conn scott/tiger
Connected.
11:22:19 SQL>
11:22:19 SQL> select * from emp1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 100 40
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
11:22:25 SQL> update emp1 set sal=9000 where empno=7788;
1 row updated.
11:22:40 SQL> delete from emp1 where rownum<2;
1 row deleted.
11:22:49 SQL> commit;
Commit complete.
11:22:52 SQL> conn /as sysdba
Connected.
11:22:55 SQL>
11:22:55 SQL> select username,ses_actions,obj_name,to_char(timestamp,'yyyy-mm-dd HH24:MI:SS')
11:23:35 2 FROM dba_audit_trail;
USERNAME SES_ACTIONS OBJ_NAME TO_CHAR(TIMESTAMP,'
---------- ------------------- ---------- -------------------
SCOTT ---S-----SS----- EMP1 2011-08-11 11:22:25
其中S表示successful ，表示在这个位置操作是成功的，F表示failure 失败，B表示both，两者都有。
5、精细审计Fine Grained Auditing (FGA)
-----建立审计策略
11:30:44 SQL> exec dbms_fga.add_policy(object_schema=>'scott',-
11:30:51 > object_name=>'emp',policy_name=>'chk_emp',-
11:31:05 > audit_condition =>'deptno=20',audit_column =>'sal',-
11:31:18 > statement_types =>'update,select');
PL/SQL procedure successfully completed.
11:31:28 SQL> conn scott/tiger
Connected.
11:31:35 SQL>
11:31:35 SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 100 40
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
11:31:41 SQL> select * from emp where deptno=20;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
11:31:48 SQL> update emp set deptno=10 where empno=7788;
1 row updated.
11:32:05 SQL> update emp set sal=8000 where empno=7788;
1 row updated.
11:32:12 SQL> update emp set sal=8000 where deptno=20;
4 rows updated.
11:32:21 SQL> commit;
Commit complete.
---------验证审计结果
11:32:24 SQL> conn /as sysdba
Connected.
11:32:27 SQL>
11:33:52 SQL> select db_user,to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') "time" ,sql_text from dba_fga_audit_trail;
DB_USER time SQL_TEXT
---------- ------------------- --------------------------------------------------
SCOTT 2011-08-11 11:31:42 select * from emp
SCOTT 2011-08-11 11:31:49 select * from emp where deptno=20
SCOTT 2011-08-11 11:32:12 update emp set sal=8000 where empno=7788
SCOTT 2011-08-11 11:32:21 update emp set sal=8000 where deptno=20
------精细审计结果存放到fga_log$的基表里，通过dba_fga_audit_trail 查看。
11:34:36 SQL> select count(*) from fga_log$;
COUNT(*)
----------
4
11:36:20 SQL> delete from fga_log$;
4 rows deleted.
11:36:26 SQL> select db_user,to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') "time" ,sql_text from dba_fga_audit_trail;
no rows selected
11:36:30 SQL>
禁止精细审计
04:08:08 SQL> exec dbms_fga.disable_policy(-
04:08:21 > object_schema=>'scott',object_name=>'emp',-
04:08:49 > policy_name=>'chk_emp');
PL/SQL procedure successfully completed.
激活精细审计
04:10:33 SQL> exec dbms_fga.enable_policy(-
04:10:40 > object_schema=>'scott',object_name=>'emp',-
04:10:51 > policy_name=>'chk_emp');
PL/SQL procedure successfully completed.
删除FGA策略
04:11:52 SQL> exec dbms_fga.drop_policy(-
04:11:54 > object_schema=>'scott',object_name=>'emp',-
04:11:59 > policy_name=>'chk_emp');
PL/SQL procedure successfully completed.
删除精细审计的结果
04:12:43 SQL> delete from sys.fga_log$;
7、应用审计（通过触发器来实现）
用于记载DML操作所引起的数据变化
1）建立审计表
11:37:32 SQL> conn scott/tiger
Connected.
create table audit_emp_change (
04:20:47 2 name varchar2(10),oldsal number(6,2),
04:21:12 3 newsal number(6,2) ,time date);
Table created.
2）建立DML 触发器
04:26:47 SQL> l
1 create or replace trigger tr_sal_change
2 after update of sal on scott.emp
3 for each row
4 declare
5 v_temp int;
6 begin
7 select count(*) into v_temp from audit_emp_change
8 where name=:old.ename;
9 if v_temp=0 then
10 insert into audit_emp_change
11 values(:old.ename,:old.sal,:new.sal,sysdate);
12 else
13 update audit_emp_change
14 set oldsal=:old.sal ,newsal=:new.sal ,time=sysdate
15 where name=:old.ename;
16 end if;
17* end;
/
3）执行DML操作
04:28:02 SQL> update scott.emp set sal=6000 where empno=7788;
1 row updated.
4）查看审计结果
04:28:35 SQL> select name,oldsal,newsal,
04:28:46 2 to_char(time,'YYYY-MM-DD HH24:MI') FROM AUDIT_EMP_CHANGE;
NAME OLDSAL NEWSAL TO_CHAR(TIME,'YY
---------- ---------- ---------- ----------------
SCOTT 2000 6000 2011-03-03 04:28
