Dear all,
Does it (RESOURCE ROLE) internally allows (UNLIMITED TABLESPACE) privilege in 11g ?
SQL> select PRIVILEGE from role_sys_privs where role='RESOURCE';
PRIVILEGE
----------------------------------------
CREATE SEQUENCE
CREATE TRIGGER
CREATE CLUSTER
CREATE PROCEDURE
CREATE TYPE
CREATE OPERATOR
CREATE TABLE
CREATE INDEXTYPE
8 rows selected.
Does it (RESOURCE) contain unlimited tablespace indirectly ?
Because even we set quota for the specific user , user is always consuming unlimited space.
Example
NOTE : i set only 5m quota to the user ( usr1) on users tablespace .. but he is utilizing 43M space on users tablespace.
I am getting confused.
SQL> grant connect,resource to usr1 identified by usr1;
Grant succeeded.
SQL> alter user usr1 quota 5m on users;
User altered.
SQL> conn usr1/usr1
Connected.
SQL> @demobld.sql
SP2-0734: unknown command beginning "copyright ..." - rest of line ignored.
Building demonstration tables. Please wait.
Demonstration table build is complete.
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
>>
[oracle@RAC1 ~]$ rlsqlplus usr1/usr1
SQL*Plus: Release 11.2.0.1.0 Production on Wed Dec 27 15:51:49 2017
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select count(*) from emp;
COUNT(*)
----------
14
>>
SQL> insert into emp select * from emp;
14 rows created.
SQL> insert into emp select * from emp;
28 rows created.
SQL> insert into emp select * from emp;
56 rows created.
SQL> insert into emp select * from emp;
112 rows created.
..
...
[Trimmed]
SQL> select count(*) from emp;
COUNT(*)
----------
917504
SQL> select SEGMENT_NAME, BYTES/1024/1024, tablespace_name from user_segments;
SEGMENT_NAME BYTES/1024/1024 TABLESPACE_NAME
---------------------- --------------- ------------------------------
EMP 43 USERS
DEPT .0625 USERS
SALGRADE .0625 USERS
DUMMY .0625 USERS
DB : 11.2.0.1
Thanks