ORA-01017

官方文档没有关于这个的说明

错误起因：切换测试用户HR的时候，发生错误

SCOTT@ orcl>conn hr/hr
ERROR:
ORA-01017: invalid username/password; logon denied

因为HR的密码可以确定是正确的，所以查看HR的状态。发现HR的状态是：EXPIRED（过期）

YS@ orcl>select username,account_status from dba_users;

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
MGMT_VIEW                      OPEN
SYS                            OPEN
SYSTEM                         OPEN
DBSNMP                         OPEN
SYSMAN                         OPEN
SCOTT                          OPEN
XZQ                            OPEN
MY_USER                        OPEN
LIB                            OPEN
HR                             EXPIRED
OUTLN                          EXPIRED & LOCKED

那么如何更改EXPIRED（过期）的状态，以下有两种方法

方法一：更改密码。但原密码忘记了怎么办呢？网上搜集到了一种方法：用原密码的密文来更改密码

SYS@ orcl>select password from dba_users where username='HR';

PASSWORD
------------------------------
6399F3B38EDF3288

alter user HR identified by values '6399F3B38EDF3288'

更改之后状态变为了LOCKED(TIMED)

SYS@ orcl>select username,account_status from dba_users;

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
MGMT_VIEW                      OPEN
SYS                            OPEN
SYSTEM                         OPEN
DBSNMP                         OPEN
SYSMAN                         OPEN
SCOTT                          OPEN
XZQ                            OPEN
MY_USER                        OPEN
LIB                            OPEN
HR                             LOCKED(TIMED)
OUTLN                          EXPIRED & LOCKED

将用户解锁就能将状态恢复正常

SYS@ orcl>alter user hr account unlock;

User altered.

再次查看HR的状态，恢复正常

SYS@ orcl>select username,account_status from dba_users where username = 'HR';

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
HR                             OPEN

那么LOCKED(TIMED)的状态产生的原因是什么呢

SYS@ orcl>SELECT resource_name,resource_type,limit FROM dba_profiles WHERE profile='DEFAULT';

RESOURCE_NAME                    RESOURCE LIMIT
-------------------------------- -------- ----------
COMPOSITE_LIMIT                  KERNEL   UNLIMITED
SESSIONS_PER_USER                KERNEL   UNLIMITED
CPU_PER_SESSION                  KERNEL   UNLIMITED
CPU_PER_CALL                     KERNEL   UNLIMITED
LOGICAL_READS_PER_SESSION        KERNEL   UNLIMITED
LOGICAL_READS_PER_CALL           KERNEL   UNLIMITED
IDLE_TIME                        KERNEL   UNLIMITED
CONNECT_TIME                     KERNEL   UNLIMITED
PRIVATE_SGA                      KERNEL   UNLIMITED
<span style="color:#ff0000;">FAILED_LOGIN_ATTEMPTS            PASSWORD 10</span>
PASSWORD_LIFE_TIME               PASSWORD UNLIMITED

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

16 rows selected.

因为在profile中做了登陆失败次数的限制。既登陆10次后锁定用户。

可以扩大登陆次数或者不做限制

1.扩大登陆次数

alter profile default limit FAILED_LOGIN_ATTEMPTS 9999;

2.不做限制

alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;

方法二：更简洁的方法就是将ASTATUS的状态置0

UPDATE USER$ SET ASTATUS=0 WHERE NAME='HR'; 

那么ASTATUS和EXPIRED是什么关系呢

$user系统表存储用户的信息,遗憾的是没有在官方文档中找到$user系统表的说明。这里用SYS用户进行对比。

SYS@ orcl>select username,account_status from dba_users where username in('SYS','HR');  

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
SYS                            OPEN
HR                             EXPIRED

关于ASTATUS的信息在user_astatus_map表中有说明

SYS@ orcl>select * from user_astatus_map;

   STATUS# STATUS
---------- --------------------------------
         0 OPEN
         1 EXPIRED
         2 EXPIRED(GRACE)
         4 LOCKED(TIMED)
         8 LOCKED
         5 EXPIRED & LOCKED(TIMED)
         6 EXPIRED(GRACE) & LOCKED(TIMED)
         9 EXPIRED & LOCKED
        10 EXPIRED(GRACE) & LOCKED

9 rows selected.

所以将状态置0，不要忘记COMMIT。查看状态就变成OPEN了。

SYS@ orcl>UPDATE USER$ SET ASTATUS=0 WHERE NAME='HR';


1 row updated.


SYS@ orcl>commit;


Commit complete.


SYS@ orcl>select username,account_status from dba_users where username in('SYS','HR'); 


USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
HR                             OPEN
SYS                            OPEN

再次连接HR用户，但状态又回到了EXPIRED？？？（问题未解决）

SYS@ orcl>conn hr/hr
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
@ >conn / as sysdba
Connected.
SYS@ orcl>select username,account_status from dba_users where username in('SYS','HR'); 

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
SYS                            OPEN
HR                             EXPIRED

最后采用方法一解决，方法二待研究。。。