View Privileges

The next objects that have privileges associated with them are the views. Note that users have access to data from a table through a view even though they do not have access to the base table. This technique can be used to limit the amount of information that certain users can work with, thereby eliminating the need to use column-level access privileges. When a view is used to join together columns from multiple tables, the creator needs the appropriate access to all the tables included in the view to create the view. Note that you cannot alter the structure of a view. Because it does not contain any data itself, the designers of Oracle felt that it was easier to drop and re-create a view than to allow modification of the view. Therefore, Oracle enables you to issue a command to CREATE or REPLACE VIEW (which enables you to keep any privileges or grants that you may have made before re-creating the view). The following are the object privileges associated with views:

•  DELETE allows the user to remove rows of data using the view. This can be very tricky because you may not want to delete the rows of data from both tables when you have the two joined together. Try to avoid allowing users to delete data using a view.

•  INSERT allows the user to add rows of data using the view. Again, this can be somewhat tricky because there may be mandatory fields in the base table that are not included in the view (for example, primary keys). INSERT does not work for views that contain data from multiple tables.

•  SELECT allows the user to read data using the view. This is the “safe” view privilege because all the user looks at is the data.

•  UPDATE allows the user to modify existing data using the view. Allowing the user to modify such things as primary keys should be avoided in views. UPDATE does not work for most views that contain data from multiple tables.

Sequence Privileges

Another database object that has its own set of object privileges is the sequence. These are relatively simple objects (a list of numbers). There are only two object privileges associated with sequences:

•  ALTER allows the user to modify the structure of the sequence.

•  SELECT allows the user to read the next value from the sequence.

Procedure Privileges

The final database object with object privileges is the procedure. This group includes packages (PL/SQL routines) and functions (which are similar to procedures, but have slightly different calling parameters). It does not include triggers, which are bits of software that are automatically tied to tables. The procedure has only one object privilege applicable to it—EXECUTE. This allows a user to run the software contained within the procedure.

Column Privileges

A final note on the column privileges capability of Oracle: As mentioned earlier, I have never used them because of the overhead associated with having to check column privileges in addition to table privileges in a query. I usually use views for this function, but there are applications wherein column privileges are the only practical answer. The good news is that column privileges are relatively simple to grant and revoke. You use all the privileges that your are familiar with for tables (for example, SELECT) and merely put the names of the applicable columns in parentheses after the table name.

User Privileges

The first set of privileges belongs to common users of the database (see Table 13.2). Even though these users share this common and relatively simple function (read and write to existing objects), Oracle provides a few variations in access privileges that allow organizations to tighten up security to the maximum extent possible (for example, by removing the ALTER SESSION privilege).