| act as a different schema [message #133083] |
Wed, 17 August 2005 11:22  |
mito
Messages: 19
Registered: August 2005
|
Junior Member |
|
|
Suppose there are users(schemas) sch1, sch2, sch3, sch4...
sch2, sch3, sch4 each has its sys and obj privileges/grants of their own. How can I enable sch1 so that it can access other schemas and act as these other schemas. i.e. sch1 can do everything that sch3 do without the need to login as sch3.
Thanks!
|
|
|
|
|
|
| Re: act as a different schema [message #133136 is a reply to message #133083] |
Wed, 17 August 2005 16:01  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
I think it depends more on what you mean by "sch1 can do everything that sch3 can do". If it is just a matter of object access, then yes public synonyms are an option (I don't prefer them but many do). alter session set current_schema is another option in that case.
|
|
|
|
| Re: act as a different schema [message #133139 is a reply to message #133136] |
Wed, 17 August 2005 16:22 |
mito
Messages: 19
Registered: August 2005
|
Junior Member |
|
|
Yes, it is mainly for object access. Can you please elaborate on the alternative option you introduced? My understanding is it provides a way to eliminate the need to qualify the objects with the foreign schema name but it does not provide any privilege transfer i.e. If sch1 does not have the priv to access sch2's objects, using CURRENT_SCHEMA clause wouldn't help sch1 to access sch2's objects. Right???
[Updated on: Wed, 17 August 2005 16:30] Report message to a moderator |
|
|
|
| Re: act as a different schema [message #133317 is a reply to message #133083] |
Thu, 18 August 2005 08:43 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Right. And maybe I misunderstood your question if that is not what you are after. Also with synonyms you would need to still grant access to the objects.
Other option includes granting permissions to roles and then granting roles to users instead of direct privs.
Or you could use plsql packages to access the objects and serve as an API and then grant execute to those packages to your users.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
