Oracle FGA
介绍:
全称是Fine-Grained Audit ,是Audit的一种特殊方式.
使用FGA只要调用Oracle的包DBMS_FGA.ADD_POLICY创建一些policy(审计策略)就行.
每个policy只能针对一个表或视图,建好策略后所以对表或视图的DML操作(select,insert,update,delete都可以记录到,
当然也可以添加一些筛选条件只监测某些特殊的操作.
全称是Fine-Grained Audit ,是Audit的一种特殊方式.
使用FGA只要调用Oracle的包DBMS_FGA.ADD_POLICY创建一些policy(审计策略)就行.
每个policy只能针对一个表或视图,建好策略后所以对表或视图的DML操作(select,insert,update,delete都可以记录到,
当然也可以添加一些筛选条件只监测某些特殊的操作.
不支持对sys用户的审计。
fga审计策略可以通过如下视图查询:
dba_audit_policies
fga审计策略可以通过如下视图查询:
dba_audit_policies
fga审计结果可以通过以下视图查询:
dba_fga_audit_trail,V$XML_AUDIT_TRAIL
dba_fga_audit_trail,V$XML_AUDIT_TRAIL
可以通过如下语句直接删除保存在数据库的fga审计记录:
delete from sys.fga_log$;
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;
SQL> drop table scott.emp1;
Table dropped.
SQL>
SQL> create table scott.emp1
2 as
3 select * from scott.emp;
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 /
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;
SQL>
SQL> select object_schema, object_name, policy_name from dba_audit_policies;
OBJECT_SCHEMA OBJECT_NAME
------------------------------ ------------------------------
POLICY_NAME
------------------------------
SCOTT EMP1
MYPOLICY1
------------------------------ ------------------------------
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
------------------------------ ------------------------------
POLICY_NAME POLICY_COLUMN
------------------------------ ------------------------------
SCOTT EMP1
MYPOLICY1 SAL
SCOTT EMP1
MYPOLICY1 COMM
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
---------- ---------- --------- ---------- ------------------ ----------
COMM DEPTNO
---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800
20
...
14 rows selected.
14 rows selected.
SQL>
SQL> select sal,comm from scott.emp1 where sal<1300;
SQL> select sal,comm from scott.emp1 where sal<1300;
SAL COMM
---------- ----------
800
1250 500
1250 1400
1100
950
---------- ----------
800
1250 500
1250 1400
1100
950
SQL>
SQL> select sal,comm from scott.emp1 where mgr=7902;
SQL> select sal,comm from scott.emp1 where mgr=7902;
SAL COMM
---------- ----------
800
---------- ----------
800
SQL>
SQL> select * from scott.emp1 where mgr=7902;
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
---------- ---------- --------- ---------- ------------------ ----------
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
----------
1
SQL>
SQL> select count(*) from scott.emp1 where comm is null;
SQL> select count(*) from scott.emp1 where comm is null;
COUNT(*)
----------
10
----------
10
SQL>
SQL> select empno from scott.emp1 where mgr=7902;
SQL> select empno from scott.emp1 where mgr=7902;
EMPNO
----------
7369
----------
7369
SQL>
SQL> select count(*) from scott.emp1;
SQL> select count(*) from scott.emp1;
COUNT(*)
----------
14
----------
14
SQL>
SQL> select sal,comm from scott.emp1 where sal<>800;
SQL> select sal,comm from scott.emp1 where sal<>800;
SAL COMM
---------- ----------
1600 300
1250 500
2975
...
13 rows selected.
---------- ----------
1600 300
1250 500
2975
...
13 rows selected.
SQL>
SQL>
SQL> select count(*) from scott.emp1 where comm is not null;
SQL>
SQL> select count(*) from scott.emp1 where comm is not null;
COUNT(*)
----------
4
----------
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"
USER is "SYS"
SQL> show parameter audit_sys
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_sys_operations boolean FALSE
audit_syslog_level string
------------------------------------ ----------- ------------------------------
audit_sys_operations boolean FALSE
audit_syslog_level string
==>没有对SYS进行审计
3,对普通用户审计
SQL> conn scott/tiger
Connected.
SQL>
SQL> select * from scott.emp1;
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
---------- ---------- --------- ---------- ------------------ ----------
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
---------- ----------
800
1250 500
1250 1400
1100
950
SQL> select sal,comm from scott.emp1 where mgr=7902;
SAL COMM
---------- ----------
800
---------- ----------
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
---------- ---------- --------- ---------- ------------------ ----------
COMM DEPTNO
---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800
20
SQL> select count(sal) from scott.emp1 where mgr=7902;
COUNT(SAL)
----------
1
----------
1
SQL> select count(*) from scott.emp1 where comm is null;
COUNT(*)
----------
10
----------
10
SQL> select empno from scott.emp1 where mgr=7902;
EMPNO
----------
7369
==>没有审计记录,列不满足审计条件
----------
7369
==>没有审计记录,列不满足审计条件
SQL> select count(*) from scott.emp1;
COUNT(*)
----------
14
----------
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
---------- ----------
1600 300
1250 500
2975
1250 1400
2850
2450
3000
5000
1500 0
1100
950
3000
1300
13 rows selected.
==>没有审计记录,没有记录符合审计条件(sal=800)
==>没有审计记录,没有记录符合审计条件(sal=800)
SQL> select count(*) from scott.emp1 where comm is not null;
COUNT(*)
----------
4
==>没有审计记录,没有记录符合审计条件(sal=800),sal=800时comm为空
----------
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)
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
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
[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
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;
------------------------------------ ----------- ------------------------------
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
Connected.
SQL> startup
SQL> show parameter audit_sys
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_sys_operations boolean TRUE
audit_syslog_level string
------------------------------------ ----------- ------------------------------
audit_sys_operations boolean TRUE
audit_syslog_level string
SQL> delete from sys.fga_log$;
7 rows deleted.
SQL> commit;
Commit complete.
7 rows deleted.
SQL> commit;
Commit complete.
SQL> show user
USER is "SYS"
USER is "SYS"
SQL> select * from scott.emp1;
...
14 rows selected.
...
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 * 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
800
1250 500
1250 1400
1100
950
SQL> select sal,comm from scott.emp1 where mgr=7902;
800
800
SQL> select * from scott.emp1 where mgr=7902;
7369 SMITH CLERK 7902 17-DEC-80 800
20
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
10
SQL> select empno from scott.emp1 where mgr=7902;
7369
7369
SQL> select count(*) from scott.emp1;
14
14
SQL> select sal,comm from scott.emp1 where sal<>800;
...
13 rows selected.
...
13 rows selected.
SQL> select count(*) from scott.emp1 where comm is not null;
4
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 /
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 /
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;
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
300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250
500 30
500 30
7566 JONES MANAGER 7839 02-APR-81 2975
20
20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250
1400 30
1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30
30
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
10
7788 SCOTT ANALYST 7566 19-APR-87 3000
20
20
7839 KING PRESIDENT 17-NOV-81 5000
10
10
7844 TURNER SALESMAN 7698 08-SEP-81 1500
0 30
0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100
20
20
7900 JAMES CLERK 7698 03-DEC-81 950
30
30
7902 FORD ANALYST 7566 03-DEC-81 3000
20
20
7934 MILLER CLERK 7782 23-JAN-82 1300
10
10
14 rows selected.
SQL> select sal,comm from scott.emp1 where sal<1300;
SAL COMM
---------- ----------
800
1250 500
1250 1400
1100
950
---------- ----------
800
1250 500
1250 1400
1100
950
SQL> select sal,comm from scott.emp1 where deptno=20;
SAL COMM
---------- ----------
800
2975
3000
1100
3000
---------- ----------
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
---------- ----------
1600 300
1250 500
1250 1400
2850
2450
5000
1500 0
950
1300
9 rows selected.
==>没有审计记录,没有记录符合审计条件(sal=800 or deptno=20)
==>没有审计记录,没有记录符合审计条件(sal=800 or deptno=20)
SQL> select sal,comm from scott.emp1 where mgr=7902;
SAL COMM
---------- ----------
800
---------- ----------
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
---------- ---------- --------- ---------- ------------------ ----------
COMM DEPTNO
---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800
20
SQL> select count(sal) from scott.emp1 where mgr=7902;
COUNT(SAL)
----------
1
----------
1
SQL> select count(*) from scott.emp1 where comm is null;
COUNT(*)
----------
10
----------
10
SQL> select empno from scott.emp1 where mgr=7902;
EMPNO
----------
7369
==>没有审计记录,没有记录符合审计条件(sal=800 or deptno=20)
----------
7369
==>没有审计记录,没有记录符合审计条件(sal=800 or deptno=20)
SQL> select count(*) from scott.emp1;
COUNT(*)
----------
14
----------
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
---------- ----------
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)
----------
4
==>没有审计记录,没有记录符合审计条件(sal=800 or deptno=20)
SQL> select sal,comm from scott.emp1 where sal=800 and deptno=20;
SAL COMM
---------- ----------
800
---------- ----------
800
SQL> select sal,comm from scott.emp1 where sal=800 or deptno=20;
SAL COMM
---------- ----------
800
2975
3000
1100
3000
---------- ----------
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
---------- ----------
1600 300
1250 500
2975
1250 1400
2850
2450
3000
5000
1500 0
1100
950
SAL COMM
---------- ----------
3000
1300
---------- ----------
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
---------- ----------
1600 300
1250 500
1250 1400
2850
2450
5000
1500 0
950
1300
9 rows selected.
==>没有审计记录,没有记录符合审计条件(sal=800 or deptno=20)
==>没有审计记录,没有记录符合审计条件(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
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