Re: Oracle 12c: ALL_SYNONYMS excludes Objects behind DB_LINKs

11.2 ALL_SYNONYMS DLL does the same test, it is just not "DB_LINK is null", but "s.node is null" - but it does the same thing. I don't have an 11.1 so I can see if there is "s.node is null"...

/Kim

On Wed, Jul 8, 2015 at 3:39 PM, Kim Berg Hansen <kibeha_at_gmail.com> wrote:

> Hi, Uwe
>
> Hmm... Documentation for ALL_SYNONYMS state:
>
> Synonyms that point to remote objects are excluded because the system
> privileges just listed do not automatically convey access to those remote
> objects. Also, if the synonyms point to objects other than tables and views
> (such as sequences, PL/SQL procedures, and so on) then this rule may show
> synonyms that ultimately resolve to objects that this session cannot access.
>
>
> That's a restriction to the rule that a schema with for example SELECT ANY
> TABLE privilege otherwise can see all synonyms, as the schema has obvious
> access to underlying tables.
>
> This is stated in docs for several versions:
>
> 12.1:
> http://docs.oracle.com/database/121/REFRN/GUID-DCDB52FF-8339-4EDE-B36A-2E12AFE25D33.htm
> 11.2:
> http://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_2100.htm#REFRN20273
> 11.1:
> http://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_2088.htm#REFRN20273
> 10.2:
> http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2091.htm#REFRN20273
>
> So it shouldn't be anything new.
>
> I've tried searching MOS if perhaps it used to be a bug that now is
> "fixed", but I can't find any info with a quick search.
>
>
> Looking at the 12.1 ALL_SYNONYMS DLL, it seems that the test for DB_LINK
> is null is for those cases, where synonym access is to be determined from
> grants, as such access cannot be determined on remote objects.
> While synonyms created in the calling schema or public will show also
> synonyms for remote objects.
>
>
> Your "missing" synonyms, are they created in the application schema or
> public?
> Or are they created in a third schema and your application schema used to
> be able (pre-migration) to access them because of grants? (Either object
> grants or SELECT ANY grants?)
>
> If the latter, it could look like you on 11.1 inadvertently were using a
> "security hole" that's been fixed?
> That's a theory, at least, though I can't find corroborating evidence ;-)
>
>
> Regards
>
>
> Kim Berg Hansen
>
> http://dspsd.blogspot.com
> kibeha_at_gmail.com
> _at_kibeha
>
>
>
> On Wed, Jul 8, 2015 at 2:53 PM, "Uwe Küchler" <uwe_at_kuechler.org> wrote:
>
>> Dear fellows of the Oracle,
>>
>> currently I have to deal with a customer's application that broke after
>> migrating from 11.1.0.7 to 12.1.0.2. The issue is, that the app gets an
>> object list from ALL_SYNONYMS expects some objects that are on a remote
>> DB. In 12c, this query doesn't return the expected rows anymore.
>>
>> Looking closer at the ALL_SYNONYMS DDL, it shows an additional "WHERE
>> db_link IS NULL" in two places. This wasn't there in 11.1 (I don't have
>> 11.2 at hand currently).
>> WTF?!
>> Searching MOS and Google, I haven't found any clue so far what made Oracle
>> do this. I can only guess that this was added for security reasons.
>>
>> Does anyone know more about Oracle's reasoning behind this? Or, even
>> better: Do you have a documentation pointer for me?
>>
>> Kind regards,
>> Uwe
>> --
>> http://oraculix.com/
>>
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>
>

--
http://www.freelists.org/webpage/oracle-l