CIS 421b: Database Administration I

Chapter 12: System and Object Privileges

Objectives:

 

This chapter presents methods for managing privileges to objects in the system. The objectives important to this chapter are:

1. Understanding privileges
2. Granting and revoking privileges
3. Understanding auditing

Concepts:

As the last chapter explained, a user must be granted a specific privilege that allows them to log on to an Oracle database. This chapter expands on that concept. A user may be given privileges that relate to the database system in general, or privileges that relate to specific objects in the database.

The text explains that users may be granted any of over a hundred system privileges. Some examples from the text:

• SYSDBA - a powerful privilege that allows a user to start and stop the database, and manage all objects
• CREATE SESSION - allows a user to log on
• CREATE TABLE - allows a user to create tables in his/her own schema (compare this to the next privilege)
• CREATE ANY TABLE - allows a user to create tables in any schema

Different types of objects in a database have different types of permissions that can be granted to them. Most of the ones listed in the text apply to tables and views. A user may be granted permission to ALTER, DELETE, INDEX, INSERT, SELECT, and UPDATE a table. ALTER and INDEX do not apply to views, but the other privileges do.

When granting one or more system privileges, you may use a command whose syntax follows this pattern:
GRANT SystemPrivilege
TO user
WITH ADMIN OPTION;
You may grant one or several privileges, separating a list with commas. The same applies to the users. You may grant all privileges with the keyword ALL, or may grant privileges to all users by substituting the keyword PUBLIC for the user list. If you use the optional WITH ADMIN OPTION clause, you give your list of users the right to grant their rights to other users.

When removing privileges, the GRANT... TO syntax is replaced by REVOKE... FROM.

Granting object privileges is just a bit more complicated. The syntax is similar, but allows you to grant privileges to specific columns in a table (or view) and to objects in specific schemas. To allow your users to grant their rights to other users, use WITH GRANT OPTION. (Assume that a user with system privileges may act as an admin, but a user with only object privileges may not.) The syntax to grant a privilege may follow this example:
GRANT ObjectPrivilege
(ColumnList) ON schema.object
TO user
WITH GRANT OPTION
WITH HIERARCHY OPTION;
Another difference is the last option, which is used to grant the same rights to any objects contained in the named objects. (Called subobjects in the text.)

The text notes that if you grant an object privilege to a user, who then grants it to others, you can remove it from all of them by revoking the privilege from the original grantee. This is true of object privileges, but not true of system privileges.

The text discusses three types of auditing that can be enabled on the Oracle database.

• statement auditing - watches for specific commands issued by particular users
• privilege auditing - watches for commands from any user
• object auditing - watches for specific actions on specific objects

Each type of audit can be modified to perform particular ways:

• by session - writes one log entry for each session in which an action takes place
• by access - writes one log entry for each time an action occurs
• when successful - writes a log entry only if the action attempted is successful
• when not successful - writes a log entry only if the action attempted is unsuccessful

The text lists database views that are associated with each type of auditing:

• DBA_AUDIT_OBJECT - object audit logs
• DBA_AUDIT_SESSION - session audit logs
• DBA_AUDIT_STATEMENT - statement audit logs
• DBA_AUDIT_TRAIL - all audit logs