暂无图片
暂无图片
10
暂无图片
暂无图片
暂无图片
首页 / Oracle 19c新特性之用户同时使用2个密码

Oracle 19c新特性之用户同时使用2个密码

原创 Xiaofei Huangfu 云和恩墨交付战队 2022-12-21
1511

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

image.png

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
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 on   Wed Dec 21  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
    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   Wed Dec 21  14:30:51 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_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 on   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-

墨力计划oracle19coracle创建用户
最后修改时间：2023-01-31 11:00:01
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
1人已赞赏
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论