Oracle审计失败的用户登陆(Oracle audit)(三)
RMINAL:[13] 'DEVELOPERPC01'
STATUS:[1] '0' ---->登陆成功的状态码
DBID:[10] '3456778221'
C:\Users\robinson.cheng>sqlplus sys/wrongpwd@usbo as sysdba
[oracle@linux1 adump]$ more usbo_ora_13677_1.aud
Audit file /u03/database/usbo/adump/usbo_ora_13677_1.aud
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/db_1
System name: Linux
Node name: linux1.orasrv.com
Release: 2.6.18-194.el5PAE
Version: #1 SMP Mon Mar 29 20:19:03 EDT 2010
Machine: i686
Instance name: usbo
Redo thread mounted by this instance: 1
Oracle process number: 31
Unix process pid: 13677, image: oracle@linux1.orasrv.com
Tue Oct 22 15:44:59 2013 +08:00
LENGTH : '181'
ACTION :[7] 'CONNECT'
DATABASE USER:[3] 'sys'
PRIVILEGE :[4] 'NONE'
CLIENT USER:[14] 'Robinson.Cheng'
CLIENT TERMINAL:[13] 'DEVELOPERPC01'
STATUS:[4] '1017' ---->登陆失败的状态码1017
DBID:[10] '3456778221'
--下面使用普通的帐户登陆，没有相应的os审计文件，但是被添加到了表SYS.AUD$
C:\Users\robinson.cheng>sqlplus scott/tg@usbo
--Author : Leshami
--Blog : http://blog.csdn.net/leshami
sys@USBO> select sessionid,userid,userhost,comment$text,spare1,ntimestamp# from aud$ where returncode=1017;
SESSIONID USERID USERHOST COMMENT$TEXT SPARE1 NTIMESTAMP#
---------- ------ ------------------------- ---------------------------------------- ------------------ -------------------------------
1470011 SCOTT TRADESZ\DEVELOPERPC01 Authenticated by: DATABASE; Client addre Robinson.Cheng 21-OCT-13 08.51.15.528497 AM
ss: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168
.7.133)(PORT=53432))
1480153 SCOTT TRADESZ\DEVELOPERPC01 Authenticated by: DATABASE; Client addre Robinson.Cheng 22-OCT-13 08.06.49.012661 AM
ss: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168
.7.133)(PORT=60613))
1480154 SCOTT TRADESZ\DEVELOPERPC01 Authenticated by: DATABASE; Client addre Robinson.Cheng 22-OCT-13 08.09.41.927143 AM
ss: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168
.7.133)(PORT=60622))
5、使用过程分析失败登陆的审计记录
[sql]
CREATE OR REPLACE PROCEDURE auditlogin (since VARCHAR2, times PLS_INTEGER)
IS
user_id VARCHAR2 (20);
CURSOR c1
IS
SELECT userid, COUNT (*)
FROM sys.aud$
WHERE returncode = '1017' AND ntimestamp# >= TO_DATE (since, 'yyyy-mm-dd')
GROUP BY userid;
CURSOR c2
IS
SELECT userhost, terminal, TO_CHAR (ntimestamp#, 'YYYY-MM-DD:HH24:MI:SS')
FROM sys.aud$
WHERE returncode = '1017' AND ntimestamp# >= TO_DATE (since, 'yyyy-mm-dd') AND userid = user_id;
ct PLS_INTEGER;
v_userhost VARCHAR2 (40);
v_terminal VARCHAR (40);
v_date VARCHAR2 (40);
BEGIN
OPEN c1;
DBMS_OUTPUT.enable (1024000);
LOOP
FETCH c1
INTO user_id, ct;
EX