OraFAQ Forum: Server Administration » DB link for one to many user access

Home » RDBMS Server » Server Administration » DB link for one to many user access (Oracle 10g on Unix)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

DB link for one to many user access [message #475403]

Tue, 14 September 2010 17:25

caprikar
Messages: 226
Registered: March 2007

Senior Member

Hi,
I need to create one to many user DB link in oracle 10g. Meaning I have a user A in database 1 and I want to access the objects from user B,C,D in database 2, how to create a public database link so that i can have this one to many user access?
Thanks

Report message to a moderator

Re: DB link for one to many user access [message #475404 is a reply to message #475403]

Tue, 14 September 2010 17:32

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: DB link for one to many user access [message #475405 is a reply to message #475403]

Tue, 14 September 2010 18:05

cmartin
Messages: 5
Registered: July 2010

Junior Member

Create USER E in database 2 and grant the nessessary privledges from USER B, C and D to USER E.

Configure the DBLINK in database 1 to access database 2 with USER E

Report message to a moderator

Re: DB link for one to many user access [message #475408 is a reply to message #475405]

Tue, 14 September 2010 20:55

caprikar
Messages: 226
Registered: March 2007

Senior Member

Thanks

Report message to a moderator

Re: DB link for one to many user access [message #475639 is a reply to message #475405]

Thu, 16 September 2010 07:06

caprikar
Messages: 226
Registered: March 2007

Senior Member

Hi,
I tried this:

On database 2 --> Create user E and grant select on all the tables from Schema A,B,C to user E

On database 1 -->

Login as SYS

CREATE DATABASE LINK TEST CONNECT TO E identified by xyz123
USING 'DB2';

Now when I login as User A in database 1 and issue

select count(*) from DB2.testtable@test;

I'm getting invalid username/password Sad

Report message to a moderator

Re: DB link for one to many user access [message #475654 is a reply to message #475639]

Thu, 16 September 2010 08:38

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

An observation - DBLINK references "DBZ" & post SQL uses different alias.

[Updated on: Thu, 16 September 2010 08:57]

Report message to a moderator

Re: DB link for one to many user access [message #475676 is a reply to message #475639]

Thu, 16 September 2010 10:32

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

Login as SYS

CREATE DATABASE LINK TEST CONNECT TO E identified by xyz123
USING 'DB2';

Now when I login as User A in database 1 and issue

select count(*) from DB2.testtable@test;

User A has NO access to database link you created with SYS.
And what is this DB2 in schema name place?

Regards
Michel

Report message to a moderator

Re: DB link for one to many user access [message #475793 is a reply to message #475676]

Fri, 17 September 2010 11:29

jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai

Senior Member

Hi Caprikar,

You have created private database link using SYS. Hence it is not accessible for a non-sys user. Try to create a PUBLIC database link and check. As Michel has mentioned, you cannot have a DB name in the place of a schema name.

Regards,
Antony

Report message to a moderator

Re: DB link for one to many user access [message #475794 is a reply to message #475793]

Fri, 17 September 2010 11:32

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

As Michel has mentioned, you cannot have a DB name in the place of a schema name.

You can have a schema with the same name as the database name but using the database name at the place of schema name is not correct.

Regards
Michel

Report message to a moderator

Previous Topic:	Access redo generation After migrating from 9.2.0 to 10.2.0.4
Next Topic:	upgrade from 11.2.1 to 11.2.2.0

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jun 29 05:13:21 CDT 2024