Oracle用户的PASSWORD_LIFE_TIME有哪些坑？

潇湘秦

于 2025-04-15 10:28:50 发布

阅读量760

点赞数 22

CC 4.0 BY-SA版权

分类专栏： oracle DBA oracle概念文章标签： ffmpeg oracle 数据库

本文链接：https://blog.csdn.net/xiaofan23z/article/details/147240818

oracle DBA 同时被 2 个专栏收录

63 篇文章

订阅专栏

oracle概念

28 篇文章

订阅专栏

前言

oracle用户的 PASSWORD_LIFE_TIME可能应用中都是设置为unlimited，但是关于PASSWORD_LIFE_TIME有几个坑需要大家知道，有时候一不小心可能会造成系统宕机。

请大家先思考如下几个问题？

场景一：数据库初始化时PASSWORD_LIFE_TIME为默认值180天，现在密码还有7天过期，将PASSWORD_LIFE_TIME设置为360，问7天后，用户还能正常登录吗？

场景二：数据库初始化时PASSWORD_LIFE_TIME为默认值180天，现在密码还有7天过期，将PASSWORD_LIFE_TIME设置为unlimited，问7天后，用户还能正常登录吗？

场景三：数据库当前PASSWORD_LIFE_TIME设置为UNLIMITED，为了应对内外审计将PASSWORD_LIFE_TIME为90天，截图，然后改回UNLIMITED，会不会有问题？

1.PASSWORD_LIFE_TIME改长有用吗？

这里找了一台很久没用的测试机来看看

SQL> set line 300
SQL>  select * from dba_profiles where profile='DEFAULT' and RESOURCE_NAME like 'PASSWORD_%';  

PROFILE              RESOURCE_NAME             RESOURCE_TYPE    LIMIT
-------------------- ------------------------- ---------------- --------------------------------------------------------------------------------
DEFAULT              PASSWORD_LIFE_TIME        PASSWORD         180
DEFAULT              PASSWORD_REUSE_TIME       PASSWORD         UNLIMITED
DEFAULT              PASSWORD_REUSE_MAX        PASSWORD         UNLIMITED
DEFAULT              PASSWORD_VERIFY_FUNCTION  PASSWORD         NULL
DEFAULT              PASSWORD_LOCK_TIME        PASSWORD         1
DEFAULT              PASSWORD_GRACE_TIME       PASSWORD         7

6 rows selected.
##可以看到目前PASSWORD_LIFE_TIME是默认值180天SQL>  select USERNAME,EXPIRY_DATE,ACCOUNT_STATUS from dba_users where username='MYCIM';  

USERNAME             EXPIRY_DATE         ACCOUNT_STATUS
-------------------- ------------------- --------------------
MYCIM                2024-04-16 15:03:23 OPEN
##密码早已经过期，但是状态还是open的？
SQL>   SELECT name,ctime,ptime,exptime FROM SYS."USER$" WHERE name='MYCIM';##在基表sys.user$看到CTIME是用户创建时间，PTIME是最近一次的密码修改时间，EXPTIME是密码过期时间

NAME                                                         CTIME               PTIME               EXPTIME
------------------------------------------------------------ ------------------- ------------------- -------------------
MYCIM                                                        2023-02-22 10:49:03 2023-10-19 15:03:23 2023-08-28 10:50:26

SQL> conn mycim/mycim
ERROR:
ORA-28002: the password will expire within 7 days
##用户仍然可以连接 提示ORA-28002
Connected.
SQL>

将PASSWORD_LIFE_TIME改长会怎么样？

SQL> alter profile default limit PASSWORD_LIFE_TIME 720;

Profile altered.

SQL>  select USERNAME,EXPIRY_DATE,ACCOUNT_STATUS from dba_users where username='MYCIM';

USERNAME             EXPIRY_DATE         ACCOUNT_STATUS
-------------------- ------------------- --------------------
MYCIM                2025-04-21 14:47:28 EXPIRED(GRACE)
SQL> conn mycim/mycim
ERROR:
ORA-28002: the password will expire within 7 days

##虽然将密码有效期修改的特别长，但是修改后，账号状态变为EXPIRED(GRACE)延缓期，EXPIRY_DATE变为当前日期+7（PASSWORD_GRACE_TIME 的值），登陆仍然ORA-28002
Connected.
SQL>  alter profile default limit PASSWORD_LIFE_TIME  unlimited;

Profile altered.

SQL> select USERNAME,EXPIRY_DATE,ACCOUNT_STATUS from dba_users where username='MYCIM';

USERNAME             EXPIRY_DATE         ACCOUNT_STATUS
-------------------- ------------------- --------------------
MYCIM                2025-04-21 14:47:28 EXPIRED(GRACE)

