Logo

Navigation
  • Home
  • Services
    • ERP Solutions
    • Implementation Solutions
    • Support and Maintenance Solutions
    • Custom Solutions
    • Upgrade Solutions
    • Training and Mentoring
    • Web Solutions
    • Production Support
    • Architecture Designing
    • Independent Validation and Testing Services
    • Infrastructure Management
  • Expertise
    • Microsoft Development Expertise
    • Mobile Development
    • SQL Server Database and BI
    • SAP BI, SAP Hana, SAP BO
    • Oracle and BI
    • Oracle RAC
  • Technical Training
    • Learn Data Management
      • Business Intelligence
      • Data Mining
      • Data Modeling
      • Data Warehousing
      • Disaster Recovery
    • Learn Concepts
      • Application Development
      • Client Server
      • Cloud Computing Tutorials
      • Cluster Computing
      • CRM Tutorial
      • EDI Tutorials
      • ERP Tutorials
      • NLP
      • OOPS
      • Concepts
      • SOA Tutorial
      • Supply Chain
      • Technology Trends
      • UML
      • Virtualization
      • Web 2.0
    • Learn Java
      • JavaScript Tutorial
      • JSP Tutorials
      • J2EE
    • Learn Microsoft
      • MSAS
      • ASP.NET
      • ASP.NET 2.0
      • C Sharp
      • MS Project Training
      • Silverlight
      • SQL Server 2005
      • VB.NET 2005
    • Learn Networking
      • Networking
      • Wireless
    • Learn Oracle
      • Oracle 10g
      • PL/SQL
      • Oracle 11g Tutorials
      • Oracle 9i
      • Oracle Apps
    • Learn Programming
      • Ajax Tutorial
      • C Language
      • C++ Tutorials
      • CSS Tutorial
      • CSS3 Tutorial
      • JavaScript Tutorial
      • jQuery Tutorial
      • MainFrame
      • PHP Tutorial
      • VBScript Tutorial
      • XML Tutorial
    • Learn Software Testing
      • Software Testing Types
      • SQA
      • Testing
  • Career Training
    • Career Improvement
      • Career Articles
      • Certification Articles
      • Conflict Management
      • Core Skills
      • Decision Making
      • Entrepreneurship
      • Goal Setting
      • Life Skills
      • Performance Development
      • Personal Excellence
      • Personality Development
      • Problem Solving
      • Relationship Management
      • Self Confidence
      • Self Supervision
      • Social Networking
      • Strategic Planning
      • Time Management
    • Education Help
      • Career Tracks
      • Essay Writing
      • Internship Tips
      • Online Education
      • Scholarships
      • Student Loans
    • Managerial Skills
      • Business Communication
      • Business Networking
      • Facilitator Skills
      • Managing Change
      • Marketing Management
      • Meeting Management
      • Process Management
      • Project Management
      • Project Management Life Cycle
      • Project Management Process
      • Project Risk Management
      • Relationship Management
      • Task Management
      • Team Building
      • Virtual Team Management
    • Essential Life Skills
      • Anger Management
      • Anxiety Management
      • Attitude Development
      • Coaching and Mentoring
      • Emotional Intelligence
      • Stress Management
      • Positive Thinking
    • Communication Skills
      • Conversation Skills
      • Cross Culture Competence
      • English Vocabulary
      • Listening Skills
      • Public Speaking Skills
      • Questioning Skills
    • Soft Skills
      • Assertive Skills
      • Influence Skills
      • Leadership Skills
      • Memory Skills
      • People Skills
      • Presentation Skills
    • Finding a Job
      • Etiquette Tips
      • Group Discussions
      • HR Interviews
      • Interview Notes
      • Job Search Tips
      • Resume Tips
      • Sample Resumes
 

Oracle VPD implementation

By Saurabh Gupta | on October 24, 2011 |
Oracle 11g Tutorials

1. What is Oracle Virtual Private Database(VPD)

