sys用户在sys.user$中的一些特殊现象浅析，dba_user的定

在Oracle数据库中，一般我们使用sys.user或dba_users去监控/检查用户密码是否快过期，另外，它还能监控新用户的创建时间、账号密码过期、账号修改时间等，dba_user其实是一个系统视图，它的数据来源于sys.user$等基础表。dba_user的定义如下所示：

CREATE FORCE VIEW "SYS"."DBA_USERS" ("USERNAME", "USER_ID", "PASSWORD", "ACCOUNT_STATUS", "LOCK_DATE", "EXPIRY_DATE", "DEFAULT_TABLESPACE", "TEMPORARY_TABLESPACE", "LOCAL_TEMP_TABLESPACE", "CREATED", "PROFILE", "INITIAL_RSRC_CONSUMER_GROUP", "EXTERNAL_NAME", "PASSWORD_VERSIONS", "EDITIONS_ENABLED", "AUTHENTICATION_TYPE", "PROXY_ONLY_CONNECT", "COMMON", "LAST_LOGIN", "ORACLE_MAINTAINED", "INHERITED", "DEFAULT_COLLATION", "IMPLICIT", "ALL_SHARD", "PASSWORD_CHANGE_DATE") AS
  select u.name, u.user#,
     decode(u.password, 'GLOBAL', u.password,
                        'EXTERNAL', u.password,
                        NULL),
     m.status,
     decode(mod(u.astatus, 16), 4, u.ltime,
                                5, u.ltime,
                                6, u.ltime,
                                8, u.ltime,
                                9, u.ltime,
                                10, u.ltime, to_date(NULL)),
     decode(mod(u.astatus, 16),
            1, u.exptime,
            2, u.exptime,
            5, u.exptime,
            6, u.exptime,
            9, u.exptime,
            10, u.exptime,
            decode(u.password, 'GLOBAL', to_date(NULL),
                               'EXTERNAL', to_date(NULL),
              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, ltts.name,
     u.ctime, p.name,
     nvl(cgm.consumer_group, 'DEFAULT_CONSUMER_GROUP'),
     u.ext_username,
     decode(bitand(u.spare1, 65536), 65536, NULL, decode(
       REGEXP_INSTR(
         NVL2(u.password, u.password, ' '),
         '^                $'
       ),
       0,
       decode(length(u.password), 16, '10G ', NULL),
       ''
     ) ||
     decode(
       REGEXP_INSTR(
         REGEXP_REPLACE(
           NVL2(u.spare4, u.spare4, ' '),
           'S:000000000000000000000000000000000000000000000000000000000000',
           'not_a_verifier'
         ),
         'S:'
       ),
       0, '', '11G '
     ) ||
     decode(
       REGEXP_INSTR(
         NVL2(u.spare4, u.spare4, ' '),
         'T:'
       ),
       0, '', '12C '
     ) ||
     decode(
       REGEXP_INSTR(
         REGEXP_REPLACE(
           NVL2(u.spare4, u.spare4, ' '),
           'H:00000000000000000000000000000000',
           'not_a_verifier'
         ),
         'H:'
       ),
       0, '', 'HTTP '
     )),
     decode(bitand(u.spare1, 16),
            16, 'Y',
                'N'),
     decode(bitand(u.spare1,65536), 65536, 'NONE',
                   decode(u.password, 'GLOBAL',   'GLOBAL',
                                      'EXTERNAL', 'EXTERNAL',
                                      'PASSWORD')),
     decode(bitand(u.spare1, 10272),
            32, 'Y', 2048, 'Y',  2080, 'Y',
          8192, 'Y', 8224, 'Y', 10240, 'Y',
         10272, 'Y',
                'N'),
     decode(bitand(u.spare1, 128), 0, 'NO', 'YES'),
    from_tz(to_timestamp(to_char(u.spare6, 'DD-MON-YYYY HH24:MI:SS'),
                          'DD-MON-YYYY HH24:MI:SS'), '0:00')
     at time zone sessiontimezone,
     decode(bitand(u.spare1, 256), 256, 'Y', 'N'),
     decode(bitand(u.spare1, 4224),
            128, decode(SYS_CONTEXT('USERENV', 'CON_ID'), 1, 'NO', 'YES'),
            4224, decode(SYS_CONTEXT('USERENV', 'IS_APPLICATION_PDB'),
                         'YES', 'YES', 'NO'),
            'NO'),
     nls_collation_name(nvl(u.spare3, 16382)),
     -- IMPLICIT
     decode(bitand(u.spare1, 32768), 32768, 'YES', 'NO'),
     -- ALL_SHARD
     decode(bitand(u.spare1, 16384), 16384, 'YES', 'NO'),
     -- PASSWORD_CHANGE_DATE
     u.ptime
     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) left outer join sys.ts$ ltts
                                       on (u.spare9 = ltts.ts#),
          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#) or
          (u.astatus = (m.status# + 16 - BITAND(m.status#, 16))))
     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;

但是在sys.user$中，sys用户非常特殊，下面简单介绍一下一些特殊现象(问题)，希望你在遇到时有所帮助，下面测试的版本为Oracle 19.16.0.0.0等好几个版本测试环境

现象1：sys用户的expiry_date不准确。

一般我们给用户sys/system等用户设置一个自定义profile，密码60天过期，但是临近密码快过期,你监控或检查密码快过期时，你会发现sys用户密码过期时间不正确(system用户正确)。

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> set pagesize 60 linesize 200
SQL> col username for a25
SQL> col account_status for a25
SQL> col profile for a30
SQL> select username,account_status,profile,expiry_date,password_versions from dba_users
  2  where username IN ('SYS','SYSTEM')
  3  order by 2,4,1
  4  /

USERNAME   ACCOUNT_STATUS   PROFILE           EXPIRY_DATE         PASSWORD_VERSIONS
---------- ---------------- ----------------- ------------------- ---------------------------------------------------
SYS        OPEN             C##DBA_PROFILE    2021-09-21 12:23:59 10G 11G 12C HTTP
SYSTEM     OPEN             C##DBA_PROFILE    2024-07-08 16:34:03 10G 11G 12C HTTP

SQL>

如上所示，sys用户的EXPIRE_DATE似乎跟其它账号不一样，它一成不变，它的值不准确（原因稍后述说）。

现象2：sys用户的用户密码修改后，ptime（用户密码修改时间）不会变化。

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
SQL> select name,ctime,ptime,exptime from sys.user$ where name='SYS';

NAME               CTIME               PTIME               EXPTIME
------------------ ------------------- ------------------- -------------------
SYS                2019-04-17 00:56:32 2019-04-17 00:56:32 2021-09-21 12:23:59
SQL> alter user sys identified by "KjdhuHd31837KJD";
SQL> select name,ctime,ptime,exptime from sys.user$ where name='SYS';

NAME               CTIME               PTIME               EXPTIME
------------------ ------------------- ------------------- -------------------
SYS                2019-04-17 00:56:32 2019-04-17 00:56:32 2021-09-21 12:23:59
SQL> 

其实有些版本（例如Oracle 12.2.0.1）是因为Bug问题(Bug 28538439 )，例如Bug 28538439 - USER$.PTIME Is Not Updated for SYS User After Applying 12.2.0.1.180717 RU (Doc ID 28538439.8)

而有些则是因为隐藏参数设置问题。如下所示

SQL> set linesize 680;
SQL> col inst_id for 99999
SQL> col con_id for 99999
SQL> col name for a20;
SQL> col description for a18;
SQL> col value for a8;
SQL> select x.inst_id
  2       , y.con_id
  3       , x.ksppinm  name
  4       , x.ksppdesc description
  5       , y.ksppstvl  value
  6       , y.ksppstdf  isdefault
  7       , decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE')  ismod
  8       , decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE')  isadj 
  9  from sys.x$ksppi x, sys.x$ksppcv y 
 10  where 1=1 and x.inst_id = y.inst_id and x.indx = y.indx and x.ksppinm like '%_enable_ptime%';

INST_ID CON_ID NAME                 DESCRIPTION        VALUE    ISDEFAULT ISMOD      ISADJ
------- ------ -------------------- ------------------ -------- --------- ---------- -----
      1      1 _enable_ptime_update Enable/Disable upd FALSE    TRUE      FALSE      FALSE
               _for_sys             ate of user$ for s
                                    ys

SQL> 

我们修改隐藏参数_enable_ptime_update_for_sys的值，然后重启数据库数据库。

alter system set "_enable_ptime_update_for_sys"=true scope=spfile;
SQL> alter system set "_enable_ptime_update_for_sys"=true scope=spfile;

注意：有些数据库版本由于补丁缘故，可能不存在隐藏参数_enable_ptime_update_for_sys。

数据库实例重启过后，我们重新测试，你会发现修改sys密码后，密码修改时间（PTIME)会正确更新，如下所示：

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
SQL> select name,ctime,ptime,exptime from sys.user$ where name='SYS';

NAME                 CTIME               PTIME               EXPTIME
-------------------- ------------------- ------------------- -------------------
SYS                  2019-04-17 00:56:32 2019-04-17 00:56:32 2024-04-13 21:51:05
SQL> alter user sys identified by "KjdhuHd31837KJD1";
SQL> select name,ctime,ptime,exptime from sys.user$ where name='SYS';

NAME                 CTIME               PTIME               EXPTIME
-------------------- ------------------- ------------------- -------------------
SYS                  2019-04-17 00:56:32 2023-10-16 22:34:32 2024-04-13 22:34:32
SQL>