Using Privileges in Oracle Database XE

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).

Using Object Privileges

In contrast to a system privilege, an object privilege is a right to perform a particular type of action on a specific object, such as a table or a sequence that is not in the user’s own schema. As with system privileges, you use the GRANT and REVOKE commands to grant and revoke privileges on objects. Also with system privileges, you can grant object privileges to PUBLIC , and users with object privileges may pass them on to others by granting them with the WITH GRANT OPTION clause.

A user with objects in his own schema automatically has all object privileges on those objects and can grant any object privilege on these objects to any user or another role, with or without the WITH GRANT OPTION clause.

Table 31-8 shows the object privileges available for different types of objects; some privileges are only applicable to certain types of objects. For example, the INSERT privilege only makes sense with tables, views, and materialized views; the EXECUTE privilege, on the other hand, is applicable to functions, procedures, and packages, but not tables.

Table 31-8. Object Privileges   

Object Privilege

Capability

ALTER

Can alter a table or sequence definition.

DELETE

Can delete rows from a table, view, or materialized view.

EXECUTE

Can execute a function or procedure, with or without a package.

DEBUG

Can allow access to PL/SQL code in triggers defined on a table, or SQL statements that reference a table. For object types, this privilege allows access to all public and private variables, methods, and types defined on the object type.

FLASHBACK

Can allow flashback queries on tables, views, and materialized views using retained undo information

INDEX

Can create an index on a table.

INSERT

Can insert rows into a table, view, or materialized view.

ON COMMIT REFRESH

Can create a refresh-on-commit materialized view based on a table.

QUERY REWRITE

Can create a materialized view that can be used by Oracle to rewrite a query based on a table. 

READ

Can read the contents of an operating system directory using an Oracle DIRECTORYdefinition.

REFERENCES

Can create a foreign key constraint that references another table’s primary key or unique key.

SELECT

Can read rows from a table, view, or materialized view, in addition to reading current or next values from a sequence.

UNDER

Can create a view based on an existing view.

UPDATE

Can update rows in a table, view, or materialized view.

WRITE

Can write information to an operating system directory using an Oracle DIRECTORYdefinition.

Some of these object privileges overlap with system privileges. For example, if you don’t have the FLASHBACK object privilege on a table, you can still perform flashback queries if you have the FLASHBACK ANY TABLE system privilege.

In the following example, the DBA grants KELLYC full access to the table HR.EMPLOYEES , but only allows KELLYC to pass on the SELECT object privilege to other users:

SQL> grant insert, update, delete on hr.employees to kellyc ;
Grant succeeded.
SQL> grant select on hr.employees to kellyc with grant option;
Grant succeeded.

Note that if the SELECT privilege on the table HR.EMPLOYEES is revoked from KELLYC , the SELECT privilege is also revoked from anyone she granted the privilege.

{mospagebreak title=Table Privileges}

The types of privileges that can be granted on a table fall into two broad categories: DML operations and DDL operations. DML operations include DELETE , INSERT , SELECT , and UPDATE , 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 allow KELLYC to see and update all the rows and columns in the HR.EMPLOYEES table except for the SALARY column. To do this, you first need to revoke the existing SELECT privilege on the table:

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

Next, you will let KELLYC update all the columns except for the SALARY column:

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.

KELLYC will be able to update all columns in the HR.EMPLOYEES table except for the SALARY 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 the CREATE VIEW system privilege (to create a view in your own schema) or the CREATE ANY VIEW system privilege (to create a view in any schema). Even to create the view, you must also have at least SELECT object privileges on the underlying tables of the view, along with INSERT , UPDATE , and DELETE , if you wish to perform those operations on the view and the view is updatable. Alternatively, you can have the SELECT ANY TABLE , INSERT ANY TABLE , UPDATE ANY TABLE  or DELETE 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 the GRANT OPTION , or you must have the system privileges with the ADMIN OPTION . For example, if you are creating a view against the HR.EMPLOYEES table, you must have been granted the SELECT object privilege WITH GRANT OPTION on HR.EMPLOYEES , or you must have the SELECT ANY TABLE system privilege WITH 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.

[gp-comments width="770" linklove="off" ]
antalya escort bayan antalya escort bayan