Oracle标准数据库审核

2025-04-14 技术教程

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 createany table;

audit createany trigger;

审核某用户的系统权限操作，如

audit selectany table by scott;

访问自己的表时不会做审核。

审核用户的创建和删除

audit createuser, drop user;

查开启的系统权限审核，通过数据字典dba_priv_audit_opts，11g默认会开启以下审核

col user_namefor a20

col proxy_namefor a20

col privilegefor a30

col successfor a20

col failurefor a20

select * fromdba_priv_audit_opts;

USER_NAME PROXY_NAME PRIVILEGE SUCCESS FAILURE

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

CREATE EXTERNALJOB BY ACCESS BY ACCESS

CREATE ANYJOB BYACCESS BY ACCESS

GRANT ANYOBJECT PRIVILEGE BYACCESS BY ACCESS

EXEMPT ACCESSPOLICY BY ACCESS BY ACCESS

CREATE ANYLIBRARY BYACCESS BY ACCESS

GRANT ANYPRIVILEGE BYACCESS BY ACCESS

DROPPROFILE BYACCESS BY ACCESS

ALTERPROFILE BYACCESS BY ACCESS

DROP ANYPROCEDURE BYACCESS BY ACCESS

ALTER ANYPROCEDURE BYACCESS BY ACCESS

CREATE ANYPROCEDURE BYACCESS BY ACCESS

ALTERDATABASE BYACCESS BY ACCESS

GRANT ANYROLE BYACCESS BY ACCESS

CREATE PUBLICDATABASE LINK BY ACCESS BY ACCESS

DROP ANYTABLE BYACCESS BY ACCESS

ALTER ANYTABLE BYACCESS BY ACCESS

CREATE ANYTABLE BYACCESS BY ACCESS

DROP USER BY ACCESS BY ACCESS

ALTER USER BY ACCESS BY ACCESS

CREATEUSER BYACCESS BY ACCESS

CREATESESSION BYACCESS BY ACCESS

AUDITSYSTEM BYACCESS BY ACCESS

ALTERSYSTEM BYACCESS BY ACCESS

2）对象权限审核

对所有用户（不包括sys，sys是不审核的），如

aduitalter, delete, drop, insert on scott.emp;

对某个用户，如

audit selecton hr.employees by scott;

对所有操作，如

audit all onhr.employees;

查开启的对象审核，通过数据字典dba_obj_audit_opts，默认是都没有开启

select * fromdba_obj_audit_opts;

OWNER OBJECT_NAME OBJECT_TYPE ALTAUD COM DELGRA IND INSLOC REN SELUPD REF EXE CREREA WRI FBK

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

3）语句审核

如审核表的所有DDL操作

audit table;

查开启的语句审核，通过数据字典dba_stmt_audit_opts，11g默认会开启以下审核，其中也包含了上述属于系统权限的审核

col user_namefor a20

col proxy_namefor a20

colaudit_option for a30

col successfor a20

col failurefor a20

select * fromdba_stmt_audit_opts;

USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE

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

ALTERSYSTEM BYACCESS BY ACCESS

SYSTEMAUDIT BYACCESS BY ACCESS

CREATESESSION BYACCESS BY ACCESS

CREATEUSER BYACCESS BY ACCESS

ALTER USER BY ACCESS BY ACCESS

DROP USER BY ACCESS BY ACCESS

PUBLICSYNONYM BYACCESS BY ACCESS

DATABASELINK BYACCESS BY ACCESS

ROLE BYACCESS BY ACCESS

PROFILE BYACCESS BY ACCESS

CREATE ANYTABLE BYACCESS BY ACCESS

ALTER ANYTABLE BYACCESS BY ACCESS

DROP ANYTABLE BYACCESS BY ACCESS

CREATE PUBLICDATABASE LINK BY ACCESS BY ACCESS

GRANT ANYROLE BYACCESS BY ACCESS

