How to keep objects from different users in one schema? 2

[BarbaraFuhrmann]

Hello,

we are a very small company running 9i. For the moment we all work with the same oracle-user (e.g. UserA). Now it is requested to create individual oracle-users for the employees with database access (e.g. Name1, Name2).

My question is if it is possible to create and use new users but keep the objects (tables, views) created by them together in the same schema (UserA)?

Thank you!
Barbara

 

[hoinz]

Hi,

when creating a user, you may specify a 'default tablespace' clause.
All the objects that will be created by them will be in this tablespace.

hope this helps

 

[hoinz]

oh, I was too fast.
You asked for a common schema. Thsi is not possible, for all I know.

 

[stefanhei]

Barbara,
you may create logon triggers for the new users which issues:

alter session set current_schema=UserA

Those users will need "create any table" and "create any view" system privileges to create objects in a foreign schema.

Stefan

 

[hoinz]

good point, Stefan!
I didn't know about current_session.

 

[BarbaraFuhrmann]

Hi Stefan,

even if I expose myself as a real stupido I have to ask for the secrets one has to know to succesfully create an logon trigger. I tried the following:

CREATE OR REPLACE TRIGGER Database_logon
AFTER LOGON
ON DATABASE
BEGIN
if SYS_CONTEXT('userenv','session_user') = 'Name1'
then
execute immediate ('alter session set current_schema=UserA;');
end if;
END;
/

The trigger is compiled without errors but it seems not to work. When I use the ALTER statement after logon directly in the SQLPlus-Worksheet it is exactly what I need. But when I create the trigger nothing happens.

Perhaps it's a question of who is creating it? I tried it as Name1 as well as UserA but the effect is the same: None.

Thank's
Barbara

 

[stefanhei]

Hi Barbara,
You were on the right track, just two minor syntactical issues:
* SYS_CONTEXT gives the name in upper case letters
* leave the semicolon in the execute immediate string
Btw, if you are going to hardcode the names (I'd put them inside a table and check from there) you can use following syntax:

CREATE OR REPLACE TRIGGER trg_db_chg_schema
AFTER LOGON
ON DATABASE WHEN (user IN ('NAME1','NAME2'))
BEGIN
 EXECUTE IMMEDIATE('alter session set current_schema=UserA');
END;
/

I think if the user has all privileges to create db-level triggers you don't have to worry about who creates it.
I personally create db-level triggers as db-owner (SYS) as they affect the whole database and I don't want a "normal" user to mess around with it.

Stefan

 

[Turkbear]

Hi,
To ask a follow-up question: Why do this?

What business need overrides good database administration practice?







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[BarbaraFuhrmann]

Hi Turkbear,

we are two developers in the company and both of us - for the moment - work with the same user when creating new database objects, etc. So all objects are in the same schema.
Besides the two developers there are three more persons working with the application. That means: Inserting and analyzing data, creating reports, ....

All these people use the same user for their activities.

Now the management gave the rule that their has to be an peculiar user for each employee. Each user shall have only the privileges he needs for his work.
I began to test arround with different users and saw that the objects (tables, views ...) created by the employee-related users where created in different schemas.

For me it is more clearly arranged to have all objects together in the same schema. Both developers work alternating on the same projects. And from time to time we export the schema of this one-and-only user to import the data as development environment.
So I thought it would be a good idea to keep the "old" user and continue collecting all objects in this schema.

Derived from the way you ask this is no common proceeding, I suppose. What would you suggest to do?

Thank you
Barbara

 

[Turkbear]

Hi,
I would create a user ( and, therefore a schema) whose only task was to create all the objects needed by the application and grant the needed access to the other users/roles)Only the developers should use ( or even know the password for) this user account. -
That way you do not need all that trigger action..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[BarbaraFuhrmann]

Hi,

I just thought about that solution, but
- the management gives the directive that their should be no user used by more than one person
and
- creating and using objects is changing very fast during the same day so I fear that it would result in a situation similar to the current: Logging on in the morning with the "creating user" and doing all the work with it.
So I think we will try it the triggered way.