Business requirement
User MILLER should only see records in the EMP table that match his name.
User ADAMS should only see records in the EMP table that match his name
User KING should be able to access all records in the EMP table
Solution Overview
Oracle Virtual Private Database (VPD) enables you to create security policies to control database access at the row and column level. Essentially, Oracle Virtual Private Database adds a dynamic WHERE clause to a SQL statement that is issued against the table.
For example, in this case based on the user who connects to the database, the dynamic SQL WHERE clause will be added to any query in the form of for example WHERE ENAME=’KING’ or WHERE ENAME=’MILLER’. This is VPD at work restricting access at the row level.
Overview of steps involved
- Create a Database Session-Based Application Context
- Create a PL/SQL Package to Set the Application Context
- Create a Logon Trigger to Run the Application Context PL/SQL Package
- Create a PL/SQL Policy Function to Limit User Access
- Create the New Security Policy and apply it to the required table
- Create a user that will own the security package and context – SYSADMIN_VPD
SQL> GRANT CREATE SESSION, CREATE ANY CONTEXT, CREATE PROCEDURE, CREATE TRIGGER, ADMINISTER DATABASE TRIGGER TO SYSADMIN_VPD IDENTIFIED BY sysadmin;
Grant succeeded.
SQL> GRANT EXECUTE ON DBMS_SESSION TO sysadmin_vpd;
Grant succeeded.
SQL> GRANT EXECUTE ON DBMS_RLS TO sysadmin_vpd;
Grant succeeded.
Create test users – MILLER, ADAMS and KING
SQL> GRANT CREATE SESSION TO MILLER IDENTIFIED BY miller;
Grant succeeded.
SQL> GRANT CREATE SESSION TO ADAMS IDENTIFIED BY adams;
Grant succeeded.
SQL> GRANT CREATE SESSION TO KING IDENTIFIED BY KING;
Grant succeeded.
Grant SELECT on the table EMP to the users
SQL> conn scott/tiger
Connected.
SQL> GRANT SELECT ON EMP TO MILLER,ADAMS, KING;
Grant succeeded.
As user SYSADMIN_VPD, create the application context and the package which will set the context
SQL> CONN SYSADMIN_VPD/SYSADMIN
Connected.
SQL> CREATE OR REPLACE CONTEXT EMP_CONTEXT USING EMP_CTX_PKG;
Context created.
SQL> CREATE OR REPLACE PACKAGE EMP_ctx_pkg IS
PROCEDURE set_ename;
END;
/
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY emp_ctx_pkg IS
PROCEDURE set_ename
AS
empname varchar2(20);
BEGIN
SELECT ename INTO empname FROM scott.emp
WHERE ename = SYS_CONTEXT(‘USERENV’, ‘SESSION_USER’);
DBMS_SESSION.SET_CONTEXT(‘ename_ctx’, ‘ename’, empname);
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
END set_ename;
END;
/
Package body created.
Create a LOGON trigger which will call the context package for each user
SQL> CREATE TRIGGER set_emp_context_trig AFTER LOGON ON DATABASE
BEGIN
sysadmin_vpd.emp_ctx_pkg.set_ename;
END;
/
Trigger created.
Create the security policies
SQL> CREATE OR REPLACE FUNCTION get_emp_info(
schema_p IN VARCHAR2,
table_p IN VARCHAR2)
RETURN VARCHAR2
AS
emp_predicate VARCHAR2 (400);
BEGIN
emp_predicate := ‘1=2’;
IF (SYS_CONTEXT(‘USERENV’,’SESSION_USER’) = ‘KING’) THEN
emp_predicate := NULL;
ELSE
emp_predicate := ‘ename = ””||SYS_CONTEXT(”USERENV”,”SESSION_USER”)||”” ‘;
END IF;
RETURN emp_predicate;
END;
/
Function created.
Apply the security policy to the table EMP
SQL> BEGIN
DBMS_RLS.ADD_POLICY (
object_schema => ‘scott’,
object_name => ’emp’,
policy_name => ‘ename_policy’,
function_schema => ‘sysadmin_vpd’,
policy_function => ‘get_emp_info’,
statement_types => ‘select’);
END;
/
PL/SQL procedure successfully completed.
Test VPD at work
Note the records that MILLER and ADAMS can view as compared to KING. They can oly access the rows that match their usename which is returned by the function used in the security packeg
SYS_CONTEXT(‘USERENV’, ‘SESSION_USER’)
SQL> conn miller/miller
Connected.
SQL> SELECT SYS_CONTEXT(‘USERENV’, ‘SESSION_USER’) from dual;
SYS_CONTEXT(‘USERENV’,’SESSION_USER’)
——————————————————————————–
MILLER
SQL> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7934 MILLER CLERK 7782 23-JAN-82 1300
10
SQL> conn adams/adams
Connected.
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7876 ADAMS CLERK 7788 23-MAY-87 1100
20
SQL> conn miller/miller
Connected.
SQL> /
COUNT(*)
———-
1
Note: User KING has access to all the records in the EMP table
SQL> conn king/KING
Connected.
SQL> /
COUNT(*)
———-
14
Gavin Soorma
