Confused - Quota's

From: Ethan Post <epost1_at_my-deja.com>
Date: Wed, 16 Aug 2000 00:48:05 GMT
Message-ID: <8ncoc0$58p$1@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 - 19:48:05 CDT