Oracle开启审计，记录登录用户的所有操作

1、以DBA登录Oracle
 
# su - oracle
$ sqlplus /nolog
SQL> conn / as sysdba
 
2、查看当前审计设置
 
SQL> show parameter audit;
 
参数说明

AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }
none or false - Auditing is disabled.
db or true - Auditing is enabled, with all audit records stored in the database audit trial (SYS.AUD$).
db,extended - As db, but the SQL_BIND and SQL_TEXT columns are also populated.
xml- Auditing is enabled, with all audit records stored as XML format OS files.
xml,extended - As xml, but the SQL_BIND and SQL_TEXT columns are also populated.
os- Auditing is enabled, with all audit records directed to the operating system's audit trail.
The AUDIT_SYS_OPERATIONS static parameter
enables or disables the auditing of operations issued by users connecting with SYSDBA or SYSOPER privileges,
including the SYS user. All audit records are written to the OS audit trail.
The AUDIT_FILE_DEST parameter
specifies the OS directory used for the audit trail when the os, xml and xml,extended options are used.
It is also the location for all mandatory auditing specified by the AUDIT_SYS_OPERATIONS parameter.
3、修改audit参数，开启审计

SQL> alter system set audit_trail=db_extended scope=spfile;
注意，如果audit_trail=db，不记录SQL_BIND和SQL_TEXT
 
4、重启数据库

SQL> shutdown immediate;
SQL> startup;

################### 

###################

###################
5、测试基于用户的审计打开
 
创建用户AUDIT_TEST
 
$ sqlplus /nolog
SQL> conn / as sysdba
SQL> audit all by audit_test by access;
SQL> audit select table, update table, insert table, delete table by audit_test by access;
SQL> audit execute procedure by audit_test by access;
 
分别对应以下三种：
DDL (CREATE, ALTER & DROP of objects)
DML (INSERT UPDATE, DELETE, SELECT, EXECUTE).
SYSTEM EVENTS (LOGON, LOGOFF etc.)
SQL> conn audit_test/password
SQL> create table test(id  number);
SQL> insert into test(id) values (1);
SQL> insert into test(id) values (2);
SQL> update test set id = 3 where id = 1;
SQL> select * from test;
SQL> delete from test;
SQL> commit;
SQL> drop table test;
SQL> select view_name from dba_views where  view_name like 'dba%audit%' order by view_name;
VIEW_NAME
------------------------------
DBA_AUDIT_EXISTS
DBA_AUDIT_OBJECT
DBA_AUDIT_POLICIES
DBA_AUDIT_POLICY_COLUMNS
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
DBA_AUDIT_TRAIL
DBA_COMMON_AUDIT_TRAIL
DBA_FGA_AUDIT_TRAIL
DBA_OBJ_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS
DBA_REPAUDIT_ATTRIBUTE
DBA_REPAUDIT_COLUMN
DBA_STMT_AUDIT_OPTS
视图说明:
1. SYS.AUD$
审计功能的底层视图,如果需要对数据进行删除,只需要对aud$视图进行删除既可,其他视图里的数据都是由aud$所得.
2. DBA_AUDIT_EXISTS
列出audit not exists和audit exists产生的审计跟踪,我们默认的都是audit exists.
3. DBA_AUDIT_TRAIL
可以在里面查处所有审计所跟踪的信息.
4. DBA_AUDIT_OBJECT
可以查询所有对象跟踪信息.(例如,对grant,revoke等不记录),信息完全包含于dba_audit_trail
5. DBA_AUDIT_SESSION
所得到的数据都是有关logon或者logoff的信息.
6. DBA_AUDIT_STATEMENT
列出grant ,revoke ,audit ,noaudit ,alter system语句的审计跟踪信息.
7. DBA_PRIV_AUDIT_OPTS
通过系统和由用户审计的当前系统特权
8. DBA_OBJ_AUDIT_OPTS
可以查询到所有用户所有对象的设计选项
9. ALL_DEF_AUDIT_OPTS
10. AUDIT_ACTIONS
可以查询出在aud$等视图中actions列的含义
11. SYSTEM_PRIVILEGE_MAP
可以查询出aud$等视图中priv$used列的含义(注意前面加'-')
常用视图：
DBA_AUDIT_TRAIL
DBA_FGA_AUDIT_TRAIL
DBA_COMMON_AUDIT_TRAIL
查看审计内容，主要字段：os_username, userhost, timestamp, owner，sql_bind, sql_text
SQL> select * from dba_audit_trail where  owner = 'AUDIT_TEST' order by timestamp;
注意：owner的值必须大写，例如 owner = 'AUDIT_TEST'
-------------------------------------------------------------------------------
关闭审计
-------------------------------------------------------------------------------
SQL> alter system set audit_trail=none scope=spfile;

