Oracle 檢視使用者登入失敗次數及詳細資訊

一、概述

在管理資料庫時，我們總是發現有些使用者被莫名其妙的鎖了，有是因為過期、有的是手動，但也有一部分不太確定原因，當然，我們可能知道由於其他人員嘗試登入造成使用者鎖定，那麼怎麼定位呢？下面我們來看一下

二、具體步驟

檢視使用者資訊

sys@XYSOUL> select username,lock_date,profile,EXPIRY_DATE from dba_users order by EXPIRY_DATE desc;

USERNAME LOCK_DATE PROFILE EXPIRY_DATE

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

XYSOUL DEFAULT 2014-05-05 21:01:53

SYSTEM DEFAULT 2014-04-23 09:26:23

SYS DEFAULT 2014-04-23 09:26:22

APEX_030200 2014-02-22 10:24:08 DEFAULT 2014-02-22 10:24:08

OWBSYS 2014-02-22 10:24:08 DEFAULT 2014-02-22 10:24:08

SYSMAN 2014-02-22 10:24:08 DEFAULT 2014-02-22 10:24:08

ANONYMOUS 2014-02-22 10:24:08 DEFAULT 2014-02-22 10:24:08

CTXSYS 2014-02-22 10:24:08 DEFAULT 2014-02-22 10:24:08

OWBSYS_AUDIT 2014-02-22 10:24:08 DEFAULT 2014-02-22 10:24:08

FLOWS_FILES 2014-02-22 10:24:08 DEFAULT 2014-02-22 10:24:08

MGMT_VIEW 2014-02-22 10:24:08 DEFAULT 2014-02-22 10:24:08

APEX_PUBLIC_USER 2014-02-22 10:24:08 DEFAULT 2014-02-22 10:24:08

MDDATA 2014-02-22 10:24:08 DEFAULT 2014-02-22 10:24:08

SPATIAL_CSW_ADMIN_USR 2014-02-22 10:04:36 DEFAULT 2014-02-22 10:04:36

SPATIAL_WFS_ADMIN_USR 2014-02-22 10:04:30 DEFAULT 2014-02-22 10:04:30

OLAPSYS 2014-02-22 09:59:37 DEFAULT 2014-02-22 09:59:37

ORDDATA 2014-02-22 09:52:06 DEFAULT 2014-02-22 09:52:06

SI_INFORMTN_SCHEMA 2014-02-22 09:52:06 DEFAULT 2014-02-22 09:52:06

MDSYS 2014-02-22 09:52:06 DEFAULT 2014-02-22 09:52:06

ORDSYS 2014-02-22 09:52:06 DEFAULT 2014-02-22 09:52:06

ORDPLUGINS 2014-02-22 09:52:06 DEFAULT 2014-02-22 09:52:06

XS$NULL 2014-02-22 09:51:39 DEFAULT 2014-02-22 09:51:39

XDB 2014-02-22 09:47:03 DEFAULT 2014-02-22 09:47:03

EXFSYS 2014-02-22 09:45:33 DEFAULT 2014-02-22 09:45:33

WMSYS 2014-02-22 09:37:45 DEFAULT 2014-02-22 09:37:45

APPQOSSYS 2014-02-22 09:36:49 DEFAULT 2014-02-22 09:36:49

DBSNMP 2014-02-22 09:36:47 DEFAULT 2014-02-22 09:36:47

ORACLE_OCM 2014-02-22 09:30:25 DEFAULT 2014-02-22 09:30:25

DIP 2014-02-22 09:29:06 DEFAULT 2014-02-22 09:29:06

OUTLN 2014-02-22 09:26:24 DEFAULT 2014-02-22 09:26:24

檢視預設PROFILE的策略

sys@XYSOUL>select * from dba_profiles where profile='DEFAULT';

PROFILE RESOURCE_NAME RESOURCE LIMIT

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

DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED

DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED

DEFAULT CPU_PER_SESSION KERNEL UNLIMITED

DEFAULT CPU_PER_CALL KERNEL UNLIMITED

DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED

DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED

DEFAULT IDLE_TIME KERNEL UNLIMITED

DEFAULT CONNECT_TIME KERNEL UNLIMITED

DEFAULT PRIVATE_SGA KERNEL UNLIMITED

DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 3

DEFAULT PASSWORD_LIFE_TIME PASSWORD 60

DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED

DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED

DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL

DEFAULT PASSWORD_LOCK_TIME PASSWORD 1

DEFAULT PASSWORD_GRACE_TIME PASSWORD 7

檢視XYSOUL使用者登入的失敗次數

sys@XYSOUL> select lcount from user$ where name='XYSOUL';

LCOUNT

----------

1

再次測試，檢視

ys@XYSOUL> conn xysoul/abc

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

sys@XYSOUL> conn / as sysdba

Connected.

sys@XYSOUL> select lcount from user$ where name='XYSOUL';

LCOUNT

----------

2

又一次嘗試連線時，我們發現，錯誤已不是密碼無效，而是使用者已鎖

sys@XYSOUL> conn xysoul/abc

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

sys@XYSOUL>

sys@XYSOUL> conn xysoul/xysoul

ERROR:

ORA-28000: the account is locked

連線，檢視使用者資訊,發現使用者已鎖

sys@XYSOUL> conn / as sysdba

Connected.

sys@XYSOUL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

sys@XYSOUL> set lines 150

sys@XYSOUL> select username,lock_date,profile,EXPIRY_DATE from dba_users where username=’XYSOUL’ order by EXPIRY_DATE desc;

USERNAME LOCK_DATE PROFILE EXPIRY_DATE

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

XYSOUL 2014-03-06 21:09:28 DEFAULT 2014-05-05 21:01:53

下面開啟審計，並檢視審計資訊

sys@XYSOUL> audit session whenever not successful;

Audit succeeded.

-------檢視當前審計資訊
sys@XYSOUL> set lines 200

sys@XYSOUL> col userhost for a20

sys@XYSOUL> col COMMENT$TEXT for a30

sys@XYSOUL> col SPARE1 for a20

sys@XYSOUL> col NTIMESTAMP# for a35

