eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« 使用errorstack跟踪ORA-01438错误 | Blog首页 | 儿子墨墨记事 - 我家有子初长成 »

10g Profile FAILED_LOGIN_ATTEMPTS如何计数?
modb.pro

Oracle Database 10g在缺省用户管理上有个小的改进,就是FAILED_LOGIN_ATTEMPTS的口令失败尝试变成了10次,这个设置的确有点问题。如果有一个用户不停尝试错误口令,那就会导致用户被锁。

如果恢复之前无限制,可以通过一条命令解决:
alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;

数据库的profiles信息:
SQL> select * from dba_profiles;

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 10
DEFAULT          PASSWORD_LIFE_TIME               PASSWORD UNLIMITED

PROFILE          RESOURCE_NAME                    RESOURCE LIMIT
---------------- -------------------------------- -------- ------------------------------
DEFAULT          PASSWORD_REUSE_TIME              PASSWORD UNLIMITED
DEFAULT          PASSWORD_REUSE_MAX               PASSWORD UNLIMITED
DEFAULT          PASSWORD_VERIFY_FUNCTION         PASSWORD NULL
DEFAULT          PASSWORD_LOCK_TIME               PASSWORD UNLIMITED
DEFAULT          PASSWORD_GRACE_TIME              PASSWORD UNLIMITED

16 rows selected.
可是朋友提出一个问题,当前失败了多少次从何处查询,这个问题我不知道,但是我的习惯是看一下字典表的底层视图,我喜欢用autotrace跟踪一下,那么dba_users这个字典记录用户状态:

SQL> desc dba_users
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERNAME                                  NOT NULL VARCHAR2(30)
 USER_ID                                   NOT NULL NUMBER
 PASSWORD                                           VARCHAR2(30)
 ACCOUNT_STATUS                            NOT NULL VARCHAR2(32)
 LOCK_DATE                                          DATE
 EXPIRY_DATE                                        DATE
 DEFAULT_TABLESPACE                        NOT NULL VARCHAR2(30)
 TEMPORARY_TABLESPACE                      NOT NULL VARCHAR2(30)
 CREATED                                   NOT NULL DATE
 PROFILE                                   NOT NULL VARCHAR2(30)
 INITIAL_RSRC_CONSUMER_GROUP                        VARCHAR2(30)
 EXTERNAL_NAME                                      VARCHAR2(4000)

使用autotrace看一下执行计划(不用也知道底层表是user$):
SQL> set linesize 120
SQL> set autotrace trace explain
SQL> select count(*) from dba_users;

Execution Plan
----------------------------------------------------------
Plan hash value: 3102810126

