sys用户在sys.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), 65536NULLdecode(
       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),
            128decode(SYS_CONTEXT('USERENV''CON_ID'), 1'NO''YES'),
            4224decode(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
SQLcol username for a25
SQLcol account_status for a25
SQLcol profile for a30
SQLselect 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> 
扫描上面二维码关注我
如果你真心觉得文章写得不错,而且对你有所帮助,那就不妨帮忙“推荐"一下,您的“推荐”和”打赏“将是我最大的写作动力!
本文版权归作者所有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接.