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 ora_check_data_privilege.eps follows
Description of the illustration ora_check_data_privilege.eps

privilege


Description of privilege.eps follows
Description of the illustration privilege.eps

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