【Oracle】审计

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

                                                 p

audit_sys_operations                 boolean     FALSE

audit_syslog_level                   string

audit_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 bytes

Fixed Size                  2257840 bytes

Variable Size             536874064 bytes

Database Buffers          289406976 bytes

Redo Buffers                6565888 bytes

Database mounted.

Database opened.

SYS@LGR> show parameter audit;

NAME                                 TYPE        VALUE

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

audit_file_dest                      string      /u01/app/oracle/admin/lgr/adum

                                                 p

audit_sys_operations                 boolean     FALSE

audit_syslog_level                   string

audit_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                    10

14 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                    20

SCOTT@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:28

09:40:03 SQL> r

  1* select TRIGGER_NAME,TRIGGER_TYPE,TABLE_NAME,COLUMN_NAME,status,TRIGGER_BODY from user_triggers

TRIGGER_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

巴特西

【Oracle】审计

最新文章

热门文章