Q

Users have unlimited tablespace system privileges

I recently I found out that the users in production have been given unlimited tablespace quotas and system privileges as a result of which they are creating their own datafiles and allocating as much space they need.

Hi Brian, We have a severe problem with our production database. I am new to this job, but recently I found out that the users in production have been given unlimited tablespace quotas and system privileges as a result of which they are creating their own datafiles and allocating as much space they need. Could you please advise as to how to handle this issue? Thanks.

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;
This was first published in June 2007

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close