April 05, 2002 - 2:13 pm UTC
Reviewer: A reader
April 11, 2002 - 10:29 am UTC
Reviewer: Mike from NY,NY
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"??
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.
April 11, 2002 - 4:01 pm UTC
Reviewer: Mike from NY, NY
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
configured to have all users sign in to database B as SYS/ORACLE, regardless of
actual privileges in database A.
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
October 27, 2003 - 8:21 pm UTC
Reviewer: A reader
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.
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
October 28, 2003 - 10:44 am UTC
Reviewer: A reader
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.
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...
November 02, 2004 - 1:06 pm UTC
Reviewer: Kashif from Reston, VA
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!
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
February 14, 2006 - 11:25 pm UTC
Reviewer: Vidya from Arlington,VA
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.
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;
ops$tkyte@ORA10GR2> create user b identified by b;
ops$tkyte@ORA10GR2> grant create session, create database link, create view to a;
ops$tkyte@ORA10GR2> grant create session to b;
ops$tkyte@ORA10GR2> create table t ( x int );
ops$tkyte@ORA10GR2> insert into t values ( 1 );
1 row created.
ops$tkyte@ORA10GR2> grant select on t to a with grant option;
ops$tkyte@ORA10GR2> connect a/a
a@ORA10GR2> create database link remote
2 connect to a identified by a
3 using 'ora10gr2';
Database link created.
a@ORA10GR2> create view vw
3 select * from ops$tkyte.t;
a@ORA10GR2> grant select on vw to b;
a@ORA10GR2> select * from vw;
a@ORA10GR2> connect b/b
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;
DB link Creation
April 05, 2006 - 6:59 am UTC
Reviewer: Gaurav from INDIA
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.
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
August 06, 2020 - 5:41 pm UTC
Reviewer: JL from USA
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.
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".