Re: User with NO tablespace quota can still accesss and write to tablespace!

laurie.kulinsky_at_wadnr.gov (Laurie) wrote in message news:<2e87f958.0401211126.1ba8c7c9_at_posting.google.com>...
> Ok.... 9.2 of Oracle on Sun...
>
> I built a test database a while back and am testing some user access
> stuff.
>
> When I create a user with NO tablespace quota, and only the connect
> role (even backed this back to the create session role), the user can
> still create tables and insert data into the tables regardless of the
> quota given to that user, be it no quota or even limited. If limited,
> the users can blow right past that mark. Also the user can move the
> table to any other tablespace on the database (no quota on those
> tablespaces either)! A bad thing....
>
> Our other databases do not display this behavior and are all the same
> version of Oracle....
>
> Has anyone come across this?? Any ideas??
>
> Last resort will be to log a TAR, didn't see anything quite like this
> in Metalink...
>
> Thanks ;)
> Laurie

Laurie, from your post I can not tell who is the table owner? If the owner is another user then the quota privilege of A is not involved. It is the table owner who has to have a quota.

If user A is the owner then as long as the inserts fit into the already allocated extents user A will still be able to insert more data until such time as the target table or one of its indexes needs to extend.

Also create session is a privilege, not a role. The connect role is obsolete (per Oracle documentation) and is included only for backward compatibility. You should create your own roles with standard privileges and grant those to users instead.

HTH -- Mark D Powell -- Received on Wed Jan 21 2004 - 18:51:51 CST