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:
|
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:
|
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:
|
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.
微信扫一扫，打赏作者吧～上一篇： 工具: 分析core dump file
下一篇： 工具：oswbba java 分析
对不起，这篇文章暂时关闭评论。