Skip to Main Content
  • Questions
  • private database link public synonym

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Angelo.

Asked: April 05, 2002 - 1:34 pm UTC

Last updated: August 07, 2020 - 12:33 am UTC

Version: 9.0.1

Viewed 10K+ times! This question is

You Asked

Tom,

What I am attempting to do is create a private database link then create a public synonym for that link.
I need the link to run procedeure A on the remote database.

i.e

Execute immediate procedure a @remote.database.

These reasoning behind the private link is a secure issue dictated by the higher offices.

Is this possible?

Is there a better way to do it?



and Tom said...

private is private.

public isn't private.

conflict of interest here.


If you could create a public synonym to expose a private dblink, it would be the same as a public database link -- hence it would raise the same "security" issues (whatever that might be -- i cannot think of any if everyone needs access to the other system, they need access don't they).

You would create a public database link. I'll be glad to address the real security concerns they might be having but I would need to know what they are exactly.


Another option for you could be:

a) create the private link "foo"
b) create a procedure a
as
begin
a@foo;
end;
c) grant execute on a
d) running A as other users will work. it'll run a@foo and foo will be the
private database link.





Rating

  (9 ratings)

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

Comments

A reader, April 05, 2002 - 2:13 pm UTC


Mike, April 11, 2002 - 10:29 am UTC

"
If you could create a public synonym to expose a private dblink, it would be the
same as a public database link -- hence it would raise the same "security"
issues (whatever that might be -- i cannot think of any if everyone needs access
to the other system, they need access don't they).
"

Tom, could you provid your benchmart test on the point of "security"??

Tom Kyte
April 11, 2002 - 10:38 am UTC

You need to tell me what your concerns with regards to a public database link are (assuming that the DATA does need to be exposed that is).

Look at the requirement in the question, then detail the concerns a public database link would bring out in such a circumstance.

I will address those concerns. Me, I haven't any.

Mike, April 11, 2002 - 4:01 pm UTC

Sorry for the 1st attemped posting, here is one I wanted to posted. Please remove the above one.

The following was copied from another forum. Do you agree with it. Please address in yours/benchmark test, if so.
Thanks so much.

"
The point is this: You can create a PUBLIC database link
that includes the authentication
information for a SINGLE USER on the remote database. For example:

In database A you create a public db link that connects to database B. The db
link is
configured to have all users sign in to database B as SYS/ORACLE, regardless of
the users
actual privileges in database A.
"

Tom Kyte
April 11, 2002 - 7:28 pm UTC

well, I quite simply question the santity of anyone using SYS in any capacity, let alone in a dblink

Hey -- did you know, you can run your cgi-bin's on your webserver as root, you have that ability. NOW, the question is -- would you? (no, no you wouldn't)

change sys to scott
change oracle to tiger

now, does that database link look so bad?

Inside Oracle we use public database links lots. There are two ways to setup the link:

a) as a proxy account (as above).. In this case, we setup the link with a username/password however the username is generally something like "PUBLIC_READ" and the password is "PASSWORD". This account has granted to it on the remote site "CREATE SESSION" and "SELECT <on specific views>". This is a generic account, anyone is allowed to read those views of the data.

b) not as a proxy account, meaning each user will log in as themselves. Here security is done user by user as normal.

Anyone that uses SYS -- well, they are <word deleted>.



How should we refer remote packages locally using private dblink

A reader, October 27, 2003 - 8:21 pm UTC

Hi Tom,

I have two accounts in my local database. One is the owner of the database schema and another is the application db account which has only access/update provilege on tables. No DDL privilege.

I have a package in the remote DB which refers TYPES etc.

In this case, how do i reference the remote package
locally using private db link.

Also, my remote package contains many stored procedures which has arguments.

How do i access this remote package locally with out PUBLIC database link. Up on successful local packge creation,
i need to provide execute privilege on this local package to Application db account (in local database).

Could you suggest a way of doing it.




Tom Kyte
October 28, 2003 - 7:27 am UTC

you refer to it in the same fashion as a public database link. The syntax of a dblink is the same regardless of the type.


I would suggest a synonym:

create synonym local_pkg for remote_pkg@private_db_link;

and then just use local_pkg as if it were a local package.

I am using SQL types in the remote package

A reader, October 28, 2003 - 10:44 am UTC

Hi Tom,

Thanks.

My remote package is using SQL types. In this case, Will i still able to access this remote package locally in local DB.

My local database has two accounts. One is the owner of the schema and another is the controlled db access account with necessary privilege.


I tried as below as local db schema owner.

create database link remotelink connect to <remoteuser> identified by <remotepasswd> using 'connectstring' ;

create synonym <package_name> for store_asr2iom_order_lps@remotelink ;

This remote package refers SQL types as below :

create or replace type asrtype as table of varchar2(18) ;

All the above synonym setup, i am doing it as a local db schema owner.

I need to give all these privilege to my local application db user (controlled db account) to execute the local package (actually remote package).