Oracle has provided several solutions to ensure and secure the database. An efficient security policy not only prevents the database against the database corruption or hacking threats but also minimizes the risk of unprivileged user access. Several data security surveys have concluded that unauthorized data access to unprivileged users is a frequent severe threat to the data. Simple password settings, DBA roles and grants or user accounts are just not enough to monitor the data security of an organization.

The tutorial discusses one of the security methods which allow a user to see only the privileged data i.e. an explicit policy will restrict the user data access. The security feature is known as Virtual Private Database.

2. Virtual Private Database: The Need

Quite often, we encounter a traditional database design which follows the protocol of single Master server with multiple user accounts to access it.

For instance, a table designed to hold the data for different sub-entities, each one of them differentiated by a key column. An employee table can hold the employees data flowing from all locations. Table structure and sample data can be as below.

Now, a user from Australia (AUS) must have the privilege to view or modify employee details working in AUS only. Several options tend to achieve this problem

  • Local servers must maintain only local data
  • For Single master servers, one table per location must exist. Alternatively, separate views for each location can also be created.
  • For single Master servers, each query must be filtered (WHERE clause) based on ‘Location’.

The third option seems most straightforward, basic oriented and self explanatory, but still runs the risk of data authorization. But it comes out with certain potential problems like code development, maintenance overhead, and most importantly The Protocol follow up; no surety of how soon the protocol would vanish in large applications.

Therefore, Oracle extended the same concept to create Virtual Private Database for each location, where a local user would be able to view the data from its respective VPD only.

From the above table, three abstract VPDs can be easily extracted. First VPD would hold employee data from GER, second one from AUS and third one from RUS.

3. Virtual Private Database: The understanding and implementation

VPD works with the help of security policies and context to ensure the fine grained control on database objects. Virtual Private database policy dynamically appends the WHERE clause in SELECT queries and DML operations on the target table or view or synonyms.

It enables the creation and setting of security policies, which ensure authorized data access at row and column level. Note that VPD actions and objectives are limited up to data access operations (query and DMLs) only and does not supports data definitions (DDL).

How it works?

Whenever a user tries to access a table, the VPD policy on the table is activated. The figure below shows the process of VPD policy evaluation and query execution.

So, if a user from AUS queries the EMPLOYEES table as

SELECT * FROM EMPLOYEES

The VPD policy function appends the LOCATION filter and the server rewrites the above query as

SELECT * FROM EMPLOYEES WHERE LOCATION=’AUS’

Since the VPD policy execution is dynamic, users cannot view the data from other locations.

 4. VPD implementation

Implementation of VPD in database involves the below steps, which we shall discuss in detail thereafter.

  1. Create a Policy Function
  2. Attach the Policy function to the Table

4.1. Creating a Policy function

A Policy is a condition to filter the target data, which appears as virtual database for the authorized user. During its evaluation, the server executes and prepares the predicate i.e. the WHERE clause. It might be a performance hit since an extra amount of time is involved in the query execution. This cause branches a Policy into two categories, Static Policy and Dynamic Policy.

Static policies are the one which are evaluated only once for each query. In the first execution, the policy result is cached in SGA. Thus, for the subsequent executions of the same query, the cached policy function result is used.

Dynamic Policies are evaluated each time it is invoked.

4.2. Usage Notes

The Policy function must be created with definer’s rights, not with the Invoker’s rights.

  • Policy functions can either hardcode the predicate condition (static) or use context attributes to define a predicate (dynamic).
  • Multiple policies can be imposed on a table

4.3. Creating a Policy

Once the policy function is associated with a database object (Table, views or synonyms), the security policy creation process is completed. A policy handling is done using DBMS_RLS package subprograms. The package serves to achieve all the objectives in regards to row and column level security in database tables, view or synonyms. The DBA must grant EXECUTE privilege on the DBMS_RLS package to the user, which intends to implement VPD.

  • DBMS_RLS.ADD_POLICY – Adds a policy to a table, view, or synonym
  • DBMS_RLS.ENABLE_POLICY – Enables (or disables) a policy you previously added to a table, view, or synonym
  • DBMS_RLS.REFRESH_POLICY – Invalidates cursors associated with non static policies
  • DBMS_RLS.DROP_POLICY – To drop a policy from a table, view, or synonym

