Hi,
We gave a create user priv. for some application users and they they create the user without specifying default tablespace and their defaut tablespace is system now.I took care now.My question is
During creation of a new user ,is there any way to force/restrict to make tablespace as default tablespace.
I dont think Giani was being smart. If you want users to create other users then you should insist that they follow your rules.
Perhaps you could supply them with a creation script that they MUST use when creating users.
Something like
accept user_name prompt 'Please enter the Oracle user name: '
accept password hide prompt 'Please enter the users password: '
create user &&user_name identified by &&password
grant create session to &&user_name;
alter user &&user_name quota 0 on user_tablespace;
alter user &&user_name default tablespace user_tablespace;
alter user &&user_name temporary tablespace temp;
exit
I don't think it's possible. You can restrict default temporary tablespace (only on Oracle 9i), but not default tablespace. You can use a trigger as a workaround (AFTER CREATE ON DATABASE) and change the default tablespace just after the user creation.
However, that workaround seems disgusting (at least for me). What do you think about changing the policy? Is it really necessary that application users have the right to create users on database?
Last edited by adrianomp; 03-20-2003 at 11:26 AM.
An ounce of action is worth a ton of theory.
—Friedrich Engels
Originally posted by adrianomp Is it really necessary that application users have the right to create users on database?
In the best security model I know ("'tis my own invention" said the White Knight), the IT dept should not be creating users - it's the job of HR and/or line-management. OF COURSE they shouldn't be using a "create user" statement! {descends from soap-box}
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
In the best security model I know ("'tis my own invention" said the White Knight), the IT dept should not be creating users - it's the job of HR and/or line-management. OF COURSE they shouldn't be using a "create user" statement! {descends from soap-box}
In these terms, I completely agree with you. However, take care of the database health is a duty of the DBA. If it's possible to embed the correct operation on an application, fine. Otherwise it's better to restrict access than let others make a mess on the database.
An ounce of action is worth a ton of theory.
—Friedrich Engels
Originally posted by msuresh Dont be smart.I asked you how to restrict other users if they were not using default tablespace in their script.
I think you should be more smart, or at least read some documents to understand basics before attacing others that are trying to help you.
Originally posted by msuresh create user xyz identified by xyz
default tablespace TBS_NAME
temporary tablespace TEMP ;
There's absolutely nothing smart in that, it's just how users should be created. And if you had been smart enough you would have realised that the above command creates a user that can't create any object in any tablespace whatsoever - not in TBS_NAME (the default tablespace for that user), nor in any other tablespace!
It's not the assigned defaut tablespace that gives user the ability to create objects - it's what privileges and tablespace quotas you assign to that user.
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Bookmarks