SQL>  SELECT name,ctime,ptime,exptime FROM SYS."USER$" WHERE name='MYCIM';

NAME                                                         CTIME               PTIME               EXPTIME
------------------------------------------------------------ ------------------- ------------------- -------------------
MYCIM                                                        2023-02-22 10:49:03 2023-10-19 15:03:23 2025-04-21 14:47:28

##将PASSWORD_LIFE_TIME 设置为unlimited后 都没有改变
SQL> alter user mycim identified by mycim;

User altered.

SQL>  select USERNAME,EXPIRY_DATE,ACCOUNT_STATUS from dba_users where username='MYCIM';

USERNAME             EXPIRY_DATE         ACCOUNT_STATUS
-------------------- ------------------- --------------------
MYCIM                                    OPEN

SQL>  SELECT name,ctime,ptime,exptime FROM SYS."USER$" WHERE name='MYCIM';

NAME                                                         CTIME               PTIME               EXPTIME
------------------------------------------------------------ ------------------- ------------------- -------------------
MYCIM                                                        2023-02-22 10:49:03 2025-04-14 15:29:30 2025-04-21 14:47:28

##修改密码后 账号状态恢复正常，但是基表的EXPTIME并未改变？

2.应对审计短暂改一下PASSWORD_LIFE_TIME会怎么样？

如下是数据库当前profile设置和用户的状态，都是正常的

SQL> select * from dba_profiles where profile='DEFAULT' and RESOURCE_NAME like 'PASSWORD_%';
PROFILE              RESOURCE_NAME             RESOURCE_TYPE    LIMIT
-------------------- ------------------------- ---------------- --------------------------------------------------------------------------------
DEFAULT              PASSWORD_LIFE_TIME        PASSWORD         UNLIMITED
DEFAULT              PASSWORD_REUSE_TIME       PASSWORD         UNLIMITED
DEFAULT              PASSWORD_REUSE_MAX        PASSWORD         UNLIMITED
DEFAULT              PASSWORD_VERIFY_FUNCTION  PASSWORD         NULL
DEFAULT              PASSWORD_LOCK_TIME        PASSWORD         1
DEFAULT              PASSWORD_GRACE_TIME       PASSWORD         7

6 rows selected.

SQL>select USERNAME,EXPIRY_DATE,ACCOUNT_STATUS from dba_users where username='MYCIM';
USERNAME             EXPIRY_DATE         ACCOUNT_STATUS
-------------------- ------------------- --------------------
MYCIM                                    OPEN

SQL>SELECT name,ctime,ptime,exptime FROM SYS."USER$" WHERE name='MYCIM';  ##注意这里的PTIME

NAME                                                         CTIME               PTIME               EXPTIME
------------------------------------------------------------ ------------------- ------------------- -------------------
MYCIM                                                        2023-02-23 09:37:06 2025-04-10 17:15:48 2024-05-02 19:28:51

为了模拟我这里直接将PASSWORD_LIFE_TIME设置为1

SQL>  alter profile default limit PASSWORD_LIFE_TIME 1;

Profile altered.

SQL> select USERNAME,EXPIRY_DATE,ACCOUNT_STATUS from dba_users where username='MYCIM';  ##注意这里的EXPIRY_DATE

USERNAME             EXPIRY_DATE         ACCOUNT_STATUS
-------------------- ------------------- --------------------   
MYCIM                2025-04-11 17:15:48 OPEN

SQL> conn mycim/mycim
ERROR:
ORA-28002: the password will expire within 7 days


Connected.

再将PASSWORD_LIFE_TIME改为unlimited 会恢复正常吗？

SQL>   alter profile default limit PASSWORD_LIFE_TIME unlimited;

Profile altered.

SQL>  select USERNAME,EXPIRY_DATE,ACCOUNT_STATUS from dba_users where username='MYCIM';   ##可以看到用户的EXPIRY_DATE和 ACCOUNT_STATUS都变了

USERNAME             EXPIRY_DATE         ACCOUNT_STATUS
-------------------- ------------------- --------------------
MYCIM                2025-04-22 09:11:33 EXPIRED(GRACE)

SQL>  SELECT name,ctime,ptime,exptime FROM SYS."USER$" WHERE name='MYCIM';

NAME                                                         CTIME               PTIME               EXPTIME
------------------------------------------------------------ ------------------- ------------------- -------------------
MYCIM                                                        2023-02-23 09:37:06 2025-04-10 17:15:48 2025-04-22 09:11:33

这里就一定需要修改用户的密码才能恢复

SQL> alter user mycim identified by mycim;

User altered.

SQL> select USERNAME,EXPIRY_DATE,ACCOUNT_STATUS from dba_users where username='MYCIM';

USERNAME             EXPIRY_DATE         ACCOUNT_STATUS
-------------------- ------------------- --------------------
MYCIM                                    OPEN

3.EXPIRY_DATE如何计算？

如果要理清EXPIRY_DATE和EXPTIME的逻辑关系需要看一下DBA_USERS这个视图的ddl sql，通过SQL可以找到EXPIRY_DATE和EXPTIME的对应关系，以及整个的计算逻辑才能真正的理清。


  CREATE OR REPLACE FORCE VIEW "SYS"."DBA_USERS" ("USERNAME", "USER_ID", "PASSWORD", "ACCOUNT_STATUS", "LOCK_DATE", "EXPIRY_DATE", "DEFAULT_TABLESPACE", "TEMPORARY_TABLESPACE", "CREATED", "PROFILE", "INITIAL_RSRC_CONSUMER_GROUP", "EXTERNAL_NAME", "PASSWORD_VERSIONS", "EDITIONS_ENABLED", "AUTHENTICATION_TYPE") AS 
  select u.name, u.user#,
       decode(u.password, 'GLOBAL', u.password,
                          'EXTERNAL', u.password,
                          NULL),
       m.status,
       decode(u.astatus, 4, u.ltime,
                         5, u.ltime,
                         6, u.ltime,
                         8, u.ltime,
                         9, u.ltime,
                         10, u.ltime, to_date(NULL)),
       decode(u.astatus,
              1, u.exptime,
              2, u.exptime,
              5, u.exptime,
              6, u.exptime,
              9, u.exptime,
              10, u.exptime,
              decode(u.ptime, '', to_date(NULL),
                decode(pr.limit#, 2147483647, to_date(NULL),
                 decode(pr.limit#, 0,
                   decode(dp.limit#, 2147483647, to_date(NULL), u.ptime +
                     dp.limit#/86400),
                   u.ptime + pr.limit#/86400)))),
       dts.name, tts.name, u.ctime, p.name,
       nvl(cgm.consumer_group, 'DEFAULT_CONSUMER_GROUP'),
       u.ext_username,
       decode(length(u.password),16,'10G ',NULL)||NVL2(u.spare4, '11G ' ,NULL),
       decode(bitand(u.spare1, 16),
              16, 'Y',
                  'N'),
       decode(u.password, 'GLOBAL',   'GLOBAL',
                          'EXTERNAL', 'EXTERNAL',
                          'PASSWORD')
       from sys.user$ u left outer join sys.resource_group_mapping$ cgm
            on (cgm.attribute = 'ORACLE_USER' and cgm.status = 'ACTIVE' and
                cgm.value = u.name),
            sys.ts$ dts, sys.ts$ tts, sys.profname$ p,
            sys.user_astatus_map m, sys.profile$ pr, sys.profile$ dp
       where u.datats# = dts.ts#
       and u.resource$ = p.profile#
       and u.tempts# = tts.ts#
       and u.astatus = m.status#
       and u.type# = 1
       and u.resource$ = pr.profile#
       and dp.profile# = 0
       and dp.type#=1
       and dp.resource#=1
       and pr.type# = 1
       and pr.resource# = 1

过期时间计算关键部分为如下decode部分

decode(u.astatus,
  1, u.exptime,
  2, u.exptime,
  5, u.exptime,
  6, u.exptime,
  9, u.exptime,
  10, u.exptime,
 -- astatus的状态为1，2，5，6，9，10时两个值是一样的， -- 其他情况：动态计算
  decode(u.ptime, '', to_date(NULL),
    decode(pr.limit#, 2147483647, to_date(NULL),
      decode(pr.limit#, 0,
        decode(dp.limit#, 2147483647, to_date(NULL), u.ptime + dp.limit#/86400),
        u.ptime + pr.limit#/86400))))

astatus的状态为1，2，5，6，9，10时 EXPIRY_DATE和EXPTIME两个值是一样的，用户的状态可以查看如下系统表，也就是只有当状态为OPEN，LOCKED(TIMED)，LOCKED时才会动态计算。

SELECT * FROM sys.user_astatus_map ORDER BY status#;0	OPEN
1	EXPIRED
2	EXPIRED(GRACE)
4	LOCKED(TIMED)
5	EXPIRED & LOCKED(TIMED)
6	EXPIRED(GRACE) & LOCKED(TIMED)
8	LOCKED
9	EXPIRED & LOCKED
10	EXPIRED(GRACE) & LOCKED

参数介绍

u.ptime: 用户上次修改密码的时间（来自 user$ 表）
pr.limit#: 用户当前配置的 PROFILE 中 PASSWORD_LIFE_TIME（密码有效期），单位是秒（来自 profile$ 表）
dp.limit#: 默认 PROFILE 的 PASSWORD_LIFE_TIME 值（对应 profile#=0）
2147483647: 相当于无限（UNLIMITED）的标识数值

计算逻辑

decode(u.ptime, '', to_date(NULL), ...)
- 如果 u.ptime 是空字符串，表示用户从未修改过密码 → EXPIRY_DATE 为 NULL
否则，继续往下判断：
- decode(pr.limit#, 2147483647, to_date(NULL), ...)
  - 如果该用户当前 PROFILE 的密码有效期为 UNLIMITED → EXPIRY_DATE 为 NULL
否则，继续判断：
- decode(pr.limit#, 0, ...)
  - 如果当前 PROFILE 的密码有效期为 0（特殊值，视为"未设置"）：
    - 判断默认 PROFILE（profile# = 0）中的 PASSWORD_LIFE_TIME 是否为 UNLIMITED（2147483647）：
      - 如果是 → NULL
      - 否则 → u.ptime + dp.limit#/86400（将秒换算为天加上）
否则（即 pr.limit# > 0 且不为特殊值）：
- 使用 u.ptime + pr.limit#/86400 计算密码过期时间

简单一个图为如下

4. oracle用户密码过期时间影响因子

A.用户的状态

EXPIRED类的状态DBA_USERS.EXPIRY_DATE均是和基表user$.exptime相同，其他OPEN和LOCK类均是要动态计算

`ASTATUS` 值	状态名称	说明	`DBA_USERS.EXPIRY_DATE` 是否显示 `EXPTIME`
0	OPEN	账号正常，无过期无锁定	❌ 否（进入动态计算）
4	LOCKED(TIMED)	被自动锁定（通常因登录失败次数过多）	❌ 否（进入动态计算）
8	LOCKED	被手动锁定	❌ 否（进入动态计算）
1	EXPIRED	密码已过期	✅ 是
2	EXPIRED(GRACE)	密码过期宽限期中	✅ 是
5	EXPIRED & LOCKED(TIMED)	密码过期 + 自动锁定	✅ 是
6	EXPIRED(GRACE) & LOCKED(TIMED)	密码宽限期中 + 自动锁定	✅ 是
9	EXPIRED & LOCKED	密码过期 + 手动锁定	✅ 是
10	EXPIRED(GRACE) & LOCKED	宽限期中 + 手动锁定	✅ 是

B.动态计算影响因子

影响因素为PASSWORD_LIFE_TIME 和PTIME（最后一次密码修改时间）如果用户状态是EXPIRED类，那么想恢复正常一定要修改密码。

5.检查密码有效期的脚本

1.检查最近七天即将密码过期的账号（适用11G-19C）

SELECT
	u.username,
	u.account_status,
	u.profile,
	p.limit AS password_life_time,
	u.expiry_date,
	TRUNC(u.expiry_date - SYSDATE) AS days_until_expire
FROM
	dba_users u
JOIN
  dba_profiles p
  ON
	u.profile = p.profile
WHERE
	p.resource_name = 'PASSWORD_LIFE_TIME'
	AND p.limit != 'UNLIMITED'
	AND u.username NOT IN ('SYS', 'SYSTEM')
	AND u.expiry_date < SYSDATE + 7
ORDER BY
	days_until_expire;

2.批量“刷新”密码有效期（密码不变，重置生效时间）仅适用于11G

该sql谨慎使用因为用了基表SYS.USER$的password，官方不建议。

 BEGIN
  FOR usr IN (
    SELECT username FROM dba_users
    WHERE account_status = 'OPEN'
      AND username NOT IN ('SYS', 'SYSTEM')  -- 排除系统用户
  ) LOOP
    EXECUTE IMMEDIATE 'ALTER USER ' || usr.username || ' IDENTIFIED BY VALUES (SELECT password FROM sys.user$ WHERE name = ''' || usr.username || ''')';
  END LOOP;
END;
/

总结

如果不了解用户密码的过期时间的计算逻辑，很可能遇到前面这几个坑，特别是场景三中在面对审计时，以为将PASSWORD_LIFE_TIME 改一下截个图，再改回去就完事了？实际上可能留了一个大坑，甚至造成系统宕机的风险，特别是对于应用账号，只有应用使用，ORA-28002不影响系统运行，直到账号过期无法使用，造成应用宕机，所以针对PASSWORD_LIFE_TIME 的修改大家要谨慎，涉及到修改PASSWORD_LIFE_TIME 参数后一定要检查用户的状态。

参考文档：ORA-28002 On User Connection Immediately After PASSWORD_LIFE_TIME Changed (Doc ID 162818.1)