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
