RESOURCE role and UNLIMITED TABLESPACE role
                                                 Last update (2010-07-06 21:22:09)
                                                                                                                   Date added (2010-07-06 21:10:48)

Summary
When a user is granted the RESOURCE role, then automatically gets and the UNLIMITED TABLESPACE role as a bonus(funny)!

Unlimited tablespace privilege means that the user can write on any tablespace. It is very common in exports/imports the DBA to create a new schema to insert the data. After that, grants the RESOURCE role to the new user so without knowing it grants and the UNLIMITED TABLESPACE role.

So the result is during the import the objects that are not stored in the user's default tablespace but in the exported user's tablespace!

The workaround is to revoke the UNLIMITED TABLESPACE role from the new user before the import
revoke UNLIMITED TABLESPACE from username;
Reviews
Categories
Oracle DBA-> (147)
  Alertlog (3)
  Archivelog (5)
  Auditing (1)
  Backup - Recovery (19)
  Database files (6)
  Database options (2)
  DB links (2)
  Dependencies (1)
  Dictionary (2)
  Exp-Imp Datapump (6)
  Jobs (2)
  Mview (2)
  Networking (3)
  Objects (7)
  Parameters (2)
  Redologs (6)
  Roles - Grants (2)
  Rollback - Undo (8)
  Segments (3)
  Sequences (2)
  Sessions (14)
  SGA (14)
  Tables (10)
  Tablespaces (10)
  Temp (4)
  Toad (5)
  Transactions (1)
  Upgrade (2)
  Users (3)
Oracle APPS DBA-> (66)
Exadata (1)
Performance Tuning-> (59)
Oracle Real Cases (24)
Oracle Errors (23)
Oracle SQL tricks (32)
Oracle RAC (3)
Oracle Security (8)
Filters
Search