Solved

public or private links

Posted on 2014-03-02
3
324 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 22

Accepted Solution

by:
Steve Wales earned 250 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 250 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

821 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