首页 » ORACLE, ORACLE [C]系列 » Alert: Oracle 12c/18c/19c “SYS” 用户密码也会自动过期

Alert: Oracle 12c/18c/19c “SYS” 用户密码也会自动过期

某年某月某一天,有个客户的oracle归档日志空间突然满了,实例挂起,客户的告警系统一如既往的保持沉默, 我们自己部署的“保镖”脚本被友军打上了#号,友军的“打手”脚本说sys用户密码已过期,  wait!wait! are you kidding me?  SYS user 密码过期?  逻辑是这样的这套脚本是在DATAGUARD的standby 使用sys@tns方式远程查询已applyed 日志删除,在调用日志里提示“ORA-28002: the password will expire within 7 days“。

ORA-28002&ORA-28001

这种错误并不陌生, 就是db user因为配置了user profile中的PASSWORD_LIFE_TIME和PASSWORD_GRACE_TIME,以用户登录时对比上次更改密码时间与当前时间判断,是否需要修改密码的策略。 应用程序用户通常创建新user profile,对于懒的定期改的就把Password life time搞成unlimted 永不过期, 数据库安装部署里需要考虑这问题,免的半年后应用用户突然因为密码过期原因影响了全业务。 那sys对于PASSWORD_LIFE_TIME是否生效呢?以前不会但现在会。

User SYS Does Not Get ORA-28002 Nor ORA-28001 Even When PASSWORD_LIFE_TIME or PASSWORD_GRACE_TIME are Set (Doc ID 289898.1)
中也有指出”The PASSWORD_LIFE_TIME and PASSWORD_GRACE_TIME parameters of a resource profile seem to be working for all the users but not for SYS“
也就是说user profile中配置的这两个参数并不对sys启作用, 这是oracle的预期行为,这也是问了几个做了十几、二十年的ORACLE DBA从来没听说过sys还密码过期的原因,但是,从Oracle 12c开始安全增强,改变了这个行为, SYS也开始受profile 中PASSWORD_LIFE_TIME and PASSWORD_GRACE_TIME影响。如果没有使用SYS远程登录的习惯可能也不太容易发现,在12c 安全增强中还有另一个关于用户状态的profile limit INACTIVE_ACCOUNT_TIME顾名思义,就是用户多长时间不用可以自动lock, 但该参数默认没有配置天数限制。

v$pwfile_users

我们知道sys(sysdba)和其它用户不一样,属于内部的管理用户,可以启动或关闭数据库实例,如果密码和用户状态只存在数据库内的数据字典,那在数据库open前就无法校验,所以它的密码是存在数据库外的密码文件,实例启动时同步到数据库字典, 查看密码文件中用户有个视图v$pwfile_users, 那这个视图是否可以看到用户状态呢?

11g

Column Datatype Description
USERNAME VARCHAR2(30) Name of the user that is contained in the password file
SYSDBA VARCHAR2(5) Indicates whether the user can connect with SYSDBA privileges (TRUE) or not (FALSE)
SYSOPER VARCHAR2(5) Indicates whether the user can connect with SYSOPER privileges (TRUE) or not (FALSE)
SYSASM VARCHAR2(5) Indicates whether the user can connect with SYSASM privileges (TRUE) or not (FALSE)

12C(12.2)

Column Datatype Description
USERNAME VARCHAR2(128) Name of the user that is contained in the password file
SYSDBA VARCHAR2(5) Indicates whether the user can connect with SYSDBA privileges (TRUE) or not (FALSE)
SYSOPER VARCHAR2(5) Indicates whether the user can connect with SYSOPER privileges (TRUE) or not (FALSE)
SYSASM VARCHAR2(5) Indicates whether the user can connect with SYSASM privileges (TRUE) or not (FALSE)
SYSBACKUP VARCHAR2(5) Indicates whether the user can connect with SYSBACKUP privileges (TRUE) or not (FALSE)
SYSDG VARCHAR2(5) Indicates whether the user can connect with SYSDG privileges (TRUE) or not (FALSE)
SYSKM VARCHAR2(5) Indicates whether the user can connect with SYSKM privileges (TRUE) or not (FALSE)
ACCOUNT_STATUS VARCHAR2(30) Account status:

  • OPEN
  • EXPIRED
  • EXPIRED (GRACE)
  • LOCKED (TIMED)
  • LOCKED
  • EXPIRED & LOCKED (TIMED)
  • EXPIRED & LOCKED
  • EXPIRED (GRACE) & LOCKED
