Re: Granting 'connect' role- which 7 privilages it gives ?

nirav wrote:
> Hi Ed,
>
> Thank you very much...
>
> I am a bit surprised, that given this,
> why granting a connect role, and then altering a user to have quota 0
> on system, still that user can create a table in system tablespace?
> Just looking for more insight..
>
> Thanks again.
> N.

Unless you're running a VERY buggy release of Oracle (and you have so far failed to mention WHICH relrease you are using) such a task should fail if your quota is properly set for the SYSTEM tablespace. Following your example with 9.2.0.6:

SQL> create user blonk identified by blenk;

User created.

SQL> grant connect to blonk;

Grant succeeded.

SQL> alter user blonk quota 0 on system;

User altered.

SQL> connect blonk/blenk
Connected.
SQL> create table blunk(norbert number); create table blunk(norbert number)
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'SYSTEM'

SQL> Of course your first mistake was to allow Oracle to set the default and temp tablespaces for new users to SYSTEM; a proper create user statement would be:

SQL> create user blonk identified by blenk default tablepsace users temporary tablespace temp;

Such a statement would force a user to CHOOSE the system tablespace for object creation, not provide that path by default.

I would again set the quota to 0 on SYSTEM for the user in question. I would also report which release of Oracle you are using, as any further responses to such a question would be nothing more than speculation.

David Fitzjarrell Received on Mon Oct 03 2005 - 08:19:37 CDT