可以审计三种类型的对象：1系统权限；2对象权限；3语句审计（按照某种关键字进行审计）
开启系统数据库审计，查询审计参数所有值及修改状态；
SQL> select * from v$parameter_valid_values where name like 'audit%';
NUM NAME ORDINAL VALUE ISDEFAULT
---------- ---------------------------------------------------------------- ---------- -------------------------------------------------------------------------------- ----------------------------------------------------------------
1587 audit_trail 1 DB FALSE
1587 audit_trail 2 OS FALSE
1587 audit_trail 3 NONE FALSE
1587 audit_trail 4 TRUE FALSE
1587 audit_trail 5 FALSE FALSE
1587 audit_trail 6 DB_EXTENDED FALSE
1587 audit_trail 7 XML FALSE
1587 audit_trail 8 EXTENDED FALSE
8 rows selected
SQL> select * from v$parameter where name = 'audit_trail';
NUM NAME TYPE VALUE DISPLAY_VALUE ISDEFAULT ISSES_MODIFIABLE ISSYS_MODIFIABLE ISINSTANCE_MODIFIABLE ISMODIFIED ISADJUSTED ISDEPRECATED ISBASIC DESCRIPTION UPDATE_COMMENT HASH
---------- -------------------------------------------------------------------------------- ---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------- ---------------- ---------------- --------------------- ---------- ---------- ------------ ------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------
1587 audit_trail 2 DB DB FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE enable system auditing 4289193100
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string D:\APP\CALVIN9\ADMIN\TEST\ADUM
P
audit_sys_operations boolean FALSE
audit_trail string DB
SQL> alter parameter set audit_trial = db_extendSQL> alter system set audit_trail = DB_EXTENDED scope=spfile;
System altered.
SQL> startup;
ORACLE instance started.
Total System Global Area 1.0689E+10 bytes
Fixed Size 2185160 bytes
Variable Size 5435820088 bytes
Database Buffers 5234491392 bytes
Redo Buffers 16977920 bytes
Database mounted.
Database opened.SQL> select * from dba_priv_audit_opts;
Warning: connection was lost and re-established
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
USER_NAME PROXY_NAME PRIVILEGE SUCCESS FAILURE
------------------------------ ------------------------------ ---------------------------------------- ---------- ----------
CREATE SESSION BY ACCESS BY ACCESS
AUDIT SYSTEM BY ACCESS BY ACCESS
ALTER SYSTEM BY ACCESS BY ACCESS
23 rows selectedSQL> audit select any table by calvin whenever successful;
Audit succeeded
SQL> create user calvin1 identified by calvin1;
User created
SQL> grant dba to calvin1;
Grant succeededSQL> conn calvin1/calvin1@test
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as calvin1@test
SQL> create table test ( num integer, id varchar(20));
Table created
SQL> insert into test values (1,'1');
1 row inserted
SQL> commit;
Commit completeSQL> select * from calvin1.test;
NUM ID
--------------------------------------- --------------------
1 1
SQL> select * from dba_audit_trail;
OS_USERNAME USERNAME USERHOST TERMINAL TIMESTAMP OWNER OBJ_NAME ACTION ACTION_NAME NEW_OWNER NEW_NAME OBJ_PRIVILEGE SYS_PRIVILEGE ADMIN_OPTION GRANTEE AUDIT_OPTION SES_ACTIONS LOGOFF_TIME LOGOFF_LREAD LOGOFF_PREAD LOGOFF_LWRITE LOGOFF_DLOCK COMMENT_TEXT SESSIONID ENTRYID STATEMENTID RETURNCODE PRIV_USED CLIENT_ID ECONTEXT_ID SESSION_CPU EXTENDED_TIMESTAMP PROXY_SESSIONID GLOBAL_UID INSTANCE_NUMBER OS_PROCESS TRANSACTIONID SCN SQL_BIND SQL_TEXT OBJ_EDITION_NAME DBID
-------------------------------------------------------------------------------- ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------- ------------------------------ -------------------------------------------------------------------------------- ---------- ---------------------------- ------------------------------ -------------------------------------------------------------------------------- ---------------- ---------------------------------------- ------------ ------------------------------ ---------------------------------------- ------------------- ----------- ------------ ------------ ------------- ---------------------------------------- -------------------------------------------------------------------------------- ---------- ---------- ----------- ---------- ---------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ----------- -------------------------------------------------------------------------------- --------------- -------------------------------- --------------- ---------------- ---------------- ---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------ ----------
homepc\calvin CALVIN WORKGROUP\HOMEPC HOMEPC 2013/9/1 16 104 SYSTEM AUDIT CALVIN SELECT ANY TABLE 201262 3 70 0 AUDIT SYSTEM 01-SEP-13 04.45.15.069000 PM +08:00 0 1716:13488 02000400D8060000 2762855 2118303218
audit select any table by calvin whenever successful
homepc\calvin CALVIN WORKGROUP\HOMEPC HOMEPC 2013/9/1 16 CALVIN1 51 CREATE USER 201262 4 86 0 CREATE USER 01-SEP-13 04.45.34.722000 PM +08:00 0 1716:13488 0900040015070000 2762877 2118303218
create user calvin1 identified by ********
homepc\calvin CALVIN WORKGROUP\HOMEPC HOMEPC 2013/9/1 16 108 SYSTEM GRANT UNLIMITED TABLESPACE - CALVIN1 201262 5 114 0 GRANT ANY ROLE 01-SEP-13 04.45.38.847000 PM +08:00 0 1716:13488 0400160053060000 2762887 2118303218
grant dba to calvin1
homepc\calvin CALVIN WORKGROUP\HOMEPC HOMEPC 2013/9/1 16 DBA 114 GRANT ROLE - CALVIN1 201262 6 114 0 01-SEP-13 04.45.38.847000 PM +08:00 0 1716:13488 0400160053060000 2762887 2118303218
grant dba to calvin1
homepc\calvin CALVIN WORKGROUP\HOMEPC HOMEPC 2013/9/1 16 101 LOGOFF 2013/9/1 16 5092 845 126 0 201262 7 1 0 23 01-SEP-13 04.45.46.336000 PM +08:00 0 1716:13488 2118303218
homepc\calvin CALVIN WORKGROUP\HOMEPC HOMEPC 2013/9/1 16 CALVIN1 TEST 103 SESSION REC ---------S------ 201308 1 17 0 SELECT ANY TABLE 01-SEP-13 04.47.58.113000 PM +08:00 0 1716:13140 2763159 2118303218
select * from calvin1.test
6 rows selected撤销之前的系统审计；
SQL> noaudit select any table by calvin;
Noaudit succeeded测试对象权限审计；创建对象审计项目，查看审计表结果；
SQL> audit select on calvin1.test;
Audit succeeded
SQL> select * from calvin1.test;
NUM ID
--------------------------------------- --------------------
1 1
SQL> 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
------------------------------ ------------------------------ ----------------------- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- --- ----- ----- ----- ----- -----
CALVIN1 TEST TABLE -/- -/- -/- -/- -/- -/- -/- -/- -/- S/S -/- -/- -/- -/- -/- -/- -/-
SQL> select * from dba_audit_trail where owner = 'CALVIN1';
OS_USERNAME USERNAME USERHOST TERMINAL TIMESTAMP OWNER OBJ_NAME ACTION ACTION_NAME NEW_OWNER NEW_NAME OBJ_PRIVILEGE SYS_PRIVILEGE ADMIN_OPTION GRANTEE AUDIT_OPTION SES_ACTIONS LOGOFF_TIME LOGOFF_LREAD LOGOFF_PREAD LOGOFF_LWRITE LOGOFF_DLOCK COMMENT_TEXT SESSIONID ENTRYID STATEMENTID RETURNCODE PRIV_USED CLIENT_ID ECONTEXT_ID SESSION_CPU EXTENDED_TIMESTAMP PROXY_SESSIONID GLOBAL_UID INSTANCE_NUMBER OS_PROCESS TRANSACTIONID SCN SQL_BIND SQL_TEXT OBJ_EDITION_NAME DBID
-------------------------------------------------------------------------------- ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------- ------------------------------ -------------------------------------------------------------------------------- ---------- ---------------------------- ------------------------------ -------------------------------------------------------------------------------- ---------------- ---------------------------------------- ------------ ------------------------------ ---------------------------------------- ------------------- ----------- ------------ ------------ ------------- ---------------------------------------- -------------------------------------------------------------------------------- ---------- ---------- ----------- ---------- ---------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ----------- -------------------------------------------------------------------------------- --------------- -------------------------------- --------------- ---------------- ---------------- ---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------ ----------
homepc\calvin A/A 代表成功access失败access
测试语句审计；打开语句审计，语句审计和权限审计有交叉，有些系统权限审计也属于语句审计；
SQL> audit table by calvin;
Audit succeeded.
SQL> select * from dba_stmt_audit_opts;
USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE
------------------------------ ------------------------------ ---------------------------------------- ---------- ----------
CALVIN TABLE BY ACCESS BY ACCESS
SELECT ANY TABLE BY SESSION BY SESSIONSQL> create table test5(id int);
Table created
SQL> select * from dba_audit_trail;
OS_USERNAME USERNAME USERHOST TERMINAL TIMESTAMP OWNER OBJ_NAME ACTION ACTION_NAME NEW_OWNER NEW_NAME OBJ_PRIVILEGE SYS_PRIVILEGE ADMIN_OPTION GRANTEE AUDIT_OPTION SES_ACTIONS LOGOFF_TIME LOGOFF_LREAD LOGOFF_PREAD LOGOFF_LWRITE LOGOFF_DLOCK COMMENT_TEXT SESSIONID ENTRYID STATEMENTID RETURNCODE PRIV_USED CLIENT_ID ECONTEXT_ID SESSION_CPU EXTENDED_TIMESTAMP PROXY_SESSIONID GLOBAL_UID INSTANCE_NUMBER OS_PROCESS TRANSACTIONID SCN SQL_BIND SQL_TEXT OBJ_EDITION_NAME DBID
-------------------------------------------------------------------------------- ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------- ------------------------------ -------------------------------------------------------------------------------- ---------- ---------------------------- ------------------------------ -------------------------------------------------------------------------------- ---------------- ---------------------------------------- ------------ ------------------------------ ---------------------------------------- ------------------- ----------- ------------ ------------ ------------- ---------------------------------------- -------------------------------------------------------------------------------- ---------- ---------- ----------- ---------- ---------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ----------- -------------------------------------------------------------------------------- --------------- -------------------------------- --------------- ---------------- ---------------- ---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------ ----------
homepc\calvin CALVIN WORKGROUP\HOMEPC HOMEPC 2013/9/1 17 SYS AUD$ 7 DELETE 201308 5 343 0 01-SEP-13 05.05.06.786000 PM +08:00 0 1716:13140 0100090046060000 2768279 2118303218
delete from sys.aud$
homepc\calvin CALVIN WORKGROUP\HOMEPC HOMEPC 2013/9/1 17 CALVIN1 TEST 103 SESSION REC ---------S------ 201308 6 429 0 SELECT ANY TABLE 01-SEP-13 05.06.28.441000 PM +08:00 0 1716:13140 2768396 2118303218
select * from calvin1.test
homepc\calvin CALVIN WORKGROUP\HOMEPC HOMEPC 2013/9/1 17 SYS AUD$ 7 DELETE 201308 3 257 0 01-SEP-13 05.02.44.613000 PM +08:00 0 1716:13140 02001200D9060000 2765558 2118303218
delete from sys.aud$
尝试创建表，其中命令包含table关键字，但是并没有审计，可能有问题，具体还需要详细测试；
找到问题，打开审计之后，被审计的用户需要重新登录；
再次测试：
SQL> conn calvin/123456@test
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as calvin@TEST
SQL> create table test6 (id int);
Table createdSQL> select * from dba_audit_trail where obj_name = 'TEST6';
OS_USERNAME USERNAME USERHOST TERMINAL TIMESTAMP OWNER OBJ_NAME ACTION ACTION_NAME NEW_OWNER NEW_NAME OBJ_PRIVILEGE SYS_PRIVILEGE ADMIN_OPTION GRANTEE AUDIT_OPTION SES_ACTIONS LOGOFF_TIME LOGOFF_LREAD LOGOFF_PREAD LOGOFF_LWRITE LOGOFF_DLOCK COMMENT_TEXT SESSIONID ENTRYID STATEMENTID RETURNCODE PRIV_USED CLIENT_ID ECONTEXT_ID SESSION_CPU EXTENDED_TIMESTAMP PROXY_SESSIONID GLOBAL_UID INSTANCE_NUMBER OS_PROCESS TRANSACTIONID SCN SQL_BIND SQL_TEXT OBJ_EDITION_NAME DBID
-------------------------------------------------------------------------------- ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------- ------------------------------ -------------------------------------------------------------------------------- ---------- ---------------------------- ------------------------------ -------------------------------------------------------------------------------- ---------------- ---------------------------------------- ------------ ------------------------------ ---------------------------------------- ------------------- ----------- ------------ ------------ ------------- ---------------------------------------- -------------------------------------------------------------------------------- ---------- ---------- ----------- ---------- ---------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ----------- -------------------------------------------------------------------------------- --------------- -------------------------------- --------------- ---------------- ---------------- ---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------ ----------
homepc\calvin CALVIN WORKGROUP\HOMEPC HOMEPC 2013/9/1 20 CALVIN TEST6 1 CREATE TABLE 201753 1 19 0 CREATE TABLE 01-SEP-13 08.26.37.736000 PM +08:00 0 1716:6276 07001B0073060000 2794030 2118303218
dba_audit_trail中的记录源于基表sys.aud$,sys.aud$是oracle系统中为数不多的可以允许我们直接删除记录的数据字典表，可以执行类似delete from sys.aud$命令；
3244
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