-------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                         |     1 |    78 |    29  (11)| 00:00:01 |
|   1 |  SORT AGGREGATE             |                         |     1 |    78 |            |          |
|*  2 |   HASH JOIN                 |                         |    14 |  1092 |    29  (11)| 00:00:01 |
|*  3 |    HASH JOIN                |                         |    14 |  1050 |    22  (14)| 00:00:01 |
|*  4 |     HASH JOIN               |                         |    14 |  1008 |    14  (15)| 00:00:01 |
|*  5 |      HASH JOIN OUTER        |                         |    14 |   966 |    12  (17)| 00:00:01 |
|*  6 |       HASH JOIN             |                         |    14 |   616 |     9  (12)| 00:00:01 |
|*  7 |        HASH JOIN            |                         |    14 |   588 |     7  (15)| 00:00:01 |
|   8 |         MERGE JOIN CARTESIAN|                         |     1 |    16 |     4   (0)| 00:00:01 |
|*  9 |          TABLE ACCESS FULL  | PROFILE$                |     1 |     8 |     2   (0)| 00:00:01 |
|  10 |          BUFFER SORT        |                         |     1 |     8 |     2   (0)| 00:00:01 |
|* 11 |           TABLE ACCESS FULL | PROFILE$                |     1 |     8 |     2   (0)| 00:00:01 |
|* 12 |         TABLE ACCESS FULL   | USER$                   |    20 |   520 |     2   (0)| 00:00:01 |
|  13 |        TABLE ACCESS FULL    | PROFNAME$               |     1 |     2 |     2   (0)| 00:00:01 |
|* 14 |       TABLE ACCESS FULL     | RESOURCE_GROUP_MAPPING$ |     2 |    50 |     2   (0)| 00:00:01 |
|  15 |      TABLE ACCESS FULL      | USER_ASTATUS_MAP        |     9 |    27 |     2   (0)| 00:00:01 |
|  16 |     TABLE ACCESS FULL       | TS$                     |    23 |    69 |     7   (0)| 00:00:01 |
|  17 |    TABLE ACCESS FULL        | TS$                     |    23 |    69 |     7   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("U"."TEMPTS#"="TTS"."TS#")
   3 - access("U"."DATATS#"="DTS"."TS#")
   4 - access("U"."ASTATUS"="M"."STATUS#")
   5 - access("CGM"."VALUE"(+)="U"."NAME")
   6 - access("U"."RESOURCE$"="P"."PROFILE#")
   7 - access("U"."RESOURCE$"="PR"."PROFILE#")
   9 - filter("DP"."RESOURCE#"=1 AND "DP"."TYPE#"=1 AND "DP"."PROFILE#"=0)
  11 - filter("PR"."RESOURCE#"=1 AND "PR"."TYPE#"=1)
  12 - filter("U"."TYPE#"=1)
  14 - filter("CGM"."STATUS"(+)='ACTIVE' AND "CGM"."ATTRIBUTE"(+)='ORACLE_USER')
再来看底层表的字段,其中一个字段立刻引起了我的注意lcount:
SQL> desc user$
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 USER#                                                             NOT NULL NUMBER
 NAME                                                              NOT NULL VARCHAR2(30)
 TYPE#                                                             NOT NULL NUMBER
 PASSWORD                                                                   VARCHAR2(30)
 DATATS#                                                           NOT NULL NUMBER
 TEMPTS#                                                           NOT NULL NUMBER
 CTIME                                                             NOT NULL DATE
 PTIME                                                                      DATE
 EXPTIME                                                                    DATE
 LTIME                                                                      DATE
 RESOURCE$                                                         NOT NULL NUMBER
 AUDIT$                                                                     VARCHAR2(38)
 DEFROLE                                                           NOT NULL NUMBER
 DEFGRP#                                                                    NUMBER
 DEFGRP_SEQ#                                                                NUMBER
 ASTATUS                                                           NOT NULL NUMBER
 LCOUNT                                                            NOT NULL NUMBER
 DEFSCHCLASS                                                                VARCHAR2(30)
 EXT_USERNAME                                                               VARCHAR2(4000)
 SPARE1                                                                     NUMBER
 SPARE2                                                                     NUMBER
 SPARE3                                                                     NUMBER
 SPARE4                                                                     VARCHAR2(1000)
 SPARE5                                                                     VARCHAR2(1000)
 SPARE6                                                                     DATE
找个用户测试一下:

SQL> select name,lcount from user$ where name='EYGLEE';

NAME                               LCOUNT
------------------------------ ----------
EYGLEE                                  0

SQL> connect eyglee/ee
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> connect / as sysdba
Connected.
SQL> select name,lcount from user$ where name='EYGLEE';

NAME                               LCOUNT
------------------------------ ----------
EYGLEE                                  1
看来就是这个字段了,LCOUNT---Login attempts COUNT,这个命名和我们自己编程的变量命名是那么相似。

-The End-


历史上的今天...
    >> 2010-07-14文章:
    >> 2008-07-14文章:
    >> 2007-07-14文章:
           Linux单磁盘IO速度概要
    >> 2006-07-14文章:

By eygle on 2009-07-14 20:30 | Comments (3) | FAQ | Oracle12c/11g | 2339 |

3 Comments

研究得好细.

大师就是大师啊,我曾经就遇见这个问题,老师想查一下,但是就是没有做下去,这就是差距啊,敬仰ing

我也遇到这个问题,怕设置了unlimited 怕有安全隐患,设少了,出现了两次用户被锁的情况


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com