ORA_IS_COLUMN_AUTHORIZED

Purpose

ORA_IS_COLUMN_AUTHORIZED differentiates an actual NULL cell value from an unauthorized NULL cell value for a specified column reference. If the Deep Sec end user is authorized to access the column value in the current row, or if the column is not protected by Deep Sec, then the function returns TRUE. Otherwise, it returns FALSE.

Syntax

Description of the illustration ora_is_column_authorized.eps

privilege

Description of the illustration privilege.eps

• column_reference: specifies a column reference in a table or view that needs to be checked for authorization on a particular row. This parameter does not accept expressions. column_reference can be qualified with schema and object name (schema.object.column). Schema and object are both optional.

  Note that if there is more than one object in the query where the function is used and more than one object has the same column name, then column_reference needs to be qualified to prevent failure.

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

  Note: You cannot specify DELETE with ORA_IS_COLUMN_AUTHORIZED.

Example

The following example shows how ORA_IS_COLUMN_AUTHORIZED (with the default SELECT privilege) is used inside the SQL select list to replace unauthorized NULL SSN values with 000-00-0000:

SELECT name, 
       DECODE (ORA_IS_COLUMN_AUTHORIZED(ssn), false,  
                '000-00-0000', true, ssn) ssn, email, manager 
FROM hr.employees;

NAME       SSN           EMAIL          MANAGER
---------  ------------  -----------    ---------
Marvin     108-51-4569   msmith         Neena
John       000-00-0000   japplebee      Marvin
Daniel     000-00-0000   dradcliff      Marvin
3 rows selected.