Table Privileges - Oracle

The types of privileges that can be granted on a table fall into two broad categories: DML operations and DDL operations. DML operations includeDELETE,INSERT,SELECT, andUPDATE, whereas DDL operations include adding, dropping, and changing columns in the table as well as creating indexes on the table.

When granting DML operations on a table, it is possible to restrict those operations only to certain columns. For example, you may want to allowKELLYCto see and update all the rows and columns in theHR.EMPLOYEES table except for theSALARYcolumn. To do this, you first need to revoke the existingSELECTprivilege on the table:

SQL> revoke update on hr.employees from kellyc;
Revoke succeeded.

Next, you will letKELLYCupdate all the columns except for theSALARYcolumn:

SQL> grant update (employee_id, first_name, last_name, email,
  2               phone_number, hire_date, job_id, commission_pct,
  3               manager_id, department_id)
  4  on hr.employees to kellyc;
Grant succeeded.

KELLYCwill be able to update all columns in theHR.EMPLOYEEStable except for theSALARY column:

SQL> update hr.employees set first_name = 'Stephen' where employee_id = 100;
1 row updated.
SQL> update hr.employees set salary = 150000 where employee_id = 203;
update hr.employees set salary = 150000 where employee_id = 203
          *
ERROR at line 1:
ORA-01031: insufficient privileges

View Privileges

Privileges on views are similar to those granted on tables. (We show you how to create views in Chapter 35.) Rows in a view can be selected, updated, deleted, or inserted, assuming that the view is updatable. To create a view, first you need either theCREATE VIEWsystem privilege (to create a view in your own schema) or theCREATE ANY VIEWsystem privilege (to create a view in any schema). Even to create the view, you must also have at leastSELECTobject privileges on the underlying tables of the view, along withINSERT,UPDATE, andDELETE, if you wish to perform those operations on the view and the view is updatable. Alternatively, you can have theSELECT ANY TABLE,INSERT ANY TABLE,UPDATE ANY TABLE  orDELETE ANY TABLE privileges if the underlying objects are not in your schema.

To allow others to use your view, you must also have permissions on the view’s base tables with theGRANT OPTION, or you must have the system privileges with theADMIN OPTION. For example, if you are creating a view against theHR.EMPLOYEEStable, you must have been granted theSELECTobject privilegeWITH GRANT OPTIONonHR.EMPLOYEES, or you must have theSELECT ANY TABLEsystem privilegeWITH ADMIN OPTION.

Object Privilege Data Dictionary Views

A number of data dictionary views contain information about object privileges assigned to users. Table 31-9 lists the most important views containing object privilege information.

Table 31-9. Object Privilege Data Dictionary Views 

Please check back next week for the eighth part of the series.