4.4. Illustration

4.4.1. In the master server (which can be accessed globally), EMPLOYEES table contains employee details (NAME, JOB and LOCATION) from various locations. A user from each location is able to see the details of the EMPLOYEES table.

SQL> SELECT * FROM EMPLOYEES;
 
EMPNAME    EMPJOB     LOCAT
---------- ---------- -----
John       DEV        GER
Kate       ADM        GER
Miller     HR         AUS
Andy       DEV        AUS
Mac        DEV        AUS
Dan        ADM        RUS
Philip     HR         RUS
 
7 ROWS selected.

The SCOTT user creates a policy function, which returns a condition to filter the LOCATION by AUS.

CREATE OR REPLACE FUNCTION F_LOC_POLICY (p_schema varchar2, p_obj varchar2)
RETURN VARCHAR2
IS
BEGIN
  RETURN 'LOCATION=''AUS''';
END;
/
 
FUNCTION created.

The above policy function can be used to create a Policy EMPLOCATION using DBMS_RLS package. Note the SQL statement type parameter to specify the VPD protected statements.

BEGIN
dbms_rls.add_policy 
(object_schema => ‘SCOTT’,
object_name => ‘EMPLOYEES’,
policy_name => ‘EMPLOCATION’,
function_schema => ‘SCOTT’,
policy_function => ‘F_LOC_POLICY’,
statement_types => ‘SELECT, UPDATE, INSERT’,
update_check => TRUE );
END;
/

Query the EMPLOYEES table to get the employee which belong to AUS only

SQL> SELECT * FROM EMPLOYEES;
 
EMPNAME    EMPJOB     LOCAT
---------- ---------- -----
Miller     HR         AUS
Andy       DEV        AUS
Mac        DEV        AUS

5. Policy Function using Context attributes

The above Policy function uses hardcoded information to prepare the predicate. In large and extending application, it might violate the application standards. Since the predicate information is set for the user, it can make use of the persistent session variables. Let us catch up the Policy function using Contexts.

5.1. Context

Oracle defines certain attributes in key-value format under a label, which reveal relevant information about the user in a session. The label is known as ‘Namespace’, while key-value pair is a ‘Name-Value’ set. They are stored in a secure data cache of UGA (user Global Area) for session persistence and faster access in applications. A context can be created, modified and deleted.

Since the Policy predicate is a deemed feature of the user in a session, it can be realized as a context attribute.

5.1.1. Creation of a Context

The Context creation requires a trusted package, which has to be created by the user.

CONN SCOTT/TIGER
Connected.
 
SQL> CREATE OR REPLACE PACKAGE PKG_CONTEXT IS
  2  PROCEDURE P_SET_CONT (P_VAL VARCHAR2);
  3  END;
  4  /
 
Package created.

The package takes a value as input and assigns it to LOCATION attribute under LOC_CONTEXT namespace. The context shall be created using this namespace only.

SQL>  CREATE OR REPLACE PACKAGE BODY PKG_CONTEXT IS
  2    PROCEDURE P_SET_CONT (P_VAL VARCHAR2)
  3    IS
  4    BEGIN
  5       dbms_session.set_context(namespace => 'LOC_CONTEXT',
                                   attribute => 'LOCATION' ,
                                   VALUE => P_VAL);
  6    END;
  7   END;
  8  /
 
Package body created.

A DBA can create the context with the user’s trusted package

SQLPLUS / AS SYSDBA
Connected.
 
SQL> CREATE CONTEXT LOC_CONTEXT USING SCOTT.PKG_CONTEXT;
 
Context created.

5.1.2. Verify the Context Setting

CONN SCOTT/TIGER
Connected

Explicitly set the context by executing the Package PKG_CONTEXT

SQL> EXEC PKG_CONTEXT.P_SET_CONT;
 
PL/SQL PROCEDURE successfully completed.

