HomeOracle Page 2 - Using Privileges in Oracle Database XE
Table Privileges - Oracle
In this seventh part of a ten-part series on the security features of Oracle Database XE, you'll learn how to use object privileges, table privileges, and more. This article is excerpted from chapter 31 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).
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
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
Data Dictionary View
Table privileges granted to roles and users. Includes the user who granted the privilege to the role or user, with or without GRANT OPTION.
Column privileges granted to roles or users, containing the column name and the type of privilege on the column.
For the current session, privileges granted on tables via roles.
Please check back next week for the eighth part of the series.