概述：
數(shù)據(jù)庫(kù)審計(jì)功能是oracle自身提供的對(duì)數(shù)據(jù)庫(kù)操作進(jìn)行記錄的功能。
可以審計(jì)權(quán)限的調(diào)用記錄、用戶(hù)的dml操作記錄、查詢(xún)操作記錄等等
功能分類(lèi)
oracle審計(jì)分標(biāo)準(zhǔn)審計(jì)和細(xì)粒度審計(jì)（FGA）。
標(biāo)準(zhǔn)審計(jì)又分語(yǔ)句審計(jì)、權(quán)限審計(jì)、模式對(duì)象審計(jì)。
基于值的審計(jì)（Value-Based, 觸發(fā)器審計(jì)）
精細(xì)審計(jì)（FGA）
審計(jì)有關(guān)參數(shù)
SYS@PROD> show parameter audit;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/PROD/adu
mp
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string DB
audit_trail
AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }
none 禁用數(shù)據(jù)庫(kù)審計(jì) 不啟用audit
os 將數(shù)據(jù)庫(kù)審計(jì)記錄定向到操作系統(tǒng)審計(jì)記錄
將審計(jì)結(jié)果存放到操作系統(tǒng)的文件里， audit_file_dest 指定的位置，
一般用于審計(jì) sys
db 將數(shù)據(jù)庫(kù)所有審計(jì)記錄定向到數(shù)據(jù)庫(kù)的SYS.AUD$表
一般用于審計(jì)非sys用戶(hù)
db,extended 將數(shù)據(jù)庫(kù)所有審計(jì)記錄定向到數(shù)據(jù)庫(kù)的SYS.AUD$表。
可以包括綁定變量， CLOB 類(lèi)型大對(duì)象等審計(jì)信息
另外，填充SYS.AUD$表的SQLBIND 列和SQLTEXT CLOB 列。
xml 將所有記錄寫(xiě)到XML格式的操作系統(tǒng)文件中。
xml,extended 輸出審計(jì)記錄的所有列，包括SqlText和SqlBind的值。
1、強(qiáng)制性審計(jì)
應(yīng)用： 記錄用戶(hù)登錄數(shù)據(jù)庫(kù)的信息、數(shù)據(jù)庫(kù)啟動(dòng)關(guān)閉
文件存儲(chǔ)：
SYS@PROD> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/PROD/adu
mp
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string DB
2、標(biāo)準(zhǔn)審計(jì)
應(yīng)用： 默認(rèn)對(duì)指定普通用戶(hù)在數(shù)據(jù)庫(kù)上的操作進(jìn)行行為監(jiān)控
審計(jì)記錄 audit_trail DB:審計(jì)結(jié)果存儲(chǔ)在數(shù)據(jù)字典中(sys可以更新)
XML：審計(jì)結(jié)果以xml格式存儲(chǔ)在操作系統(tǒng)下
sys用戶(hù)審計(jì) audit_sys_operations 默認(rèn)false，不啟用對(duì)sys用戶(hù)的審計(jì)；
建立sys審計(jì)，需設(shè)置為true，但審計(jì)結(jié)果不能存儲(chǔ)在DB
審計(jì)結(jié)果
1、audit$基表（可以刪除）
2、dba_audit_trail 視圖
標(biāo)準(zhǔn)審計(jì)不記錄用戶(hù)的具體操作(sql_text)
【分類(lèi)：】
1）基于語(yǔ)句的審計(jì)Auditing SQL statement
審計(jì)指定用戶(hù)關(guān)于table的操作
SYS@PROD> create user kobe identified by oracle;
User created.
SYS@PROD> grant create session,unlimited tablespace,create table to kobe;
Grant succeeded.
SYS@PROD> audit table by kobe whenever successful;
對(duì)kobe用戶(hù)進(jìn)行審計(jì)，當(dāng)對(duì)表操作成功
Audit succeeded.
SYS@PROD> conn scott/tiger 在scott下刪除表失敗
Connected.
SCOTT@PROD> drop table t1 purge;
drop table t1 purge
*
ERROR at line 1:
ORA-00942: table or view does not exist
SCOTT@PROD> drop table test purge;
drop table test purge
*
ERROR at line 1:
ORA-00942: table or view does not exist
SCOTT@PROD> conn kobe/oracle
Connected.
KOBE@PROD> create table test(id number); kobe建表成功
Table created.
KOBE@PROD> insert into test values(1);
1 row created.
KOBE@PROD> drop table t1 ; 刪除表失敗
drop table t1
*
ERROR at line 1:
ORA-00942: table or view does not exist
KOBE@PROD> drop table test purge; 刪除表成功
Table dropped.
sys下查詢(xún)審計(jì)結(jié)果
SYS@PROD> select username,timestamp,obj_name,action_name from dba_audit_trail
2 where username='KOBE';
USERNAME TIMESTAMP OBJ_NAME ACTION_NAME
-------------------- ------------------ -------------------- ----------------------------
KOBE 31-MAY-18 LOGON
KOBE 31-MAY-18 TEST CREATE TABLE
KOBE 31-MAY-18 TEST DROP TABLE
KOBE 31-MAY-18 LOGOFF
最后審計(jì)結(jié)果里只有建表、刪表成功
關(guān)閉審計(jì)
SYS@PROD> noaudit table by kobe;
Noaudit succeeded.
刪除審計(jì)結(jié)果
SYS@PROD> delete from audit$;
29 rows deleted.
SYS@PROD> commit;
Commit complete.
2) 基于權(quán)限的審計(jì)Auditing Privileges
審計(jì)kobe用戶(hù)的select any table權(quán)限
SYS@PROD> grant select any table to kobe;
Grant succeeded.
SYS@PROD> audit select any table by kobe;
Audit succeeded.
SYS@PROD> conn kobe/oracle
Connected.
KOBE@PROD> select count(*) from scott.emp;
COUNT(*)
----------
14
KOBE@PROD> conn / as sysdba
Connected.
SYS@PROD> col owner for a10
SYS@PROD> select username,timestamp,owner,obj_name,priv_used
2 from dba_audit_trail where username='KOBE';
USERNAME TIMESTAMP OWNER OBJ_NAME PRIV_USED
-------------------- ------------------ ---------- -------------------- ----------------------------------------
KOBE 31-MAY-18 CREATE SESSION
KOBE 31-MAY-18 KOBE TEST CREATE TABLE
KOBE 31-MAY-18 KOBE TEST
KOBE 31-MAY-18
KOBE 31-MAY-18 SCOTT EMP SELECT ANY TABLE
關(guān)閉審計(jì)
SYS@PROD> noaudit select any table by kobe;
Noaudit succeeded.
3) 基于對(duì)象的審計(jì)Auditing Schema Objects
應(yīng)用 對(duì)重要的object建立用戶(hù)訪問(wèn)行為的跟蹤
SYS@PROD> audit all on scott.emp;
Audit succeeded.
SYS@PROD> revoke select any table from kobe;
Revoke succeeded.
SYS@PROD> grant select,update on scott.emp to kobe;
Grant succeeded.
SYS@PROD> conn kobe/oracle
Connected.
KOBE@PROD> select * from scott.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.
KOBE@PROD> update scott.emp set sal=100 where deptno=10;
3 rows updated.
KOBE@PROD> commit;
Commit complete.
KOBE@PROD> conn scott/tiger
Connected.
SCOTT@PROD> 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 100 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 100 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 100 10
14 rows selected.
SCOTT@PROD> delete from emp where deptno=10;
3 rows deleted.
SCOTT@PROD> conn / as sysdba
Connected.
SYS@PROD> select username,ses_actions,owner,obj_name,to_char(timestamp,'yyyy-mm-dd hh34:mi:ss')
from dba_audit_trail order by 1;
USERNAME SES_ACTIONS OWNER OBJ_NAME TO_CHAR(TIMESTAMP,'
-------------------- ------------------- ---------- -------------------- -------------------
KOBE 2018-05-31 14:19:25
KOBE KOBE TEST 2018-05-31 14:19:40
KOBE KOBE TEST 2018-05-31 14:20:22
KOBE ----------S----- SCOTT EMP 2018-05-31 14:48:12
KOBE ---------S------ SCOTT EMP 2018-05-31 14:39:05
KOBE ---------S------ SCOTT EMP 2018-05-31 14:47:47
KOBE 2018-05-31 14:22:11
SCOTT 2017-06-15 21:51:47
SCOTT 2017-06-15 21:51:58
SCOTT 2018-05-31 14:18:54
SCOTT ---S------------ SCOTT EMP 2018-05-31 14:48:55
SCOTT ---------S------ SCOTT EMP 2018-05-31 14:48:37
SCOTT 2018-05-31 14:19:25
S：success 成功訪問(wèn)
F: failure 失敗的訪問(wèn)
B：both 對(duì)對(duì)象的操作有成功，也有失敗
標(biāo)準(zhǔn)審計(jì)不記錄用戶(hù)的具體操作(sql_text)
SES_ACTIONS字段共包含16個(gè)字符，初始狀態(tài)都為“-”，當(dāng)被審計(jì)的對(duì)象被操作后，
SES_ACTIONS會(huì)在相應(yīng)的位置作出標(biāo)識(shí)，標(biāo)識(shí)為“S”的代表操作成功，標(biāo)識(shí)為“F”的代表操作失敗，
16個(gè)位置的字符所代表的操作依次如下：
1. Auditing ALTER
2. Auditing AUDIT
3. Auditing COMMIT
4. Auditing DELETE
5. Auditing GRANT
6. Auditing INDEX
7. Auditing INSERT
8. Auditing LOCK
9. Aduiting RENAME
10.Auditing SELECT
11.Auditing UPDATE
12.Auditing EXECUTE
13.Auditing CREATE
14.Auditing READ
15.Auditing WRITE
16.Auditing FLASHBACK
關(guān)閉審計(jì)
SYS@PROD> noaudit all on scott.emp;
Noaudit succeeded.
3、精細(xì)化審計(jì)（FGA）
應(yīng)用
通過(guò)DBMS_FGA建立審計(jì)策略，更細(xì)化的記錄用戶(hù)訪問(wèn)object的相應(yīng)sql text及不同條件建立審計(jì)
建立對(duì)emp1表的FGA
SYS@PROD> create table scott.emp1 as select * from scott.emp;
Table created.
添加一個(gè)精細(xì)度審計(jì)策略
SYS@PROD> begin
2 dbms_fga.add_policy(
3 object_schema=>'scott',
4 object_name=>'emp1',
5 policy_name=>'chk_emp1',
6 audit_condition =>'deptno=20',
7 audit_column =>'sal',
8 statement_types =>'update,select');
9 end;
10 /
PL/SQL procedure successfully completed.
測(cè)試
SYS@PROD> conn scott/tiger
Connected.
SCOTT@PROD> select * from emp1 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@PROD> update scott.emp1 set sal=8000 where empno=7902;
1 row updated.
SCOTT@PROD> select empno,ename from scott.emp1 where deptno=20; 缺少sal列 不審計(jì)
EMPNO ENAME
---------- ----------
7369 SMITH
7566 JONES
7788 SCOTT
7876 ADAMS
7902 FORD
SCOTT@PROD> conn / as sysdba
Connected. 雖然符合條件，默認(rèn)不審計(jì)sys
SYS@PROD> select empno,ename,sal from scott.emp1 where deptno=20;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7566 JONES 2975
7788 SCOTT 3000
7876 ADAMS 1100
7902 FORD 8000
查看審計(jì)結(jié)果
SYS@PROD> col db_user for a10
SYS@PROD> col sql_text for a60
SYS@PROD> select db_user,to_char(timestamp,'yyyy-mm-dd hh34:mi:ss') "time" ,sql_text
2 from dba_fga_audit_trail;
DB_USER time SQL_TEXT
---------- ------------------- ------------------------------------------------------------
SCOTT 2018-05-31 15:28:52 select * from emp1 where deptno=20
SCOTT 2018-05-31 15:29:01 update scott.emp1 set sal=8000 where empno=7902
刪除FGA策略
SYS@PROD> exec dbms_fga.drop_policy(object_schema=>'scott',object_name=>'emp1',policy_name=>'chk_emp1');
PL/SQL procedure successfully completed.
或者
SYS@PROD> execute dbms_fga.DROP_POLICY('scott','emp1','chk_emp1');
PL/SQL procedure successfully completed.
刪除審計(jì)結(jié)果
SYS@PROD> select count(*) from fga_log$;
COUNT(*)
----------
2
SYS@PROD> delete from fga_log$;
2 rows deleted.
SYS@PROD> commit;
Commit complete.
審計(jì)結(jié)果
fga_log$
dba_fga_audit_trail
4、應(yīng)用審計(jì)（觸發(fā)器）
應(yīng)用 對(duì)object中數(shù)據(jù)的變化進(jìn)行監(jiān)控和跟蹤
案例 跟蹤emp表中sal字段的變化
SCOTT@PROD> create table audit_emp_change(name varchar2(10),
oldsal number(6,2),newsal number(6,2),time date);
Table created.
SCOTT@PROD> desc audit_emp_change;
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
NAME VARCHAR2(10)
OLDSAL NUMBER(6,2)
NEWSAL NUMBER(6,2)
TIME DATE
建立觸發(fā)器
SCOTT@PROD> 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;
18 /
Trigger created.
更新sal
SCOTT@PROD> update emp set sal=777 where empno=7788;
1 row updated.
SCOTT@PROD> commit;
Commit complete.
查看
SCOTT@PROD> select * from audit_emp_change;
NAME OLDSAL NEWSAL TIME
---------- ---------- ---------- ------------------
SCOTT 3000 777 31-MAY-18
分享文章：oracle審計(jì)
本文地址：
http://weahome.cn/article/gcsoee.html
重慶分公司
重慶分公司
