Overview:
This blog talks about VPD (Virtual Private Database) in Oracle Apex, a feature that helps address security concerns by restricting access to data at the row level based on certain conditions.
Technologies and Tools Used:
The following technologies have been used to implement this requirement.
- SQL
- PL/SQL
- Oracle Apex
Use Case:
- Consider a corporation that utilizes Oracle Apex for its web-based apps. The organization has several departments, and employees from each need access to data in the program. However, sensitive information should only be accessible to specified people, depending on their positions or conditions.
- For example, the Human Resources department should only see employee records relating to their department, whereas the Finance department should only have access to financial data relevant to their role. Without sufficient security measures, all users may have uncontrolled access to all data, potentially breaching confidentiality and allowing unwanted access to critical information.
Architecture:
The following steps are explained in detail:
Step 1: Create a policy function in oracle database using PL/SQL
create or replace function get_emp_criteria
(
object_schema IN VARCHAR2,
object_name VARCHAR2
)
return varchar2 DETERMINISTIC as
l_criteria varchar2(4000);
begin
if sys_context(‘APEX$SESSION’, ‘APP_ID’) in (121) — Only apply VPD to specific APEX applications
then
l_criteria:=q’#(ENAME=sys_context(‘APEX$SESSION’, ‘APP_USER’))#’;
This will have current apex user who logged in.
end if;
return l_criteria;
end get_emp_criteria;
Step 2: Create a database policy that will use the above-created function.
begin
dbms_rls.add_policy
(object_schema=>’HR’ /*provide the schema name*/
,object_name=>’EMP’ /* provide your table name used in your application*/
,policy_name=>’EMP_POLICY’ /* Policy name*/
function_schema=>’HR’ /* schema name where your policy function was created*/
,policy_function=>’get_emp_criteria’ /* function name above created*/
statement_types=>’SELECT’ /* statement we use in our application*/
);
end;
Step 3: Enable the policy created:
BEGIN
DBMS_RLS.enable_POLICY(‘HR’, ‘EMP’, ‘EMP_POLICY’ , TRUE);
END;
Step 4: Create an apex application and a report page with the query as below.
Note that I am not using any filtering conditions; I am just querying my table.
Scenario:
If you want to restrict users to a particular location, you need to follow the steps below to achieve the same.
Step 1:Create a context in Oracle PL/SQL.
create or replace
context MYLOC using LOC_FN;
Step 2: Create a context function and a policy function to filter employee name and location details.
create or replace function LOC_FN
return NUMBER
is
L_LOC_ID NUMBER;
L_LOC_NAME VARCHAR2(100);
begin
select location_id
into L_LOC_ID
from
emp_demo
where ENAME=sys_context(‘APEX$SESSION’, ‘APP_USER’);
dbms_session.set_context(‘MYLOC’,’ATTR’,L_LOC_ID);/*Need privilege in your schema to execute the same.*/
return L_LOC_ID;
end;
CREATE OR REPLACE FUNCTION vpd_location_policy_function (
schema_var IN VARCHAR2,
table_var IN VARCHAR2
) RETURN VARCHAR2
AS
l_location_id NUMBER;
BEGIN
Retrieve the location ID from the custom context using the LOC_FN function
l_location_id := LOC_FN();
Return a predicate to filter records based on location_id
RETURN ‘location_id = ‘ || l_location_id;
EXCEPTION
WHEN OTHERS THEN
Handle exceptions, but do not return a value incompatible with the function’s return type
Raise_application_error(‘-20000’, sqlerrm);
RETURN NULL; return null in case of an exception
END vpd_location_policy_function;
Step 3: Create a policy in your table as per below code.
BEGIN
DBMS_RLS.ADD_POLICY(
object_schema => ‘hr’,/* Schema name*/
object_name => ’emp_demo’,/* Table name*/
policy_name => ’emp_demo_vpd_policy’,/* Policy name*/
function_schema => ‘hr’,/* policy function schema name*/
policy_function => ‘vpd_location_policy_function’,/* policy function name*/
statement_types =’select’ /* we can select,insert, update, and delete based on scenarios.
);
END;
Enable policy:
BEGIN
DBMS_RLS.enable_POLICY(‘HR’, ‘EMP_DEMO’, ’emp_demo_vpd_policy’, TRUE);
END;
Step 4:Create an Apex application and create a report page with the below query.
Outcome:
Here, I am logging in as Employee “king” ,I am only getting the details related to “king.”
The required outcome is given below.
Here, I am logged in as Employee “BLAKE” ,I am only getting the details related to “BLAKE.”
Here,I am selecting the employees according to location:10
Conclusion :
- By implementing VPD in Oracle Apex, the company can ensure that sensitive data is appropriately secured and that users have access only to the information that is relevant to their roles and responsibilities.
- This enhances data confidentiality and minimizes the risk of unauthorized access, contributing to a more secure and compliant application environment.
