Fine-Grained Data Access

Most security measures are too broad-based, either the result that you end up unnecessarily restricting users when your primary goal is to ensure that users can freely access information they need, Oracle provides a more fine-gained, lower-level data security technology. The technology can restrict a user to just viewing rows in a table for just the department they work for, all this is transparent to the user.

Oracle uses two mechanisms to enforce fined-grained security

Oracle uses virtual private database (VPD) to refer to the implementation of fine-grained access-control policies using application context.

You can use fine-grained access-control for the following policies

The row-level security is enforced by attaching a security policy directly to a database object, such as a table, view or a synonym, this method ensures that what ever way you access the object you cannot elude this row-level security. The way Oracle does this is that it rewrites the query to restrict the rows by appending a where cause at the end of the statement which limits the access.

To create a VPD you have to create what is known as an application context and then implement fine-grained access control to enforce the row-level security. There are two ways to implement, the first is to use a trigger:

Create a package to set the application context

create or replace package hr_context as procedure select_emp_no;

create or replace package body hr_context as
procedure select_emp_no is
empnum number;
    select employee_id into empnum from employees where
    upper(last_name) = sys_context('USERENV','SESSION_USER');
    dbms_session.set_context('employee_info', 'emp_num', empnum);
END select_emp_no;

Creating the application context

grant create any context to hr;

create context employee_info using hr_context;

create or replace trigger hr.security_context
after logon on database

The second way is you use FGDA, there are 5 types of security policies

You control the secruity polices by using the procedure of the dbms_rls package

Create the package

create or replace package hr_security as
function empnum_sec (a1 varchar2, a2 varchar2)
return varchar2;

create or replace package body hr_security as
function empnum_sec (a1 varchar2, a2 varchar2)
   return varchar2
      d_predicate:= ' employee_id = sys_context("employee_info", "emp_num") ';
      return d_predicate;
end empnum_sec;
end hr_security;

Create the policy

execute dbms_rls.add_policy('hr', 'employee', 'manager_policy', 'hr', 'hr_security.empnum_sec', 'select');

(object_schema=> 'hr',
object_name=> 'employees',
policy_name=> 'manager_policy',
function_schema => 'hr',
policy_function => 'hr_security.empnum_sec',
statement_types => 'select');

Display the policy select object_name, policy_name, sel, ins, upd, del, enable from all_policies;
make the secuirty accessible grant execute on hr_security to public;
Column level VPD BEGIN
(object_schema=> 'hr',
object_name=> 'employees',
policy_name=> 'manager_policy',
function_schema => 'hr',
policy_function => 'hr_security.empnum_sec',
statement_types => 'select,insert',
sec_relevant_cols => 'salary');