PASSWORD_PROFILE VARCHAR2(128) Password profile name
LAST_LOGIN TIMESTAMP(9) WITH TIME ZONE The time of the last user login
LOCK_DATE DATE Date the account was locked if account status was LOCKED
EXPIRY_DATE DATE Date of expiration of the account
EXTERNAL_NAME VARCHAR2(1024) Shows Certificate DN or Principal Name of externally authenticated users
AUTHENTICATION_TYPE VARCHAR2(8) Indicates the authentication mechanism for the user:

  • EXTERNAL: CREATE USER user1 IDENTIFIED EXTERNALLY;
  • GLOBAL: CREATE USER user2 IDENTIFIED GLOBALLY;
  • PASSWORD: CREATE USER user3 IDENTIFIED BY user3;
COMMON VARCHAR2(3) This column has a value of YES if an administrative privilege (for example, SYSDBA) was granted with CONTAINER=ALL. Otherwise, the column has a value of NO.
CON_ID NUMBER The ID of the container to which the data pertains. Possible values include:

  • 0: This value is used for rows containing data that pertain to the entire CDB. This value is also used for rows in non-CDBs.
  • 1: This value is used for rows containing data that pertain to only the root
  • n: Where n is the applicable container ID for the rows containing data

Note:
其实从12c开始该view增加了很多字段,其中就有用户状态和过期时间,所以理论上就可以在数据库打开前从密码文件就可以验证用户是否过期。

实践验证理论

Note: test in Oracle 19.3 CDB on linux

[oracle@oel7db1 ~]$ cd $ORACLE_HOME
[oracle@oel7db1 db_1]$ cd dbs
l[oracle@oel7db1 dbs]$ ls
arch  c-3414393273-20201126-00  hc_anbob19c.dat  initanbob19c.ora  init.ora  lkANBOB19C  orapwanbob19c  snapcf_anbob19c.f  spfileanbob19c.ora
[oracle@oel7db1 dbs]$ ll orapwanbob19c
-rw-r----- 1 oracle oinstall 6144 Dec 28 12:49 orapwanbob19c
[oracle@oel7db1 dbs]$ md5sum orapwanbob19c
0b3ec322d07035edbabc8afe8d062594  orapwanbob19c

SQL> @cc pdb1
ALTER SESSION SET container = pdb1;

Session altered.


USERNAME             INST_NAME            HOST_NAME                  I# SID   SERIAL#  VERSION    STARTED  SPID       OPID  CPID            SADDR            PADDR
-------------------- -------------------- ------------------------- --- ----- -------- ---------- -------- ---------- ----- --------------- ---------------- ----------------
SYS                  PDB1-anbob19c        oel7db1                     1 1     14       19.0.0.0.0 20220107 2186       33    2093            0000000078481028 00000000790F7F48


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

USERNAME                  DEFAULT_TABLESPACE        TEMPORARY_TABLESPACE              USER_ID CREATED   ACCOUNT_STATUS                   PROFILE
------------------------- ------------------------- ------------------------------ ---------- --------- -------------------------------- --------------------------------------------------------------------------------------------------------------------------------
SYS                       SYSTEM                    TEMP                                    0 17-APR-19 OPEN                             DEFAULT
SYSTEM                    SYSTEM                    TEMP                                    9 17-APR-19 OPEN                             DEFAULT
LBACSYS                   SYSTEM                    TEMP                                  100 17-APR-19 LOCKED                           DEFAULT
APPQOSSYS                 SYSAUX                    TEMP                                   58 17-APR-19 LOCKED                           DEFAULT


SQL> alter profile default limit PASSWORD_LIFE_TIME 5/24/60;
Profile altered.

SQL> select * from dba_profiles where profile='DEFAULT' and RESOURCE_NAME like '%PASSWORD%';

PROFILE         RESOURCE_NAME                    RESOURCE LIMIT                                                        COM INH IMP
--------------- -------------------------------- -------- ------------------------------------------------------------ --- --- ---
DEFAULT         PASSWORD_LIFE_TIME               PASSWORD .0034                                                        NO  NO  NO
DEFAULT         PASSWORD_REUSE_TIME              PASSWORD UNLIMITED                                                    NO  NO  NO
DEFAULT         PASSWORD_REUSE_MAX               PASSWORD UNLIMITED                                                    NO  NO  NO
DEFAULT         PASSWORD_VERIFY_FUNCTION         PASSWORD NULL                                                         NO  NO  NO
DEFAULT         PASSWORD_LOCK_TIME               PASSWORD 1                                                            NO  NO  NO
DEFAULT         PASSWORD_GRACE_TIME              PASSWORD .0001                                                        NO  NO  NO

