#### 1.概述

在管理資料庫時，我們總是發現有些使用者被莫名其妙的鎖了，有是因為過期、有的是手動，但也有一部分不太確定原因，當然，我們可能知道由於其他人員嘗試登入造成使用者鎖定，那麼怎麼定位呢？下面我們來看一下
 
####  2.具體步驟
``` sql
--檢視使用者資訊
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
```

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

``` sql
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
```

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

``` sql
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$檢視可以查出登入失敗的時間、客戶端等資訊，

``` sql
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))
```

#### 3.	總結：

透過上述測試，發現幾個檢視並非我們常用的檢視，也不太好查詢檢視中列的作用，以上檢視資訊在指令碼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 檢視使用者登入失敗次數及詳細資訊

相關文章