ORA-28007: the password cannot be reused 如何破

首页 » Cloud, ORACLE 9i-23ai » ORA-28007: the password cannot be reused 如何破

ORA-28007: the password cannot be reused 如何破

2022/02/18
Cloud, ORACLE 9i-23ai
382 views
ORA-28007: the password cannot be reused 如何破已关闭评论

今天在测试一个小功能时发现个人用户密码已过期，当然这时只能去更改密码，改密码时递归更新密码最后更改时间来改变用户状态，此时profile延长过期天数PASSWORD_LIFE_TIME已无法解决，但是大多数需求是希望是通过改密码的动作清除过期标记又不变原密码，当然这时又受到了user profile中PASSWORD_REUSE_TIME的限制，你可能已想到了可以使用alter user identifed by VALUES ‘’; 但是这里也有个小细节。

Oracle 12c 关于密码(password)的几个新特性小结之前记录过12c以后密码引入新的hash算法, 对于没有10的密码版本在dba_user.password列为空，使用identified by values改密码时可以参考 user$.spare4列，该列又是有多个版本的hash值比如S: 或T: 两部分,有S:用于11g,T:用于12c以后。下面直接上测试环境oracle 19.3

SQL> alter user anbob identified by anbob;
alter user anbob identified by anbob
*
ERROR at line 1:
ORA-28007: the password cannot be reused

SQL> alter user anbob identified by anbob_12345;
User altered.

SQL> alter user anbob identified by anbob;
alter user anbob identified by anbob
*
ERROR at line 1:
ORA-28007: the password cannot be reused

提示：

密码无法重用，且原密码hash值已被替换，我们可以尝试创建个其它用户使用相同的密码，利用新生的hash值试试是否可以？

SQL> create user u11 identified by anbob;
User created.

SQL> select spare4 from user$ where name='U11';

SPARE4
---------------------------------------------------------------------------------------------------
S:B1ED0377396B42B94D79C14F4B0D7566B657EFF83C4EEA0FFD45D8CA57A8;T:EBFA128291183CF3D8404179C02FA6AA54305CA1CE58426E4DB13167E938C8D192340C607C196046B8B9744576564BE08EC6D8AC8D27E23684DD2763DCC3EB39DE97E317E79BF01B484E854C08DC3C38

SQL> alter user anbob identified by values 'S:B1ED0377396B42B94D79C14F4B0D7566B657EFF83C4EEA0FFD45D8CA57A8;';
User altered.

[oracle@oel7db1 ~]$ sqlplus anbob/anbob@cdb1pdb1

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Feb 18 10:50:17 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Last Successful login time: Fri Feb 18 2022 10:04:00 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>

注：
只使用hash中的第一部分S：的hash值是可以的。我们再用全hash值试试

SQL> alter user anbob identified by values 'S:B1ED0377396B42B94D79C14F4B0D7566B657EFF83C4EEA0FFD45D8CA57A8;T:EBFA128291183CF3D8404179C02FA6AA54305CA1CE58426E4DB13167E938C8D192340C607C196046B8B9744576564BE08EC6D8AC8D27E23684DD2763DCC3EB39DE97E317E79BF01B484E854C08DC3C38';

User altered.

[oracle@oel7db1 ~]$ sqlplus anbob/anbob@cdb1pdb1

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Feb 18 10:50:17 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Fri Feb 18 2022 10:04:00 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>

Note:
使用spare4全hash值这也是常用的方法，当然是可以的，那这种方法多次更改受reuse限制吗？

SQL> alter user anbob identified by values 'S:B1ED0377396B42B94D79C14F4B0D7566B657EFF83C4EEA0FFD45D8CA57A8;T:EBFA128291183CF3D8404179C02FA6AA54305CA1CE58426E4DB13167E938C8D192340C607C196046B8B9744576564BE08EC6D8AC8D27E23684DD2763DCC3EB39DE97E317E79BF01B484E854C08DC3C38';
alter user anbob identified by values 'S:B1ED0377396B42B94D79C14F4B0D7566B657EFF83C4EEA0FFD45D8CA57A8;T:EBFA128291183CF3D8404179C02FA6AA54305CA1CE58426E4DB13167E938C8D192340C607C196046B8B9744576564BE08EC6D8AC8D27E23684DD2763DCC3EB39DE97E317E79BF01B484E854C08DC3C38'
*
ERROR at line 1:
ORA-28007: the password cannot be reused