select 'DBA_USERS', USERNAME, PASSWORD_VERSIONS,PROFILE, ACCOUNT_STATUS, LOCK_DATE,EXPIRY_DATE, PASSWORD_CHANGE_DATE PTIME  from dba_users where username='SYS'
union
select 'PWFILE', USERNAME, NULL, PASSWORD_PROFILE, ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE, null PTIME   from v$pwfile_users where username='SYS'
union
  select 'USER$', NAME, NULL, NULL, to_char(ASTATUS), LTIME, EXPTIME, PTIME from user$ where name='SYS'
 
select 'DBA_USERS', USERNAME, PASSWORD_VERSIONS,PROFILE, ACCOUNT_STATUS, LOCK_DATE,EXPIRY_DATE, PASSWORD_CHANGE_DATE PTIME from dba_users where username='SYS'
union
select 'PWFILE', USERNAME, NULL, PASSWORD_PROFILE, ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE, null PTIME from v$pwfile_users where username='SYS'
union
  5    select 'USER$', NAME, NULL, NULL, to_char(ASTATUS), LTIME, EXPTIME, PTIME from user$ where name='SYS';

'DBA_USER USERNAME                  PASSWORD_VERSIONS PROFILE         ACCOUNT_ST LOCK_DATE         EXPIRY_DATE       PTIME
--------- ------------------------- ----------------- --------------- ---------- ----------------- ----------------- -----------------
DBA_USERS SYS                       11G 12C           DEFAULT         OPEN                         20210624 17:12:42 20210624 17:07:42
PWFILE    SYS                                         DEFAULT         OPEN                         20220626 12:49:10
USER$     SYS                                                         0                                              20210624 17:07:42


SQL> @printtab "select * from v$pwfile_users";
USERNAME                      : SYS
SYSDBA                        : TRUE
SYSOPER                       : TRUE
SYSASM                        : FALSE
SYSBACKUP                     : FALSE
SYSDG                         : FALSE
SYSKM                         : FALSE
ACCOUNT_STATUS                : OPEN
PASSWORD_PROFILE              : DEFAULT
LAST_LOGIN                    : 07-JAN-22 04.45.16.000000000 PM +08:00
LOCK_DATE                     :
EXPIRY_DATE                   : 26-jun-2022 12:49:10
EXTERNAL_NAME                 :
AUTHENTICATION_TYPE           : PASSWORD
COMMON                        : YES
CON_ID                        : 0

SQL> select to_date('20220626','yyyymmdd')-180 from dual;

