DBMS_FGA簡介

redhouser發表於2013-01-25
Oracle FGA
介紹:
全稱是Fine-Grained Audit ,是Audit的一種特殊方式.
使用FGA只要呼叫Oracle的包DBMS_FGA.ADD_POLICY建立一些policy(審計策略)就行.
每個policy只能針對一個表或檢視,建好策略後所以對錶或檢視的DML操作(select,insert,update,delete都可以記錄到,
當然也可以新增一些篩選條件只監測某些特殊的操作.
 
不支援對sys使用者的審計。
 
fga審計策略可以透過如下檢視查詢:
dba_audit_policies
fga審計結果可以透過以下檢視查詢:
dba_fga_audit_trail,V$XML_AUDIT_TRAIL
可以透過如下語句直接刪除儲存在資料庫的fga審計記錄:
delete from sys.fga_log$;
 
測試:
1,設定fga
[oracle@bnet95 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 11 14:21:56 2013
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>
SQL> drop table scott.emp1;
Table dropped.
SQL>
SQL> create table scott.emp1
  2  as
  3  select * from scott.emp;
Table created.
SQL>
SQL> begin
  2    DBMS_FGA.ADD_POLICY(object_schema     => 'scott',
  3                        object_name       => 'emp1',
  4                        policy_name       => 'mypolicy1',
  5                        audit_condition   => 'sal=800',
  6                        audit_column      => 'comm,sal',
  7                        handler_schema    => NULL,
  8                        handler_module    => NULL,
  9                        enable            => TRUE,
 10                        statement_types   => 'SELECT,INSERT,UPDATE',
 11                        audit_trail       => DBMS_FGA.DB + DBMS_FGA.EXTENDED,
 12                        audit_column_opts => DBMS_FGA.ANY_COLUMNS);
 13  end;
 14  /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> select object_schema, object_name, policy_name from dba_audit_policies;
OBJECT_SCHEMA                  OBJECT_NAME
------------------------------ ------------------------------
POLICY_NAME
------------------------------
SCOTT                          EMP1
MYPOLICY1

SQL>
SQL> select * from dba_audit_policy_columns;
OBJECT_SCHEMA                  OBJECT_NAME
------------------------------ ------------------------------
POLICY_NAME                    POLICY_COLUMN
------------------------------ ------------------------------
SCOTT                          EMP1
MYPOLICY1                      SAL
SCOTT                          EMP1
MYPOLICY1                      COMM
2,預設不對sys使用者審計
[oracle@bnet95 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Jan 12 08:49:15 2013
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select * from scott.emp1;
     EMPNO ENAME      JOB              MGR HIREDATE                  SAL
---------- ---------- --------- ---------- ------------------ ----------
      COMM     DEPTNO
---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80                 800
                   20
...
14 rows selected.
SQL>
SQL> select sal,comm from scott.emp1 where sal<1300;
       SAL       COMM
---------- ----------
       800
      1250        500
      1250       1400
      1100
       950
SQL>
SQL> select sal,comm from scott.emp1 where mgr=7902;
       SAL       COMM
---------- ----------
       800
SQL>
SQL> select * from scott.emp1 where mgr=7902;
     EMPNO ENAME      JOB              MGR HIREDATE                  SAL
---------- ---------- --------- ---------- ------------------ ----------
      COMM     DEPTNO
---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80                 800
                   20

SQL>
SQL> select count(sal) from scott.emp1 where mgr=7902;
COUNT(SAL)
----------
         1
SQL>
SQL> select count(*) from scott.emp1 where comm is null;
  COUNT(*)
----------
        10
SQL>
SQL> select empno from scott.emp1 where mgr=7902;
     EMPNO
----------
      7369
SQL>
SQL> select count(*) from scott.emp1;
  COUNT(*)
----------
        14
SQL>
SQL> select sal,comm from scott.emp1 where sal<>800;
       SAL       COMM
---------- ----------
      1600        300
      1250        500
      2975
...
13 rows selected.
SQL>
SQL>
SQL> select count(*) from scott.emp1 where comm is not null;
  COUNT(*)
----------
         4
SQL> select to_char(timestamp,'YYYYMMDD HH24MISS') ts,sql_text from dba_fga_audit_trail;
no rows selected
SQL> show user
USER is "SYS"
SQL> show parameter audit_sys
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
==>沒有對SYS進行審計
3,對普通使用者審計
SQL> conn scott/tiger                                  
Connected.
SQL>
SQL> select * from scott.emp1;
     EMPNO ENAME      JOB              MGR HIREDATE                  SAL
---------- ---------- --------- ---------- ------------------ ----------
      COMM     DEPTNO
---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80                 800
                   20
...
14 rows selected.
SQL> select sal,comm from scott.emp1 where sal<1300;
       SAL       COMM
---------- ----------
       800
      1250        500
      1250       1400
      1100
       950
SQL> select sal,comm from scott.emp1 where mgr=7902;
       SAL       COMM
---------- ----------
       800
SQL> select * from scott.emp1 where mgr=7902;
     EMPNO ENAME      JOB              MGR HIREDATE                  SAL
---------- ---------- --------- ---------- ------------------ ----------
      COMM     DEPTNO
---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80                 800
                   20

SQL> select count(sal) from scott.emp1 where mgr=7902;
COUNT(SAL)
----------
         1
SQL> select count(*) from scott.emp1 where comm is null;
  COUNT(*)
----------
        10
SQL> select empno from scott.emp1 where mgr=7902;
     EMPNO
----------
      7369
==>沒有審計記錄,列不滿足審計條件
SQL> select count(*) from scott.emp1;
  COUNT(*)
----------
        14
SQL> select sal,comm from scott.emp1 where sal<>800;
       SAL       COMM
---------- ----------
      1600        300
      1250        500
      2975
      1250       1400
      2850
      2450
      3000
      5000
      1500          0
      1100
       950
      3000
      1300
13 rows selected.
==>沒有審計記錄,沒有記錄符合審計條件(sal=800)

SQL> select count(*) from scott.emp1 where comm is not null;
  COUNT(*)
----------
         4
==>沒有審計記錄,沒有記錄符合審計條件(sal=800),sal=800時comm為空
SQL> desc emp1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                              NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)
SQL>
SQL> col sql_text for a50
SQL> set pagesize 0
SQL> select to_char(timestamp,'YYYYMMDD HH24MISS') ts,sql_text from dba_fga_audit_trail order by 1;
20130112 084557 select * from scott.emp1
20130112 084605 select sal,comm from scott.emp1 where sal<1300
20130112 084613 select sal,comm from scott.emp1 where mgr=7902
20130112 084618 select * from scott.emp1 where mgr=7902
20130112 084622 select count(sal) from scott.emp1 where mgr=7902
20130112 084628 select count(*) from scott.emp1 where comm is null
20130112 084635 select count(*) from scott.emp1
7 rows selected.
4,是否能開啟對sys的fga審計?
[oracle@bnet95 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Jan 12 08:49:15 2013
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> show user
USER is "SYS"
SQL> show parameter audit_sys
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
SQL> alter system set audit_sys_operations=true scope=spfile;
System altered.
SQL> shutdown immediate;
SQL> conn / as sysdba
Connected.
SQL> startup
SQL> show parameter audit_sys
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_sys_operations                 boolean     TRUE
audit_syslog_level                   string
SQL> delete from sys.fga_log$;
7 rows deleted.
SQL> commit;
Commit complete.
SQL> show user
USER is "SYS"
SQL> select * from scott.emp1;
...
14 rows selected.
SQL> set pagesize 0
SQL> select * from scott.emp1;
      7369 SMITH      CLERK           7902 17-DEC-80                 800
                   20
....
14 rows selected.
SQL> select sal,comm from scott.emp1 where sal<1300;
       800
      1250        500
      1250       1400
      1100
       950
SQL> select sal,comm from scott.emp1 where mgr=7902;
       800
SQL> select * from scott.emp1 where mgr=7902;
      7369 SMITH      CLERK           7902 17-DEC-80                 800
                   20

SQL> select count(sal) from scott.emp1 where mgr=7902;
         1
SQL> select count(*) from scott.emp1 where comm is null;
        10
SQL> select empno from scott.emp1 where mgr=7902;
      7369
SQL> select count(*) from scott.emp1;
        14
SQL> select sal,comm from scott.emp1 where sal<>800;
...
13 rows selected.
SQL> select count(*) from scott.emp1 where comm is not null;
         4
SQL> select to_char(timestamp,'YYYYMMDD HH24MISS') ts,sql_text from dba_fga_audit_trail order by 1;
no rows selected
==>設定audit_sys_operations=true無法開啟對sys的fga審計
5,使用or連線的審計條件
SQL> begin
  2     DBMS_FGA.DROP_POLICY(object_schema     => 'scott',
  3                         object_name       => 'emp1',
  4                         policy_name       => 'mypolicy1');
  5  end;
  6  /
PL/SQL procedure successfully completed.
SQL> begin
  2     DBMS_FGA.ADD_POLICY(object_schema     => 'scott',
  3                         object_name       => 'emp1',
  4                         policy_name       => 'mypolicy1',
  5                         audit_condition   => 'sal=800 or deptno=20',
  6                         audit_column      => 'comm,sal',
  7                        handler_schema    => NULL,
  8                         handler_module    => NULL,
  9                          enable            => TRUE,
 10                         statement_types   => 'SELECT,INSERT,UPDATE',
 11                         audit_trail       => DBMS_FGA.DB + DBMS_FGA.EXTENDED,
 12                         audit_column_opts => DBMS_FGA.ANY_COLUMNS);
 13  end;
 14  /
PL/SQL procedure successfully completed.
SQL> conn scott/tiger
Connected.
SQL> select * from scott.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
                   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.
SQL> select sal,comm from scott.emp1 where sal<1300;
       SAL       COMM
---------- ----------
       800
      1250        500
      1250       1400
      1100
       950
SQL> select sal,comm from scott.emp1 where deptno=20;
       SAL       COMM
---------- ----------
       800
      2975
      3000
      1100
      3000
SQL> select sal,comm from scott.emp1 where deptno<>20;
       SAL       COMM
---------- ----------
      1600        300
      1250        500
      1250       1400
      2850
      2450
      5000
      1500          0
       950
      1300
9 rows selected.
==>沒有審計記錄,沒有記錄符合審計條件(sal=800 or deptno=20)
SQL> select sal,comm from scott.emp1 where mgr=7902;
       SAL       COMM
---------- ----------
       800
SQL> select * from scott.emp1 where mgr=7902;
     EMPNO ENAME      JOB              MGR HIREDATE                  SAL
---------- ---------- --------- ---------- ------------------ ----------
      COMM     DEPTNO
---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80                 800
                   20

SQL> select count(sal) from scott.emp1 where mgr=7902;
COUNT(SAL)
----------
         1
SQL> select count(*) from scott.emp1 where comm is null;
  COUNT(*)
----------
        10
SQL> select empno from scott.emp1 where mgr=7902;
     EMPNO
----------
      7369
==>沒有審計記錄,沒有記錄符合審計條件(sal=800 or deptno=20)
SQL> select count(*) from scott.emp1;
  COUNT(*)
----------
        14
SQL> select sal,comm from scott.emp1 where sal<>800;
       SAL       COMM
---------- ----------
      1600        300
      1250        500
      2975
      1250       1400
      2850
      2450
      3000
      5000
      1500          0
      1100
       950
      3000
      1300
13 rows selected.
SQL> select count(*) from scott.emp1 where comm is not null;
  COUNT(*)
----------
         4
==>沒有審計記錄,沒有記錄符合審計條件(sal=800 or deptno=20)
SQL> select sal,comm from scott.emp1 where sal=800 and deptno=20;
       SAL       COMM
---------- ----------
       800
SQL> select sal,comm from scott.emp1 where sal=800 or deptno=20;
       SAL       COMM
---------- ----------
       800
      2975
      3000
      1100
      3000
SQL> select sal,comm from scott.emp1 where sal<>800 or deptno<>20;
       SAL       COMM
---------- ----------
      1600        300
      1250        500
      2975
      1250       1400
      2850
      2450
      3000
      5000
      1500          0
      1100
       950
       SAL       COMM
---------- ----------
      3000
      1300
13 rows selected.
SQL> select sal,comm from scott.emp1 where sal<>800 and deptno<>20;
       SAL       COMM
---------- ----------
      1600        300
      1250        500
      1250       1400
      2850
      2450
      5000
      1500          0
       950
      1300
9 rows selected.
==>沒有審計記錄,沒有記錄符合審計條件(sal=800 or deptno=20)
SQL> set pagesize 0
SQL> col sql_text for a50
SQL> select to_char(timestamp,'YYYYMMDD HH24MISS') ts,sql_text from dba_fga_audit_trail order by 1
20130112 094316 select * from scott.emp1
20130112 094321 select sal,comm from scott.emp1 where sal<1300
20130112 094325 select sal,comm from scott.emp1 where deptno=20
20130112 094332 select sal,comm from scott.emp1 where mgr=7902
20130112 094335 select * from scott.emp1 where mgr=7902
20130112 094339 select count(sal) from scott.emp1 where mgr=7902
20130112 094343 select count(*) from scott.emp1 where comm is null
20130112 094351 select count(*) from scott.emp1
20130112 094355 select sal,comm from scott.emp1 where sal<>800
20130112 094518 select sal,comm from scott.emp1 where sal=800 and deptno=20
20130112 094522 select sal,comm from scott.emp1 where sal=800 or deptno=20
20130112 094526 select sal,comm from scott.emp1 where sal<>800 or deptno<>20
12 rows selected.

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/18922393/viewspace-753303/,如需轉載,請註明出處,否則將追究法律責任。