System Privileges

As discussed in the previous section, Oracle system privileges control a user’s access to the Oracle instance as a whole. They specify the Oracle functions (for example, CREATE TABLE) that a user can perform. Before reading about the various system privileges, note these points:

•  Certain privileges have prerequisites of other privileges. For example, you must have CREATE TABLE to have the CREATE SNAPSHOT privilege.

•  Read the descriptions of the privileges. There are a few privileges (such as GRANT ANY PRIVILEGE) that are not exactly what you would expect by just reading their titles.

•  The key to being able to grant an Oracle system privilege in Oracle6 is having the DBA privilege.

•  The key to being able to grant an Oracle system privilege in Oracle7 is having the GRANT ANY PRIVILEGE privilege assigned to your account.

•  There are additional mechanisms to provide finer levels of control that are available to the DBA. For example, if you want to allow users to create tables, but only in a certain tablespace, you can give them the CREATE TABLE privilege and then give them a space quota only on the desired tablespace.

First, let’s review the privilege sets that are available under Oracle6. Oracle6 users fall into three categories:

•  CONNECT. These users can access Oracle data as permitted by the object privileges that are assigned to them. They cannot create database objects or perform any administrative functions.

•  RESOURCE. These users can access Oracle data as permitted by their object privileges and also create database objects such as tables and views. They cannot perform any database administrative functions.

•  DBA. These users can access data, create objects, and perform Oracle administrative functions. They have full access to all data in the database and can delete any database objects. There are very few restrictions placed on users with the DBA privilege, so be extremely careful when using these accounts.

Now on to the more sophisticated privilege scheme in Oracle7 and Oracle8. Rather than present system privileges as a laundry list, one after the other, these privileges are grouped into functional categories (see Figure 13.4).

Figure 13.4.  System privilege categories.

•  The User Privileges. These privileges relate to users who will access the system, but not create any objects. These are similar to the CONNECT users under Oracle6.

•  The Developer Privileges. These privileges relate to the creation of database objects. These are similar to the RESOURCE users under Oracle6.

•  The “Any” Privileges. These are an especially powerful set of privileges within Oracle. Normally, I would give these only to DBAs and extremely senior developers working in dedicated development instances. These are similar to regular privileges except that they apply not only to objects that you own, but objects that other users (including SYS and SYSTEM) own.

•  The Database Maintenance Privileges. These privileges relate to the care and feeding of the database. They are typically assigned only to DBAs and other computer support staff.

•  The Monitoring Privileges. These privileges relate to keeping an eye on the Oracle database. They are typically assigned to DBAs, but can also be used by separate security administrators to monitor system security.

Object Privileges

Every object in the database has a set of access privileges associated with it. Some are implied by the type of object. Public synonyms, for example, are universally accessible by their very definition. However, most objects have some form of protection that the owners and database administrators need to keep track of as part of the overall security schema. That is what this chapter is all about.

The first subject to master is the list of privileges provided by Oracle:

•  ALTER allows the user to modify the internal structure of the object. For example, it allows the user to add columns to a table. It does not allow the user to modify the contents of the data itself (see UPDATE, INSERT, and DELETE). Note that you cannot drop columns from a database object; you can only add or modify them.

•  DELETE allows the user to remove one or more rows of data from the object.

•  EXECUTE allows the user to execute a stored package, procedure, or function.

•  INDEX allows the user to have access to the data in a table to create an index associated with the table. Note that the select privilege is not enough for a user to create an index on the table.

•  INSERT allows the user to add rows of data to the object.

•  REFERENCES allows the user to create or alter another table that references this table as a foreign key. It does not allow the user who granted this privilege to alter this table in any way. Instead, it grants that user permission to create a new table, which has a rule that the contents of one or more of its columns has to match a corresponding column in the table on which the REFERENCES privilege is being granted (or else be null).

•  SELECT allows the user to read rows from the object using the SQL select statement. This is probably the most common privilege.

•  UPDATE allows the user to modify the contents of existing rows in the table by using the update command.

The owner of the object has the full set of privileges available on that object when the object is created. These privileges can be given up via the revoke statement, but usually the owner can do anything that is necessary.