Re: creating tables in SYSTEM

vertigo wrote:
> Hello
>
> DOes every user have the possibility to create tables in SYSTEM
> tablespace ?

That question can't really be answered simply.

For a start, no user has rights to create tables *anywhere*, unless they have been granted a quota on a tablespace (and, of course, the create table privilege). In other words, the default quota for all new users you might create is 0 on all tablespaces.

Unless you have therefore done an 'alter user fred quota 100m on SYSTEM', then no: Fred cannot create tables in the SYSTEM tablespace.

However, you can always do an 'alter user fred quota 50m on system', and then Fred *does* have the ability to create tables in SYSTEM.

And, more alarmingly, it is the fashion amongst certain DBAs to do 'grant connect, resource to fred' at the time of Fred's creation... and both "grant" and "connect" are roles (which you shouldn't actually use, except for backwards compatibility), one of which does actually grant unlimited tablespace quotas to users.

So, as I say, there's no real answer to your question. Oracle certainly doesn't permit users to create tables in SYSTEM lightly, but poor DBA practices (such as granting resource to all users) could mean that, indeed, all your users could do the deed.

> If yes how can i prevent it ?

Quota 0 on SYSTEM for all users is a good start. And don't use the connect or resource roles.

> If not what 'block such
> command' (privilegdes or something like this ?) ?

Controlling quotas is the way to do it, I think.

Regards
HJR
> Thanx
> Michal
>
Received on Sat Nov 27 2004 - 15:25:05 CST