public or private links

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?
LVL 4
pma111Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pma111Author Commented:
for 1) would you refer to the "owner" column if its  a named user its a "private" link?
0
Steve WalesSenior Database AdministratorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DavidSenior Oracle Database AdministratorCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.