TO_DATE('20220626
-----------------
20211228 00:00:00


Note:
这里是切到了PDB中,修改了PDB的user profile. 可以v$pwfile的时间是密码文件的最后一个Modify time+180天,而dba_users(user$)计算的才是我们刚修改的5分钟,但是一个过去的时间,也未提示用户过期

通过alter user改密码

SQL> alter user sys identified by oracle container=all;
alter user sys identified by oracle container=all
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database

SQL> conn / as sysdba
Connected.

USERNAME             INST_NAME            HOST_NAME                  I# SID   SERIAL#  VERSION    STARTED  SPID       OPID  CPID            SADDR            PADDR
-------------------- -------------------- ------------------------- --- ----- -------- ---------- -------- ---------- ----- --------------- ---------------- ----------------
SYS                  CDB$ROOT-anbob19c    oel7db1                     1 1     18919    19.0.0.0.0 20220107 4372       33    3461            0000000078481028 00000000790F7F48


SQL> alter user sys identified by oracle container=all;
User altered.

shudown immediate
startup

SQL>
SQL> @cc pdb1
ALTER SESSION SET container = pdb1;

Session altered.

USERNAME             INST_NAME            HOST_NAME                  I# SID   SERIAL#  VERSION    STARTED  SPID       OPID  CPID            SADDR            PADDR
-------------------- -------------------- ------------------------- --- ----- -------- ---------- -------- ---------- ----- --------------- ---------------- ----------------
SYS                  PDB1-anbob19c        oel7db1                     1 1     18919    19.0.0.0.0 20220107 4372       33    3461            0000000078481028 00000000790F7F48


select 'DBA_USERS', USERNAME, PASSWORD_VERSIONS,PROFILE, ACCOUNT_STATUS, LOCK_DATE,EXPIRY_DATE, PASSWORD_CHANGE_DATE PTIME,LAST_LOGIN LLST from dba_users where username='SYS'
union
select 'PWFILE', USERNAME, NULL, PASSWORD_PROFILE, ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE, null PTIME ,NULL from v$pwfile_users where username='SYS'
union
  5    select 'USER$', NAME, NULL, NULL, to_char(ASTATUS), LTIME, EXPTIME, PTIME,NULL from user$ where name='SYS';

'DBA_USER USERNAME                  PASSWORD_VERSIONS PROFILE              ACCOUNT_STATUS                 LOCK_DATE         EXPIRY_DATE       PTIME             LLST
--------- ------------------------- ----------------- -------------------- ------------------------------ ----------------- ----------------- ----------------- --------------------
DBA_USERS SYS                       11G 12C           DEFAULT              OPEN                                             20210624 17:12:42 20210624 17:07:42
PWFILE    SYS                                         DEFAULT              OPEN                                             20220706 17:02:17
USER$     SYS                                                              0                                                                  20210624 17:07:42

SQL> host
[oracle@oel7db1 dbs]$ ls -l orapwanbob19c
-rw-r----- 1 oracle oinstall 6144 Jan  7 17:04 orapwanbob19c

Note:
使用alter user会同步修改密码文件,PDB中的 user$的过期时间并不会更新, v$pwfile_users的过期时间继续推后180天。可能会想是不是oracle bug, PDB都改成5分钟了,这里还是按180天算,不是的,下面再说原因。 那我们先改操作系统时间到未来那个时间,看是不是真的会过期。

# 改OS 时间

[root@oel7db1 ~]# date -s '20220807'
Sun Aug  7 00:00:00 CST 2022
[root@oel7db1 ~]# date
Sun Aug  7 00:00:07 CST 2022
[root@oel7db1 ~]# su - oracle
Last login: Fri Jan  7 16:24:22 CST 2022 on pts/0
[oracle@oel7db1 ~]$ ora

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Aug 7 00:00:13 2022
Version 19.3.0.0.0

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  700445040 bytes
Fixed Size                  9138544 bytes
Variable Size             637534208 bytes
Database Buffers           50331648 bytes
Redo Buffers                3440640 bytes
Database mounted.
Database opened.
SQL> select sysdate from dual;

SYSDATE
---------
07-AUG-22

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@oel7db1 ~]$ sqlplus sys/oracle@cdb1pdb1 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Aug 7 00:00:45 2022
Version 19.3.0.0.0

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

ERROR:
ORA-28002: the password will expire within 7 days


SQL> r
  1  select 'DBA_USERS', USERNAME, PASSWORD_VERSIONS,PROFILE, ACCOUNT_STATUS, LOCK_DATE,EXPIRY_DATE, PASSWORD_CHANGE_DATE PTIME  from dba_users where username='SYS'
  2  union
  3  select 'PWFILE', USERNAME, NULL, PASSWORD_PROFILE, ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE, null PTIME   from v$pwfile_users where username='SYS'
  4  union
  5*   select 'USER$', NAME, NULL, NULL, to_char(ASTATUS), LTIME, EXPTIME, PTIME from user$ where name='SYS'

'DBA_USER USERNAME                  PASSWORD_VERSIONS PROFILE    ACCOUNT_STATUS                           LOCK_DATE           EXPIRY_DATE         PTIME
--------- ------------------------- ----------------- ---------- ---------------------------------------- ------------------- ------------------- -------------------
DBA_USERS SYS                       11G 12C           DEFAULT    OPEN                                                         2021-06-24 17:12:42 2021-06-24 17:07:42
PWFILE    SYS                                         DEFAULT    EXPIRED(GRACE)                                               2022-08-14 00:00:45
USER$     SYS                                                    0                                                                                2021-06-24 17:07:42

# 日期继续改

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@oel7db1 ~]$ exit
logout
[root@oel7db1 ~]# date -s '20220815'
Mon Aug 15 00:00:00 CST 2022
[root@oel7db1 ~]# su - oracle
Last login: Sun Aug  7 00:00:09 CST 2022 on pts/0


[oracle@oel7db1 ~]$ sqlplus sys/oracle@cdb1pdb1 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Aug 15 00:00:54 2022
Version 19.3.0.0.0

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

ERROR:
ORA-28001: the password has expired


Changing password for sys
New password:


