Re: tablespace SYSTEM used by others user, not sys. Help!

Frank van Bortel <fvanbortel_at_netscape.net> wrote in message news:<c9qd39$53p$1_at_news1.tilbu1.nb.home.nl>...
> tracy wrote:
> > hi, x
> > dev_user only have priviledge to create table under his own tablespace
> > which is dev_user only. he only granted the priviledge 'create table'
> > not 'create any table'
> >
> >
> > "x" <x_at_x.hr> wrote in message news:<c9p4i6$ndq$1_at_ls219.htnet.hr>...
> >
> >>>I wonder, how can the table
> >>>created by them is in system schema? Pls let me know, if u have the
> >>>answer.
> >>>
> >>
> >>create table my_table (x varchar2(1)) tablespace system
> >>
> >>Easy as that. If your dev_user has right priviledges he can create table
> >>where ever he wants.
>
> Unless granted the resource role, which offers quota unlimited on
> any tablespace. Unless you specifically do
> alter user quota 0 on system;
> your dev_user can still create objects in the system tablespace

Frank provided the information on how to lock the user out if it has the resource role. Privileges should be aligned to what the ID has to be able to do and no more.

Depending on the age of the objects it is also possible that they were created before the default tablespace of the user was changed and were never relocated.

Once you review and modify the privileges as necessary you still need to clean up the system tablespace.

• Mark D Powell --