[20111230]11Gr2审计.txt

[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;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-714204/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-714204/