16.5 Enforce Mandatory Data Privileges

Use the SET USE DATA GRANTS ONLY command to enable mandatory access control (MAC) on a specific table or view.

By design, Oracle Deep Data Security (Deep Sec) applies data grants additively on top of standard database privileges. This means that a Deep Sec user who holds both a data grant (restricting access only to their row) and a direct database privilege (such as SELECT granted by the table owner) can bypass the row restriction by accessing the table through a view that runs with the view owner's privileges.

When you enable MAC on an object, Deep Sec users can access the object only through data grants, regardless of any database privileges they hold. This ensures consistent policy enforcement across all access paths, including views defined on the protected object.

See Also:

Mandatory Access Control in Part I: Oracle Deep Data Security Fundamentals.

Required privilege

• The SET USE DATA GRANTS ONLY system privilege (can be granted at the system level or for a specific schema).

Syntax

SET USE DATA GRANTS ONLY
    ON [schema.]object [ENABLED | DISABLED];

Parameters

Parameter	Description
schema.object	The table or view on which to enable or disable MAC.
ENABLED | DISABLED	The option that specifies whether USE DATA GRANTS ONLY should be enabled or disabled for the specified object. The default value is ENABLED if this option is omitted.

Usage notes and restrictions

• MAC enforcement is applicable only to Deep Sec users. Standard database users are not affected by this setting.
• If USE DATA GRANTS ONLY is enabled for an object and no data grants exist for that object, Deep Sec users cannot access the object at all.
• MAC propagates through view hierarchies. If USE DATA GRANTS ONLY is enabled on an intermediate view (not the base table), a Deep Sec user accessing an outer view that builds on it requires a data grant on the intermediate view as well.
• MAC is enforced regardless of whether the user accesses the object directly or through a view, preventing access-path bypass.

Example 16-16 Enable MAC on a base table

Consider a scenario where the employee_role data role has been granted unrestricted access to hr.employees_view (a view over hr.employees), but is restricted to own-record access on the base table hr.employees. Without MAC, Emma (an employee) can see all the rows by querying through the view.

-- Grant broad access to the view
CREATE OR REPLACE DATA GRANT hr.employees_view_grant
    AS SELECT
    ON hr.employees_view
    TO employee_role;
 
-- Grant restricted access to the base table (own record only)
CREATE OR REPLACE DATA GRANT hr.employees_own_record
    AS SELECT
    ON hr.employees
    WHERE email = ORA_END_USER_CONTEXT.username
    TO employee_role;

-- Without MAC, Emma sees 1 row via the table but all rows via the view.
EMMA> SELECT first_name FROM hr.employees;       -- 1 row
EMMA> SELECT first_name FROM hr.employees_view;   -- 5 rows

Enable MAC on hr.employees to enforce consistent access.

SET USE DATA GRANTS ONLY ON hr.employees ENABLED;

-- With MAC enabled, Emma now sees only her own row through both paths:
EMMA> SELECT first_name FROM hr.employees;
 
FIRST_NAME
----------
Emma

1 row selected.


EMMA> SELECT first_name FROM hr.employees_view;
 
FIRST_NAME
----------
Emma
 
1 row selected.

Example 16-17 Enable MAC on an intermediate view

Consider a view hr.employees_personal_info defined on hr.employees_view, which is itself defined on hr.employees. If MAC is enabled on hr.employees_view (not the base table), then a Deep Sec user accessing hr.employees_personal_info requires a data grant on both hr.employees_personal_info and hr.employees_view.

-- Create view hierarchy
CREATE VIEW hr.employees_view AS
    SELECT * FROM hr.employees;
 
CREATE VIEW hr.employees_personal_info AS
    SELECT first_name, last_name, phone, email FROM hr.employees_view;
 
-- Grant access to the outer view
CREATE OR REPLACE DATA GRANT hr.employees_personal_info_grant
    AS SELECT
    ON hr.employees_personal_info
    WHERE email = ORA_END_USER_CONTEXT.username
    TO employee_role;
 
-- Enable MAC on the intermediate view
SET USE DATA GRANTS ONLY ON hr.employees_view ENABLED;
 
-- End user queries the employees_personal_info view but sees no data
-- A data grant on hr.employees_view is also required
SELECT first_name FROM hr.employees_personal_info;
 
FIRST_NAME
----------
0 rows selected.
 
-- Grant access to the intermediate view as well:
CREATE OR REPLACE DATA GRANT hr.employees_view_grant
    AS SELECT (first_name, last_name, phone, email)
    ON hr.employees_view
    WHERE email = ORA_END_USER_CONTEXT.username
    TO employee_role;
 
-- End user can now access their own row
SELECT first_name FROM hr.employees_personal_info;
 
FIRST_NAME
----------
Emma
 
1 row selected.

Example 16-18 Disable MAC on a base table

SET USE DATA GRANTS ONLY ON hr.employees DISABLED;