SQL> r
  1  select 'DBA_USERS', USERNAME, PASSWORD_VERSIONS,PROFILE, ACCOUNT_STATUS, LOCK_DATE,EXPIRY_DATE, PASSWORD_CHANGE_DATE PTIME  from dba_users where username='SYS'
  2  union
  3  select 'PWFILE', USERNAME, NULL, PASSWORD_PROFILE, ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE, null PTIME   from v$pwfile_users where username='SYS'
  4  union
  5*   select 'USER$', NAME, NULL, NULL, to_char(ASTATUS), LTIME, EXPTIME, PTIME from user$ where name='SYS'

'DBA_USER USERNAME                  PASSWORD_VERSIONS PROFILE              ACCOUNT_STATUS                           LOCK_DATE           EXPIRY_DATE         PTIME
--------- ------------------------- ----------------- -------------------- ---------------------------------------- ------------------- ------------------- -------------------
DBA_USERS SYS                       11G 12C           DEFAULT              OPEN                                                         2021-06-24 17:12:42 2021-06-24 17:07:42
PWFILE    SYS                                         DEFAULT              EXPIRED                                                      2022-08-14 00:00:45
USER$     SYS                                                              0                                                                                2021-06-24 17:07:42


NOTE:
可以看到这里仅向后修改操作系统的时间,模拟出了SYS过期,但是并不是日期大于开始的20220706就直接过期,而是在大于这个日期首次登录时会提示EXPIRED(GRACE),同时把v$pwfile_user.expire_time过期时间有当前时间再加上grace 7天时间。如果这7点后还没改,状态变为EXPIRED已过期, 但是PDB中的dba_users还是OPEN 没有参考意义了。可以看出可以以v$pwfile_user的过期时间巡检,那再看180天根据哪来的。

刚才一直是在PDB查询,下面切到CDB.
SQL> conn / as sysdba
Connected.

USERNAME             INST_NAME            HOST_NAME                  I# SID   SERIAL#  VERSION    STARTED  SPID       OPID  CPID            SADDR            PADDR
-------------------- -------------------- ------------------------- --- ----- -------- ---------- -------- ---------- ----- --------------- ---------------- ----------------
SYS                  CDB$ROOT-anbob19c    oel7db1                     1 68    28670    19.0.0.0.0 20220815 7187       33    7059            00000000785281E0 00000000790F7F48


SQL> select * from dba_profiles where profile='DEFAULT' and RESOURCE_NAME like '%PASSWORD%';

PROFILE              RESOURCE_NAME                    RESOURCE LIMIT                          COM INH IMP
-------------------- -------------------------------- -------- ------------------------------ --- --- ---
DEFAULT              PASSWORD_LIFE_TIME               PASSWORD 180                            NO  NO  NO
DEFAULT              PASSWORD_REUSE_TIME              PASSWORD UNLIMITED                      NO  NO  NO
DEFAULT              PASSWORD_REUSE_MAX               PASSWORD UNLIMITED                      NO  NO  NO
DEFAULT              PASSWORD_VERIFY_FUNCTION         PASSWORD NULL                           NO  NO  NO
DEFAULT              PASSWORD_LOCK_TIME               PASSWORD 1                              NO  NO  NO
DEFAULT              PASSWORD_GRACE_TIME              PASSWORD 7                              NO  NO  NO

6 rows selected.


select 'DBA_USERS', USERNAME, PASSWORD_VERSIONS,PROFILE, ACCOUNT_STATUS, LOCK_DATE,EXPIRY_DATE, null PTIME from dba_users where username='SYS'
union
select 'PWFILE', USERNAME, NULL, PASSWORD_PROFILE, ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE, null PTIME from v$pwfile_users where username='SYS'
union
 select 'USER$', NAME, NULL, NULL, to_char(ASTATUS), LTIME, EXPTIME, PTIME from user$ where name='SYS';

'DBA_USER USERNAME                  PASSWORD_VERSIONS PROFILE              ACCOUNT_STATUS                           LOCK_DATE           EXPIRY_DATE         PTIME
--------- ------------------------- ----------------- -------------------- ---------------------------------------- ------------------- ------------------- -------------------
DBA_USERS SYS                       11G 12C           DEFAULT              EXPIRED                                                      2022-08-14 00:00:45
PWFILE    SYS                                         DEFAULT              EXPIRED                                                      2022-08-14 00:00:45
USER$     SYS                                                              1                                                            2022-08-14 00:00:45 2019-04-17 00:56:32

Note:
注意 原来sys的计划user profile是根据CDB而来,修改PDB 并不会改变, 而且在CDB中dba_users已显示为expired.  另外注意user$.ptime就是意思是上次修改密码时间是2019-4-17这可能是ORACLE安装介质中镜像生成的,好多数据库都是这个日期。 接下来再改一下密码。

