VIEW: SYS.KU$_ROLE_VIEW

Source

View Text - Preformatted

select '1','1',
        u.user#,
        u.name,
        u.type#,
        u.password,
          to_char(u.ctime,'YYYY/MM/DD HH24:MI:SS'),
          to_char(u.ptime,'YYYY/MM/DD HH24:MI:SS'),
          to_char(u.exptime,'YYYY/MM/DD HH24:MI:SS'),
          to_char(u.ltime,'YYYY/MM/DD HH24:MI:SS'),
          u.resource$,
          replace(u.audit$,chr(0),'-'),
          u.defrole,
          u.defgrp#,
          u.defgrp_seq#,
          u.astatus,
          u.lcount,
          u.ext_username,
          u.spare1,
          u.spare2,
          u.spare3,
          u.spare4,
          u.spare5,
          to_char(u.spare6,'YYYY/MM/DD HH24:MI:SS'),
        (select r.schema from sys.approle$ r  where r.role#=u.user#),
        (select r.package from sys.approle$ r  where r.role#=u.user#)
  from sys.user$ u
  where   u.type# = 0
  AND (SYS_CONTEXT('USERENV','CURRENT_USERID') = 0
        OR EXISTS ( SELECT * FROM sys.session_roles
                   WHERE role='EXP_FULL_DATABASE' ))
UNION
  select '1','1',
        u.user#,
        u.name,
        u.type#,
        NULL,
          to_char(u.ctime,'YYYY/MM/DD HH24:MI:SS'),
          to_char(u.ptime,'YYYY/MM/DD HH24:MI:SS'),
          to_char(u.exptime,'YYYY/MM/DD HH24:MI:SS'),
          to_char(u.ltime,'YYYY/MM/DD HH24:MI:SS'),
          u.resource$,
          replace(u.audit$,chr(0),'-'),
          u.defrole,
          u.defgrp#,
          u.defgrp_seq#,
          u.astatus,
          u.lcount,
          u.ext_username,
          u.spare1,
          u.spare2,
          u.spare3,
          NULL,
          u.spare5,
          to_char(u.spare6,'YYYY/MM/DD HH24:MI:SS'),
        (select r.schema from sys.approle$ r  where r.role#=u.user#),
        (select r.package from sys.approle$ r  where r.role#=u.user#)
  from sys.user$ u
  where   u.type# = 0
     AND (SYS_CONTEXT('USERENV','CURRENT_USERID') != 0 )
     AND NOT (EXISTS ( SELECT * FROM sys.session_roles
                   WHERE role='EXP_FULL_DATABASE' ))
     AND (EXISTS ( SELECT * FROM sys.session_roles
                   WHERE role='SELECT_CATALOG_ROLE' ))

View Text - HTML Formatted

SELECT '1'
, '1'
, U.USER#
, U.NAME
, U.TYPE#
, U.PASSWORD
, TO_CHAR(U.CTIME
, 'YYYY/MM/DD HH24:MI:SS')
, TO_CHAR(U.PTIME
, 'YYYY/MM/DD HH24:MI:SS')
, TO_CHAR(U.EXPTIME
, 'YYYY/MM/DD HH24:MI:SS')
, TO_CHAR(U.LTIME
, 'YYYY/MM/DD HH24:MI:SS')
, U.RESOURCE$
, REPLACE(U.AUDIT$
, CHR(0)
, '-')
, U.DEFROLE
, U.DEFGRP#
, U.DEFGRP_SEQ#
, U.ASTATUS
, U.LCOUNT
, U.EXT_USERNAME
, U.SPARE1
, U.SPARE2
, U.SPARE3
, U.SPARE4
, U.SPARE5
, TO_CHAR(U.SPARE6
, 'YYYY/MM/DD HH24:MI:SS')
, (SELECT R.SCHEMA
FROM SYS.APPROLE$ R
WHERE R.ROLE#=U.USER#)
, (SELECT R.PACKAGE
FROM SYS.APPROLE$ R
WHERE R.ROLE#=U.USER#)
FROM SYS.USER$ U
WHERE U.TYPE# = 0
AND (SYS_CONTEXT('USERENV'
, 'CURRENT_USERID') = 0 OR EXISTS ( SELECT *
FROM SYS.SESSION_ROLES
WHERE ROLE='EXP_FULL_DATABASE' )) UNION SELECT '1'
, '1'
, U.USER#
, U.NAME
, U.TYPE#
, NULL
, TO_CHAR(U.CTIME
, 'YYYY/MM/DD HH24:MI:SS')
, TO_CHAR(U.PTIME
, 'YYYY/MM/DD HH24:MI:SS')
, TO_CHAR(U.EXPTIME
, 'YYYY/MM/DD HH24:MI:SS')
, TO_CHAR(U.LTIME
, 'YYYY/MM/DD HH24:MI:SS')
, U.RESOURCE$
, REPLACE(U.AUDIT$
, CHR(0)
, '-')
, U.DEFROLE
, U.DEFGRP#
, U.DEFGRP_SEQ#
, U.ASTATUS
, U.LCOUNT
, U.EXT_USERNAME
, U.SPARE1
, U.SPARE2
, U.SPARE3
, NULL
, U.SPARE5
, TO_CHAR(U.SPARE6
, 'YYYY/MM/DD HH24:MI:SS')
, (SELECT R.SCHEMA
FROM SYS.APPROLE$ R
WHERE R.ROLE#=U.USER#)
, (SELECT R.PACKAGE
FROM SYS.APPROLE$ R
WHERE R.ROLE#=U.USER#)
FROM SYS.USER$ U
WHERE U.TYPE# = 0
AND (SYS_CONTEXT('USERENV'
, 'CURRENT_USERID') != 0 )
AND NOT (EXISTS ( SELECT *
FROM SYS.SESSION_ROLES
WHERE ROLE='EXP_FULL_DATABASE' ))
AND (EXISTS ( SELECT *
FROM SYS.SESSION_ROLES
WHERE ROLE='SELECT_CATALOG_ROLE' ))