oracle学习之：审计

来源：互联网发布：北京鼎泰网络推广公司编辑：程序博客网时间：2024/05/20 14:41

1、审计的功能：监控用户在database 的 action （操作）
2、审计分类：
1） session ：在同一个session，相同的语句只产生一个审计结果（默认）
2） access ：在同一个session，每一个语句产生一个审计结果
3、启用审计（默认不启用）

SYS@LGR> show parameter audit;NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------audit_file_dest                      string      /u01/app/oracle/admin/lgr/adum                                                 paudit_sys_operations                 boolean     FALSEaudit_syslog_level                   stringaudit_trail                          string      NONE

SYS@LGR> alter system set audit_trail=db scope=spfile;System altered.

SYS@LGR> startup force;ORACLE instance started.Total System Global Area  835104768 bytesFixed Size                  2257840 bytesVariable Size             536874064 bytesDatabase Buffers          289406976 bytesRedo Buffers                6565888 bytesDatabase mounted.Database opened.

SYS@LGR> show parameter audit;NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------audit_file_dest                      string      /u01/app/oracle/admin/lgr/adum                                                 paudit_sys_operations                 boolean     FALSEaudit_syslog_level                   stringaudit_trail                          string      DB

——-audit_trail
1）none 不启用audit
2）db 将审计结果放在数据字典里（database），只有dba 可以访问
3）os 将审计结果存放到操作系统的文件里（由audit_file_dest指定的位置）

—-启用audit ，默认不审计sys用户的action

     audit_sys_operations=true ，启用对于sys 用户的审计

4、审计的对象：（默认情况：session ，对所有用户（除了sys），对成功和不成功的同时审计)

1）语句审计

SYS@LGR> audit table;Audit succeeded.SYS@LGR> audit table by LGR whenever successful;Audit succeeded----------查看审计设置SYS@LGR> select user_name,audit_option from dba_stmt_audit_opts;                                                                   USER_NAME                      AUDIT_OPTION------------------------------ ---------------------------                               TABLE

2）权限审计

SYS@LGR> audit create table;Audit succeeded.

3）对象审计

SYS@LGR> audit all on scott.emp;Audit succeeded.

4）精细审计Fine Grained Auditing (FGA)

—–建立审计策略

SYS@LGR> exec dbms_fga.add_policy(object_schema=>'scott',-                                                                          > object_name=>'emp',policy_name=>'chk_emp',-                                                                                 > audit_condition =>'deptno=20',audit_column =>'sal',-                                                                         > statement_types =>'update,select');PL/SQL procedure successfully completed.SCOTT@LGR> 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                    20 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                    1014 rows selected.SCOTT@LGR> 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 7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20 7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20 7902 FORD       ANALYST         7566 03-DEC-81       3000                    20SCOTT@LGR> update emp set deptno=10 where empno=7788;1 row updated.SCOTT@LGR> update emp set sal=8000 where empno=7788;1 row updated.SCOTT@LGR> update emp set sal=8000 where deptno=20;4 rows updated.SCOTT@LGR> commit;Commit complete.--禁止精细审计SYS@LGR> exec dbms_fga.disable_policy(-> object_schema=>'scott',object_name=>'emp',-> policy_name=>'chk_emp'); PL/SQL procedure successfully completed.SYS@LGR> --激活精细审计SYS@LGR> exec dbms_fga.enable_policy(-object_schema=>'scott',object_name=>'emp',-> > policy_name=>'chk_emp');PL/SQL procedure successfully completed.SYS@LGR> --删除FGA策略PL/SQL procedure successfully completed.SYS@LGR> exec dbms_fga.drop_policy(-> object_schema=>'scott',object_name=>'emp',-> policy_name=>'chk_emp');PL/SQL procedure successfully completed.SYS@LGR> --删除精细审计的结果SYS@LGR> delete from sys.fga_log$;

5）应用审计（通过触发器来实现）
用于记载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:2809:40:03 SQL> r  1* select TRIGGER_NAME,TRIGGER_TYPE,TABLE_NAME,COLUMN_NAME,status,TRIGGER_BODY from user_triggersTRIGGER_NAME         TRIGGER_TYPE     TABLE_NAME COLUMN_NAM STATUS   TRIGGER_BODY-------------------- ---------------- ---------- ---------- -------- ----------------------------------------TR_SAL_CHANGE        AFTER EACH ROW   EMP                   ENABLED  declare                                                                            v_temp int;                                                                         begin                                                                            select count(*)  into v_temp from

0 0