########################

########################

###对该表做各种DML操作###
用scott用户登录

SQL> conn scott/123     

SQL> create table t_test as select * from emp;

SQL> update t_test set emp1='111';

                  *

第 1 行出现错误:

ORA-00904: "EMP1": 标识符无效    

SQL> delete from t_test where rownum=1;

已删除 1 行。

SQL> commit;

####查询审计信息

SQL> select EXTENDED_TIMESTAMP,SESSION_ID,SQL_TEXT

SQL> from DBA_COMMON_AUDIT_TRAIL ORDER BY EXTENDED_TIMESTAMP DESC;

或者

SQL> select EXTENDED_TIMESTAMP,SESSION_ID,SQL_TEXT from DBA_COMMON_AUDIT_TRAIL

SQL> where OBJECT_NAME='T_TEST'

SQL> and STATEMENT_TYPE in ('INSERT','UPDATE','DELETE');

---结果：

SQL> /

EXTENDED_TIMESTAMP                       SESSION_ID SQL_TEXT

---------------------------------------- ---------- ----------------------------

------------

14-8月 -12 04.14.45.187000 下午 +08:00       190125 update t_test set emp1='111'

14-8月 -12 04.26.02.968000 下午 +08:00       190125 delete from t_test where rownum=1

注意：审计一般只用于对普通用户操作，一般不审计SYS用户
对于windows系统，对sys用户的审计信息并不存在AUDIT_FILE_DEST参数指定的目录里，而是在windows的事件管理器中。
---------------------------------------------------------------------------------------------------------------------
###另外通过细粒度审计FGA也可以实现上述审计：
###用法创建审计策略：

Syntax

DBMS_FGA.ADD_POLICY(

   object_schema   VARCHAR2,

   object_name     VARCHAR2,

   policy_name     VARCHAR2,

   audit_condition VARCHAR2,

   audit_column    VARCHAR2,

   handler_schema  VARCHAR2,

   handler_module  VARCHAR2, 

   enable          BOOLEAN  );
###删除审计策略：

DBMS_FGA.DROP_POLICY(

   object_schema  VARCHAR2,

   object_name    VARCHAR2,

   policy_name    VARCHAR2 );

###启用审计策略：

DBMS_FGA.ENABLE_POLICY(

   object_schema  VARCHAR2 := NULL,

   object_name    VARCHAR2,

   policy_name    VARCHAR2,

   enable         BOOLEAN := TRUE);

###禁用审计策略：

DBMS_FGA.DISABLE_POLICY(

   object_schema  VARCHAR2,

   object_name    VARCHAR2,

   policy_name    VARCHAR2 );

 
###首先，创建审计策略

SQL> conn /as sysdba

已连接。

SQL> begin

  2  dbms_fga.add_policy

  3  (

  4  object_schema=>'SCOTT',object_name=>'T_TEST',

  5  policy_name=>'Test_audit'

  6  );

  7  end;

  8  /

PL/SQL 过程已成功完成。

SQL> conn scott/tigger

###进行查询
SQL> select ename from t_test;

###使用SYS登录进行查询，

SQL> select statement_type,SQL_TEXT from dba_fga_audit_trail;

STATEME SQL_TEXT

------- ----------------------------------------

SELECT  select ename from t_test

 ------------
注意： 经过测试发现审计到的SQL语句存在着大小写2种格式。
直接执行的SQL语句，是什么样的语句，审计到的也就是什么样。
在存储里执行的语句，审计到的全是大写的语句。
存储里动态执行的语句，是什么样的语句，审计到的也就是什么样的语句。

BEGIN

EXECUTE immediate 'delete FrOm emp WHERE ROWNUM=1';

END;

###审计到的就是
 DELETE delete FrOm emp WHERE ROWNUM=1;