VIEW: SYS.DBA_XS_USERS

Source

View Text - Preformatted

select o.name, p.guid, p.ext_src, decode(p.enable, 0, 'NO', 1, 'YES'),
       decode(p.status, 1, 'ACTIVE', 2, 'INACTIVE'), m.status,
       decode(mod(p.astatus, 16), 4, p.ltime,
                                  5, p.ltime,
                                  6, p.ltime,
                                  8, p.ltime,
                                  9, p.ltime,
                                  10, p.ltime, to_date(NULL)),
       decode(mod(p.astatus, 16),
              1, p.exptime,
              2, p.exptime,
              5, p.exptime,
              6, p.exptime,
              9, p.exptime,
              10,p.exptime,
              decode(p.ptime, '', to_date(NULL),
                decode(pr.limit#, 2147483647, to_date(NULL),
                 decode(pr.limit#, 0,
                   decode(dp.limit#, 2147483647, to_date(NULL), p.ptime +
                     dp.limit#/86400),
                   p.ptime + pr.limit#/86400)))),
       decode(p.profile#, NULL, NULL, pn.name),
       p.schema, p.start_date, p.end_date,
       decode(xsver.user#, NULL, 'NO', 'YES'),
       decode(xsver.type#, 2, 'SHA512',
                           1, 'SALTED SHA1',''), p.description
  from sys.xs$prin p, sys.xs$obj o, sys.user_astatus_map m,
       sys.profname$ pn, sys.profile$ pr, sys.profile$ dp,
       sys.xs$verifiers xsver
  where o.id = p.prin# and p.type = 0 and ((p.astatus = m.status#) or
       (p.astatus = (m.status# + 16 - BITAND(m.status#, 16))))
       and p.profile# = pn.profile#
       and p.profile# = pr.profile#
       and pr.type# = 1 and pr.resource# = 1
       and dp.profile# = 0
       and dp.type#=1
       and dp.resource#=1
       and p.prin# = xsver.user#(+)

View Text - HTML Formatted

SELECT O.NAME
, P.GUID
, P.EXT_SRC
, DECODE(P.ENABLE
, 0
, 'NO'
, 1
, 'YES')
, DECODE(P.STATUS
, 1
, 'ACTIVE'
, 2
, 'INACTIVE')
, M.STATUS
, DECODE(MOD(P.ASTATUS
, 16)
, 4
, P.LTIME
, 5
, P.LTIME
, 6
, P.LTIME
, 8
, P.LTIME
, 9
, P.LTIME
, 10
, P.LTIME
, TO_DATE(NULL))
, DECODE(MOD(P.ASTATUS
, 16)
, 1
, P.EXPTIME
, 2
, P.EXPTIME
, 5
, P.EXPTIME
, 6
, P.EXPTIME
, 9
, P.EXPTIME
, 10
, P.EXPTIME
, DECODE(P.PTIME
, ''
, TO_DATE(NULL)
, DECODE(PR.LIMIT#
, 2147483647
, TO_DATE(NULL)
, DECODE(PR.LIMIT#
, 0
, DECODE(DP.LIMIT#
, 2147483647
, TO_DATE(NULL)
, P.PTIME + DP.LIMIT#/86400)
, P.PTIME + PR.LIMIT#/86400))))
, DECODE(P.PROFILE#
, NULL
, NULL
, PN.NAME)
, P.SCHEMA
, P.START_DATE
, P.END_DATE
, DECODE(XSVER.USER#
, NULL
, 'NO'
, 'YES')
, DECODE(XSVER.TYPE#
, 2
, 'SHA512'
, 1
, 'SALTED SHA1'
, '')
, P.DESCRIPTION
FROM SYS.XS$PRIN P
, SYS.XS$OBJ O
, SYS.USER_ASTATUS_MAP M
, SYS.PROFNAME$ PN
, SYS.PROFILE$ PR
, SYS.PROFILE$ DP
, SYS.XS$VERIFIERS XSVER
WHERE O.ID = P.PRIN#
AND P.TYPE = 0
AND ((P.ASTATUS = M.STATUS#) OR (P.ASTATUS = (M.STATUS# + 16 - BITAND(M.STATUS#
, 16))))
AND P.PROFILE# = PN.PROFILE#
AND P.PROFILE# = PR.PROFILE#
AND PR.TYPE# = 1
AND PR.RESOURCE# = 1
AND DP.PROFILE# = 0
AND DP.TYPE#=1
AND DP.RESOURCE#=1
AND P.PRIN# = XSVER.USER#(+)