This discussion is archived
5 Replies Latest reply: Nov 13, 2012 9:38 AM by EdStevens RSS

Determining who created the public synonym

Omega3 Newbie
Currently Being Moderated
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 ?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points