Why the ALL_OBJECTS doesn't show private synonyms? (Doc ID 2249527.1)

Last updated on APRIL 04, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.1 to 12.2 BETA1 [Release 9.2 to 12.2]
Information in this document applies to any platform.

Goal

Why when we query all_objects a private synonym is not shown , while if we query all_synonyms view it's shown?

TESTCASE
=========
SQL> conn / as sysdba
Connected.
SQL> grant create public synonym to usr1;

Grant succeeded.

SQL> conn usr1/usr1
Connected.
SQL> create public synonym test_pub_syn for test;

Synonym created.

SQL> grant select on test_pub_syn to usr2;

Grant succeeded.

SQL> conn usr2/usr2
Connected.
SQL> select * from all_objects
where
object_type = 'SYNONYM'
and object_name = 'TEST_PUB_SYN'
and owner = 'PUBLIC';

OWNER OBJECT_NAME
------------------------------ ------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- --------- ------------------- ------- - - -
PUBLIC TEST_PUB_SYN
59230 SYNONYM
02-FEB-09 02-FEB-09 2009-02-02:13:28:36 VALID N N N

But when we do :
===========

SQL> conn usr1/usr1
Connected.
SQL> create table tab1(id number);

Table created.

SQL> conn / as sysdba
Connected.
SQL> grant create synonym to usr1;

Grant succeeded.

SQL> conn usr1/usr1
Connected.
SQL> create synonym TAB for tab1;

Synonym created.

SQL> grant select on tab1 to usr2;

Grant succeeded.

SQL> conn usr2/usr2
Connected.
SQL> select * from tab;

no rows selected

SQL> select * from usr1.tab1;

no rows selected

 

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms