For just about every database object that you can create, you will find that there is an associated CREATE privilege. If the object is a schema object, the CREATE privilege will allow a user to create, alter, and delete such objects in their own schema. For such objects you will also usually have CREATE ANY, ALTER ANY, and DROP ANY privileges. These allow privileged users, such as a DBA, to manage objects which they do not themselves own. For non-schema objects, in other words for objects not owned by a specific user, you usually just have separate CREATE, ALTER, and DROP privileges.
Oracle defines a very large number of system privileges that a DBA can use to fine-tune
his or her control over what users are allowed to do. Once you know the functions of the various privileges, you will need to weigh the needs of the organization and the employee when deciding which privileges to grant.
The following table defines and describes the use of these privileges:
Type of privilege
Function of privilege
Schema Object Management
Includes privileges like CREATE TABLE, that allow users to create, alter, and drop objects in their own schema
The ANY Privileges
Includes privileges like CREATE ANY TABLE and ALTER ANY TABLE that allow users to create and manage objects owned by other users
System Object Management
Includes privileges like CREATE USER and ALTER USER that allow you to manage objects that aren't attached to any particular schema
Includes such privileges as ALTER DATABASE and ALTER SYSTEM that allow you to control various aspects of database operation
Includes anything that doesn't fall neatly into any of the other categories
The following diagram outlines the privileges in each category.
The object management privileges, the ones that you grant to users so that they can create their own objects, convey broader rights than do the corresponding ANY privileges. Grant a user CREATE TABLE, for example, and he or she will be able to create,
modify, and drop tables into his or her own schema. On the other hand, the CREATE ANY TABLE privilege only allows the user to create a table, but that table may be created in any user's schema. To modify or delete another user's table, the
ALTER ANY TABLE or DROP ANY TABLE privileges must be granted.
System Privileges - Quiz
Before moving on to the next lesson, click the Quiz link below to test your understanding of system privileges.
System Privileges - Quiz