Managing Roles   «Prev  Next»

Lesson 3 Types of system privileges
Objective List the different types of system privileges.

Types of System Privileges

Object Create Privilege

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
Database Administration Includes such privileges as ALTER DATABASE and ALTER SYSTEM that allow you to control various aspects of database operation
Miscellaneous Includes anything that doesn't fall neatly into any of the other categories

The following diagram outlines the privileges in each category.
System-privileges
System-privileges
  1. Allows a user to create, drop, and alter tables in their own schema
  2. Allows a user to create, drop, and alter indexes in their own schema.
  3. Allows a user to create, drop, and alter views in their own schema.
  4. Allows a user to create, drop, and alter sequences in their own schema.
  5. Allows a user to create, drop, and alter stored procedures, stored functions, and packages in their own schema.
  6. Allows a user to create, drop, and alter synonyms in their own schema.
  7. Allows a user to create, drop, and alter triggers in their own schema.
  8. Allows a user to create a table in someone else's schema.
  9. Allows a user to alter someone else's table.
  10. Allows a user to drop someone else's table.
  11. Allows a user to create an index in someone else's schema.
  12. Allows a user to alter someone else's index.
  13. Allows a user to drop someone else's index.
  14. Allows a user to create synonyms that affect all users.
  15. Allows a user to create a publicly accessible database link.
  16. Allows a user to drop a public database link.
  17. Allows a user to drop a public synonym.
  18. Allows a user to create a tablespace.
  19. Allows a user to modify a tablespace definition.
  20. Allows a user to drop a tablespace.
  21. Allows a user to create a rollback segment.
  22. Allows a user modify a rollback segment.
  23. Allows a user to issue the ALTER DATABASE command.
  24. Allows a user to issue the ALTER SYSTEM command.
  25. Allows a user to use any amount of space in any tablespace.

System Privileges Types
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

Ad OCA Oracle Database