Oracle标准数据库审核
Oracle标准数据库审核可以对一般用户（不包括SYS）的各种权限操作进行审核和跟踪。
一、标准数据库审核的基本方法
1、开启标准数据库审核
初始化参数audit_trail是一个静态参数，该参数确定如何启用审核，不同的值表示是否开启审核以及如何记录审核。
该参数可以设定为如下的值：
none或flase（10g为默认）：不审核；
db或true（11g为默认）：审核结果记录到数据库表sys.aud$，可以通过视图dba_audit_trail来查看结果；
os：审核结果记录到操作系统文件中，Unix在audit_file_dest参数中指定，Windows则在应用程序日志中（事件查看器eventvwr）；
db_extended：与db大致相同，但审核结果包含了具有绑定变量的SQL语句；
xml：与os大致相同，但使用xml来标记；
xml_extended：与xml大致相同，但审核结果包含了具有绑定变量的SQL语句。
2、指定审核选项
使用audit命令可以配置数据库审核，标准数据库审核包含以下几类：
1）系统权限审核
审核系统权限的操作，如
audit create any table;
audit create any trigger;
审核某用户的系统权限操作，如
audit select any table by scott;
访问自己的表时不会做审核。
审核用户的创建和删除
audit create user, drop user;
查开启的系统权限审核，通过数据字典dba_priv_audit_opts，11g默认会开启以下审核
col user_name for a20
col proxy_name for a20
col privilege for a30
col success for a20
col failure for a20
select * from dba_priv_audit_opts;
USER_NAME PROXY_NAME PRIVILEGE SUCCESS FAILURE
--------------- --------------- ---------------------------------------- ---------- ----------
CREATE EXTERNAL JOB BY ACCESS BY ACCESS
CREATE ANY JOB BY ACCESS BY ACCESS
GRANT ANY OBJECT PRIVILEGE BY ACCESS BY ACCESS
EXEMPT ACCESS POLICY BY ACCESS BY ACCESS
CREATE ANY LIBRARY BY ACCESS BY ACCESS
GRANT ANY PRIVILEGE BY ACCESS BY ACCESS
DROP PROFILE BY ACCESS BY ACCESS
ALTER PROFILE BY ACCESS BY ACCESS
DROP ANY PROCEDURE BY ACCESS BY ACCESS
ALTER ANY PROCEDURE BY ACCESS BY ACCESS
CREATE ANY PROCEDURE BY ACCESS BY ACCESS
ALTER DATABASE BY ACCESS BY ACCESS
GRANT ANY ROLE BY ACCESS BY ACCESS
CREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESS
DROP ANY TABLE BY ACCESS BY ACCESS
ALTER ANY TABLE BY ACCESS BY ACCESS
CREATE ANY TABLE BY ACCESS BY ACCESS
DROP USER BY ACCESS BY ACCESS
ALTER USER BY ACCESS BY ACCESS
CREATE USER BY ACCESS BY ACCESS
CREATE SESSION BY ACCESS BY ACCESS
AUDIT SYSTEM BY ACCESS BY ACCESS
ALTER SYSTEM BY ACCESS BY ACCESS
2）对象权限审核
对所有用户（不包括sys，sys是不审核的），如
aduit alter, delete, drop, insert on scott.emp;
对某个用户，如
audit select on hr.employees by scott;
对所有操作，如
audit all on hr.employees;
查开启的对象审核，通过数据字典dba_obj_audit_opts，默认是都没有开启
select * from dba_obj_audit_opts;
OWNER OBJECT_NAME OBJECT_TYPE ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE REA WRI FBK
---------- --------------- --------------- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- --- ----- ----- ----- ----- -----
3）语句审核
如审核表的所有DDL操作
audit table;
查开启的语句审核，通过数据字典dba_stmt_audit_opts，11g默认会开启以下审核，其中也包含了上述属于系统权限的审核
col user_name for a20
col proxy_name for a20
col audit_option for a30
col success for a20
col failure for a20
select * from dba_stmt_audit_opts;
USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE
--------------- --------------- ---------------------------------------- ---------- ----------
ALTER SYSTEM BY ACCESS BY ACCESS
SYSTEM AUDIT BY ACCESS BY ACCESS
CREATE SESSION BY ACCESS BY ACCESS
CREATE USER BY ACCESS BY ACCESS
ALTER USER BY ACCESS BY ACCESS
DROP USER BY ACCESS BY ACCESS
PUBLIC SYNONYM BY ACCESS BY ACCESS
DATABASE LINK BY ACCESS BY ACCESS
ROLE BY ACCESS BY ACCESS
PROFILE BY ACCESS BY ACCESS
CREATE ANY TABLE BY ACCESS BY ACCESS
ALTER ANY TABLE BY ACCESS BY ACCESS
DROP ANY TABLE BY ACCESS BY ACCESS
CREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESS
GRANT ANY ROLE BY ACCESS BY ACCESS
SYSTEM GRANT BY ACCESS BY ACCESS
ALTER DATABASE BY ACCESS BY ACCESS
CREATE ANY PROCEDURE BY ACCESS BY ACCESS
ALTER ANY PROCEDURE BY ACCESS BY ACCESS
DROP ANY PROCEDURE BY ACCESS BY ACCESS
ALTER PROFILE BY ACCESS BY ACCESS
DROP PROFILE BY ACCESS BY ACCESS
GRANT ANY PRIVILEGE BY ACCESS BY ACCESS
CREATE ANY LIBRARY BY ACCESS BY ACCESS
EXEMPT ACCESS POLICY BY ACCESS BY ACCESS
GRANT ANY OBJECT PRIVILEGE BY ACCESS BY ACCESS
CREATE ANY JOB BY ACCESS BY ACCESS
CREATE EXTERNAL JOB BY ACCESS BY ACCESS
4）其它审核配置
审核会话登录
audit session;
这与审核create session权限的使用效果相同。
取消审核，通过noaudit命令指定
noaudit session;
noaudit all on scott.emp;
审核成功的操作，通过whenever successful选项指定，如审核表的成功插入
audit insert on scott.emp whenever successful;
审核不成功的操作，通过whenever not successful选项指定，如审核失败的会话登录
audit session whenever not successful;
默认情况下是审核所有的操作，不论成功与否。
会话级别上的审核，通过by session选项指定
audit update on scott.emp by session;
操作级别上的审核，通过by access选项指定
audit update on scott.emp by access;
对象权限审核默认是by session
系统权限审核默认是by access
3、查看审核记录
如果审核针对数据库（audit_trail=db或db_extended），则审核记录写入数据字典表sys.aud$中，虽然可以直接查看，但通过建立在其上的视图来查看将更加方便。
常用的视图是dba_audit_trail，其常用列的解释如下：
os_username：执行操作的用户的操作系统用户名
username：执行操作的用户的Oracle用户名
userhost：运行用户进程的计算机名称
timestamp：审核事件的发生时间
owner，obj_name：受影响对象的模式和名称
action，action_name：审核的操作，操作代码action的对照含义可查数据字典表audit_actions
priv_used：使用的系统权限
sql_text：执行的语句
如果没有表aud$及视图dba_audit_trail，则需要执行审核相关的数据字典表的安装脚本，安装后要重启数据库，安装脚本位于
%ORACLE_HOME%\rdbms\admin\cataudit.sql
其它审核视图显示了dba_audit_trail视图的一个子集：
dba_audit_object
dba_audit_statement
dba_audit_session
二、标准数据库审核实验
1、创建实验用表
create table scott.emp1 as select * from scott.emp;
grant all on scott.emp1 to hr;
2、启用审核
audit all on scott.emp1 by access;
audit table;
alter system set audit_sys_operations=true scope=spfile;
alter system set audit_trail='db_extended' scope=spfile;
重启数据库实例
shutdown immediate
startup
实验前可先清除审核结果表的所有记录
truncate table sys.aud$;
3、进行sysdba的活动，并查看审核结果
以sysdba身份登录并操作
select * from dba_users;
select * from scott.emp1;
create user audr1 identified by audr1;
drop user audr1;
create table scott.emp2 as select * from scott.emp1;
select * from scott.emp2;
drop table scott.emp2 purge;
查看sysdba的审核结果，由于开启了sysdba审核，所以可以在操作系统文件和日志中看到所有操作记录。Unix查看audit_file_dest指定的目标文件，Windows通过事件查看器eventvwr查看应用程序日志。另外可以看到SYS管理员的操作不会记入aud$中，视图dba_audit_trail没有相关记录。
4、用system用户登录操作，并查看审核结果
select * from scott.emp;
select * from scott.emp1;
create user audr1 identified by audr1;
grant connect to audr1;
create table audr1.t1(n number);
select * from audr1.t1;
drop table audr1.t1 purge;
drop user audr1;
退出system的登录
查看审核结果
col os_username for a20
col username for a20
col userhost for a20
col owner for a10
col obj_name for a20
col action_name for a20
col priv_used for a20
col sql_text for a50
select os_username, username, userhost, timestamp, owner, obj_name, action, action_name, priv_used, sql_text from dba_audit_trail order by timestamp desc;
OS_USERNAME USERNAME USERHOST TIMESTAMP OWNER OBJ_NAME ACTION ACTION_NAME PRIV_USED SQL_TEXT
-------------------- -------------------- -------------------- ------------------- ---------- -------------------- ---------- -------------------- -------------------- --------------------------------------------------
Administrator SYSTEM WORKGROUP\MYPC 2017-09-20 10:36:05 101 LOGOFF
Administrator SYSTEM WORKGROUP\MYPC 2017-09-20 10:35:59 AUDR1 53 DROP USER DROP USER drop user audr1
Administrator SYSTEM WORKGROUP\MYPC 2017-09-20 10:35:49 AUDR1 T1 12 DROP TABLE DROP ANY TABLE drop table audr1.t1 purge
Administrator SYSTEM WORKGROUP\MYPC 2017-09-20 10:35:32 AUDR1 T1 1 CREATE TABLE CREATE ANY TABLE create table audr1.t1(n number)
Administrator SYSTEM WORKGROUP\MYPC 2017-09-20 10:35:24 CONNECT 114 GRANT ROLE GRANT ANY ROLE grant connect to audr1
Administrator SYSTEM WORKGROUP\MYPC 2017-09-20 10:35:14 AUDR1 51 CREATE USER CREATE USER create user audr1 identified by *****
Administrator SYSTEM WORKGROUP\MYPC 2017-09-20 10:35:04 SCOTT EMP1 3 SELECT SELECT ANY TABLE select * from scott.emp1
Administrator SYSTEM WORKGROUP\MYPC 2017-09-20 10:34:33 100 LOGON CREATE SESSION
由于没有对表scott.emp加入审核，因此对它的查询未计入表中，而会话的登入登出、建表、删表、授权是默认开启的系统权限审核，因此这些操作被记入表中，同样对表audr1.t1的查询也不会加入审核。
5、清空aud$记录，在sys下创建用户audr1，并分别用audr1和hr用户登录操作，查看审核结果
sys的操作
create user audr1 identified by audr1;
grant connect to audr1;
audr1用户登录操作
select * from scott.emp1;
由于没有给audr1访问scott.emp1的权限，因此以上查询将失败
audr1用户退出登录
hr用户登录操作
select * from scott.emp1;
update scott.emp1 set sal=2000 where empno=7369;
commit;
update scott.emp1 set sal=2500 where empno=7369;
rollback;
hr用户退出登录
查看审核记录
col os_username for a20
col username for a20
col userhost for a20
col owner for a10
col obj_name for a20
col action_name for a20
col priv_used for a20
col sql_text for a50
select os_username, username, userhost, timestamp, owner, obj_name, action, action_name, priv_used, sql_text from dba_audit_trail order by timestamp desc;
OS_USERNAME USERNAME USERHOST TIMESTAMP OWNER OBJ_NAME ACTION ACTION_NAME PRIV_USED SQL_TEXT
-------------------- -------------------- -------------------- ------------------- ---------- -------------------- ---------- -------------------- -------------------- --------------------------------------------------
Administrator HR WORKGROUP\MYPC 2017-09-20 10:42:29 101 LOGOFF
Administrator HR WORKGROUP\MYPC 2017-09-20 10:45:33 SCOTT EMP1 6 UPDATE update scott.emp1 set sal=2500 where empno=7369
Administrator HR WORKGROUP\MYPC 2017-09-20 10:42:17 SCOTT EMP1 6 UPDATE update scott.emp1 set sal=2000 where empno=7369
Administrator HR WORKGROUP\MYPC 2017-09-20 10:42:09 SCOTT EMP1 3 SELECT select * from scott.emp1
Administrator HR WORKGROUP\MYPC 2017-09-20 10:41:49 100 LOGON CREATE SESSION
Administrator AUDR1 WORKGROUP\MYPC 2017-09-20 10:41:41 101 LOGOFF
Administrator AUDR1 WORKGROUP\MYPC 2017-09-20 10:41:29 SCOTT EMP1 3 SELECT select * from scott.emp1
Administrator AUDR1 WORKGROUP\MYPC 2017-09-20 10:41:01 100 LOGON CREATE SESSION
由于默认是操作不论成功与否都会纳入审核，因此audr1用户失败的查询也被记录，commit和rollback语句并没有记录，不管执行的语句最后是被提交还是回滚，更新操作总是被审核的。
6、取消对象审核
noaudit all on scott.emp1;
hr用户再次登录操作
select * from scott.emp1;
hr用户退出登录
查看审核记录，确认审核只有用户的登入登出，其它已取消。如果要将会话的登入登出记录也取消，则执行noaudit session，但这样一来，默认的系统权限审核将不再包括该项，除非执行audit session重新加入。
7、清空aud$记录，改为会话级别的审核，并查看结果
audit all on scott.emp1 by session;
再次以hr用户登录并操作
select * from scott.emp1;
update scott.emp1 set sal=800 where empno=7369;
commit;
hr用户退出登录
查看审核记录
col os_username for a20
col username for a20
col userhost for a20
col owner for a10
col obj_name for a20
col action_name for a20
col priv_used for a20
col sql_text for a50
select os_username, username, userhost, timestamp, owner, obj_name, action, action_name, priv_used, sql_text from dba_audit_trail order by timestamp desc;
OS_USERNAME USERNAME USERHOST TIMESTAMP OWNER OBJ_NAME ACTION ACTION_NAME PRIV_USED SQL_TEXT
-------------------- -------------------- -------------------- ------------------- ---------- -------------------- ---------- -------------------- -------------------- --------------------------------------------------
Administrator HR WORKGROUP\MYPC 2017-09-20 10:54:55 101 LOGOFF
Administrator HR WORKGROUP\MYPC 2017-09-20 10:54:44 SCOTT EMP1 103 SESSION REC update scott.emp1 set sal=800 where empno=7369
Administrator HR WORKGROUP\MYPC 2017-09-20 10:54:36 SCOTT EMP1 103 SESSION REC select * from scott.emp1
Administrator HR WORKGROUP\MYPC 2017-09-20 10:54:29 100 LOGON CREATE SESSION
比较可知，操作级别的审核明确记录了action_name为select、update等，而会话级别的审核action_name只标明为session rec，但sql_text仍记录了会话中每一步操作的SQL语句。
8、取消审核，清理恢复
noaudit all on scott.emp1;
drop user audr1;
drop table scott.emp1 purge;
alter system set audit_trail=false scope=spfile;
alter system set audit_sys_operations=false scope=spfile;
重启数据库实例
清空aud$记录
