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%rdbmsadmincataudit.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 WORKGROUPMYPC 2017-09-20 10:36:05 101 LOGOFF
Administrator SYSTEM WORKGROUPMYPC 2017-09-20 10:35:59 AUDR1 53 DROP USER DROP USER drop user audr1
Administrator SYSTEM WORKGROUPMYPC 2017-09-20 10:35:49 AUDR1 T1 12 DROP TABLE DROP ANY TABLE drop table audr1.t1 purge
Administrator SYSTEM WORKGROUPMYPC 2017-09-20 10:35:32 AUDR1 T1 1 CREATE TABLE CREATE ANY TABLE create table audr1.t1(n number)
Administrator SYSTEM WORKGROUPMYPC 2017-09-20 10:35:24 CONNECT 114 GRANT ROLE GRANT ANY ROLE grant connect to audr1
Administrator SYSTEM WORKGROUPMYPC 2017-09-20 10:35:14 AUDR1 51 CREATE USER CREATE USER create user audr1 identified by *****
Administrator SYSTEM WORKGROUPMYPC 2017-09-20 10:35:04 SCOTT EMP1 3 SELECT SELECT ANY TABLE select * from scott.emp1
Administrator SYSTEM WORKGROUPMYPC 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 WORKGROUPMYPC 2017-09-20 10:42:29 101 LOGOFF
Administrator HR WORKGROUPMYPC 2017-09-20 10:45:33 SCOTT EMP1 6 UPDATE update
scott.emp1 set sal=2500 where empno=7369
Administrator HR WORKGROUPMYPC 2017-09-20 10:42:17 SCOTT EMP1 6 UPDATE update
scott.emp1 set sal=2000 where empno=7369
Administrator HR WORKGROUPMYPC 2017-09-20 10:42:09 SCOTT EMP1 3 SELECT select *
from scott.emp1
Administrator HR WORKGROUPMYPC 2017-09-20 10:41:49 100 LOGON CREATE SESSION
Administrator AUDR1 WORKGROUPMYPC 2017-09-20 10:41:41 101 LOGOFF
Administrator AUDR1 WORKGROUPMYPC 2017-09-20 10:41:29 SCOTT EMP1 3 SELECT select *
from scott.emp1
Administrator AUDR1 WORKGROUPMYPC 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 WORKGROUPMYPC 2017-09-20 10:54:55 101 LOGOFF
Administrator HR WORKGROUPMYPC 2017-09-20 10:54:44 SCOTT EMP1 103 SESSION REC update
scott.emp1 set sal=800 where empno=7369
Administrator HR WORKGROUPMYPC 2017-09-20 10:54:36 SCOTT EMP1 103 SESSION REC select * from
scott.emp1
Administrator HR WORKGROUPMYPC 2017-09-20 10:54:29 100 LOGON CREATE SESSION
比较可知，操作级别的审核明确记录了action_name为select、update等，而会话级别的审核action_n免费云主机域名ame只标明为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$记录
本篇文章给大家分享的是有关如何理解Oracle的INITRANS与事务，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。 Oracle的INITRANS与事务 每个块都有一个块首部。这个块首部中有一个…
免责声明：本站发布的图片视频文字，以转载和分享为主，文章观点不代表本站立场，本站不承担相关法律责任；如果涉及侵权请联系邮箱：360163164@qq.com举报，并提供相关证据，经查实将立刻删除涉嫌侵权内容。
微信扫一扫 
