SET USE DATA GRANTS ONLY

Purpose

Use SET USE DATA GRANTS ONLY to enable mandatory access control for a table or view. Mandatory access control restricts DML access to the table or view by requiring data grants for Deep Sec users. This means that without data grants, Deep Sec users will not be allowed to access the table or view even if they are granted database privileges.

Preequisites

You must have the SET USE DATA GRANTS ONLY privilege at the system level or on the object’s schema in order to execute this command.

Syntax

Description of the illustration set_use_data_grants_only.gif

Semantics

• schema.object: refers to the table or view you want to set mandatory access control on. This means that only the data grants will be checked and honored for object access.

  If USE DATA GRANTS ONLY is enabled for an object and no data grants are present for the object, then the Deep Sec user will not be able to access the object.

  Note that this setting is applicable and enforced only for Deep Sec users.

• ENABLED | DISABLED: refers to whether the command should be disabled or enabled for the object. The default value is ENABLED.

Examples

Example 1

In the example below an employee is granted access to both hr.employees_view and the base table hr.employees:

CREATE VIEW hr.employees_view
   AS SELECT * FROM hr.employees;

Grant SELECT DATA privilege on the view hr.employees_view:

CREATE OR REPLACE DATA GRANT app_admin.EmployeesViewGrant
  AS
  SELECT
  ON hr.employees_view
  TO app_employee_role;

Grant DATA privilege on the base table hr.employees:

CREATE OR REPLACE DATA GRANT
  app_admin.EmployeesOwnRecord AS
  SELECT
  ON hr.employees
  WHERE name = ORA_END_USER_CONTEXT.username
  TO app_employee_role;

When employees access hr.employees, they can only view their own record:

SELECT name FROM hr.employees;

NAME
--------
Marvin
1 row selected.

When they access the view hr.employees_view, they can view all the rows as the base table is accessed as the view owner hr:

SELECT name FROM hr.employees_view;

NAME
--------
Marvin
John
Daniel
3 rows selected.

When USE DATA GRANTS ONLY is enabled, employees only see their own row regardless of how they access the table (directly or through a view), or what database privileges they are granted. Once enabled, data privileges are always checked and enforced regardless of the mode of access. The following example illustrates this:

SET USE DATA GRANTS ONLY ON hr.employees ENABLED;

SELECT name FROM hr.employees;

NAME
--------
Marvin
1 row selected.

SELECT name FROM hr.employees_view;

NAME
--------
Marvin
1 row selected.

Example 2

The following example illustrates this scenario:

The view hr.employees_personal_info is defined on view hr.employees_view which is defined on hr.employees base table.

If USE DATA GRANTS ONLY is enabled for hr.employees_view, then the Deep Sec user will need a data grant or object privilege on hr.employees_view_personal_info and data grant on hr_employees_view. hr.employees base table will be accessed as the view owner.

Create a view hr.employees_view in the hr schema:

CREATE VIEW hr.employees_view
  AS SELECT * FROM hr.employees;

Create a view hr.employees_personal_info in the hr schema on hr.employees_view:

CREATE VIEW hr.employees_personal_info
   AS SELECT name, phone_number, email
   FROM hr.employees_view;

Grant data privilege on hr.employees_personal_info:

CREATE OR REPLACE DATA GRANT
  app_admin.EmployeesPersonalInfoGrant AS
  SELECT
  ON hr.employees_personal_info
  WHERE name = ORA_END_USER_CONTEXT.username
  TO app_employee_role;

SET USE DATA GRANTS ONLY on hr.employees_view:

SET USE DATA GRANTS ONLY
  ON hr.employees_view ENABLED;

When employee queries hr.employees_personal_info, they do not see any data as they need the data grant on hr.employees_view as SET USE DATA GRANTS ONLY is enabled on hr.employees_view:

SELECT name FROM hr.employees_personal_info;

NAME
--------
0 rows selected.

Grant data privilege on hr.employees_view:

CREATE OR REPLACE DATA GRANT
  app_admin.EmployeesViewGrant AS
  SELECT (name, phone_number, email)
  ON hr.employees_view
  WHERE name = ORA_END_USER_CONTEXT.username
  TO app_employee_role;

Employee queries the view hr.employees_personal_info and can see their record:

NAME
--------
Marvin

1 row selected.