Home » SQL & PL/SQL » SQL & PL/SQL » Question about working with a "default" schema
Question about working with a "default" schema [message #221782] Wed, 28 February 2007 07:01 Go to next message
Uwe
Messages: 260
Registered: February 2003
Location: Zürich, Switzerland
Senior Member
Hi All,

I am not sure how to solve the question from our developers.

Here's our Szenaio:
Oracle 10.2.0.1
2 Schemata: Scheme1 and Scheme2
Table owner is Scheme1
Scheme2 has neither tables noe views, he shall only work on views and tables within Scheme1, but the don't want to use the syntax Scheme1.<View_Name>.
They want to use only select * from viewx.

Is this posible if we grant all necc. priviliges (eg. select on Scheme1.viewx) and the query from Scheme1 and Scheme2 could be the same query (select * from viewx).

I told them to use select * from Scheme1.viewx for any users - but they want to have it without.

regards
Uwe
Re: Question about working with a "default" schema [message #221784 is a reply to message #221782] Wed, 28 February 2007 07:12 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
No problem.
Grant SELECT privilege to all tables/views from schema1 to schema2, and in schema2 create SYNONYMS for those tables/views.

Doing so, they will be able to use something like "SELECT * FROM synonym_name;", without needing to specify owner name.

[Updated on: Wed, 28 February 2007 07:13]

Report message to a moderator

Re: Question about working with a "default" schema [message #221789 is a reply to message #221784] Wed, 28 February 2007 07:30 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or,
Grant SELECT privilege on all tables/views in Schema_1 to Schema_2, and then in an on logon trigger issue the command 'ALTER SESSION SET current_schema = Schema_1'

This will cause all SQL issued to look first in Schema_1 for the objects, rather than in Schema_2
Re: Question about working with a "default" schema [message #221796 is a reply to message #221789] Wed, 28 February 2007 08:41 Go to previous message
Uwe
Messages: 260
Registered: February 2003
Location: Zürich, Switzerland
Senior Member
Thanks,
I hope that our devs will be satisfied with this

regards
Uwe
Previous Topic: Bypassing the index
Next Topic: QASPro Interface
Goto Forum:
  


Current Time: Fri Dec 09 23:25:40 CST 2016

Total time taken to generate the page: 0.06332 seconds