| Defaulting an account to another accounts schema? [message #63909] |
Wed, 24 November 2004 08:24  |
Scott B
Messages: 9
Registered: July 2004
|
Junior Member |
|
|
Appologies for the repost, the first one choked on some angle brackets in my text...
Hi,
Coming from other databases, I am used to being able to change schemas in the sense that I can omit schema portion in the [[schema]].[[object]] naming structure. I realize (although admitidly don't appreciate it as a design feature) that in Oracle the schema is directly tied to the user account.
Is this merely a default, or is it something I am stuck with? In other words, if I have user1 and user2, and I want user2 to operate in user1's schema without having to prefix everything with user1.[[object]], is this possible? I don't want to have to create a synonym for every object; just one action to change the schema entirely.
Furthermore, if it's not possible, can anyone enlighten me as to why this is the way it is in Oracle.
TIA, Scott
|
|
|
|
| Re: Defaulting an account to another accounts schema? [message #63911 is a reply to message #63909] |
Wed, 24 November 2004 11:27  |
croK
Messages: 170
Registered: April 2002
|
Senior Member |
|
|
You can create public synonym for every object in user2 schema.
Or you can write your application with PL/SQL stored procedures on user2's schema, and run your application from user1. So, user1 only will need execution privileges on procedures and packages.
You can also log into the database with user1 and execute: alter user become user2 (something like that, i don't remeber the exact sintaxis).
You can also read documentation and explore about PROXY USERS.
Best luck.
Looking for remote job.
|
|
|
|
| Re: Defaulting an account to another accounts schema? [message #63914 is a reply to message #63911] |
Wed, 24 November 2004 18:27 |
Daljit Singh
Messages: 290
Registered: October 2003
Location: Texas
|
Senior Member |
|
|
Hi,
In ur case if user1 has proper privileges on user2 objects so u can issue following command from user1 :
ALTER SESSION SET CURRENT_SCHEMA=user2;
After that there is no need to specify schema name along with the object name to access it. But again user1 must have appropriate privileges on user2 objects.
Crok, I dont think there is any command like "alter user become user2.." in oracle. If u have any idea so please explore it.
Daljit Singh.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
