Hi All,
When I create a regular user and specify default tablespace, the user is not allowed to create table on it default tablespace:-
SYS@orasbt1> create user u1 identified by u1 default tablespace testts;
User created.
SYS@orasbt1> grant create session, create table to u1;
Grant succeeded.
SYS@orajst1> conn u1/u1
Connected.
U1@orasbt1> create table t1 (sr_no number, name varchar2(25));
create table t1 (sr_no number, name varchar2(25))
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'TESTTS'
However, when I grant resource role to this user, the user gains the ability to create table. I followed on few blogs, where they say that resource role grants unlimited tablespace on user's default tablespace. But, I don't see any such information on DBA_TS_QUOTAS view. Also, I checked on DBA_ROLE_PRIVS, DBA_SYS_PRIVS, DBA_TAB_PRIVS, ROLE_ROLE_PRIVS, ROLE_SYS_PRIVS and ROLE_TAB_PRIVS views, and there is no such grants mentioned about allocation unlimited tablespace on user's default tablespace to resource role. My question is then how come after granting resource role to below user(u1), this user is able to create table in its default tablespace?
U1@orasbt1> conn / as sysdba
Connected.
SYS@orasbt1> grant resource to u1;
Grant succeeded.
SYS@orasbt1> conn u1/u1
Connected.
U1@orasbt1> create table t1 (sr_no number, name varchar2(25));
Table created.