Query the LOCATION attribute of namespace LOC_CONTEXT using SYS_CONTEXT function.

SQL>  SELECT SYS_CONTEXT('LOC_CONTEXT','LOCATION') FROM DUAL;
 
SYS_CONTEXT('LOC_CONTEXT','LOCATION')
-----------------------------------------
AUS

5.1.3. Re define the Policy Function

The policy function has to be modified to remove hardcoded predicate clause and include Context.

CREATE OR REPLACE FUNCTION F_LOC_POLICY (p_schema varchar2, p_obj varchar2)
RETURN VARCHAR2
IS
BEGIN
  RETURN 'LOCATION='||'sys_context(''LOC_CONTEXT'',''LOCATION'')';
END;
 
FUNCTION created.

5.1.4. Re associate the policy with the EMPLOYEES table

BEGIN
dbms_rls.add_policy 
(object_schema => ‘SCOTT’,
object_name => ‘EMPLOYEES’,
policy_name => ‘EMPLOCATION’,
function_schema => ‘SCOTT’,
policy_function => ‘F_LOC_POLICY’,
statement_types => ‘SELECT, UPDATE, INSERT’,
update_check => TRUE );
END;

5.1.5. Verify the VPD policy by querying the table

SQL> SELECT * FROM EMPLOYEES;
 
EMPNAME    EMPJOB     LOCAT
---------- ---------- -----
Miller     HR         AUS
Andy       DEV        AUS
Mac        DEV        AUS

5.1.6. Create a LOGON trigger to automate the Context setting process for a user

To avoid explicit context setting, usually, DBAs set the context as soon as a user logs in to the database. This can be done by creating a LOGON trigger and invoking the context setting program.

SQLPLUS / AS SYSDBA
Connected.
 
CREATE OR REPLACE TRIGGER ON_LOGON
AFTER LOGON
ON DATABASE
BEGIN
IF USER='SCOTT' THEN 
 SCOTT.PKG_CONTEXT.P_SET_CONT('AUS');
END IF;
END;
/
 
TRIGGER created.

5.1.7. Verify the working of the LOGON trigger

Disconnect and re-log into the database to observe the VPD effect

CONN SCOTT/TIGER
Connected.
 
SQL> SELECT * FROM employees;
 
EMPNAME              EMPJOB               LOCAT
-------------------- -------------------- -----
Miller               HR                   AUS
Andy                 DEV                  AUS
Mac                  DEV                  AUS

5.1.8. Modify the LOGON trigger definition to alter the context

For verification, I shall just change the context setting call during the LOGON trigger

SQLPLUS / AS SYSDBA
Connected.
 
CREATE OR REPLACE TRIGGER ON_LOGON
AFTER LOGON
ON DATABASE
BEGIN
IF USER='SCOTT' THEN 
 SCOTT.PKG_CONTEXT.P_SET_CONT(‘GER’);
END IF;
END;
 
TRIGGER created.
SQL> SELECT * FROM employees;
 
EMPNAME              EMPJOB               LOCAT
-------------------- -------------------- -----
John                 DEV                  GER
Kate                 ADM                  GER

6. Column Level VPD

Similar to row level enforcement, VPD can be enforced at the column level also. It behaves in the same way but only when the specified columns are selected. The policy would not be activated if the specified columns are not queried or included in the SELECT statement.

The column specification has to be made in the DBMS_RLS package at the same time when policy function is aligned with the database table. The parameter ‘sec_relevant_cols’ accepts the list of columns to be protected under the Policy.

6.1. Define the policy EMP_COL_LOCATION. Note that LOCATION column has been secured with the policy F_LOC_POLICY against SELECT and DMLs.

BEGIN
DBMS_RLS.add_policy 
(object_schema => 'SCOTT',
OBJECT_NAME => 'EMPLOYEES',
policy_name => 'EMP_COL_LOCATION',
function_schema => 'SCOTT',
POLICY_FUNCTION => 'F_LOC_POLICY',
STATEMENT_TYPES => 'SELECT, UPDATE, INSERT',
sec_relevant_cols => 'LOCATION' );
END;

