oracle-查看和修改sys用户的密码过期时间

详情查看大神的文章：https://www.anbob.com/archives/6562.html

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

查看sys用户密码过期时间

col USERNAME for a10
col PROFILE for a15
col ACCOUNT_STATUS for a15
col LLST for a20
col DB_UNIQUE_NAME for a20
select (select DB_UNIQUE_NAME from v$database) DB_UNIQUE_NAME, (select INSTANCE_NAME from v$instance) INSTANCE_NAME, (select 'DBA_USERS' from dual) SELECT_VIEW,(select sysdate from dual) CHECK_TIME, 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 (select DB_UNIQUE_NAME from v$database) DB_UNIQUE_NAME,(select INSTANCE_NAME from v$instance) INSTANCE_NAME, (select 'PWFILE' from dual) SELECT_VIEW,(select sysdate from dual) CHECK_TIME, USERNAME, NULL, PASSWORD_PROFILE, ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE, null PTIME ,NULL from v$pwfile_users where username='SYS'
union
select (select DB_UNIQUE_NAME from v$database) DB_UNIQUE_NAME, (select INSTANCE_NAME from v$instance) INSTANCE_NAME, (select 'USER$' from dual) SELECT_VIEW,(select sysdate from dual) CHECK_TIME, NAME, NULL, NULL, to_char(ASTATUS), LTIME, EXPTIME, PTIME,NULL from user$ where name='SYS';

DB_UNIQUE_NAME  INSTANCE_NAME  SELECT_VI CHECK_TIME        USERNAME   PASSWORD_VERSIONS PROFILE         ACCOUNT_STATUS  LOCK_DATE         EXPIRY_DATE       PTIME             LLST
--------------- -------------- --------- ----------------- ---------- ----------------- --------------- --------------- ----------------- ----------------- ----------------- --------------------
orcl            orcl1          DBA_USERS 20220125 16:27:37 SYS        10G 11G 12C       DEFAULT         OPEN                                                20210320 17:19:00
orcl            orcl1          PWFILE    20220125 16:27:37 SYS                          DEFAULT         OPEN                              20220221 07:15:02
orcl            orcl1          USER$     20220125 16:27:37 SYS                                          0                                 20220221 07:15:02 20210320 17:19:00

v$pwfile_users.EXPIRY_DATE 显示过期时间是 20220221 07:15:02

检查PROFILE的配置

从上面的查询可以看出，sys用户使用的PROFILE是DEFAULT。

col PROFILE for a20
col RESOURCE_NAME for a30
col LIMIT for a30
select * from dba_profiles where PROFILE='DEFAULT';

PROFILE              RESOURCE_NAME                  RESOURCE LIMIT                          COM INH IMP
-------------------- ------------------------------ -------- ------------------------------ --- --- ---
DEFAULT              COMPOSITE_LIMIT                KERNEL   UNLIMITED                      NO  NO  NO
DEFAULT              SESSIONS_PER_USER              KERNEL   UNLIMITED                      NO  NO  NO
DEFAULT              CPU_PER_SESSION                KERNEL   UNLIMITED                      NO  NO  NO
DEFAULT              CPU_PER_CALL                   KERNEL   UNLIMITED                      NO  NO  NO
DEFAULT              LOGICAL_READS_PER_SESSION      KERNEL   UNLIMITED                      NO  NO  NO
DEFAULT              LOGICAL_READS_PER_CALL         KERNEL   UNLIMITED                      NO  NO  NO
DEFAULT              IDLE_TIME                      KERNEL   UNLIMITED                      NO  NO  NO
DEFAULT              CONNECT_TIME                   KERNEL   UNLIMITED                      NO  NO  NO
DEFAULT              PRIVATE_SGA                    KERNEL   UNLIMITED                      NO  NO  NO
DEFAULT              FAILED_LOGIN_ATTEMPTS          PASSWORD UNLIMITED                      NO  NO  NO
DEFAULT              PASSWORD_LIFE_TIME             PASSWORD UNLIMITED                      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 UNLIMITED                      NO  NO  NO
DEFAULT              PASSWORD_GRACE_TIME            PASSWORD UNLIMITED                      NO  NO  NO
DEFAULT              INACTIVE_ACCOUNT_TIME          PASSWORD UNLIMITED                      NO  NO  NO

