Re: Confused - Quota's

RESOURCE role implies UNLIMITED TABLESPACE system privilege for historical reason. It's always a good idea to manually

REVOKE UNLIMITED TABLESPACE FROM THATUSER; whenver you grant somebody RESOURCE.

Yong Huang

Ethan Post <epost1_at_my-deja.com> wrote in message news:8ncoc0$58p$1_at_nnrp1.deja.com...
> It seems like I'm missing somthing pretty big and easy here, I just
> never ran across this problem before. We had a guy put a trigger on a
> table to audit some information. Eventually he ran over his quota on
> USERS and the trigger started erroring out. No handler so big trouble
> with the app not commiting data to particular tables. Gave him
> unlimited space on USERS for time being and all is well.
>
> In researching the problem his quota on USERS was set to "NONE" but he
> has been creating objects for a very long time on USERS. He has
> connect and resource privs. I ran some more tests and it appears that
> when he gets resource it grants him some sort of quota on USERS but I
> can't figure out how much it grants by default using OEM and have not
> found and DBA tables that have the info in it.
>
> What follows is a transcript of a Test session with comments.
>
> *************************************************************
>
> -- created user test, default table space USERS, quota=none
> -- he has connect privs, which includes create table
> -- logged in and attempt to create table
>
> SQL> create table test as (select sysdate test from dual);
> create table test as (select sysdate test from dual)
> *
> ERROR at line 1:
> ORA-01950: no privileges on tablespace 'USERS'
>
> -- as you can see he has no privs on USERS, so I grant him
> -- RESOURCE
>
> SQL> /
>
> Table created.
>
> -- no problem
>
> SQL> drop table test;
>
> Table dropped.
>
> -- now lets take away resource and see what happens
>
> SQL> create table test as (select sysdate test from dual);
> create table test as (select sysdate test from dual)
> *
> ERROR at line 1:
> ORA-01536: space quota exceeded for tablespace 'USERS'
>
> -- different error message than first time even though he only has
> -- CONNECT in both circumstances
>
> SQL> select sysdate test from dual;
>
> TEST
> ---------
> 15-AUG-00
>
> -- grant unlimited quota on USERS
>
> SQL> create table test as (select sysdate test from dual);
>
> Table created.
>
> SQL>
>
> -- works fine again
>
> **********************************************************
> Basically I do not understand how he was ever able to create tables
> with quota of 'NONE'. If he got some sort of privs with RESOURCE role
> that allowed it how do I see what the QUOTA actually is and why doesn't
> it show up in Security Manager?
>
> Thanks,
> Ethan
> http://www.freetechnicaltraining.com
> http://www.gnumetrics.com
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Wed Aug 16 2000 - 16:37:04 CDT