The UNLIMITED TABLESPACE system privilege does not let a user create their own datafiles. The CREATE TABLESPACE system privilege lets a user create a tablespace. The ALTER TABLESPACE system privilege lets a user add a datafile to an existing tablespace. Make sure your users do not have either of these system privileges as these operations should only be performed by the DBA.
The UNLIMITED TABLESPACE system privilege lets the user allocate as much space as they want in any and all tablespaces that make up the database. This should be revoked from the user and you should grant quotas on the tablespaces the user can allocate space in. The quotas are defined with the ALTER USER QUOTA command similar to the following:
ALTER USER scott QUOTA unlimited ON users; ALTER USER scott QUOTA 100M ON appl_data; ALTER USER scott QUOTA 0 ON system;
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.