ORA-28000: the account is locked


    最近两个朋友问我同样的问题,他们发现数据库里面有一个账户总是莫名其妙的被锁住,不知道是什么原因。
    我首先想到的是用户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大师的指点(呵呵,呼呼,啊啊 003.gif)

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

转载于:http://blog.itpub.net/9252210/viewspace-609169/

  • 0
    点赞
  • 0
    收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值