ORA_CHECK_DATA_PRIVILEGE

Purpose

ORA_CHECK_DATA_PRIVILEGE returns TRUE for rows and column values where the specified privilege is granted on the table or view specified by object.

Syntax

Description of the illustration ora_check_data_privilege.gif

privilege

Description of the illustration privilege.gif

• object: can be a schema-qualified object or an object alias.

• privilege: specifies the privilege that object or column_reference must be authorized for. It can be one of : SELECT, INSERT, UPDATE, DELETE.

  You cannot specify DELETE with column_reference.

Example

In the following example, we can check which operations Marvin (an employee and a manager) can perform on hr.employees:

SELECT name, manager,
    ORA_CHECK_DATA_PRIVILEGE (emp, 'SELECT') AS list,
    ORA_CHECK_DATA_PRIVILEGE (emp, 'UPDATE', phone)
                                  AS update_phone
FROM hr.employees emp;

The result is:

NAME     MANAGER      LIST      UPDATE_PHONE
-------- ------------ --------- ------------
Marvin   Neena        TRUE      TRUE
John     Marvin       TRUE      FALSE
Daniel   John         TRUE      FALSE
...
n rows selected

The results show that Marvin can view all employee records and update only his own phone number.