SQL> alter user anbob identified by values 'S:B1ED0377396B42B94D79C14F4B0D7566B657EFF83C4EEA0FFD45D8CA57A8;';
User altered.

SQL> alter user anbob identified by values 'S:B1ED0377396B42B94D79C14F4B0D7566B657EFF83C4EEA0FFD45D8CA57A8;T:EBFA128291183CF3D8404179C02FA6AA54305CA1CE58426E4DB13167E938C8D192340C607C196046B8B9744576564BE08EC6D8AC8D27E23684DD2763DCC3EB39DE97E317E79BF01B484E854C08DC3C38';
alter user anbob identified by values 'S:B1ED0377396B42B94D79C14F4B0D7566B657EFF83C4EEA0FFD45D8CA57A8;T:EBFA128291183CF3D8404179C02FA6AA54305CA1CE58426E4DB13167E938C8D192340C607C196046B8B9744576564BE08EC6D8AC8D27E23684DD2763DCC3EB39DE97E317E79BF01B484E854C08DC3C38'
*
ERROR at line 1:
ORA-28007: the password cannot be reused

SQL> alter user anbob identified by anbob;
alter user anbob identified by anbob
*
ERROR at line 1:
ORA-28007: the password cannot be reused

SQL> alter user anbob identified by values 'S:B1ED0377396B42B94D79C14F4B0D7566B657EFF83C4EEA0FFD45D8CA57A8;';
User altered.

SQL> alter user anbob identified by values 'S:B1ED0377396B42B94D79C14F4B0D7566B657EFF83C4EEA0FFD45D8CA57A8;';
User altered.

NOTE:
使用第一部分S，可以多次重用，但是全hash和密码一样会提示无法复用。那使用第二部分T:呢：

SQL> alter user anbob identified by values 'T:EBFA128291183CF3D8404179C02FA6AA54305CA1CE58426E4DB13167E938C8D192340C607C196046B8B9744576564BE08EC6D8AC8D27E23684DD2763DCC3EB39DE97E317E79BF01B484E854C08DC3C38';
alter user anbob identified by values 'T:EBFA128291183CF3D8404179C02FA6AA54305CA1CE58426E4DB13167E938C8D192340C607C196046B8B9744576564BE08EC6D8AC8D27E23684DD2763DCC3EB39DE97E317E79BF01B484E854C08DC3C38'
*
ERROR at line 1:
ORA-28007: the password cannot be reused

Note:
即使只使用T:部分hash 也是提示密码无法reused, 判断密码reused必定需要记录历史，做个sql trace不难发现记录在user_history$表。

--enable 10046 trace 
alter user xx identified by xxx;

grep history xxx.trc

