oracle審計
概述：
數據庫審計功能是oracle自身提供的對數據庫操作進行記錄的功能。
可以審計權限的調用記錄、用戶的dml操作記錄、查詢操作記錄等等
功能分類
oracle審計分標準審計和細粒度審計（FGA）。
標準審計又分語句審計、權限審計、模式對象審計。
基于值的審計（Value-Based, 觸發器審計）
精細審計（FGA）
審計有關參數
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 禁用數據庫審計 不啟用audit
os 將數據庫審計記錄定向到操作系統審計記錄
將審計結果存放到操作系統的文件里， audit_file_dest 指定的位置，
一般用于審計 sys
db 將數據庫所有審計記錄定向到數據庫的SYS.AUD$表
一般用于審計非sys用戶
db,extended 將數據庫所有審計記錄定向到數據庫的SYS.AUD$表。
可以包括綁定變量， CLOB 類型大對象等審計信息
另外，填充SYS.AUD$表的SQLBIND 列和SQLTEXT CLOB 列。
xml 將所有記錄寫到XML格式的操作系統文件中。
xml,extended 輸出審計記錄的所有列，包括SqlText和SqlBind的值。
1、強制性審計
應用： 記錄用戶登錄數據庫的信息、數據庫啟動關閉
文件存儲：
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、標準審計
應用： 默認對指定普通用戶在數據庫上的操作進行行為監控
審計記錄 audit_trail DB:審計結果存儲在數據字典中(sys可以更新)
XML：審計結果以xml格式存儲在操作系統下
sys用戶審計 audit_sys_operations 默認false，不啟用對sys用戶的審計；
建立sys審計，需設置為true，但審計結果不能存儲在DB
審計結果
1、audit$基表（可以刪除）
2、dba_audit_trail 視圖
標準審計不記錄用戶的具體操作(sql_text)
【分類：】
1）基于語句的審計Auditing SQL statement
審計指定用戶關于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;
對kobe用戶進行審計，當對表操作成功
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下查詢審計結果
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
最后審計結果里只有建表、刪表成功
關閉審計
SYS@PROD> noaudit table by kobe;
Noaudit succeeded.
刪除審計結果
SYS@PROD> delete from audit$;
29 rows deleted.
SYS@PROD> commit;
Commit complete.
2) 基于權限的審計Auditing Privileges
審計kobe用戶的select any table權限
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
關閉審計
SYS@PROD> noaudit select any table by kobe;
Noaudit succeeded.
3) 基于對象的審計Auditing Schema Objects
應用 對重要的object建立用戶訪問行為的跟蹤
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 成功訪問
F: failure 失敗的訪問
B：both 對對象的操作有成功，也有失敗
標準審計不記錄用戶的具體操作(sql_text)
SES_ACTIONS字段共包含16個字符，初始狀態都為“-”，當被審計的對象被操作后，
SES_ACTIONS會在相應的位置作出標識，標識為“S”的代表操作成功，標識為“F”的代表操作失敗，
16個位置的字符所代表的操作依次如下：
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
關閉審計
SYS@PROD> noaudit all on scott.emp;
Noaudit succeeded.
3、精細化審計（FGA）
應用
通過DBMS_FGA建立審計策略，更細化的記錄用戶訪問object的相應sql text及不同條件建立審計
建立對emp1表的FGA
SYS@PROD> create table scott.emp1 as select * from scott.emp;
Table created.
添加一個精細度審計策略
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.
測試
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列 不審計
EMPNO ENAME
---------- ----------
7369 SMITH
7566 JONES
7788 SCOTT
7876 ADAMS
7902 FORD
SCOTT@PROD> conn / as sysdba
Connected. 雖然符合條件，默認不審計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
查看審計結果
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.
刪除審計結果
SYS@PROD> select count(*) from fga_log$;
COUNT(*)
----------
2
SYS@PROD> delete from fga_log$;
2 rows deleted.
SYS@PROD> commit;
Commit complete.
審計結果
fga_log$
dba_fga_audit_trail
4、應用審計（觸發器）
應用 對object中數據的變化進行監控和跟蹤
案例 跟蹤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
建立觸發器
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
