VIEW: SYS.ALL_SYNONYMS

Source

View Text - Preformatted

select OWNER, SYNONYM_NAME, TABLE_OWNER, TABLE_NAME, DB_LINK,
       ORIGIN_CON_ID
from INT$DBA_SYNONYMS
where (
       OWNER = SYS_CONTEXT('USERENV', 'CURRENT_USER')
       or OWNER = 'PUBLIC'
       or /* local object, and user has system privileges */
         (DB_LINK is null
          and exists (select null from v$enabledprivs
                 where priv_number in (-45 /* LOCK ANY TABLE */,
                                       -47 /* SELECT ANY TABLE */,
                                       -397/* READ ANY TABLE */,
                                       -48 /* INSERT ANY TABLE */,
                                       -49 /* UPDATE ANY TABLE */,
                                       -50 /* DELETE ANY TABLE */)
                     )
         )
       or /* user has any privs on base object in local database */
        exists
        (select null
         from sys.objauth$ ba, sys."_CURRENT_EDITION_OBJ" bo, sys.user$ bu
         where DB_LINK is null
           and bu.name = TABLE_OWNER
           and bo.name = TABLE_NAME
           and bu.user# = bo.owner#
           and ba.obj# = bo.obj#
           and (   ba.grantee# in (select kzsrorol from x$kzsro)
                or ba.grantor# = USERENV('SCHEMAID')
               )
        )
      )
union
select st.SYN_OWNER, st.SYN_SYNONYM_NAME, st.SYN_TABLE_OWNER,
       st.SYN_TABLE_NAME, st.SYN_DB_LINK, st.ORIGIN_CON_ID
from sys."_ALL_SYNONYMS_TREE" st

View Text - HTML Formatted

SELECT OWNER
, SYNONYM_NAME
, TABLE_OWNER
, TABLE_NAME
, DB_LINK
, ORIGIN_CON_ID FROM INT$DBA_SYNONYMS WHERE ( OWNER = SYS_CONTEXT('USERENV'
, 'CURRENT_USER') OR OWNER = 'PUBLIC' OR /* LOCAL OBJECT
,
AND USER HAS SYSTEM PRIVILEGES */ (DB_LINK IS NULL
AND EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (-45 /* LOCK ANY TABLE */
, -47 /* SELECT ANY TABLE */
, -397/* READ ANY TABLE */
, -48 /* INSERT ANY TABLE */
, -49 /* UPDATE ANY TABLE */
, -50 /* DELETE ANY TABLE */) ) ) OR /* USER HAS ANY PRIVS ON BASE OBJECT IN LOCAL DATABASE */ EXISTS (SELECT NULL
FROM SYS.OBJAUTH$ BA
, SYS."_CURRENT_EDITION_OBJ" BO
, SYS.USER$ BU
WHERE DB_LINK IS NULL
AND BU.NAME = TABLE_OWNER
AND BO.NAME = TABLE_NAME
AND BU.USER# = BO.OWNER#
AND BA.OBJ# = BO.OBJ#
AND ( BA.GRANTEE# IN (SELECT KZSROROL
FROM X$KZSRO) OR BA.GRANTOR# = USERENV('SCHEMAID') ) ) ) UNION SELECT ST.SYN_OWNER
, ST.SYN_SYNONYM_NAME
, ST.SYN_TABLE_OWNER
, ST.SYN_TABLE_NAME
, ST.SYN_DB_LINK
, ST.ORIGIN_CON_ID FROM SYS."_ALL_SYNONYMS_TREE" ST