17 rows selected.

将 PASSWORD_LIFE_TIME 设置为 UNLIMITED，密码用不过期

SQL> alter profile default limit PASSWORD_LIFE_TIME unlimited;

检查密码文件

从Oracle 12C开始，密码文件默认是放到ASM磁盘组中，实现RAC节点之间的密码文件共享,例如：

$ srvctl config database -db orcl
Database unique name: orcl
Database name: orcl
Oracle home: /u02/app/oracle/product/19.0.0/db_1
Oracle user: oracle
Spfile: +DATA/orcl/PARAMETERFILE/spfile.278.1037360841
Password file: +DATA/orcl/PASSWORD/pwdorcl.256.1037359781  # <<<<<<<<<<<<<<<<
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC

有的友商还是习惯使用老的方式，将密码文件放到 $ORACLE_HOME/dbs 下面：

$ srvctl config database -db orcl
Database unique name: orcl
Database name: orcl
Oracle home: /oracle/app/oracle/product/19c/db_1
Oracle user: oracle
Spfile: +DATADG/orcl/PARAMETERFILE/spfile.277.1067941571
Password file:       # 空的
Domain: 
Start options: open
Stop options: immediate

oracle@rac1:/home/oracle> cd $ORACLE_HOME/dbs
oracle@rac1:/oracle/app/oracle/product/19c/db_1/dbs> ls -lrt
total 731256
-rw-r--r-- 1 oracle oinstall      3079 May 14  2015 init.ora
-rw-r--r-- 1 oracle oinstall        58 Mar 23  2021 initorcl1.ora
-rw-rw---- 1 oracle asmadmin      1544 Jul 17  2021 hc_orcl1.dat
-rw-rw---- 1 oracle asmadmin 124829696 Jul 26  2021 snapcf_orcl1.f
-rw-r----- 1 oracle oinstall      6144 Jan 25 16:47 orapworcl1   # <<<<<<<<<

小细节：每次使用sys登录数据库，都会更新这个密码文件，怀疑密码文件记录了最后登录时间。

oracle@rac1:/home/oracle> sqlplus sys/'Oracle'@192.168.0.25:1521/orcl as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jan 25 16:57:17 2022  # <<<<<<<<
Version 19.9.0.0.0

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

Last Successful login time: Tue Jan 25 2022 16:47:00 +08:00    

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0
oracle@rac1:/oracle/app/oracle/product/19c/db_1/dbs> ls -lrt
total 731256
-rw-r--r-- 1 oracle oinstall      3079 May 14  2015 init.ora
-rw-r--r-- 1 oracle oinstall        58 Mar 23  2021 initorcl1.ora
-rw-rw---- 1 oracle asmadmin      1544 Jul 17  2021 hc_orcl1.dat
-rw-rw---- 1 oracle asmadmin 124829696 Jul 26  2021 snapcf_orcl1.f
-rw-r----- 1 oracle oinstall      6144 Jan 25 16:57 orapworcl1  # <<<<<<<<

如果密码过期了会提示：
ORA-28002: the password will expire within 7 days

修改sys密码

SQL> alter user sys identified by "Oracle";

DB_UNIQUE_NAME       INSTANCE_NAME    SELECT_VI CHECK_TIME        USERNAME   PASSWORD_VERSIONS PROFILE         ACCOUNT_STATUS  LOCK_DATE         EXPIRY_DATE       PTIME             LLST
-------------------- ---------------- --------- ----------------- ---------- ----------------- --------------- --------------- ----------------- ----------------- ----------------- --------------------
orcl                 orcl1            DBA_USERS 20220125 17:10:42 SYS        10G 11G 12C       DEFAULT         OPEN                                                20210320 17:19:00
orcl                 orcl1            PWFILE    20220125 17:10:42 SYS                          DEFAULT         OPEN
orcl                 orcl1            USER$     20220125 17:10:42 SYS                                          0                                                   20210320 17:19:00

v$pwfile_users.EXPIRY_DATE 为 NULL。
最后将密码文件scp到RAC的其他节点和DataGuard的所有节点，主库 alter system archive log current; 切换日志，确保备库同步正常。
如果有什么脚本使用了sys的密码，也要注意及时更新脚本。