-
Restricting user to use particular tablespace
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.
Thanks
-
create user xyz identified by xyz
default tablespace TBS_NAME
temporary tablespace TEMP ;
if you have already created user then :
alter user xyz default tablespace TBS_NAME ;
HTH
Giani
-
Dont be smart.I asked you how to restrict other users if they were not using default tablespace in their script.
-
If that's not the answer, I guess we just don't understand the question.
Jeff Hunter
-
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
HTH
-
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 12:26 PM.
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?
-
jmodic is right . One guy tried to help you with his post. if you comment like that _______________ Fill in the blanks
Raghu
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|