OraFAQ Forum: SQL & PL/SQL » Accessing other user's objects

Home » SQL & PL/SQL » SQL & PL/SQL » Accessing other user's objects

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

Accessing other user's objects [message #379580]

Wed, 07 January 2009 02:45

kvtan
Messages: 2
Registered: January 2009
Location: Earth

Junior Member

Hi, newbie here, so forgive me if this sounds ridiculous

Let's say I have users A1 and A2. A1 has a complete database (with tables, procedures, etc). A2 is a new user with virtually nothing in his pocket. To allow A2 to perform transactions on A1 tables, I can use the following command

grant SELECT,UPDATE,DELETE,INSERT on A1.TABLE_NAME to A2;

HOWEVER, I will have to prefix any transactions for A2 with "A1"

Ex: select * from A1.TABLE_NAME

Is there any way I can "map" the tables to use "select * from TABLE_NAME" as user A2 and still get the data?

Thanks in advance

Report message to a moderator

Re: Accessing other user's objects [message #379581 is a reply to message #379580]

Wed, 07 January 2009 02:47

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Create SYNONYMS.

Report message to a moderator

Re: Accessing other user's objects [message #379582 is a reply to message #379580]

Wed, 07 January 2009 02:48

tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden

Senior Member

Create synonyms

Oracle Documentation

Report message to a moderator

Re: Accessing other user's objects [message #379583 is a reply to message #379580]

Wed, 07 January 2009 02:52

kvtan
Messages: 2
Registered: January 2009
Location: Earth

Junior Member

Goodness, that was fast. Shows how much I need to go. Thank you all very much

Report message to a moderator

Re: Accessing other user's objects [message #379584 is a reply to message #379580]

Wed, 07 January 2009 02:54

flyboy
Messages: 1903
Registered: November 2006

Senior Member

Or issue

ALTER SESSION SET current_schema = a1;

, however you will not be able to directly access objects in A2.
Documentation link: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_2012.htm#i2277774

Report message to a moderator

Re: Accessing other user's objects [message #379599 is a reply to message #379580]

Wed, 07 January 2009 04:53

hasan_uiu
Messages: 18
Registered: August 2007
Location: Dhaka

Junior Member

Create synonym for the shared object

--
hasan

[Updated on: Wed, 07 January 2009 05:43] by Moderator

Report message to a moderator

Re: Accessing other user's objects [message #379607 is a reply to message #379599]

Wed, 07 January 2009 05:47

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Stop repeating others answer just to post a link to your blog.

Regards
Michel

Report message to a moderator

Previous Topic:	oracle 10g advanced concepts (merged)
Next Topic:	Update in huge data in partitioned table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 02 02:34:36 CDT 2026