ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORA-28000: the account is locked

ORA-28000: the account is locked

原创 Linux操作系统 作者:zhanglei_itput 时间:2009-07-14 18:35:55 0 删除 编辑


    最近两个朋友问我同样的问题,他们发现数据库里面有一个账户总是莫名其妙的被锁住,不知道是什么原因。
    我首先想到的是用户default profiles中的failed_login_attempts参数设置问题,然后扩展的问题是这个参数的精确含义及相关值查询。
    测试结果如下:
   
    1. 查询failed_login_attempts参数默认值:
       10g (备注:9i环境中此参数的值为unlimited)
       SQL> conn / as sysdba
       Connected.
       SQL> desc dba_profiles;
        Name                                      Null?    Type
        ----------------------------------------- -------- ----------------------------
        PROFILE                                   NOT NULL VARCHAR2(30)
        RESOURCE_NAME                             NOT NULL VARCHAR2(32)
        RESOURCE_TYPE                                      VARCHAR2(8)
        LIMIT                                              VARCHAR2(40)
       
       SQL> select resource_name, limit from dba_profiles where resource_name = 'FAILED_LOGIN_ATTEMPTS';
       
       RESOURCE_NAME                    LIMIT
       -------------------------------- ----------------------------------------
       FAILED_LOGIN_ATTEMPTS            10
      
       1 rows selected.
     
     2. 模拟账户被锁现象
        (为方便模拟lock现象,修改default profile failed_login_attempts=3 )
        a.修改参数failed_login_attempts=3
          SQL> conn / as sysdba;
          Connected.
          SQL> alter profile default limit failed_login_attempts 3;
          Profile altered.

         
        b.重现错误登陆 
          正确登陆
          SQL> conn ecc_view/ecc@devdb1
          Connected.
          SQL> conn
ecc_view/ecc@devdb1
          Connected.
          第一次登陆失败
          SQL> conn ecc_view/hh@devdb1
          ERROR:
          ORA-01017: invalid username/password; logon denied
          Warning: You are no longer connected to ORACLE.
          第二次登陆失败
          SQL> conn ecc_view/hh@devdb1
          ERROR:
          ORA-01017: invalid username/password; logon denied
          第三次登陆失败
          SQL> conn ecc_view/hh@devdb1
          ERROR:
          ORA-01017: invalid username/password; logon denied
          连续3次登陆失败后,账户被锁住了
          SQL> conn ecc_view/hh@devdb1
          ERROR:
          ORA-28000: the account is locked
         
     3. 解锁
         SQL> conn / as sysdba
         Connected.
         SQL> alter user ecc_view account unlock;
         User altered.
        
     4. 解决方案
           (1) 可以考虑查询应用部署中错误的password或者数据库连接等可能导致错误password的地方,彻底的查询问题所在。
           (2) 修改参数failed_login_attempts=unlimited
               SQL> alter profile default limit failed_login_attempts unlimited;
               Profile altered.               
               SQL> select resource_name, limit from dba_profiles where resource_name = 'FAILED_LOGIN_ATTEMPTS';
               
               RESOURCE_NAME                    LIMIT
               -------------------------------- ----------------------------------------
               FAILED_LOGIN_ATTEMPTS            UNLIMITED
              
     5. 扩展知识点及备注说明
        (1)  Q: FAILED_LOGIN_ATTEMPTS=3 3的含义是什么?是累计失败次数还是连续失败次数?
               A: FAILED_LOGIN_ATTEMPTS=3的含义是从第一次登录失败开始计算,连续登陆失败的次数。而不是累计失败的次数。
                试验如下:
                SQL> conn ecc_view/ecc@devdb1
                Connected.
                第一次登陆失败
                SQL> conn ecc_view/hh@devdb1
                ERROR:
                ORA-01017: invalid username/password; logon denied
                Warning: You are no longer connected to ORACLE.
             
   第二次登陆失败
                SQL> conn ecc_view/hh@devdb1
                ERROR:
                ORA-01017: invalid username/password; logon denied
                正确登陆
                SQL> conn ecc_view/ecc@devdb1
                Connected.
                此时不是累计,而是重新计算
                第一次登陆失败
                SQL> conn ecc_view/hh@devdb1
                ERROR:
                ORA-01017: invalid username/password; logon denied
                Warning: You are no longer connected to ORACLE.
                第二次登陆失败
                SQL> conn ecc_view/hh@devdb1
                ERROR:
                ORA-01017: invalid username/password; logon denied
                第三次登陆失败
                SQL> conn ecc_view/hh@devdb1
                ERROR:
               ORA-01017: invalid username/password; logon denied

                三次登陆失败后,账户被锁
                SQL>  conn ecc_view/ecc@devdb1
                ERROR:
                ORA-28000: the account is locked
               
        (2)  Q: 如何从数据库中查询当前FAILED_LOGIN_ATTEMPTS的值?dba_profiles是限额,并不代表当前值,如果查询当前失败的值怎么查?
               A: select NAME,LCOUNT  from user$,user$为view dba_users的基表,通常可以查询一下试图对应的基表,oracle可能会隐藏一些参数
            
             初始值为0:
             SQL> select NAME,LCOUNT  from user$ where name = 'ECC_VIEW'; 
                  NAME                               LCOUNT
                  ------------------------------ ----------
                  ECC_VIEW                                0
                 
             错误登陆一次后,值为1
             SQL> conn ecc_view/h@devdb1
                  ERROR:                     
                  ORA-01017: invalid username/password; logon denied
            SQL> select NAME,LCOUNT  from user$ where name = 'ECC_VIEW';   
                  NAME                               LCOUNT
                  ------------------------------ ----------
                  ECC_VIEW                                1
                 
             错误登陆2次后,值为2
             SQL> conn ecc_view/h@devdb1
                  ERROR:                    
                  ORA-01017: invalid username/password; logon denied
             SQL> select NAME,LCOUNT  from user$ where name = 'ECC_VIEW';  
                  NAME                               LCOUNT
                  ------------------------------ ----------
                  ECC_VIEW                                2
                  
             正确登陆一次后,此值重置为0
             SQL> conn ecc_view/ecc@devdb1
                  Connected.              
             SQL> select NAME,LCOUNT  from user$ where name = 'ECC_VIEW';  
                  NAME                               LCOUNT
                  ------------------------------ ----------
                  ECC_VIEW                                0
                         
            
 参考文献:
1.  http://space.itpub.net/519536/viewspace-608769

2. eygle大师的指点(呵呵,呼呼,啊啊 )

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

下一篇: 随笔-人生
请登录后发表评论 登录
全部评论

注册时间:2009-02-10

  • 博文量
    400
  • 访问量
    1108278