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

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.

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?

Quote:

---

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}

Quote:

---

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. ;)

Quote:

---

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.

Quote:

---

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.

jmodic is right . One guy tried to help you with his post. if you comment like that _______________ Fill in the blanks

Quote:

---

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.

---

another peep who need to RTFM

Here is an analogy:

create role bitch_slapped;
grant bitch_slapped to msuresh;

Now, even though you are bitch_slapped, can you do anything with that role? No. Just like create user doesn't mean, by itself, the user can actually create anything in his/her default tablespaces. bitch_slapped has no privileges associated with it, just as being bitch_slapped should be.

Quote:

---

Originally posted by adrianomp
You can restrict default temporary tablespace (only on Oracle 9i), but not default tablespace.

---

No, you can't restrict TEMP tablespace also.

Code:

---

SQL> select * from database_properties
  2  where property_name like '%TEMP%';

PROPERTY_NAME                  PROPERTY_VALUE                DESCRIPTION
------------------------------ ------------------------------ ------------------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP                          Name of default temporary tablespace

SQL> create user u1 identified by u1 default tablespace users
  2  temporary tablespace temp1;

User created.

SQL> select username, default_tablespace, temporary_tablespace
  2  from dba_users where username = 'U1';

USERNAME                      DEFAULT_TABLESPACE            TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
U1                            USERS                          TEMP1

SQL>

---

Quote:

---

Originally posted by msuresh
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

---

What about writing a system level trigger for create/alter user statement? Do some homework on system Triggers. You need to have versions higher than 8 for this.

I am completely agree with pando, stecal and others that if you are asking question and people are trying to help you then you don’t have any right to show your over smartness to other people. If you are too smart then why don’t you do RTFM? Or don’t ask questions.

Hey when someone suggests u a script whether u ues it or not is up to u.Also i agree to the point that the Dba is the one who should be creating the database users.Dba is not there to clean up the mess created by the developers.He is responsible for the mess not to happen first

Quote:

---

Originally posted by dasari98
Hey when someone suggests u a script whether u ues it or not is up to u.Also i agree to the point that the Dba is the one who should be creating the database users.Dba is not there to clean up the mess created by the developers.He is responsible for the mess not to happen first

---

This is one of the most idiotic threads I have read in this forum. I agree with the above words. Only the DBA users should have the right to create users. Dot.

Quote:

---

No, you can't restrict TEMP tablespace also.

---

SANJAY,

By defining a default temporary tablespace, users created without a temporary tablespace would be assigned to it, not SYSTEM anymore. That's what I meant.

Anyway, I still do not understand why Oracle recommends not to use SYSTEM as default/temp tablespace and sets it as default, instead of looking for any temporary tablespace or simply rejecting the command.

Quote:

---

Originally posted by adrianomp
Anyway, I still do not understand why Oracle recommends not to use SYSTEM as default/temp tablespace and sets it as default, instead of looking for any temporary tablespace or simply rejecting the command.

---

I guess this might be 1 of the reasons.

* SYSTEM tablespace is the must creat TBS & is created during DB creation....and oracle simply doesnt want to throw error if any body wises to create user.....
Hence System Tbs wud be as defualt...
Now if u create a TBS, how will oracle know that it shud be defualt unless any command to change from System to XYX TBS ... and as of now no such command is avilable.
i hope they provide 1.

Abhay.

Quote:

---

Originally posted by adrianomp
SANJAY,

Anyway, I still do not understand why Oracle recommends not to use SYSTEM as default/temp tablespace and sets it as default, instead of looking for any temporary tablespace or simply rejecting the command.

---

If Oracle starts automating such things, our database will end up like a SQL SERVER database.

Hi All
I never meant hurting Giani or others from my heart and if my words look like that, i am sorry for that.

When i read a reply from giani and the image under his name i thaught he is making me stupid,thats why i made such a comment(Dont be smart) which is not so vulgar than stecel used against me.

Stecel's comments
=================
create role bitch_slapped;
grant bitch_slapped to msuresh;

Now, even though you are bitch_slapped, can you do anything with that role? No. Just like create user doesn't mean, by itself, the user can actually create anything in his/her default tablespaces. bitch_slapped has no privileges associated with it, just as being bitch_slapped should be.


Please dont continue this thread by answering for my reply.

Okay. That wasn't too smart, was it? Some people have no sense of humor.

Quote:

---

Originally posted by msuresh

Please dont continue this thread by answering for my reply.

---

Since u r the owner, close the thread...