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 ~]$ sqlplus / as sysdba
sql*plus: release 19.0.0.0.0 - production on wed dec 21 13:49:07 2022
version 19.17.0.0.0
米乐app官网下载 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
米乐app官网下载 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
米乐app官网下载 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
米乐app官网下载 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
米乐app官网下载 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
米乐app官网下载 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 on wed dec 21 14:13:10 2022
version 19.17.0.0.0
米乐app官网下载 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
米乐app官网下载 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
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
米乐app官网下载 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
米乐app官网下载 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 wed dec 21 14:30:51 2022
version 19.17.0.0.0
米乐app官网下载 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
米乐app官网下载 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
米乐app官网下载 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 on wed dec 21 14:31:47 2022
version 19.17.0.0.0
米乐app官网下载 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-
最后修改时间：2023-01-31 11:00:01
【米乐app官网下载的版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。