• 【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
  • 相关阅读:
    Python基础(三)
    Python基础(二)
    Python基础(一)
    Introduction of python
    加油,加油
    打不开Call Hierarchy和History的解决方法
    jdbc:oracle:thin:@localhost:1521:orcl和jdbc:oracle:thin:@localhost:1521/orcl的区别
    Java常见异常
    ArrayList和Array区别
    selenium利用Excel进行参数化(简单示例)
  • 原文地址:https://www.cnblogs.com/NextAction/p/7366709.html
Copyright © 2020-2023  润新知