16.4 Use Access Check Functions

Oracle Deep Data Security (Deep Sec) provides two SQL functions that allow applications and users to programmatically inspect data authorization at runtime. These functions are particularly useful for building dynamic user interfaces that reflect a user's actual privileges, or for handling unauthorized NULL values in query results.

Topics:

16.4.1 Use the ORA_IS_COLUMN_AUTHORIZED Function

The ORA_IS_COLUMN_AUTHORIZED function differentiates between a genuine NULL value stored in the database and a NULL returned due to insufficient Deep Sec user privileges.

This is important because Deep Sec returns NULL for unauthorized column cells, which is otherwise indistinguishable from a stored NULL.

Return value

  • Returns TRUE if the current Deep Sec user is authorized to access the column value for the current row, or if the column is not protected by a data grant.
  • Returns FALSE if the user is not authorized to access the column value.

Syntax

ORA_IS_COLUMN_AUTHORIZED(column_reference [, privilege])
  RETURN BOOLEAN

privilege ::= SELECT | INSERT | UPDATE

Parameters

Parameter Description

column_reference

A reference to a specific column in a table or view that requires an authorization check for a given row. This parameter does not accept expressions. It can optionally be qualified with schema and object name, for example, schema.object.column.

If the column name is ambiguous (same column name in multiple objects in the same query), the column reference must be qualified; otherwise ORA-00918 is raised.

privilege

The privilege to check authorization for. You can specify SELECT, INSERT, or UPDATE; SELECT is the default.

If DELETE is specified along with a column reference, ORA-52540 is raised. If NULL is passed, ORA-01760 is raised.

For syntax diagrams and additional details, see ORA_IS_COLUMN_AUTHORIZED in Oracle AI Database SQL Language Reference.

Example 16-14 Display a masked value for unauthorized column values

Marvin, who is both an employee and a manager, uses ORA_IS_COLUMN_AUTHORIZED to display a placeholder value (000-00-0000) instead of NULL for SSN values he is not authorized to view. 

MARVIN> SELECT first_name, last_name,
            DECODE(
                ORA_IS_COLUMN_AUTHORIZED(ssn),
                FALSE, '000-00-0000',
                TRUE, ssn
            ) AS ssn,
            email,
            manager
        FROM hr.employees;

For rows where Marvin is not authorized to see the SSN (his direct reports Emma and Taylor), the function returns FALSE and the DECODE expression substitutes 000-00-0000. For his own row, the function returns TRUE and the actual value is shown. 

FIRST_NAME LAST_NAME  SSN          EMAIL       MANAGER
---------- ---------- ------------ ----------- ----------
Marvin     Anderson   457-55-5462  manderson   vwilliams
Emma       Baker      000-00-0000  ebaker      manderson
Taylor     Mills      000-00-0000  tmills      manderson
 
3 rows selected.

16.4.2 Use the ORA_CHECK_DATA_PRIVILEGE Function

The ORA_CHECK_DATA_PRIVILEGE function allows applications and users to determine which privileges a Deep Sec user holds for specific rows or column values.

This is essential for enforcing security in APIs and controlling dynamic UI elements (for example, enabling or disabling an Edit button) based on a user’s access rights.

Return value

  • Returns TRUE for row and column values where the privilege is granted on the specified object.
  • If the privilege is not granted to the Deep Sec user, it returns FALSE.

Syntax

ORA_CHECK_DATA_PRIVILEGE(object, privilege [, column])
  RETURN BOOLEAN

Parameters

Parameter Description

object

A schema-qualified object name or object alias.

privilege

The CRUD privilege to check. You can specify SELECT, INSERT, UPDATE, or DELETE.

DELETE cannot be combined with the column parameter; if combined, ORA-52540 is raised. If NULL is passed, ORA-01760 is raised.

column

Optional. When specified, checks whether the privilege is authorized for the specified column value in the current row, in addition to the row-level check.

For syntax diagrams and additional details, see ORA_CHECK_DATA_PRIVILEGE in Oracle AI Database SQL Language Reference.

Example 16-15 Check row-level and column-level privileges

The following query determines which operations Marvin (who holds both employee_role and manager_role data roles) can perform on hr.employees. It checks the row-level SELECT access and the column-level UPDATE access for the phone column. 

MARVIN> SELECT first_name, last_name,
              manager,
              ORA_CHECK_DATA_PRIVILEGE(emp, 'SELECT')         AS can_view,
              ORA_CHECK_DATA_PRIVILEGE(emp, 'UPDATE', phone)  AS can_update_phone
       FROM   hr.employees emp;

FIRST_NAME LAST_NAME  MANAGER    CAN_VIEW  CAN_UPDATE_PHONE
---------- ---------- ---------- --------- ----------------
Marvin     Anderson   vwilliams  TRUE      TRUE
Emma       Baker      manderson  TRUE      FALSE
Taylor     Mills      manderson  TRUE      FALSE
 
3 rows selected.

The result confirms that Marvin can view all three rows (his own record and his direct reports) but can only update the phone column for his own row. His direct reports' phone columns are visible, but he cannot update them.