联系：手机/微信(+86 17813235971) QQ(107644445)
标题：PASSWORD_ROLLOVER_TIME—实现新老密码短期共存
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
在oracle老版本中(特别是11g版本),你可能多少都遇到过应用修改密码,但是由于系统运行应用较多,在修改应用密码的过程中有业务忘记修改密码,从而导致业务无法正常使用,甚至由于密码延迟认证特性导致数据库hang住,对于这些问题,oracle 从19.12开始引入了PASSWORD_ROLLOVER_TIME,可以在这个profile限制时间内,新老密码都可以登录数据库,避免了上述问题.
在手上有的23ai的数据库中进行测试,创建一个测试用户,使用的是default profile,PASSWORD_ROLLOVER_TIME为0(没有启用)
SQL> select banner from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free SQL> create user xff identified by oracle; User created. SQL> grant dba to xff; Grant succeeded. SQL> select profile from dba_users where username='XFF'; PROFILE ------------------------------ DEFAULT SQL> select profile,limit from dba_profiles where resource_name='PASSWORD_ROLLOVER_TIME'; PROFILE LIMIT ------------------------------ ------------------------------ DEFAULT 0 ORA_CIS_PROFILE DEFAULT ORA_STIG_PROFILE DEFAULT
尝试修改密码,尝试登录(老密码无法登录成功)
[oracle@192 oradata]$ sqlplus xff/oracle@127.0.0.1/freepdb1 SQL*Plus: Release 23.0.0.0.0 - Production on Fri May 24 12:58:31 2024 Version 23.4.0.24.05 Copyright (c) 1982, 2024, Oracle. All rights reserved. Connected to: Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Version 23.4.0.24.05 SQL> alter user xff identified by xifenfei; User altered. SQL> exit Disconnected from Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Version 23.4.0.24.05 [oracle@192 oradata]$ sqlplus xff/oracle@127.0.0.1/freepdb1 SQL*Plus: Release 23.0.0.0.0 - Production on Fri May 24 12:58:49 2024 Version 23.4.0.24.05 Copyright (c) 1982, 2024, Oracle. All rights reserved. ERROR: ORA-01017: invalid credential or not authorized; logon denied Help: https://docs.oracle.com/error-help/db/ora-01017/ Enter user-name: ERROR: ORA-01017: invalid credential or not authorized; logon denied Help: https://docs.oracle.com/error-help/db/ora-01017/ Enter user-name: ERROR: ORA-01017: invalid credential or not authorized; logon denied Help: https://docs.oracle.com/error-help/db/ora-01017/ SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus Help: https://docs.oracle.com/error-help/db/sp2-0157/
修改PASSWORD_ROLLOVER_TIME和密码尝试登录(新老密码都可以登录)
[oracle@192 oradata]$ sqlplus xff/xifenfei@127.0.0.1/freepdb1 SQL*Plus: Release 23.0.0.0.0 - Production on Fri May 24 12:58:58 2024 Version 23.4.0.24.05 Copyright (c) 1982, 2024, Oracle. All rights reserved. Last Successful login time: Fri May 24 2024 12:58:31 +00:00 Connected to: Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Version 23.4.0.24.05 SQL> alter profile default limit password_rollover_time 1/24; ----修改值单位为天,最小1小时 Profile altered. SQL> select profile,limit from dba_profiles where resource_name='PASSWORD_ROLLOVER_TIME'; PROFILE -------------------------------------------------------------------------------- LIMIT -------------------------------------------------------------------------------- DEFAULT .0416 ----显示值也为天 ORA_CIS_PROFILE DEFAULT ORA_STIG_PROFILE DEFAULT SQL> alter user xff identified by orasos; User altered. SQL> exit Disconnected from Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Version 23.4.0.24.05 [oracle@192 oradata]$ sqlplus xff/xifenfei@127.0.0.1/freepdb1 SQL*Plus: Release 23.0.0.0.0 - Production on Fri May 24 13:02:49 2024 Version 23.4.0.24.05 Copyright (c) 1982, 2024, Oracle. All rights reserved. Last Successful login time: Fri May 24 2024 12:58:58 +00:00 Connected to: Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Version 23.4.0.24.05 SQL> exit Disconnected from Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Version 23.4.0.24.05 [oracle@192 oradata]$ sqlplus xff/orasos@127.0.0.1/freepdb1 SQL*Plus: Release 23.0.0.0.0 - Production on Fri May 24 13:02:56 2024 Version 23.4.0.24.05 Copyright (c) 1982, 2024, Oracle. All rights reserved. Last Successful login time: Fri May 24 2024 13:02:49 +00:00 Connected to: Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Version 23.4.0.24.05 SQL>
禁用该用户PASSWORD_ROLLOVER_TIME功能(强制禁止老用户登录,新用户依然可以登录)
[oracle@192 oradata]$ sqlplus xff/orasos@127.0.0.1/freepdb1 SQL*Plus: Release 23.0.0.0.0 - Production on Fri May 24 13:02:56 2024 Version 23.4.0.24.05 Copyright (c) 1982, 2024, Oracle. All rights reserved. Last Successful login time: Fri May 24 2024 13:02:49 +00:00 Connected to: Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Version 23.4.0.24.05 SQL> ALTER USER xff EXPIRE PASSWORD ROLLOVER PERIOD; User altered. SQL> exit Disconnected from Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Version 23.4.0.24.05 [oracle@192 oradata]$ sqlplus xff/xifenfei@127.0.0.1/freepdb1 SQL*Plus: Release 23.0.0.0.0 - Production on Fri May 24 13:04:50 2024 Version 23.4.0.24.05 Copyright (c) 1982, 2024, Oracle. All rights reserved. ERROR: ORA-01017: invalid credential or not authorized; logon denied Help: https://docs.oracle.com/error-help/db/ora-01017/ Enter user-name: ^C^C [oracle@192 oradata]$ sqlplus xff/orasos@127.0.0.1/freepdb1 SQL*Plus: Release 23.0.0.0.0 - Production on Fri May 24 13:04:56 2024 Version 23.4.0.24.05 Copyright (c) 1982, 2024, Oracle. All rights reserved. Last Successful login time: Fri May 24 2024 13:02:56 +00:00 Connected to: Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Version 23.4.0.24.05 SQL>