[20111230]11Gr2审计.txt
昨天查看v$open_cursor视图，无意中发现如下语句:
INSERT INTO SYS.aud$
(sessionid, entryid, STATEMENT, ntimestamp#, userid, userhost, terminal, action#, returncode, obj$creator,
obj$name, auth$privileges, auth$grantee, new$owner, new$name, ses$actions, ses$tid, logoff$pread, logoff$lwrite,
logoff$dead, comment$text, spare1, spare2, priv$used, clientid, sessioncpu, proxy$sid, user$guid, instance#,
process#, xid, SCN, auditid, sqlbind, sqltext, obj$edition, dbid
)
VALUES (:1, :2, :3, SYS_EXTRACT_UTC (SYSTIMESTAMP), :4, :5, :6, :7, :8, :9,
:10, :11, :12, :13, :14, :15, :16, :17, :18,
:19, :20, :21, :22, :23, :24, :25, :26, :27, :28,
:29, :30, :31, :32, :33, :34, :35, :36
)
感觉很奇怪，难道11GR2缺省是打开审计功能吗？
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------------------------------------
audit_file_dest string /u01/app/oracle/admin/test/adump
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string DB
使用toad自带sqlmonitor跟踪，看看关联哪些视图：
SELECT owner, object_type, object_name, object_id, status
FROM SYS.dba_objects
WHERE object_id IN (SELECT object_id
FROM public_dependency
CONNECT BY PRIOR object_id = referenced_object_id
START WITH referenced_object_id = (SELECT object_id
FROM SYS.dba_objects
WHERE wner = :owner AND object_name = :NAME AND object_type = :TYPE))
AND wner = 'SYS'
:OWNER = 'SYS'
:NAME = 'AUD$'
:TYPE = 'TABLE'
OWNER,OBJECT_TYPE,OBJECT_NAME,OBJECT_ID,STATUS
=============================================
SYS,VIEW,DBA_AUDIT_TRAIL,3466,VALID
SYS,VIEW,USER_AUDIT_TRAIL,3468,VALID
SYS,VIEW,DBA_AUDIT_SESSION,3470,VALID
SYS,VIEW,USER_AUDIT_SESSION,3472,VALID
SYS,VIEW,DBA_AUDIT_STATEMENT,3474,VALID
SYS,VIEW,USER_AUDIT_STATEMENT,3476,VALID
SYS,VIEW,DBA_AUDIT_OBJECT,3478,VALID
SYS,VIEW,USER_AUDIT_OBJECT,3480,VALID
SYS,VIEW,DBA_AUDIT_EXISTS,3482,VALID
SYS,VIEW,DBA_COMMON_AUDIT_TRAIL,5348,VALID
我记忆11GR1版本并没有启动审计，而11GR2 AUDIT_TRAIL参数的缺省值为DB，如果对于生产系统如果设计不好，会对基表sys.aud$产生大量的操作，
而且sys.aud$位于system表空间，会导致system表空间占用太大，最好将它移动到单独的表空间，便于维护与管理。
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
看看做一些什么审计(我的是测试环境,如果是生产系统logon,logoff会很多)：
SQL> select action_name,count(*) from dba_audit_trail group by action_name order by 2 desc;
ACTION_NAME COUNT(*)
---------------------------- ----------
LOGON 1403
LOGOFF 1116
ALTER SYSTEM 110
CREATE PUBLIC SYNONYM 26
ALTER USER 16
EXPLAIN 13
SYSTEM GRANT 10
DROP PUBLIC SYNONYM 6
CREATE PROCEDURE 6
ALTER DATABASE 4
GRANT ROLE 4
CREATE ROLE 3
DROP TABLE 3
DROP PROCEDURE 3
CREATE USER 2
ALTER TABLE 2
ALTER PROCEDURE 2
GRANT OBJECT 2
CREATE TABLE 2
DROP USER 1
ALTER PUBLIC SYNONYM 1
ASSOCIATE STATISTICS 1
SYSTEM REVOKE 1
REVOKE ROLE 1
24 rows selected.
我们知道如果登录密码错误，返回错误是ORA-01017,如下：
ERROR:
ORA-01017: invalid username/password; logon denied
$oerr ora 1017
$ oerr ora 1017
01017, 00000, "invalid username/password; logon denied"
// *Cause:
// *Action:
查询如下，可以知道哪些用户longon失败:
select * from dba_audit_trail where returncode = 1017 and trunc(timestamp)>=trunc(sysdate) order by timestamp desc;