Oracle Tips by Burleson

Chapter 4 General Oracle Security

in a regular privilege granting statement, rather through a separate ALTER USER command as follows.

ALTER USER JUDY QUOTA 10K ON USER_DATA;

This will allow Judy to create only 10 Kilobytes of stored data, such as tables and materialized views, inside tablespace user_data. Although this command is designed for storage management in Oracle, it offers powerful solutions for security. By limiting how much storage the user can create in a tablespace, the user is not allowed to take over available space in a tablespace and cause failure in other applications. If a user should have unrestricted space, the word 10K should be replaced by UNLIMITED as in:

ALTER USER JUDY QUOTA UNLIMITED ON USER_DATA;

However, if the user has the UNLIMITED TABLESPACE system privilege, he or she can create tables in any tablespace, including the SYSTEM tablespace! Surely, that is not desirable. You should not grant this system privilege to any user. The following script shows the quota of each user in each tablespace.

find_ts_quota.sql

--**********************************************
--
--   Copyright © 2003 by Rampant TechPress Inc.
--
--   Free for non-commercial use.