PS.
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@xxxxxxxxx> 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@xxxxxxxxx
@kibeha
On Wed, Jul 8, 2015 at 2:53 PM, "Uwe Küchler" <uwe@xxxxxxxxxxxx> 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/
--
//www.freelists.org/webpage/oracle-l
