The hidden privilege...

... or just another reason you should not be using the standard Oracle roles CONNECT, RESOURCE and DBA.

Let's check which system privileges are granted to the role RESOURCE:

SELECT privilege
FROM   dba_sys_privs
WHERE  grantee = 'RESOURCE';

PRIVILEGE
---------
CREATE TRIGGER
CREATE SEQUENCE
CREATE TYPE
CREATE PROCEDURE
CREATE CLUSTER
CREATE OPERATOR
CREATE INDEXTYPE
CREATE TABLE

But what happens if we grant this role to a new user?

First we create a new, virgin user (no login permitted by setting an "impossible" password):

CREATE USER restest IDENTIFIED BY VALUES 'restest';

Then we check his system privileges:

SELECT privilege
FROM   dba_sys_privs
WHERE  grantee = 'RESTEST';

no rows selected

Next, we grant him the RESOURCE role. What will his system privileges be? He should have no directly granted system privileges, just the role grant (see DBA_ROLE_PRIVS).

GRANT RESOURCE TO restest;

SELECT privilege
FROM   dba_sys_privs
WHERE  grantee = 'RESTEST';

PRIVILEGE
---------
UNLIMITED TABLESPACE

Uups - is that what we want? No! The user would now be able to create database objects in every tablespace with unlimited size.