sys@XYSOUL> select sessionid,userid,userhost,comment$text,spare1,to_char(ntimestamp#+1/3,'yyyy-mm-dd hh24:mi:ss') from aud$ where returncode=1017 order by ntimestamp# desc;

SESSIONID USERID USERHOST COMMENT$TEXT SPARE1 TO_CHAR(NTIMESTAMP#

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

1840093 XYSOUL oradb1 Authenticated by: DATABASE oracle 2014-03-06 21:09:28

1840092 XYSOUL oradb1 Authenticated by: DATABASE oracle 2014-03-06 21:08:36

1840091 XYSOUL oradb1 Authenticated by: DATABASE oracle 2014-03-06 21:07:10

380001 DBSNMP oradb1 Authenticated by: DATABASE; Cl oem 2014-02-22 09:36:33

ient address: (ADDRESS=(PROTOC

OL=tcp)(HOST=192.168.8.121)(PO

RT=46286))

透過PL/SQL等工具連線，檢視審計資訊，如下所示，aud$檢視可以查出登入失敗的時間、客戶端等資訊，

sys@XYSOUL> select sessionid,userid,userhost,comment$text,spare1,to_char(ntimestamp#+1/3,'yyyy-mm-dd hh24:mi:ss') from aud$ where returncode=1017 order by ntimestamp# desc;

SESSIONID USERID USERHOST COMMENT$TEXT SPARE1 TO_CHAR(NTIMESTAMP#

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

1850096 XYSOUL WORKGROUP\XYSOUL-PC Authenticated by: DATABASE; Cl xysoul 2014-03-06 21:18:03

ient address: (ADDRESS=(PROTOC

OL=tcp)(HOST=192.168.8.1)(PORT

=56041))

1850093 XYSOUL oradb1 Authenticated by: DATABASE; Cl oracle 2014-03-06 21:15:22

ient address: (ADDRESS=(PROTOC

OL=tcp)(HOST=192.168.8.121)(PO

RT=58016))

1840093 XYSOUL oradb1 Authenticated by: DATABASE oracle 2014-03-06 21:09:28

1840092 XYSOUL oradb1 Authenticated by: DATABASE oracle 2014-03-06 21:08:36

1840091 XYSOUL oradb1 Authenticated by: DATABASE oracle 2014-03-06 21:07:10

380001 DBSNMP oradb1 Authenticated by: DATABASE; Cl oem 2014-02-22 09:36:33

ient address: (ADDRESS=(PROTOC

OL=tcp)(HOST=192.168.8.121)(PO

RT=46286))

三、總結：
透過上述測試，發現幾個檢視並非我們常用的檢視，也不太好查詢檢視中列的作用，以上檢視資訊在指令碼sql.bsq中，由於oracle11g指令碼更加詳細，可透過簡拼定位檢視。

附：user$和aud$ 列資訊說明

create table user$ /* user table */

( user# number not null, /* user identifier number */

name varchar2("M_IDEN") not null, /* name of user */

/* 0 = role, 1 = user, 2 = adjunct schema, 3 = schema synonym */

type# number not null,

password varchar2("M_IDEN"), /* encrypted password */

datats# number not null, /* default tablespace for permanent objects */

tempts# number not null, /* default tablespace for temporary tables */

ctime date not null, /* user account creation time */

ptime date, /* password change time */

exptime date, /* actual password expiration time */

ltime date, /* time when account is locked */

resource$ number not null, /* resource profile# */

audit$ varchar2("S_OPFL"), /* user audit options */

defrole number not null, /* default role indicator: */

/* 0 = no roles, 1 = all roles granted, 2 = roles in defrole$ */

defgrp# number, /* default undo group */

defgrp_seq# number, /* global sequence number for the grp *

spare varchar2("M_IDEN"), /* reserved for future */

astatus number default 0 not null, /* status of the account */

/* 0x00 = 0 = Open */

/* 0x01 = 1 = Locked */

/* 0x02 = 2 = Expired */

/* 0x03 = 3 = Locked and Expired */

/* 0x10 = 16 = Password matches a default value */

lcount number default 0 not null, /* count of failed login attempts */

defschclass varchar2("M_IDEN"), /* initial consumer group */

ext_username varchar2("M_VCSZ"), /* external username */

/* also as base schema name for adjunct schemas */

spare1 number, /* used for schema level supp. logging: see ktscts.h */

/* spare2 is used to store */

/* - edition id for adjunct schemas (type# = 2) */

/* - base schema id for schema synonyms (type# = 3) */

aud$

create table aud$ /* audit trail table */

( sessionid number not null, /* server session id */

entryid number not null, /* serial number to audit records */

statement number not null, /* sql statement number */

timestamp# date, /* OBSOLETE: 10iR1 and above: time of query */

userid varchar2("M_IDEN"), /* database username */

userhost varchar2("M_HOST"), /* client host machine name */

terminal varchar2("M_TERM"), /* client terminal id */

action# number not null, /* action responsible for auditing */

returncode number not null, /* return code for the action */

obj$creator varchar2("M_IDEN"), /* schema where object resides */

obj$name varchar2("M_XDBI"), /* name of the object */

auth$privileges varchar2("S_PRFL"), /* granted privileges */

auth$grantee varchar2("M_IDEN"), /* grantee username */

new$owner varchar2("M_IDEN"), /* schema of the dependent object */

new$name varchar2("M_XDBI"), /* name of the dependent object */

ses$actions varchar2("S_ACFL"), /* success/failure of each action */

ses$tid number, /* object id */

logoff$lread number, /* number of logical reads in the session */

logoff$pread number, /* number of physical reads in the session */

logoff$lwrite number, /* number of logical writes in the session */

logoff$dead number, /* number of deadlocks in the session */

logoff$time date, /* session duration */

comment$text varchar2("M_VCSZ"), /* type authentication/trigger/protocol */

clientid varchar2(64), /* user defined client identifier */

spare1 varchar2(255), /* OS user name */

spare2 number, /* whether this table (aud$) is modified */

obj$label raw(255), /* OBSOLETE: 8.0 and above */

ses$label raw(255), /* OBSOLETE: 8.0 and above */

priv$used number, /* system privlege used */

sessioncpu number, /* total cpu time for the session */

ntimestamp# timestamp, /* new timestamp (in UTC) of query */

proxy$sid number, /* proxy session serial number */

user$guid varchar2(32), /* global user identifier */

instance# number, /* instance number */

process# varchar2("M_PIDL"), /* OS process id */

xid raw(8), /* transaction identifier */

auditid varchar2(64), /* audit operation id */

scn number, /* SCN of the query */

dbid number, /* database identifier for source db */

sqlbind clob, /* bind variables for the query */

sqltext clob, /* sql text of the query */

obj$edition varchar2("M_IDEN") /* Object edition name */

)

【USER】oracle檢視使用者登入失敗次數及詳細資訊

相關文章