6.2. Query the EMPLOYEES table without ‘*’ or LOCATION as a selectable

SQL> SELECT EMPNAME FROM employees;
 
EMPNAME
--------------------
John
Kate
Miller
Andy
Mac
Dan
Philip
 
7 ROWS selected.

6.3. Now select employee name along with their location. The query will be VPD protected.

SQL> SELECT EMPNAME, LOCATION FROM employees;
 
EMPNAME              LOCAT
-------------------- -----
John                 GER
Kate                 GER

7. Policy Metadata

The below data dictionary views hold the necessary information about the policies enforced upon the user.

  1. [ALL | USER]_POLICIES – The view contains the complete specification of a Policy. It holds the information of the policy function, protected SQL statements, and policy type information.
  2. [ALL | USER]_POLICY_GROUPS – Maintains the association of a Policy group with the object.
  3. [ALL | USER]_POLICY_CONTEXTS – Maintains the association of a Context with an object.

The DBA maintains the view V$VPD_POLICY to store the information about the policy function and its owner details.

8. Conclusion

The tutorial lists the VPD implementation in Row and Column level. Besides the individual policies, policy groups can also be declared with many more utilities. One of the most interesting facts is that the Explain Plan for the VPD protected queries shows the VPD predicate and also follows the indexing guidelines available for the query.

Hope the tutorial drives the readers to try their hands out in VPD implementation and use to impose application security.

« « Disaster Recovery and Business Continuity
Tips to find Job during Recession » »

Author Description

Avatar

Free Training

RSSSubscribe 392 Followers
  • Popular
  • Recent
  • Conditional compilation in Oracle PL/SQL

    June 28, 2011 - 0 Comment
  • Oracle Pragma

    July 6, 2011 - 0 Comment
  • Oracle 11g SecureFiles

    September 4, 2011 - 0 Comment
  • Oracle 11g Virtual Columns

    May 30, 2011 - 0 Comment
  • Manage Oracle dependencies

    August 16, 2011 - 0 Comment
  • Oracle 11g Invisible Index

    May 27, 2011 - 0 Comment
  • UTL_FILE

    August 10, 2011 - 0 Comment
  • Oracle 11g Collections

    June 2, 2011 - 0 Comment
  • Compound Triggers in Oracle 11g

    May 9, 2011 - 0 Comment
  • Oracle 11g Result Cache

    April 28, 2011 - 0 Comment
  • Oracle XML Storage

    June 19, 2012 - 0 Comment
  • Oracle Analytic Enhancements

    June 19, 2012 - 0 Comment
  • Oracle Partitioning Overview

    January 17, 2012 - 0 Comment
  • PL/Scope and PL/SQL Hierarchical Profiler

    November 6, 2011 - 0 Comment
  • Oracle 11g SecureFiles

    September 4, 2011 - 0 Comment
  • Manage Oracle dependencies

    August 16, 2011 - 0 Comment
  • UTL_FILE

    August 10, 2011 - 0 Comment
  • Oracle Pragma

    July 6, 2011 - 0 Comment
  • Conditional compilation in Oracle PL/SQL

    June 28, 2011 - 0 Comment
  • Oracle 11g Subprogram Overloading

    June 17, 2011 - 0 Comment

Exforsys e-Newsletter

ebook
 

Related Articles

  • Oracle XML Storage
  • Oracle Analytic Enhancements
  • Oracle Partitioning Overview
  • PL/Scope and PL/SQL Hierarchical Profiler
  • Oracle 11g SecureFiles

Latest Articles

  • Project Management Techniques
  • Product Development Best Practices
  • Importance of Quality Data Management
  • How to Maximize Quality Assurance
  • Utilizing Effective Quality Assurance Strategies
  • Sitemap
  • Privacy Policy
  • DMCA
  • Trademark Information
  • Contact Us
© 2023. All Rights Reserved.IT Training and Consulting
This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish.AcceptReject Read More
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT