Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

public or private links

Posted on 2014-03-02
3
Medium Priority
?
334 Views
Last Modified: 2014-03-03
1) is there any easy way to determine whether a database link is "public" or private?

2) where would you be able to tell, are there any clues in the dba_db_links table?

3) what exactly is meant by "private" link, and how can you tell which users have access to use this "private" link? I am assuming "public" essentially means anyone with an account in the local database can access the remote database.

4) also when a database link is active, how does a user query tables in that database once logged into their local database. do you have to add a database.table.colum type prefix to your sql?
0
Comment
Question by:pma111
3 Comments
 
LVL 3

Author Comment

by:pma111
ID: 39899044
for 1) would you refer to the "owner" column if its  a named user its a "private" link?
0
 
LVL 23

Accepted Solution

by:
Steve Wales earned 1000 total points
ID: 39899068
First, here's the doco on database links:

http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_5005.htm#SQLRF01205

Note that there are three different types:  Public, Shared and Private.

I created three different types, test1 being private, test2 being public and test3 being shared.  Then I queried dba_db_links:

SQL> select * from dba_db_links;

OWNER      DB_LINK    USERNAME   HOST       CREATED
---------- ---------- ---------- ---------- ----------
SYSTEM     TEST1      DEMO       demo       02-MAR-14
PUBLIC     TEST2      DEMO       demo       02-MAR-14
SYSTEM     TEST3      DEMO       demo       02-MAR-14

Open in new window


That should cover point 1 and 2.

Point 3 is also covered in the doco, but a private db link can only be used by the user that created it.  Public can be used by anyone in the local database and for shared the user has to be valid on the remote system.

Finally for point 4, if the table in the remote system is called remote_table and the database link is called test1, the query looks like this:

select * from remote_table@test1 where col1 = value1;

Hope that helps.
0
 
LVL 23

Assisted Solution

by:David
David earned 1000 total points
ID: 39899326
There another facet at the remote database, whether or not the schema_owner.table_name has SELECT privileges granted to PUBLIC, or is restricted to named users.  A link without access will, of course, fail.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

927 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question