Home » RDBMS Server » Security » user creation
user creation [message #213845] Fri, 12 January 2007 08:47 Go to next message
amit_nanote
Messages: 56
Registered: July 2005
Location: Indore
Member

Hi,
I am again with a user problem.
Razz ....

I am having a User name - Sample
having n no of objects in it.
Having another user - usesample
This usesample is not having any object. But have privlige to select and perform DML operations on Sample.
Sample is having a table - Samplesizes
now I am connecting to usesample.
and

here starts my problem .....
Want to select samplesizes data as
select * from samplesizes;

Dont want to use access specifiers as (means following query)
select * from sample.samplesizes;

Is it possible to select objects of first schema(sample) from second scema (usesample) having privileges to access first schema(sample) without specifying the name of first schema(sample) name.

Thank you.
Re: user creation [message #213847 is a reply to message #213845] Fri, 12 January 2007 08:57 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You could create SYNONYMS or once connected as USERSAMPLE issue:

ALTER SESSION SET CURRENT_SCHEMA = sample;
Re: user creation [message #213962 is a reply to message #213845] Sat, 13 January 2007 00:27 Go to previous messageGo to next message
amit_nanote
Messages: 56
Registered: July 2005
Location: Indore
Member

Ohh Thank you,

Really helped me to find the way....

Just check the following Trigger for using SAMPLE from USESAMPLE.
CREATE OR REPLACE TRIGGER db_logon_USESAMPLE
-- Now login as the user name password given to use the schema
AFTER logon ON DATABASE WHEN (USER IN ('USESAMPLE'))
BEGIN
-- This statement set the working schema for USESAMPLE user to SAMPLE user.
execute immediate 'ALTER SESSION SET CURRENT_SCHEMA = SAMPLE';
end;
Re: user creation [message #213967 is a reply to message #213962] Sat, 13 January 2007 01:37 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Yes...that should work.
Previous Topic: Sys password change
Next Topic: Ora-28003 When updating user details
Goto Forum:
  


Current Time: Thu Mar 28 03:11:55 CDT 2024