Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Pradeesh.

Asked: March 09, 2017 - 5:29 pm UTC

Last updated: March 15, 2017 - 2:39 am UTC

Version: 10g

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I have two databases wombat and foo. Womabt has two users craig and denver. I have created a private db_link with the same name in each users of wombat to connect to foo as follows :

owner DB_LINK
------ --------------------
craig albs.foo.labs.com

denver albs.foo.labs.com

and I have created a public synonym in in craig for the table fooaudit. But both users got the table with same name "fooaudit"

owner SYNONYM TABLE NAME DB_LINK
------ -------- ---------- -------------------
public abs_synm fooaudit albs.foo.labs.com


Now if I query as follows : select * from abs_synm; in any user/schema then which db_link the synonym will use either craig's or denver's ?


and Connor said...

Public synonyms do not have to have an owner qualification in the target object. For example.

Hint: When I say "For example", it means "Here is the test case you should have provided, which probably might have answered your question anyway :-)

--
-- source db
--
SQL> grant create session, create database link, create public synonym to craig identified by craig;

Grant succeeded.

SQL> grant create session, create database link to denver identified by denver;

Grant succeeded.
--
-- target db
--
SQL> grant create session to craig identified by craig;

Grant succeeded.

SQL> grant create session to denver identified by denver;

Grant succeeded.


--
-- source db
--
SQL> conn craig/craig
Connected.

SQL> create database link foo using 'db122';

Database link created.

SQL> select * from tab@foo;

no rows selected

SQL> create public synonym fooaudit for tab@foo;

Synonym created.

SQL> select * from fooaudit;

no rows selected

SQL> conn denver/denver
Connected.

SQL> create database link foo using 'db122';

Database link created.

SQL> select * from tab@foo;

no rows selected

--
-- Now you might think that this would fail.  
-- After all, DENVER does not have access to CRAIG's objects
--
-- but it works 
--
SQL> select * from fooaudit;

no rows selected


We can see the *why* by looking at the synonym definition

SQL> select * from dba_synonyms
  2  where synonym_name like 'FOO%'
  3  @pr
==============================
OWNER                         : PUBLIC
SYNONYM_NAME                  : FOOAUDIT
TABLE_OWNER                   :
TABLE_NAME                    : TAB
DB_LINK                       : FOO
ORIGIN_CON_ID                 : 0


The TABLE_OWNER is null, we'll use "DENVER" in the last case.

If you fully qualify the synonym, you'll get the expected results

SQL> drop public synonym fooaudit;

Synonym dropped.

SQL> create public synonym fooaudit for craig.my_table@foo;

Synonym created.

SQL> conn denver/denver
Connected.

SQL> select * from fooaudit;
select * from fooaudit
              *
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-02063: preceding line from FOO


SQL> conn craig/craig
Connected.

SQL> select * from fooaudit;

no rows selected


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Why public synonyms?

pmdba, March 14, 2017 - 10:30 am UTC

If these are the only two users and you want them to only use their private DB links to see specific tables, don't use public synonyms. Use private synonyms. If you want to share one user's synonym, build a view on that synonym and grant access to the view:

create synonym craig.fooaudit for fooaudit@foo;
create view craig.fooaudit_view as select * from craig.fooaudit;
grant select on craig.fooaudit_view to denver;
create synonym denver.fooaudit for craig.fooaudit_view;

Or

Create public synonym fooaudit for craig.fooaudit_view;


This will allow you to have more control over what is exposed through your DB links.
Connor McDonald
March 15, 2017 - 2:38 am UTC

Agree.

I'm not a fan of public synonyms...they munch your namespace so its hard to have multiple apps/schemas running in there. And they make things like edition based redefinition complicated (if not impossible) etc

Pradeesh Jayachandran, March 14, 2017 - 3:06 pm UTC

Thanks for the reply.

In the first case you created public synonym in craig as

> create public synonym fooaudit for tab@foo;

You specified that select * from fooaudit; will work for both users(craig and denver).

If both users are having a table with same name tab then whose table the synonym (without fully qualifying with schema name for target object) is referring here ? either craig's or denver's?








Connor McDonald
March 15, 2017 - 2:39 am UTC

Re-read the solution

"The TABLE_OWNER is null, we'll use DENVER in the last case."

ie, we use the user we are connected with via the db link.

Pradeesh Jayachandran, March 14, 2017 - 3:06 pm UTC

Thanks for the reply.

In the first case you created public synonym in craig as

> create public synonym fooaudit for tab@foo;

You specified that select * from fooaudit; will work for both users(craig and denver).

If both users are having a table with same name tab then whose table the synonym (without fully qualifying with schema name for target object) is referring here ? either craig's or denver's?