How to stop users creating tables in SYSTEM TS

[Sameer]

Hi

User has been defined a default tablespace and also temp tablespace.

How do I stop user creating tables in SYSTEM tablespace?

Thanks

Sameer

[pando]

assign them a dfeault tablespace that is not system...???

[Sameer]

Yes. The default tablespace assigned to user is USERS and not SYSTEM, temp tablespace is TEMP

But if user executes

create table test (id number) tablespace system it gets created in system tablespace

The way we allocate quota on tablespace, Is there is any to stop them by allocating 0 quota on system tablespace.

I tried

alter user xxxx quota 0 on system

it works.. but still user able to create table in SYSTEM tablespace...

How do I stop this??

[pando]

that is because you gave user RESOURCE role

[Sameer]

Thanks.. I think u eat/breath Oracle :-)

Sameer

[stecal]

You can also assign a quota of zero to ensure a user can not create any objects in a particular tablespace, including system.

alter user username quota 0 on system;

[pando]

you cant do that if user has resource role since it has this unlimited tablespace system privilege...

[Mr.Hanky]

Generally what I do for a situation such as this is.....

Dress up in an evil clown outfit (don't forget the oversized shoes) track down the user and open fire with a paintball gun. This has proven to be a highly effective means of deterrent for both developers and users alike. Of course in some countries this is labeled as “unacceptable” or “politically incorrect” but fortunately for us we can still implement this type of educational alternative.

MH

[marist89]

I prefer:
DROP USER xyz CASCADE;

[Mr.Hanky]

Originally posted by marist89
I prefer:
DROP USER xyz CASCADE;

HARDCORE!!