Re: Confused - Quota's

Thanks for the replies. The error message was absolutely that the user had exceeded his quota for tablespace users thus he obviously did not get UNLIMITED TABLESPACE from the RESOURCE role. As soon as I granted him UNLIMITED TABLESPACE his trigger began working. My other question is what data dictionary table do I need to look in to see what the quota is on the resource role. When I look in DBA_TS_QUOTAS I don't see it.

Thanks for all the help,
Ethan

In article <8nf1o5$7cl$1_at_news.sinet.slb.com>,   "Yong" <yhuang_at_indigopool.com> wrote:
> 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.
>
>

--
-Ethan
http://www.freetechnicaltraining.com
http://www.gnumetrics.com


Sent via Deja.com http://www.deja.com/
Before you buy.