SQL> alter user sys identified by oracle1234 container=all;

User altered.

select 'DBA_USERS', USERNAME, PASSWORD_VERSIONS,PROFILE, ACCOUNT_STATUS, LOCK_DATE,EXPIRY_DATE, null PTIME from dba_users where username='SYS'
union
select 'PWFILE', USERNAME, NULL, PASSWORD_PROFILE, ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE, null PTIME from v$pwfile_users where username='SYS'
union
select 'USER$', NAME, NULL, NULL, to_char(ASTATUS), LTIME, EXPTIME, PTIME from user$ where name='SYS';

'DBA_USER USERNAME                  PASSWORD_VERSIONS PROFILE              ACCOUNT_STATUS                           LOCK_DATE           EXPIRY_DATE         PTIME
--------- ------------------------- ----------------- -------------------- ---------------------------------------- ------------------- ------------------- -------------------
DBA_USERS SYS                       11G 12C           DEFAULT              OPEN                                                         2019-10-14 00:56:32
PWFILE    SYS                                         DEFAULT              OPEN                                                         2023-02-11 00:11:24
USER$     SYS                                                              0                                                            2023-02-11 00:11:24 2019-04-17 00:56:32

SQL> select to_date('20230211','yyyymmdd')-180 from dual;

TO_DATE('20230211',
-------------------
2022-08-15 00:00:00

SQL> host
[oracle@oel7db1 ~]$ cd $ORACLE_HOME
[oracle@oel7db1 db_1]$ cd dbs

-rw-r----- 1 oracle oinstall 6144 Aug 15 00:14 orapwanbob19c


Note:
再次修改密码后,SYS用户显示OPEN, 并且v$pwfile_users显示的过期时间已自动延后,而dba_users的过期时间是根据user$.ptime计算而来,没有什么参考意义了。 接下来我们再扩大user profile的密码过期天数

select 'DBA_USERS', USERNAME, PASSWORD_VERSIONS,PROFILE, ACCOUNT_STATUS, LOCK_DATE,EXPIRY_DATE, null PTIME from dba_users where username='SYS'
union
select 'PWFILE', USERNAME, NULL, PASSWORD_PROFILE, ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE, null PTIME from v$pwfile_users where username='SYS'
union
  5  select 'USER$', NAME, NULL, NULL, to_char(ASTATUS), LTIME, EXPTIME, PTIME from user$ where name='SYS';

'DBA_USER USERNAME                  PASSWORD_VERSIONS PROFILE              ACCOUNT_STATUS                           LOCK_DATE           EXPIRY_DATE         PTIME
--------- ------------------------- ----------------- -------------------- ---------------------------------------- ------------------- ------------------- -------------------
DBA_USERS SYS                       11G 12C           DEFAULT              OPEN                                                         2019-10-14 00:56:32
PWFILE    SYS                                         DEFAULT              OPEN                                                         2023-02-11 00:11:24
USER$     SYS                                                              0                                                            2023-02-11 00:11:24 2019-04-17 00:56:32


SQL>
SQL> alter profile default limit PASSWORD_LIFE_TIME 200;

Profile altered.

select 'DBA_USERS', USERNAME, PASSWORD_VERSIONS,PROFILE, ACCOUNT_STATUS, LOCK_DATE,EXPIRY_DATE, null PTIME from dba_users where username='SYS'
union
select 'PWFILE', USERNAME, NULL, PASSWORD_PROFILE, ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE, null PTIME from v$pwfile_users where username='SYS'
union
select 'USER$', NAME, NULL, NULL, to_char(ASTATUS), LTIME, EXPTIME, PTIME from user$ where name='SYS';

'DBA_USER USERNAME                  PASSWORD_VERSIONS PROFILE              ACCOUNT_STATUS                           LOCK_DATE           EXPIRY_DATE         PTIME
--------- ------------------------- ----------------- -------------------- ---------------------------------------- ------------------- ------------------- -------------------
DBA_USERS SYS                       11G 12C           DEFAULT              OPEN                                                         2019-11-03 00:56:32
PWFILE    SYS                                         DEFAULT              OPEN                                                         2023-02-11 00:11:24
USER$     SYS                                                              0                                                            2023-02-11 00:11:24 2019-04-17 00:56:32

NOTE:
注意,增加了profile的密码过期时间,但v$pwfile.expiry_date并没有自动延长。即使使用sys再次登录也不会更新。

SQL> alter user sys identified by oracle;

User altered.

select 'DBA_USERS', USERNAME, PASSWORD_VERSIONS,PROFILE, ACCOUNT_STATUS, LOCK_DATE,EXPIRY_DATE, null PTIME from dba_users where username='SYS'
union
select 'PWFILE', USERNAME, NULL, PASSWORD_PROFILE, ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE, null PTIME from v$pwfile_users where username='SYS'
union
select 'USER$', NAME, NULL, NULL, to_char(ASTATUS), LTIME, EXPTIME, PTIME from user$ where name='SYS';

'DBA_USER USERNAME                  PASSWORD_VERSIONS PROFILE    ACCOUNT_STATUS                           LOCK_DATE           EXPIRY_DATE         PTIME
--------- ------------------------- ----------------- ---------- ---------------------------------------- ------------------- ------------------- -------------------
DBA_USERS SYS                       11G 12C           DEFAULT    OPEN                                                         2019-11-03 00:56:32
PWFILE    SYS                                         DEFAULT    OPEN                                                         2023-03-03 00:21:16
USER$     SYS                                                    0                                                            2023-03-03 00:21:16 2019-04-17 00:56:32

NOTE:
修改profile password life time天数后,直到再次变更密码,expiry_date才发生改变。那这里就要注意了,很多时候我们的理解如果密码周期就只增加profile的密码周期,又没有再改密码,到期还是会过期,如果我们把password_life_time改成unlimited呢?

SQL> alter profile default limit PASSWORD_LIFE_TIME unlimited;

Profile altered.

select 'DBA_USERS', USERNAME, PASSWORD_VERSIONS,PROFILE, ACCOUNT_STATUS, LOCK_DATE,EXPIRY_DATE, null PTIME from dba_users where username='SYS'
union
select 'PWFILE', USERNAME, NULL, PASSWORD_PROFILE, ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE, null PTIME from v$pwfile_users where username='SYS'
union
  5  select 'USER$', NAME, NULL, NULL, to_char(ASTATUS), LTIME, EXPTIME, PTIME from user$ where name='SYS';

'DBA_USER USERNAME                  PASSWORD_VERSIONS PROFILE    ACCOUNT_STATUS                           LOCK_DATE           EXPIRY_DATE         PTIME
--------- ------------------------- ----------------- ---------- ---------------------------------------- ------------------- ------------------- -------------------
DBA_USERS SYS                       11G 12C           DEFAULT    OPEN
PWFILE    SYS                                         DEFAULT    OPEN                                                         2023-03-03 00:21:16
USER$     SYS                                                    0                                                            2023-03-03 00:21:16 2019-04-17 00:56:32

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  700445040 bytes
Fixed Size                  9138544 bytes
Variable Size             637534208 bytes
Database Buffers           50331648 bytes
Redo Buffers                3440640 bytes
Database mounted.
Database opened.
select 'DBA_USERS', USERNAME, PASSWORD_VERSIONS,PROFILE, ACCOUNT_STATUS, LOCK_DATE,EXPIRY_DATE, null PTIME from dba_users where username='SYS'
union
select 'PWFILE', USERNAME, NULL, PASSWORD_PROFILE, ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE, null PTIME from v$pwfile_users where username='SYS'
union
 select 'USER$', NAME, NULL, NULL, to_char(ASTATUS), LTIME, EXPTIME, PTIME from user$ where name='SYS';

'DBA_USER USERNAME                  PASSWORD_VERSIONS PROFILE    ACCOUNT_STATUS                           LOCK_DATE EXPIRY_DA PTIME
--------- ------------------------- ----------------- ---------- ---------------------------------------- --------- --------- ---------
DBA_USERS SYS                       11G 12C           DEFAULT    OPEN
PWFILE    SYS                                         DEFAULT    OPEN                                               03-MAR-23
USER$     SYS                                                    0                                                  03-MAR-23 17-APR-19


NOTE:
注意改完PASSWORD_LIFE_TIME为 unlimited后,dba_user.expire_date显示为空,但是v$pwfile.expiry_date还是之前的过期日期。重启实例也并未改变。那可能就又埋了个雷,即使sys的profile当前PASSWORD_LIFE_TIME已经为无限,是不是到时还会过期呢?

再改日期
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@oel7db1 ~]$ exit
logout
[root@oel7db1 ~]# date -s '20230815'
Tue Aug 15 00:00:00 CST 2023
[root@oel7db1 ~]# su - oracle
Last login: Mon Aug 15 00:00:03 CST 2022 on pts/0
[oracle@oel7db1 ~]$ ora

