Home » SQL & PL/SQL » SQL & PL/SQL » how to grant database link to user
how to grant database link to user [message #633380] Tue, 17 February 2015 06:02 Go to next message
qanita786
Messages: 229
Registered: May 2007
Location: PAKISTAN
Senior Member
how to grant database link to any user
Re: how to grant database link to user [message #633381 is a reply to message #633380] Tue, 17 February 2015 06:14 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Not sure what you are actually asking for. But I guess you need to create a private database link for the other user. For all users, you could create a public database link.

Why not start reading more about it in the documentation. You could also have a look at our wiki Database link


Regards,
Lalit
Re: how to grant database link to user [message #633382 is a reply to message #633381] Tue, 17 February 2015 06:20 Go to previous messageGo to next message
qanita786
Messages: 229
Registered: May 2007
Location: PAKISTAN
Senior Member
Dear I am Creating The Following Link

create public database link link2014 connect to account identified by a using 'main'

it work only with account user i want that any user can select data from link2014
Re: how to grant database link to user [message #633387 is a reply to message #633382] Tue, 17 February 2015 06:40 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Can you show us using copy paste from your SQL*Plus session that what you claim is correct.

For example,

SQL> show user;
USER is "LALIT"
SQL> DROP PUBLIC DATABASE LINK TEST_DB_LINK;

Database link dropped.

SQL>
SQL>   CREATE PUBLIC DATABASE LINK "TEST_DB_LINK"
  2    CONNECT TO user
  3    IDENTIFIED BY Asdf1234
  4    USING '**********';

Database link created.

SQL> select * from dual@TEST_DB_LINK;

D
-
X

SQL> conn scott/tiger@pdborcl
Connected.
SQL> show user
USER is "SCOTT"
SQL> select * from dual@TEST_DB_LINK;

D
-
X

SQL>
Re: how to grant database link to user [message #633388 is a reply to message #633382] Tue, 17 February 2015 06:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And if you really created as public then anyone can use it.

Once again, COPY AND PASTE what you do and get, not just tell us what you think you do.

Re: how to grant database link to user [message #633389 is a reply to message #633388] Tue, 17 February 2015 06:46 Go to previous messageGo to next message
qanita786
Messages: 229
Registered: May 2007
Location: PAKISTAN
Senior Member
Dear I am Creating The Following Link

create public database link link2014 connect to account identified by a using 'main'

its fine with account users in Forms 6i if i Connect with Account user if i Connect With Lalit User Its Generate an Error
Re: how to grant database link to user [message #633390 is a reply to message #633389] Tue, 17 February 2015 06:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 17 February 2015 13:41

And if you really created as public then anyone can use it.

Once again, COPY AND PASTE what you do and get, not just tell us what you think you do.


Re: how to grant database link to user [message #633391 is a reply to message #633390] Tue, 17 February 2015 07:18 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
>if i Connect With Lalit User Its Generate an Error

Does the user exist? What is the error? I don't see any error, because you didn't post any error details. I have shown you above how to copy paste.
Re: how to grant database link to user [message #633392 is a reply to message #633380] Tue, 17 February 2015 08:49 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
qanita786 wrote on Tue, 17 February 2015 06:02
how to grant database link to any user


Are you creating this link int the 8.1 database you were asking about here?
Re: how to grant database link to user [message #633393 is a reply to message #633391] Tue, 17 February 2015 09:06 Go to previous messageGo to next message
qanita786
Messages: 229
Registered: May 2007
Location: PAKISTAN
Senior Member
Lalit i only want that user only select data through database link
Insertion,Updation and Deletetion must blocked
Re: how to grant database link to user [message #633394 is a reply to message #633393] Tue, 17 February 2015 09:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So use a user in your database link that can just select.
YOU manage the db link as you want.

Re: how to grant database link to user [message #633395 is a reply to message #633393] Tue, 17 February 2015 09:12 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
qanita786 wrote on Tue, 17 February 2015 07:06
Lalit i only want that user only select data through database link
Insertion,Updation and Deletetion must blocked


Above is accomplished by privileges & have nothing directly to do with DBLINK.

post full results from SQL below for both local & remote databases involved

SELECT * FROM V$VERSION;
Re: how to grant database link to user [message #633397 is a reply to message #633394] Tue, 17 February 2015 09:36 Go to previous messageGo to next message
qanita786
Messages: 229
Registered: May 2007
Location: PAKISTAN
Senior Member
I Create a User By Name Faheem in Both Database and Assign a Selection of Data Role When i apply This Command Following Error Occured

select path from interface@link2014
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-02063: preceding line from LINK2014

Re: how to grant database link to user [message #633399 is a reply to message #633394] Tue, 17 February 2015 09:39 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

post full results from SQL below for both local & remote databases involved

SELECT * FROM V$VERSION;
Re: how to grant database link to user [message #633402 is a reply to message #633399] Tue, 17 February 2015 10:24 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
That error means that the user you are connected to in the remote DB doesn't have access to a table/view of that name.
Check this works:
select * from dual@link2014
Re: how to grant database link to user [message #633404 is a reply to message #633402] Tue, 17 February 2015 10:27 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I prefer the SQL below.

select user, count(*) from user_objects@orcl;

Re: how to grant database link to user [message #633406 is a reply to message #633404] Tue, 17 February 2015 11:22 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
ALL_OBJECTS

That would error Smile
Re: how to grant database link to user [message #633407 is a reply to message #633406] Tue, 17 February 2015 11:27 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SQL> select user, count(*) from user_objects@orcl;

USER                             COUNT(*)
------------------------------ ----------
SCOTT                                  36


Re: how to grant database link to user [message #633408 is a reply to message #633397] Tue, 17 February 2015 15:38 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
qanita786 wrote on Tue, 17 February 2015 10:36
I Create a User By Name Faheem in Both Database and Assign a Selection of Data Role When i apply This Command Following Error Occured

select path from interface@link2014
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-02063: preceding line from LINK2014



We don't know WHERE you created the user.
We don't know WHERE you granted the privileges.
We don't know WHERE you executed the command.

How hard is it to show your session by cut and paste that has been asked multiple times?
Re: how to grant database link to user [message #633413 is a reply to message #633407] Wed, 18 February 2015 02:41 Go to previous message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
Doh Smile
Previous Topic: Concatenating numbers in virtual column expression throws ORA-12899: value too large for column
Next Topic: Outer join restricts a condition
Goto Forum:
  


Current Time: Fri Mar 29 02:26:20 CDT 2024