官方文档没有关于这个的说明
错误起因:切换测试用户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
最后采用方法一解决,方法二待研究。。。