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