Restricting user to use particular tablespace

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.