Re: Confused - Quota's

the resource role results in the user having unlimited tablespace privilege. This means no quota checks are done, and existing quotas will not be taken into account.
Many people (including myself) still use these obsolete Oracle 6 roles. In an ideal world we shouldn't do this but use the quota mechanism instead. Of course, usually we are (check all that apply) 0 Just too lazy
0 Just too hurried
to use the quota mechanism (I include myself in it, it's not meant as criticism)

Hth,

Sybrand Bakker, Oracle DBA

"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 Tue Aug 15 2000 - 23:51:37 CDT