Why I cannot create a table even I have the RESOURCE role?

[Quoted] Hello all,

  I have a question about Oracle 8.0.5 on Solaris.

• User A ****

  ---

SQL> select * from user_role_privs;

USERNAME GRANTED_ROLE                   ADM DEF OS_
-------- ------------------------------ --- --- ---
A        CONNECT                        NO  YES NO
A        A_ROLE                         NO  YES NO
A        RESOURCE                       NO  YES NO

3 rows selected.

SQL> select username, default_tablespace, temporary_tablespace from user_users;

USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE

-------- ------------------------------ ------------------------------
A        USERS                          TEMP

1 row selected.

SQL> create table aaa (a number);
create table aaa (a number)
*

ERROR at line 1:
[Quoted] ORA-01536: space quota exceeded for tablespace 'USERS'

• User B ****

  ---

SQL> select * from user_role_privs;

USERNAME GRANTED_ROLE                   ADM DEF OS_
-------- ------------------------------ --- --- ---
B        CONNECT                        NO  YES NO
B        A_ROLE                         NO  YES NO
B        RESOURCE                       NO  YES NO

3 rows selected.

SQL> select username, default_tablespace, temporary_tablespace from user_users;

USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE

-------- ------------------------------ ------------------------------
B        USERS                          TEMP

1 row selected.

SQL> create table aaa (a number);

Table created.

     Why user A cannot create a table while B can? Both users have the same privileges. No profile for these two users. No password file with this database. Moreover, I still cannot understand why user A cannot create table despite the fact that he has the RESOURCE role. I think a user has the RESOURCE role can have the unlimited quota on tablespaces.

Thanks,
David

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Jun 02 2000 - 00:00:00 CEST