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), 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>