5 Replies Latest reply: Nov 13, 2012 11:38 AM by EdStevens RSS

    Determining who created the public synonym

    Omega3
      Version:11.2.0.3



      When you create a public synonym , the owner will be shown under the pseudo user PUBLIC. So , you can't determine who created the public synonym. DBA_OBJECTS view doesn't have a separate object_type as PUBLIC DATABASE LINK
      SQL> conn / as sysdba
      Connected.
      SQL>
      SQL>
      SQL> grant create table , create public synonym to marko;
      
      Grant succeeded.
      
      ---- Marko creates a Public synonym for a table on its own schema
      SQL> conn marko/pass123
      Connected.
      SQL>
      SQL> create public synonym tojo for occ_tab1;
      
      Synonym created.
      
      SQL> conn / as sysdba
      Connected.
      SQL> select owner, count(*) from dba_synonyms group by owner order by count(*) desc;
      
      OWNER                            COUNT(*)
      ------------------------------ ----------
      PUBLIC                              23593
      ECDD_DEV                              169
      JVLLS_DEV                              40
      JVLLS                                  39
      TOM_BOWBJVLLS_MY_SIT_03                35
      JVLLS_TEMP                             34
      SYSTEM                                  8
      SYS                                     6
      JVLLSREADER                             2
      DBSNMP                                  1
      TESTUSR                                 1
      
      SQL> select * from dba_synonyms where SYNONYM_NAME = 'TOJO';
      
      OWNER      SYNONYM_NAME    TABLE_OWNER     TABLE_NAME      DB_LINK
      ---------- --------------- --------------- --------------- ----------
      PUBLIC     TOJO            MARKO           OCC_TAB1
      
      
      
      --- Marko creates a Public synonym for a table in another schema.
      --- But the dba_synonyms.owner column shows PUBLIC , so , there is no way to confirm who created the public synonym.
      
      SQL> create public synonym TIPPY for testuser.hrtb_emp;
      
      Synonym created.
      
      SQL>
      SQL> conn / as sysdba
      Connected.
      SQL> select owner, count(*) from dba_synonyms group by owner order by count(*) desc;
      
      OWNER                            COUNT(*)
      ------------------------------ ----------
      PUBLIC                              23594
      ECDD_DEV                              169
      JVLLS_DEV                              40
      JVLLS                                  39
      TOM_BOWBJVLLS_MY_SIT_03                35
      JVLLS_TEMP                             34
      SYSTEM                                  8
      SYS                                     6
      JVLLSREADER                             2
      DBSNMP                                  1
      TESTUSR                                 1
      
      
      
      SQL> select * from dba_synonyms where SYNONYM_NAME = 'TIPPY';
      
      OWNER      SYNONYM_NAME    TABLE_OWNER     TABLE_NAME      DB_LINK
      ---------- --------------- --------------- --------------- ----------
      PUBLIC     TIPPY           TESTUSER        HRTB_EMP
      
      SQL> select distinct object_type from dba_objects where object_type like '%SYN%';
      
      OBJECT_TYPE
      -------------------
      SYNONYM
      Is there anyway to determine that it is MARKO who created these synonyms ?