Issue on tablespace Quotas (Oracle 8.1.7)

[Chris Fung]

Hi there,

I have tried to create a user test with "1K" quota on "users" tablespace....

I would expect I can create database object that cannot exceed this "1K" value as far as storage is concern....

However, how come I can create several..."1M" or even 10M tables ??

Could anyone explain the mechanism(s) behind ??

Cheers,

Chris

[Yogeeraj_1 · Accepted solution]

hi,

you could have created a user with the quota, but after that granted him a role that gives him "unlimited tablespace" system privilege which overrides it!

can you post the output of the following sql code?

select /*+ CHOOSE */ privilege, admin_option
from SYS.dba_sys_privs
where GRANTEE='&GRANTEE'
order by 1

============================================================
Anyway, i did the following test for you.

yd@YDDB.MU> CREATE USER CHRIS IDENTIFIED BY CHRIS
DEFAULT TABLESPACE SLX1_DEV_LMT_SMALL
TEMPORARY TABLESPACE TEMP_LMT
QUOTA 1K ON SLX1_DEV_LMT_SMALL;

User created.

Elapsed: 00:00:00.06
yd@YDDB.MU> GRANT CREATE SESSION TO CHRIS;

Grant succeeded.

Elapsed: 00:00:00.01

yd@YDDB.MU> grant create table to chris;

Grant succeeded.

Elapsed: 00:00:00.01
yd@YDDB.MU> grant create table to chris;
yd@YDDB.MU> @connect chris/chris
Connected.
chris@YDDB.MU> CREATE TABLE YDTAB1 (
CLICOD VARCHAR2 (8))
TABLESPACE SLX1_DEV_LMT_SMALL
PCTFREE 10
PCTUSED 75
INITRANS 5
MAXTRANS 100
STORAGE (
INITIAL 65536
NEXT 65536
PCTINCREASE 0)
;
CREATE TABLE YDTAB1 (
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'SLX1_DEV_LMT_SMALL'


Elapsed: 00:00:00.02
chris@YDDB.MU> @connect yd
Enter password:
Connected.
yd@YDDB.MU> grant resource to chris;

Grant succeeded.

Elapsed: 00:00:00.02
yd@YDDB.MU> @connect chris/chris
Connected.
chris@YDDB.MU> CREATE TABLE YDTAB1 (
CLICOD VARCHAR2 (8))
TABLESPACE SLX1_DEV_LMT_SMALL
PCTFREE 10
PCTUSED 75
INITRANS 5
MAXTRANS 100
STORAGE (
INITIAL 65536
NEXT 65536
PCTINCREASE 0)
;

Table created.

Elapsed: 00:00:00.04
chris@YDDB.MU> @connect yd
Enter password:
Connected.
yd@YDDB.MU> select /*+ CHOOSE */ privilege, admin_option
2 from SYS.dba_sys_privs
3 where GRANTEE='&GRANTEE'
4* order by 1
Enter value for grantee: CHRIS

CREATE SESSION NO
CREATE TABLE NO
UNLIMITED TABLESPACE NO

Elapsed: 00:00:00.01
yd@YDDB.MU> revoke resource from chris;

Revoke succeeded.

Elapsed: 00:00:00.04
yd@YDDB.MU> select /*+ CHOOSE */ privilege, admin_option
from SYS.dba_sys_privs
where GRANTEE='&GRANTEE'
order by 1
2 3 4 5
yd@YDDB.MU> /
Enter value for grantee: CHRIS

CREATE SESSION NO
CREATE TABLE NO

Elapsed: 00:00:00.00
yd@YDDB.MU>
============================================================

Hope this helps!

Regards
Yogeeraj

[malay boy]

well nothing left to say here.Our database guru have spell everything out.

regards
mB

[Indira Aramandla]

Hi chris,

You can control the tablespace quotas for users by allocating a certain quota. But the moment you grant the role RESOURCE to the user this grants the system privilege "UNLIMITED TABLESPACE".

Yogeeraj has demonstrated excellently with an example.

[Chris Fung]

Hi all,

Thanks for Yogeeraj demonstrated a very detailed example !!

On the other hand, Indira used a few word to point out exactly what my problem was - "Resource" role.

I just remembered that I grant resource to the user after I create the user with quota on some tablespaces.

Many thanks,

Chris,