Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: creating tables in SYSTEM

Re: creating tables in SYSTEM

From: Mark Bole <makbo_at_pacbell.net>
Date: Tue, 30 Nov 2004 18:37:44 GMT
Message-ID: <Y53rd.52058$QJ3.46013@newssvr21.news.prodigy.com>


Gerry Sinkiewicz wrote:

> "vertigo" <ax178_at_wp.pl> wrote in message
> news:coapht$532$1_at_atlantis.news.tpi.pl...
>

>>Hello
>>
>>DOes every user have the possibility to create tables in SYSTEM
>>tablespace ? If yes how can i prevent it ? If not what 'block such
>>command' (privilegdes or something like this ?) ?
>>
>>Thanx
>>Michal
>>

>
>
> The idea is that of a schema.
> That is a user ID with create table, a default tablespace (not system), a
> default temporary tablespace (not system),
> and quota (unlimited on the tablespaces (no system) you wish).
>
> Note: do not grant resource to any such user ID, it has unlimited tablespace
> which will allow creating objects in
> system. The connect role has privs that allow a user to create ordinary
> objects.
>
>
>

I understand about CONNECT, DBA, and RESOURCE roles being deprecated.

What I don't understand, is how the RESOURCE role seems to magically include the UNLIMITED TABLESPACE system privilege. I have looked through all the docs (9.2) and even done a few tests.

SQL> select grantee from dba_sys_privs where privilege = 'UNLIMITED TABLESPACE' and grantee = 'RESOURCE';

no rows selected

And yet when you grant RESOURCE to a user, they suddenly have UNLIMITED TABLESPACE privilege, and when you revoke the role, the privilege goes away. Either I'm missing something obvious, or this is undocumented, hard-coded behavior.

Note: I guess I just found my own answer by searching Ask Tom: "[including UNLIMITED TABLESPACE privilege in RESOURCE role] is the only anomaly I am aware of". Oracle has no problem providing a default set of roles for various purposes -- just because they bollixed it up the first time around with CONNECT, RESOURCE, and DBA, it still seems like they could provide one or two "safe" roles out of the box for beginners to use, without getting bogged down in this level of complexity just to get started. (Of course, as long as they keep on granting CONNECT and RESOURCE to other Oracle-supplied roles or users such as SCOTT, how are they going to ever get rid of them?)

As a side note to another point made in this thread, instead of using the TEMPORARY TABLESPACE clause when creating each user, you can instead use the DEFAULT TEMPORARY TABLESPACE clause once when creating the database.

-Mark Bole Received on Tue Nov 30 2004 - 12:37:44 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US