Home » RDBMS Server » Server Administration » Granting object privileges to remote users (Database,10.2.0.3,Windows Server 2003)
Granting object privileges to remote users [message #360352] Thu, 20 November 2008 10:47 Go to next message
abdulaziz
Messages: 102
Registered: May 2008
Location: Douala
Senior Member

Hello,

Here's the situation:

I have 2 databases located on 2 different servers both running Win2k3. In the first database the main schema is M1 and it has to read objects on the another schema M2, located on the second database.
I created a database link on the first database, to point to the second database
create database link connect2M2db connect to M2 identified by M2 using 'connect2M2db'


Now I would like to grant object privileges(insert,update,delete) to M1 on M2' objects. How do I do that?


Thanks.
Re: Granting object privileges to remote users [message #360355 is a reply to message #360352] Thu, 20 November 2008 10:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
M1 inherites the privileges on the remote database of the user it connects through the database link.

Regards
Michel
Re: Granting object privileges to remote users [message #360360 is a reply to message #360355] Thu, 20 November 2008 11:29 Go to previous messageGo to next message
abdulaziz
Messages: 102
Registered: May 2008
Location: Douala
Senior Member
Michel Cadot wrote on Thu, 20 November 2008 10:57
M1 inherites the privileges on the remote database of the user it connects through the database link.

Regards
Michel



Does it mean that we don't need to explicitly grant insert,update and delete privileges to M1 from M2's schema?
Re: Granting object privileges to remote users [message #360361 is a reply to message #360360] Thu, 20 November 2008 11:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes this is what it means.

Regards
Michel
icon14.gif  Re: Granting object privileges to remote users [message #360468 is a reply to message #360361] Fri, 21 November 2008 02:22 Go to previous messageGo to next message
abdulaziz
Messages: 102
Registered: May 2008
Location: Douala
Senior Member
Thanks Michel.When I make a DDL from schema M1, say insert into job@connect2M2db, it works just fine. My boss made me have doubts about what I was doing.

Tu m'otes une Úpine du pied. Cool
Re: Granting object privileges to remote users [message #360492 is a reply to message #360468] Fri, 21 November 2008 03:00 Go to previous messageGo to next message
abdulaziz
Messages: 102
Registered: May 2008
Location: Douala
Senior Member
hmmm, there's one thing I forgot to say: synonyms for tables belonging to M2 were created on M1. But whenever we try to manipulate those synonyms on M1, we get the error:
ora 04045 :error during recompilation/revalidation of M1.Job, and ora 00980: Synonym translation is no longer valid.

I made a search and I got a solution for the ora 04045 (ref post #73745) on this forum, but am afraid it does not apply to my case.
As for the ora 00980, the actual tables are located on a remote database (M2), and both the databases are linked through a databse link.

Any tips?

[Updated on: Fri, 21 November 2008 03:52]

Report message to a moderator

Re: Granting object privileges to remote users [message #360521 is a reply to message #360492] Fri, 21 November 2008 04:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I don't know the objects you have, the synonyms you have, how you create them and what you did, I can't say anything.

Regards
Michel
Re: Granting object privileges to remote users [message #360528 is a reply to message #360521] Fri, 21 November 2008 05:00 Go to previous messageGo to next message
abdulaziz
Messages: 102
Registered: May 2008
Location: Douala
Senior Member
Uhum...Well I just reviewed the command used to create the synonyms and I realized that the database link was not added to those commands. Here's an example:
create or replace synonym job for m2.job
without appending the database link.

I got the idea from a post I found here (message #225847) which was a reply to similar problem. As I have a database link, it suggests to create the synonym as follow:
create or replace synonym job for m2.job@connect2M2db 
where connect2M2db is the database link.

I guest that syntax is correct, isn't it?

Re: Granting object privileges to remote users [message #360530 is a reply to message #360528] Fri, 21 November 2008 05:07 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't use "m2" as you connect with m2 through database link and be sure m2.job exists in remote database.

Regards
Michel
Previous Topic: How to check partitioned table local indexes is valid
Next Topic: Database Synchronization
Goto Forum:
  


Current Time: Mon Dec 05 14:59:49 CST 2016

Total time taken to generate the page: 0.12695 seconds