Gradual Database Password Rollover for Applications特性是Oracle 21c引入的功能，主要是为了防止密码修改导致应用连接失败和密码延时验证等特性对数据库的性能产生影响和造成不必要的生产故障，所以允许密码修改有个“过渡期”，密码修改可以逐步进行，修改数据库用户密码，同时在“一定时期”内原来的密码仍然可以使用。在此期间，应用程序可以使用旧密码或新密码成功连接数据库。
该功能建议对sys等特权用户使用，建议生产中业务用户可以使用该功能。
19c中19.12开始支持这个功能，数据库中COMPATIBLE参数是19（或者更大值）。用户密码password version 11g以上支持该功能。
一、在多租户环境中创建具有新特性profile
Gradual Database Password Rollover for Applications 特性需要在profile中使PASSWORD_ROLLOVER_TIME 。
Password Rollover Time Limits
PASSWORD_ROLLOVER_TIME 最小值1小时（1/24）,最大值是60days。
1、CDB中创建公共profile
[oracle@19cdb01 ~]$ export ORACLE_SID=cdb19c [oracle@19cdb01 ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production Wed Dec 21 13:49:07 2022 Version 19.17.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.17.0.0.0 SQL> CREATE PROFILE cdb_profile_userpw LIMIT PASSWORD_ROLLOVER_TIME 1/24; CREATE PROFILE cdb_profile_userpw LIMIT PASSWORD_ROLLOVER_TIME 1/24 * ERROR at line 1: ORA-65140: invalid common profile name
在cdb中创建公共profile需要使用c##前缀 SQL> CREATE PROFILE c##cdb_profile_userpw LIMIT PASSWORD_ROLLOVER_TIME 1/24; Profile created. SQL> 验证cdb中创建的profile col PROFILE FOR a15 col limit FOR a25 SELECT * FROM dba_profiles WHERE PROFILE='C##CDB_PROFILE_USERPW' AND resource_name='PASSWORD_ROLLOVER_TIME'; PROFILE RESOURCE_NAME RESOURCE LIMIT COM INH IMP ------------------------- -------------------------------- -------- -------------------- --- --- --- C##CDB_PROFILE_USERPW PASSWORD_ROLLOVER_TIME PASSWORD 3600 YES NO NO SQL>
2、在pdb中创建本地profile
将容器切换到pdb中
SQL> alter session set container=hrpdb; Session altered. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 3 HRPDB READ WRITE NO SQL> SQL> CREATE PROFILE pdb_profile_userpw LIMIT PASSWORD_ROLLOVER_TIME 1/24; Profile created. SQL> 验证profile SQL> SELECT * FROM dba_profiles WHERE PROFILE='PDB_PROFILE_USERPW' AND resource_name='PASSWORD_ROLLOVER_TIME'; 2 3 4 PROFILE RESOURCE_NAME RESOURCE LIMIT COM INH IMP ------------------------- -------------------------------- -------- -------------------- --- --- --- PDB_PROFILE_USERPW PASSWORD_ROLLOVER_TIME PASSWORD 3600 NO NO NO SQL>
二、在多租户环境中创建用户
1、创建公共用户并授权
SQL> create user c##xfadmin identified by Oracle_123 profile c##cdb_profile_userpw container=all; User created. SQL> SQL> grant connect,resource to c##xfadmin container=all; Grant succeeded. SQL> 连接到cdb容器 [oracle@19cdb01 ~]$ sqlplus c##xfadmin/Oracle_123@19cdb01:1525/cdb19c SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 21 14:04:36 2022 Version 19.17.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Last Successful login time: Wed Dec 21 2022 14:03:54 +08:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.17.0.0.0 SQL> show user USER is "C##XFADMIN" SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> 连接pdb容器 [oracle@19cdb01 ~]$ sqlplus c##xfadmin/Oracle_123@19cdb01:1525/hrpdb SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 21 14:03:54 2022 Version 19.17.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Last Successful login time: Wed Dec 21 2022 14:03:22 +08:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.17.0.0.0 SQL> show user USER is "C##XFADMIN" SQL> show con_name CON_NAME ------------------------------ HRPDB SQL>
2、创建本地用户并授权
SQL> create user hfxf identified by Hfxf_123 profile pdb_profile_userpw; User created. SQL> grant connect,resource to hfxf; Grant succeeded. SQL> [oracle@19cdb01 ~]$ sqlplus hfxf/Hfxf_123@19cdb01:1525/hrpdb SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 21 14:09:34 2022 Version 19.17.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Last Successful login time: Wed Dec 21 2022 14:09:25 +08:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.17.0.0.0 SQL> show con_name CON_NAME ------------------------------ HRPDB SQL> show user USER is "HFXF" SQL>
三、在多租户环境中修改用户密码
1、修改公共用户密码
SQL> alter user c##xfadmin identified by Oracle_456 container=all; User altered. SQL>
2、修改本地用户密码
SQL> alter user hfxf identified by Hfxf_456; User altered.
四、使用新旧密码验证登录
1、验证公共用户登录
[oracle@19cdb01 ~]$ sqlplus c##xfadmin/Oracle_123@19cdb01:1525/cdb19c SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 21 14:11:31 2022 Version 19.17.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Last Successful login time: Wed Dec 21 2022 14:04:36 +08:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.17.0.0.0 SQL> show user USER is "C##XFADMIN" SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.17.0.0.0 [oracle@19cdb01 ~]$ sqlplus c##xfadmin/Oracle_456@19cdb01:1525/cdb19c SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 21 14:11:44 2022 Version 19.17.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Last Successful login time: Wed Dec 21 2022 14:11:31 +08:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.17.0.0.0 SQL> show user USER is "C##XFADMIN" SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL>
2、验证本地用户登录
[oracle@19cdb01 ~]$ sqlplus hfxf/Hfxf_123@19cdb01:1525/hrpdb SQL*Plus: Release 19.0.0.0.0 - Production Wed Dec 21 24 14:13:10 2022 Version 19.17.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Last Successful login time: Wed Dec 21 2022 14:09:35 +08:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.17.0.0.0 SQL> show con_name CON_NAME ------------------------------ HRPDB SQL> show user USER is "HFXF" SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.17.0.0.0 [oracle@19cdb01 ~]$ sqlplus hfxf/Hfxf_456@19cdb01:1525/hrpdb SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 21 14:13:39 2022 Version 19.17.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Last Successful login time: Wed Dec 21 2022 14:13:10 +08:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.17.0.0.0 SQL> show con_name CON_NAME ------------------------------ HRPDB SQL> show user USER is "HFXF"
检查公共用户状态
SQL> col username for a30 SQL> col ACCOUNT_STATUS for a25 SQL> col PROFILE for a30 SQL> col COMMON for a15 SQL> col con_id for 999 SQL> select username,ACCOUNT_STATUS,PROFILE,COMMON,con_id from cdb_users 2 where username='C##XFADMIN'; USERNAME ACCOUNT_STATUS PROFILE COMMON CON_ID ------------------------------ ------------------------- ------------------------------ --------------- ------ C##XFADMIN OPEN & IN ROLLOVER C##CDB_PROFILE_USERPW YES 1 C##XFADMIN OPEN & IN ROLLOVER C##CDB_PROFILE_USERPW YES 3
检查本地用户状态
SQL> select username,ACCOUNT_STATUS,PROFILE,COMMON,con_id from cdb_users 2 where username='HFXF'; USERNAME ACCOUNT_STATUS PROFILE COMMON CON_ID ------------------------------ ------------------------- ------------------------------ --------------- ------ HFXF OPEN & IN ROLLOVER PDB_PROFILE_USERPW NO 3 SQL>
此刻，公共用户和本地用户的状态均为OPEN & IN ROLLOVER。
五、关闭密码翻转的特性
SQL> alter session set container=hrpdb; Session altered. SQL> alter user hfxf expire password rollover period; User altered. SQL>
使用旧密码登录本地用户
[oracle@19cdb01 ~]$ sqlplus hfxf/Hfxf_123@19cdb01:1525/hrpdb SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 21 14:26:20 2022 Version 19.17.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. ERROR: ORA-01017: invalid username/password; logon denied Enter user-name:
显示密码错误，使用旧密码已经无法登录hfxf用户
[oracle@19cdb01 ~]$ sqlplus hfxf/Hfxf_456@19cdb01:1525/hrpdb SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 21 14:27:03 2022 Version 19.17.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Last Successful login time: Wed Dec 21 2022 14:13:39 +08:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.17.0.0.0 SQL> show con_name CON_NAME ------------------------------ HRPDB SQL> show user USER is "HFXF" SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.17.0.0.0
修改cdb中profile的PASSWORD_ROLLOVER_TIME为0，即关闭该特性
SQL> alter profile c##cdb_profile_userpw LIMIT PASSWORD_ROLLOVER_TIME 0; Profile altered. [oracle@19cdb01 ~]$ sqlplus c##xfadmin/Oracle_123@19cdb01:1525/cdb19c SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jul 24 14:30:51 2021 Version 19.17.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. ERROR: ORA-01017: invalid username/password; logon denied Enter user-name: ERROR: ORA-12162: TNS:net service name is incorrectly specified Enter user-name: ERROR: ORA-12162: TNS:net service name is incorrectly specified SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus [oracle@19cdb01 ~]$ sqlplus c##xfadmin/Oracle_123@19cdb01:1525/hrpdb SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 21 14:31:15 2022 Version 19.17.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. ERROR: ORA-01017: invalid username/password; logon denied Enter user-name: ERROR: ORA-12162: TNS:net service name is incorrectly specified Enter user-name: ERROR: ORA-12162: TNS:net service name is incorrectly specified SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus [oracle@19cdb01 ~]$ sqlplus c##xfadmin/Oracle_456@19cdb01:1525/cdb19c SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 21 14:31:31 2022 Version 19.17.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Last Successful login time: Wed Dec 21 2022 14:11:44 +08:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.17.0.0.0 SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> show user USER is "C##XFADMIN" SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.17.0.0.0 [oracle@19cdb01 ~]$ sqlplus c##xfadmin/Oracle_456@19cdb01:1525/hrpdb SQL*Plus: Release 19.0.0.0.0 - Production Wed Dec 21 14:31:47 2022 Version 19.17.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Last Successful login time: Wed Dec 21 2022 14:31:31 +08:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.17.0.0.0 SQL> show con_name CON_NAME ------------------------------ HRPDB SQL> show user USER is "C##XFADMIN" SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.17.0.0.0 [oracle@19cdb01 ~]$
关闭Gradual Database Password Rollover特性后，使用旧的密码无法使用公共用户连接cdb和pdb，使用修改后的密码可以正常连接。
另外，启用密码翻转特性的用户不能被授予特权
SQL> grant sysdba to hfxf; grant sysdba to hfxf * ERROR at line 1: ORA-28227: Gradual password rollover is not supported for administrative users. SQL> SQL> grant sysdg to hfxf; grant sysdg to hfxf * ERROR at line 1: ORA-28227: Gradual password rollover is not supported for administrative users. SQL> grant sysbackup to hfxf; grant sysbackup to hfxf * ERROR at line 1: ORA-28227: Gradual password rollover is not supported for administrative users.
以下场景不能适用Gradual Database Password Rollover功能：
Oracle RAC安全用户的直接登录
Kerberos、证书或基于半径的外部认证连接
CMU (central managed user)连接
使用外部密码文件的管理连接
主备之间的Oracle Data Guard连接
-the end-
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/28373936/viewspace-2928998/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
最新文章
- Oracle数据库性能诊断与优化工具全景指南（11g、12c、19c与26ai）
- Oracle 19c 本地索引分区变为 UNUSABLE 后的空间占用验证
- Oracle中建立到CDB和PDB的连接
- Oracle 19c CDB中如何创建PDB
- 如何获取Data Pump dump file文件
- 19c CDB架构下使用具有SYSBACKUP权限用户执行DataPump-expdp报ORA-01045和ORA-39406
- IMPDP时有2张表报ORA-31693 ORA-38500
- Oracle中建立到CDB和PDB的连接
- 19c 多租户架构下数据库备份
- Oracle的特权管理用户终止其他用户发起的数据泵作业
