Home arrow Oracle arrow 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).

TABLE OF CONTENTS:
  1. Using Privileges in Oracle Database XE
  2. Table Privileges
By: Apress Publishing
Rating: starstarstarstarstar / 1
January 20, 2011

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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 

Data Dictionary View

Description

DBA_TAB_PRIVS

Table privileges granted to roles and users. Includes the user who granted the privilege to the role or user, with or without GRANT OPTION.

DBA_COL_PRIVS

Column privileges granted to roles or users, containing the column name and the type of privilege on the column.

ROLE_TAB_PRIVS

For the current session, privileges granted on tables via roles.

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



 
 
>>> More Oracle Articles          >>> More By Apress Publishing
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: