ITPub博客

首页 > 数据库 > Oracle > 【USER】oracle查看用户登录失败次数及详细信息

【USER】oracle查看用户登录失败次数及详细信息

原创 Oracle 作者:xysoul_云龙 时间:2014-03-06 23:47:30 0 删除 编辑

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 */

)

 

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

请登录后发表评论 登录
全部评论
主要从事数据库相关工作,其他操作系统、中间件等也有涉及,热衷分享、开源,支持国产,期待中华民族全面的伟大复兴。近日骤然醒悟,欲在IT江湖中闯荡一番,如有幸在诸多侠客中留点踪迹,也算不虚此行。 【文盲筱烨】好读书爱运动的IT技术爱好者 微博:文盲筱烨 微信公众号:筱烨视点

注册时间:2014-02-15

  • 博文量
    168
  • 访问量
    757460