SQL> startup
ORACLE instance started.

Total System Global Area  700445040 bytes
Fixed Size                  9138544 bytes
Variable Size             637534208 bytes
Database Buffers           50331648 bytes
Redo Buffers                3440640 bytes
Database mounted.
Database opened.

SQL> r
  1  select 'DBA_USERS', USERNAME, PASSWORD_VERSIONS,PROFILE, ACCOUNT_STATUS, LOCK_DATE,EXPIRY_DATE, null PTIME from dba_users where username='SYS'
  2  union
  3  select 'PWFILE', USERNAME, NULL, PASSWORD_PROFILE, ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE, null PTIME from v$pwfile_users where username='SYS'
  4  union
  5*  select 'USER$', NAME, NULL, NULL, to_char(ASTATUS), LTIME, EXPTIME, PTIME from user$ where name='SYS'

'DBA_USER USERNAME                  PASSWORD_VERSIONS PROFILE                        ACCOUNT_STATUS                           LOCK_DATE EXPIRY_DA PTIME
--------- ------------------------- ----------------- ------------------------------ ---------------------------------------- --------- --------- ---------
DBA_USERS SYS                       11G 12C           DEFAULT                        OPEN
PWFILE    SYS                                         DEFAULT                        OPEN                                               03-MAR-23
USER$     SYS                                                                        0                                                  03-MAR-23 17-APR-19