select password from user_history$ where user# = :1
insert into user_history$(user#, password, password_date)   values (:1, :2, :3)

SQL> @us u1
Show database usernames from dba_users matching %u1%

USERNAME                  DEFAULT_TABLESPACE        TEMPORARY_TABLESPACE              USER_ID CREATED             ACCOUNT_STATUS                   PROFILE
------------------------- ------------------------- ------------------------------ ---------- ------------------- -------------------------------- --------------------------------------------------------------------------------------------------------------------------------
U1                        USERS                     TEMP                                  121 2021-10-09 04:21:20 OPEN                             DEFAULT

SQL> delete user_history$ where user#=121;
1 row deleted.

SQL> commit;
Commit complete.

SQL> alter user u1 identified by oracle;
User altered.

SQL> select * from user_history$ where user#=121;
     USER#
----------
PASSWORD
------------------------------------------------------------------------------------------------
PASSWORD_DATE
-------------------
       121
T:5F4CEDE26830BA5F6B3EF66B627B883E945D8889E7D1DE5754AFB6B6E585382DD69CCF9DB7CC42F006BBCF8B407899AB7C394BC22B417F39F3C086716CDEFA8F7D7DC0559417978DE51B782D3528FED7
2022-02-18 14:30:37

SQL> select spare4 from user$ where user#=121;

SPARE4
----------------------------------------------------------------------------------------------
S:9A7FF825EFBD78A1C5ED860FC3088C780F7F6937A50D3DB0EC78DC3BFFD7;T:5F4CEDE26830BA5F6B3EF66B627B883E945D8889E7D1DE5754AFB6B6E585382DD69CCF9DB7CC42F006BBCF8B407899AB7C394BC22B417F39F3C086716CDEFA8F7D7DC0559417978DE51B782D3528FED7

Note:
可以看到更改密码是先查询user_history$是否存在，改成功后会把新生成的密码insert到user_history$一份，但是只有T:部分.

那我们再看下刚才anbob用户的修改历史。

SQL> select PASSWORD from USER_HISTORY$ where user#=106;
PASSWORD
---------------------------------------------------------------------------------
T:BF147920174662BD276FF8DE9B6A46890A756FA02BA088E89913ACB9434A58189A5E65434B9268916977CC3738850253526B0AFD2FB044FA89D97531245FD61DC49758B797C05128EF6CF059869C60C5
T:AB79CEF0BB3392FAF13D4A5C7C58511A6019E95A8AB92C2C3992379D7B7FEBF7787362C487537AEFDBFAC8AB925C2F5E36E39C642E12497390323A64B34B7CBB522C542AC6766B8C533C3694BF223E32
T:5A4B3DF6CB99E373602C746C2A1E63B6E992084D0EEB71B099E042C9F0020F8C130A1DF024CE89041AEC60B335BF6F15EB1DE008E7E88A0F73EE2CCBE3FB736BEFDB8D5AAA7D873246805586F6DE2D93
T:A306056EBE54CBA65699EAD81F1ED10E1E11A07F91EA9F73564B08BFE8E00C4A9CDC3BBEBFE2CF43BB05C3BD40CA0B40CA50ED30402BDB5A87B2FDEB08A5A8219532DFF5FC34678AE7637C4057618055
T:1E39D8D113EAF560659626485613AD570E0638DAF93F48C20B18C1EE19753128350A7745B02FD8DABBD8F9D350D51B033F0B259AD5B9F672EF6FCA8486A7B9D9E5AC67A8E7E13FE4F1A2BF96AB70B16D
T:32AFD96BA43F93813D7D1C0B9AD1013BB5462D1CCB728B1808335A44540A27258737DDCBCD95A27F6834244182CEF387809D363368AAF7ADBE6BA0188347860817CCE51E077FBC91C10C66B5E005FFD2
T:6624621479FE8A249BC6BC449160A3604773F22B5831243677C1C8BFD76D141BEF77EF22706643C56AF445C96B45F6326FAF394D2EA1C9F68039E062EBA549C3871589166FDB06C2B9C14DEB3363020C
S:B1ED0377396B42B94D79C14F4B0D7566B657EFF83C4EEA0FFD45D8CA57A8
T:EBFA128291183CF3D8404179C02FA6AA54305CA1CE58426E4DB13167E938C8D192340C607C196046B8B9744576564BE08EC6D8AC8D27E23684DD2763DCC3EB39DE97E317E79BF01B484E854C08DC3C38
S:B1ED0377396B42B94D79C14F4B0D7566B657EFF83C4EEA0FFD45D8CA57A8
S:B1ED0377396B42B94D79C14F4B0D7566B657EFF83C4EEA0FFD45D8CA57A8
S:B1ED0377396B42B94D79C14F4B0D7566B657EFF83C4EEA0FFD45D8CA57A8

12 rows selected.

Note:
注意可以看到使用identified by values只使用S:部分的也会记录，但不会约束密码复用。

SQL> delete USER_HISTORY$ where user#=106 and PASSWORD like 'T:%';
8 rows deleted.

SQL> commit;
Commit complete.

SQL> alter user anbob identified by anbob;
User altered.

Note:
删掉user_history$中的T:部分的密码历史记录就可以直接复用原密码。哪改密码只用T:部分是否可以呢？

SQL> select spare4 from user$ where user#=106;

SPARE4
--------------------------------------------------------------------------------
S:A5B3B939D517C7A708B14715BCB49A70C627BE9D032BD57835553896DC05;T:A8C8955DA4E42EFC1069F15A6F5A89A0D3C61BF235DEE9366922554A5F27691B81A43812D8C6E7A8A38C1BA1C452372E6C2C5CE339E22935731149A61FB0B41069C06A1109DD04E9464E8DE5E18BA21F

SQL> select * from user_history$ where user#=106;

     USER#
----------
PASSWORD
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
PASSWORD_DATE
-------------------
       106
T:A8C8955DA4E42EFC1069F15A6F5A89A0D3C61BF235DEE9366922554A5F27691B81A43812D8C6E7A8A38C1BA1C452372E6C2C5CE339E22935731149A61FB0B41069C06A1109DD04E9464E8DE5E18BA21F
2022-02-18 14:50:38


SQL> alter user anbob password expire;
User altered.

SQL> select account_status from dba_users where username='ANBOB';
ACCOUNT_STATUS
--------------------------------
EXPIRED

SQL> alter user anbob identified by values 'T:A8C8955DA4E42EFC1069F15A6F5A89A0D3C61BF235DEE9366922554A5F27691B81A43812D8C6E7A8A38C1BA1C452372E6C2C5CE339E22935731149A61FB0B41069C06A1109DD04E9464E8DE5E18BA21F';
alter user anbob identified by values 'T:A8C8955DA4E42EFC1069F15A6F5A89A0D3C61BF235DEE9366922554A5F27691B81A43812D8C6E7A8A38C1BA1C452372E6C2C5CE339E22935731149A61FB0B41069C06A1109DD04E9464E8DE5E18BA21F'
*
ERROR at line 1:
ORA-28007: the password cannot be reused

SQL> delete USER_HISTORY$ where user#=106 and PASSWORD like 'T:%';
1 row deleted.

SQL> commit;
Commit complete.

SQL> alter user anbob identified by values 'T:A8C8955DA4E42EFC1069F15A6F5A89A0D3C61BF235DEE9366922554A5F27691B81A43812D8C6E7A8A38C1BA1C452372E6C2C5CE339E22935731149A61FB0B41069C06A1109DD04E9464E8DE5E18BA21F';
User altered.

SQL> conn anbob/anbob@cdb1pdb1
Connected.

SQL> select account_status from dba_users where username='ANBOB';

ACCOUNT_STATUS
--------------------------------
OPEN

SQL> select spare4 from user$ where user#=106;

SPARE4
-------------------------------------------------------------------------------------------------
T:A8C8955DA4E42EFC1069F15A6F5A89A0D3C61BF235DEE9366922554A5F27691B81A43812D8C6E7A8A38C1BA1C452372E6C2C5CE339E22935731149A61FB0B41069C06A1109DD04E9464E8DE5E18BA21F

Note:
只使用T:部分的密码hash也是可以的，只是密码版本就只有12后的了，会导致低版本的数据库客户端验证失败。之前blog有写过这T部分的hash是有随机码在里面，那生成的hash值就会不一样，如果我们使用之前的相同密码不同hash修复是否也可以呢？

SQL> alter user anbob identified by values 'T:E690A5521F77250BDDA4777B5F9E0497A78E1AC66AF4C9A003577F5442E9B8CA6E2B1F399BCD285872575E55291E5FBCC1F593F15804406AC5D8A8044BBBF626509A9CE3C2D720BE722A5A2822669AF1';
User altered.

[oracle@oel7db1 ~]$ sqlplus anbob/anbob@cdb1pdb1

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Feb 18 15:01:46 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Last Successful login time: Fri Feb 18 2022 14:54:51 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select * from user_history$ where user#=106;

     USER#
----------
PASSWORD
----------------------------------------------------------------------------------
PASSWORD_DATE
-------------------
       106
T:A8C8955DA4E42EFC1069F15A6F5A89A0D3C61BF235DEE9366922554A5F27691B81A43812D8C6E7A8A38C1BA1C452372E6C2C5CE339E22935731149A61FB0B41069C06A1109DD04E9464E8DE5E18BA21F
2022-02-18 14:54:36

       106
T:E690A5521F77250BDDA4777B5F9E0497A78E1AC66AF4C9A003577F5442E9B8CA6E2B1F399BCD285872575E55291E5FBCC1F593F15804406AC5D8A8044BBBF626509A9CE3C2D720BE722A5A2822669AF1
2022-02-18 15:01:38

Note:
使用历史的密码hash值一样不影响连接使用。

Summary:

对于密码失效需要修改密码不能reused的问题，目前除了使用新密码外，我们可以使用identified by values 指定S:部分，或使用相同密码不同的hash值的T:部分，或使用相同的密码创建其它用户生成的密码hash，或手动清理掉user_history$中的历史记录都可以解决。

— enjoy —

打赏

identified by values, ORA-28007

对不起，这篇文章暂时关闭评论。

上一篇： Event# 恩墨大讲堂2022 《Oracle 19c避雷经验分享》

下一篇： 2021年终总结

ANBOB™

ORA-28007: the password cannot be reused 如何破

对不起，这篇文章暂时关闭评论。

最新文章

标签云集

文章索引

MySql Link

ORACLE Link

Others Link

国内好友

管理功能

微信公众号/Wechat