Oracle FGA

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

application context
fined-grained access control policy

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

Enforce row level access control through select, insert, update and delete statements
Create a security policy that controls access based on a certain value of a column
Create policies that are applied the same way always as well as policies that dynamically change during the execution of the query
Create sets of security policies called policy groups

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;
BEGIN
    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
begin
hr_context.select_emp_no;
end;

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

dynamic (default) - Each time a statement is parsed or executed the security policy function is executed afresh
static - The policy is executed once then stored in cache (SGA)
shared static - The same as static but is applied to multiple objects
context sensitive - The security policy can be modified based on changes in certain context attributes within a users session
shared context sensitive - The same a context sensitive but is applied to multiple objects

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

dbms_rls.add_policy - adds a policy to a table, view or synonym
dbms_rls.create_policy_group - creates a policy group
dbms_rls.add_policy_context - adds the context for the application

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 is 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'); BEGIN dbms_rls.add_policy (object_schema=> 'hr', object_name=> 'employees', policy_name=> 'manager_policy', function_schema => 'hr', policy_function => 'hr_security.empnum_sec', statement_types => 'select'); END;
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 dbms_rls.add_policy (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'); END;