用sys登录一下

[oracle@oel7db1 ~]$ sqlplus sys/oracle@cdb1pdb1 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 15 00:02:18 2023
Version 19.3.0.0.0

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

ERROR:
ORA-28002: the password will expire within 7 days


SQL> r
  1  select 'DBA_USERS', USERNAME, PASSWORD_VERSIONS,PROFILE, ACCOUNT_STATUS, LOCK_DATE,EXPIRY_DATE, null PTIME from dba_users where username='SYS'
  2  union
  3  select 'PWFILE', USERNAME, NULL, PASSWORD_PROFILE, ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE, null PTIME from v$pwfile_users where username='SYS'
  4  union
  5*  select 'USER$', NAME, NULL, NULL, to_char(ASTATUS), LTIME, EXPTIME, PTIME from user$ where name='SYS'

'DBA_USER USERNAME                  PASSWORD_VERSIONS PROFILE                        ACCOUNT_STATUS                           LOCK_DATE           EXPIRY_DATE         PTIME
--------- ------------------------- ----------------- ------------------------------ ---------------------------------------- ------------------- ------------------- -------------------
DBA_USERS SYS                       11G 12C           DEFAULT                        EXPIRED(GRACE)                                               2023-08-22 00:02:18
PWFILE    SYS                                         DEFAULT                        EXPIRED(GRACE)                                               2023-08-22 00:02:18
USER$     SYS                                                                        2                                                            2023-08-22 00:02:18 2019-04-17 00:56:32

Note:
SYS还是过期了。 如果再改一下密码会是什么样子?

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


select 'DBA_USERS', USERNAME, PASSWORD_VERSIONS,PROFILE, ACCOUNT_STATUS, LOCK_DATE,EXPIRY_DATE, null PTIME from dba_users where username='SYS'
union
select 'PWFILE', USERNAME, NULL, PASSWORD_PROFILE, ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE, null PTIME from v$pwfile_users where username='SYS'
union
  5   select 'USER$', NAME, NULL, NULL, to_char(ASTATUS), LTIME, EXPTIME, PTIME from user$ where name='SYS';

'DBA_USER USERNAME                  PASSWORD_VERSIONS PROFILE                        ACCOUNT_STATUS                           LOCK_DATE           EXPIRY_DATE         PTIME
--------- ------------------------- ----------------- ------------------------------ ---------------------------------------- ------------------- ------------------- -------------------
DBA_USERS SYS                       11G 12C           DEFAULT                        OPEN
PWFILE    SYS                                         DEFAULT                        OPEN
USER$     SYS                                                                        0           

Note:
注意在当前PASSWORD_LIFE_TIME为 unlimited后,现次变更密码这里dba_users还是v$pwfile_users的EXPIRY_DATE都显示为空,才是真正的永不过期,看这里是不是需要赶紧去查一下生产呢?

SUMMARY:
1, oracle 12c的安全增强, sys用户也受到了user profile中的PASSWORD_LIFE_TIME的参数影响。
2, 12c后的密码文件中增加了用户状态和过期时间
3, 变更profile的 password life time后,没有再次修改密码,过期时间不会生效
4, 确保sys不过期是v$pwfile_users.EXPIRY_DATE 显示为null.

打赏

, ,

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