3.5 Configure Data Access Control

Define data grants to control which rows and columns each end user can access. In data grant predicates, ORA_END_USER_CONTEXT.username returns the end user's name from the current end-user security context.

Note:

To query ORA_END_USER_CONTEXT directly or to use it in data grant predicates, you must set the database instance's COMPATIBLE initialization parameter to 20.0 or greater.

1. Create a data grant to allow employees to view their own record.

   This data grant allows users with the employee_role data role to view only the row in hr.employees where the email column matches their user name.

   CREATE DATA GRANT hr.employees_own_record
     AS SELECT
     ON hr.employees
     WHERE email = ORA_END_USER_CONTEXT.username
     TO employee_role;
   

2. Create a data grant to allow managers to view their direct reports (with SSN excluded).

   This data grant allows users with the manager_role data role to view the rows in hr.employees where the manager column matches their user name. The ALL COLUMNS EXCEPT ssn clause excludes the social security number column, so it returns NULL for these rows.

   CREATE DATA GRANT hr.manager_direct_reports
     AS SELECT (ALL COLUMNS EXCEPT ssn)
     ON hr.employees
     WHERE manager = ORA_END_USER_CONTEXT.username
     TO manager_role;