How do i grant these privilege to them.

Could you give an example if that would be possible.


note: i am using private database link.

Tom Kyte
October 28, 2003 - 10:57 am UTC

the types are not dblink friendly, no. if you need to pass inputs/outputs -- it'll not work. you would need to use plsql table types, that will work over a dblink.

Database link to my database...

Kashif, November 02, 2004 - 1:06 pm UTC

Hi Tom,

This might be a little off track and so feel free to ignore, but I was wondering if there's a way to know which database links point to my database. In other words, if someone created db link 'db123' in 'dbdev1' to database 'dbdev2', then if I am logged in in the 'dbdev2' database, is there a way I could determine the existence of the 'db123' db link to 'dbdev2' from 'dbdev1' without logging into 'dbdev1'? Hope this makes sense. Thanks!

Kashif

Tom Kyte
November 03, 2004 - 5:52 am UTC

you'd have to go to each "client" database -- dblinks are not registered in the database they "point to" since they don't really "point to" a database necessarily, they have a TNS connect string - meaning today dblink1 points to you but someone updates the tnsnames.ora and it points now to somewhere else.

Private DB links for users

Vidya, February 14, 2006 - 11:25 pm UTC

We have granted "Create database link" privilege to selected schema owners in different databases. These schema owners in turn create private db-links pointing to other databases. Can these schema owners grant access(DML-access) on their private DB-links to other users. If yes how is it possible.

Thanks,
Vidya

Tom Kyte
February 15, 2006 - 8:51 am UTC

assuming the privileges are in place, yes, you can use a view to hide the private database link and the other users can query the view:


ops$tkyte@ORA10GR2> create user a identified by a;
 
User created.
 
ops$tkyte@ORA10GR2> create user b identified by b;
 
User created.
 
ops$tkyte@ORA10GR2> grant create session, create database link, create view to a;
 
Grant succeeded.
 
ops$tkyte@ORA10GR2> grant create session to b;
 
Grant succeeded.
 
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> create table t ( x int );
 
Table created.
 
ops$tkyte@ORA10GR2> insert into t values ( 1 );
 
1 row created.
 
ops$tkyte@ORA10GR2> grant select on t to a with grant option;
 
Grant succeeded.
 
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> connect a/a
Connected.
a@ORA10GR2>
a@ORA10GR2> create database link remote
  2  connect to a identified by a
  3  using 'ora10gr2';
 
Database link created.
 
a@ORA10GR2>
a@ORA10GR2> create view vw
  2  as
  3  select * from ops$tkyte.t;
 
View created.
 
a@ORA10GR2>
a@ORA10GR2> grant select on vw to b;
 
Grant succeeded.
 
a@ORA10GR2> select * from vw;
 
         X
----------
         1
 
a@ORA10GR2>
a@ORA10GR2> connect b/b
Connected.
b@ORA10GR2> select * from ops$tkyte.t;
select * from ops$tkyte.t
                        *
ERROR at line 1:
ORA-00942: table or view does not exist
 
 
b@ORA10GR2> select * from a.vw;
 
         X
----------
         1
 

DB link Creation

Gaurav, April 05, 2006 - 6:59 am UTC

Its does'nt answer my question as i wanna create a DB link to a remote data base it is giving error "ORA-12154 TNS Service name could not be resolved" while executing a select query on a table at remote database

I am creating DB link

create Database link remote_link
connect to <username> indentified by <password> using 'Service Name';

Now Query :

Select Count(1) from B@remote_link ;

get error: "ORA-12154 TNS Service name could not be resolved

Thing to be wonder about that service name is define in my Tnsnames.ora file.

Tom Kyte
April 05, 2006 - 5:53 pm UTC

It'll be neat the day I can have one page that answers EVERYONES question - whether it was anticipated or not! That would be so cool. It would be rather long I suspect.


I guess I would have searched for ORA-12154 at least to see if something had been written about it?


But I digress - YOUR tnsnames.ora is not used by the server when it opens a database link.

The SERVERS tnsnames.ora (or whatever naming protocol it is using) is used.

Your tnsnames.ora - not useful.
The servers tnsnames.ora - very very useful.

Private Database Links in Views

JL, August 06, 2020 - 5:41 pm UTC

We recently encountered an issue where private database links were used in views but anyone with select access on that view could then leverage the private link via the view. My hope was that select on the view would fail with an error since the user running the select on the view is hitting a private database link that is not their own. Seems like this would be a security concern.
Connor McDonald
August 07, 2020 - 12:33 am UTC

Access in most things in Oracle is given from the owner.

For example, if you have a procedure P that accesses 100 tables, then when you grant access to P to another user, then whilst the user only has access to P, you could argue that they *indirectly* are accessing the 100 tables (as P runs).

The view with db link is the same. The *owner* of the view has explicitly granted access to that view to a user. It it literally a statement of: "I understand what this view is accessing and I am granting you the right to see it".


More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library