Loading....

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

Please follow and like us:

Last Update: June 18, 2009  

June 18, 2009 93 Gavin Soorma
Total 0 Votes:
0

Tell us how can we improve this post?

+ = Verify Human or Spambot ?

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*

Social media & sharing icons powered by UltimatelySocial
Back To Top

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?