SYSTEMGRANT BYACCESS BY ACCESS

ALTERDATABASE BYACCESS BY ACCESS

CREATE ANYPROCEDURE BYACCESS BY ACCESS

ALTER ANYPROCEDURE BYACCESS BY ACCESS

DROP ANYPROCEDURE BYACCESS BY ACCESS

ALTERPROFILE BYACCESS BY ACCESS

DROPPROFILE BYACCESS BY ACCESS

GRANT ANYPRIVILEGE BYACCESS BY ACCESS

CREATE ANYLIBRARY BYACCESS BY ACCESS

EXEMPT ACCESSPOLICY BY ACCESS BY ACCESS

GRANT ANYOBJECT PRIVILEGE BYACCESS BY ACCESS

CREATE ANYJOB BYACCESS BY ACCESS

CREATE EXTERNALJOB BY ACCESS BY ACCESS

4）其它审核配置

审核会话登录

audit session;

这与审核create session权限的使用效果相同。

取消审核，通过noaudit命令指定

noauditsession;

noaudit all onscott.emp;

审核成功的操作，通过whenever successful选项指定，如审核表的成功插入

audit inserton scott.emp whenever successful;

审核不成功的操作，通过whenever not successful选项指定，如审核失败的会话登录

audit sessionwhenever not successful;

默认情况下是审核所有的操作，不论成功与否。

会话级别上的审核，通过by session选项指定

audit updateon scott.emp by session;

操作级别上的审核，通过by access选项指定

audit updateon 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.emp1as select * from scott.emp;

grant all on scott.emp1to hr;

2、启用审核

audit all on scott.emp1by access;

audit table;

alter system setaudit_sys_operations=true scope=spfile;

alter system setaudit_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 audr1identified by audr1;

drop user audr1;

create table scott.emp2as select * from scott.emp1;

select * from scott.emp2;

drop table scott.emp2purge;

查看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 audr1identified by audr1;

grant connect to audr1;

create table audr1.t1(nnumber);

select * from audr1.t1;

drop table audr1.t1purge;

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 audr1identified by audr1;

grant connect to audr1;

audr1用户登录操作

select * from scott.emp1;

由于没有给audr1访问scott.emp1的权限，因此以上查询将失败

audr1用户退出登录

hr用户登录操作

select * from scott.emp1;

update scott.emp1 setsal=2000 where empno=7369;

commit;

update scott.emp1 setsal=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 updatescott.emp1 set sal=2500 where empno=7369

Administrator HR WORKGROUP\MYPC 2017-09-20 10:42:17 SCOTT EMP1 6 UPDATE updatescott.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 onscott.emp1;

hr用户再次登录操作

select * from scott.emp1;

hr用户退出登录

查看审核记录，确认审核只有用户的登入登出，其它已取消。如果要将会话的登入登出记录也取消，则执行noauditsession，但这样一来，默认的系统权限审核将不再包括该项，除非执行audit session重新加入。

7、清空aud$记录，改为会话级别的审核，并查看结果

audit all on scott.emp1by session;

再次以hr用户登录并操作

select * from scott.emp1;

update scott.emp1 setsal=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 updatescott.emp1 set sal=800 where empno=7369

Administrator HR WORKGROUP\MYPC 2017-09-20 10:54:36 SCOTT EMP1 103 SESSION REC select * fromscott.emp1

Administrator HR WORKGROUP\MYPC 2017-09-20 10:54:29 100 LOGON CREATE SESSION

比较可知，操作级别的审核明确记录了action_name为select、update等，而会话级别的审核action_name只标明为sessionrec，但sql_text仍记录了会话中每一步操作的SQL语句。

8、取消审核，清理恢复

noaudit all onscott.emp1;

drop user audr1;

drop table scott.emp1purge;

alter system setaudit_trail=false scope=spfile;

alter system setaudit_sys_operations=false scope=spfile;