Home » RDBMS Server » Security » Defaulting an account to another accounts schema?
Defaulting an account to another accounts schema? [message #63909] Wed, 24 November 2004 08:24 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
Re: Defaulting an account to another accounts schema? [message #63920 is a reply to message #63914] Thu, 25 November 2004 02:32 Go to previous message
croK
Messages: 170
Registered: April 2002
Senior Member
You are very right.
I didn't remember the exact sintaxis..
i was talking about the statement you mentioned above:
ALTER SESSION SET CURRENT_SCHEMA=user2;

Thanx
Previous Topic: Oracle Security Patch issue
Next Topic: hide/encrypt password in database
Goto Forum:
  


Current Time: Thu Dec 02 03:13:05 CST 2021