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 |
Creating the application context | grant create any context to hr; create context employee_info using hr_context; create or replace trigger hr.security_context |
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 create or replace package body hr_security as |
Create the policy | execute dbms_rls.add_policy('hr', 'employee', 'manager_policy', 'hr', 'hr_security.empnum_sec', 'select'); BEGIN |
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; |