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 ?