更改ORACLE 用户的 expired状态
oracle中, 经常用户的状态会变成locked, expired 等状态, 这种情况下怎么处理呢?
首先, 如果是locked状态还好办, DBA直接执行alter user scott account unlock 就可以了.
但是如果变成expired状态, oracle本身是不提供解锁的语句的, 因为账户过期,必须要用户更改密码, 账户才能重新使用. 但有些时候, 因为各种原因, 我们并不知道原密码的明文是什么,这时候就很麻烦了, 经研究发现,有两种方法可以实现:
一. 用原密码的密文来更改密码:
[sql] view plaincopyprint?
01.SQL>conn /as sysdba
02.SQL>select password from dba_users where username='SCOTT';
03.password
04.------------------------------
05.E65E6AF62B2449CF
06.
07.SQL>alter user SCOTT identified by values 'E65E6AF62B2449CF';
SQL>conn /as sysdba
SQL>select password from dba_users where username='SCOTT';
password
------------------------------
E65E6AF62B2449CF
SQL>alter user SCOTT identified by values 'E65E6AF62B2449CF';
这种情况下,虽然我们不知道原密码是什么,但可以用它的密文来更改密码,这样,在不知道原密码的情况下,既保持了密码不改变, 又可以把expired的状态更改掉.
二. 下面这一条语句搞定,不管用户的状态是什么:
[sql] view plaincopyprint?
01.UPDATE USER$ SET ASTATUS=0 WHERE NAME='SCOTT';
UPDATE USER$ SET ASTATUS=0 WHERE NAME='SCOTT';
原理详解:
用户的信息都是存在user$这样一个系统表里面的
[sql] view plaincopyprint?
01.SQL> select name,ASTATUS,password from user$ where name IN('SYS','SCOTT');
02.
03.
04.NAME ASTATUS PASSWORD
05.------------------------------ ---------- ------------------------------
06.SCOTT 1 E65E6AF62B2449CF
07.SYS 0 8A8F025737A9097A
08.
09.SQL> select username,account_status from dba_users where username in('SYS','SCOTT');
10.
11.USERNAME ACCOUNT_STATUS
12.------------------------------ --------------------------------
13.SYS OPEN
14.SCOTT EXPIRED
SQL> select name,ASTATUS,password from user$ where name IN('SYS','SCOTT');
NAME ASTATUS PASSWORD
------------------------------ ---------- ------------------------------
SCOTT 1 E65E6AF62B2449CF
SYS 0 8A8F025737A9097A
SQL> select username,account_status from dba_users where username in('SYS','SCOTT');
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
SYS OPEN
SCOTT EXPIRED
而用户ASTATUS对应的表为:user_astatus_map
[sql] view plaincopyprint?
01.SQL> select * from user_astatus_map;
02.
03. STATUS# STATUS
04.---------- --------------------------------
05. 0 OPEN
06. 1 EXPIRED
07. 2 EXPIRED(GRACE)
08. 4 LOCKED(TIMED)
09. 8 LOCKED
10. 5 EXPIRED & LOCKED(TIMED)
11. 6 EXPIRED(GRACE) & LOCKED(TIMED)
12. 9 EXPIRED & LOCKED
13. 10 EXPIRED(GRACE) & LOCKED
14.
15.9 rows selected.
SQL> 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.
关键是user$表本身是可以更改的, 所以此时我们就可以直接把用户的ASTATUS字段改成0就可以了
[sql] view plaincopyprint?
01.SQL> UPDATE USER$ SET ASTATUS=0 WHERE NAME='SCOTT';
02.1 row updated.
03.
04.SQL> COMMIT;
05.Commit complete.
06.
07.SQL> alter system flush shared_pool;
08.System altered.
SQL> UPDATE USER$ SET ASTATUS=0 WHERE NAME='SCOTT';
1 row updated.
SQL> COMMIT;
Commit complete.
SQL> alter system flush shared_pool;
System altered.
再来观察结果:
[sql] view plaincopyprint?
01.SQL> select name,ASTATUS,password from user$ where name IN('SYS','SCOTT');
02.
03.NAME ASTATUS PASSWORD
04.------------------------------ ---------- ------------------------------
05.SCOTT 0 E65E6AF62B2449CF
06.SYS 0 8A8F025737A9097A
07.
08.SQL> select username,account_status from dba_users where username in('SYS','SCOTT');
09.
10.USERNAME ACCOUNT_STATUS
11.------------------------------ --------------------------------
12.SCOTT OPEN
13.SYS OPEN
SQL> select name,ASTATUS,password from user$ where name IN('SYS','SCOTT');
NAME ASTATUS PASSWORD
------------------------------ ---------- ------------------------------
SCOTT 0 E65E6AF62B2449CF
SYS 0 8A8F025737A9097A
SQL> select username,account_status from dba_users where username in('SYS','SCOTT');
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
SCOTT OPEN
SYS OPEN
