RelationalDBDesign RelationalDBDesign


Creating Users   «Prev 

Unlimited Tablespace

There are two approaches you can take when you don't want to restrict the amount of disk space assigned to a user.
One approach is to assign that user unlimited quota on all the tablespaces in the database. That approach requires a lot of typing and, as you add tablespaces, you need to remember to assign the user quotas on those tablespaces. An easier approach is to grant the user the UNLIMITED TABLESPACE system privilege. This is done with the GRANT command and it allows a user to use any amount of disk space in any tablespace.


Question: How can I use an unlimited amount of any tablespace?
This privilege overrides any specific quotas assigned. If you revoke this privilege from a user, the user's schema objects remain but further tablespace allocation is denied unless authorized by specific tablespace quotas. You cannot grant this system privilege to roles.

FOR TRIGGERS:

Privilege Description
CREATE TRIGGER Create a database trigger in grantee's schema
CREATE ANY TRIGGER Create database triggers in any schema except SYS 
ALTER ANY TRIGGER Enable, disable, or compile database triggers in any schema except SYS 
DROP ANY TRIGGER Drop database triggers in any schema except SYS 
ADMINISTER DATABASE TRIGGER Create a trigger on DATABASE. (You must also have the CREATE TRIGGER or CREATE ANY TRIGGER privilege.) 

FOR TYPES:

Privilege Description
CREATE TYPE Create object types and object type bodies in grantee's schema 
CREATE ANY TYPE Create object types and object type bodies in any schema except SYS 
ALTER ANY TYPE Alter object types in any schema except SYS 
DROP ANY TYPE Drop object types and object type bodies in any schema except SYS 
EXECUTE ANY TYPE Use and reference object types and collection types in any schema except SYS, and invoke methods of an object type in any schema if you make the grant to a specific user. If you grant EXECUTE ANY TYPE to a role, users holding the enabled role will not be able to invoke methods of an object type in any schema

FOR USERS:

Privilege Description
CREATE USER Create users. This privilege also allows the creator to assign quotas on any tablespace, set default and temporary tablespaces, assign a profile as part of a CREATE USER statement
ALTER USER Alter any user. This privilege authorizes the grantee to Change another user's password or authentication method,  Assign quotas on any tablespace, Set default and temporary tablespaces, and  Assign a profile and default roles
BECOME USER Become another user. (Required by any user performing a full database import.)
DROP USER Drop users

FOR VIEWS:

Privilege Description
CREATE VIEW Create views in grantee's schema
CREATE ANY VIEW Create views in any schema except SYS 
DROP ANY VIEW Drop views in any schema except SYS