In Oracle : Virtual Private Database (in Oracle 10g)

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

Oracle DBA

Real Application Cluster (RAC) Maintenance tasks Backup and Recovery Database Architecture Replication Oracle Performance Tuning Oracle DBA - Other articles Oracle scripts/ Selects for DBA Oracle Errors (ORA-nnnnn)

The last articles in the site (HOT)

The most visited articles in the site

In-Oracle.com  -> Oracle DBA

-> Virtual Private Database (in Oracle 10g)

 

 Virtual Private Database (in Oracle 10g)

 

 

1. What is the Virtual Private Database Feature (VPD) ?

2. What is a Column-level VPD ?

3. Implementing a Column-level VPD

4. What is the Column-level VPD with Column Masking Behavior ?

5. Implementing Column-level VPD with Column Masking Behavior

6. Policy Types

7. How to drop a Column-level VPD

1. What is the Virtual Private Database Feature ?

 

When a user directly or indirectly accesses a table, view, or synonym that is protected with a VPD policy, the server dynamically modifies the user's SQL statement. The modification is based on a WHERE condition (known as a predicate) returned by a function which implements the security policy.

 

SELECT * FROM book WHERE RULE_1;     The RULE_1 must be defined and can be context-sensitive.

 

The statement is modified dynamically, transparently to the user, using any condition which can be expressed in, or returned by a function. VPD policies can be applied to SELECT, INSERT, UPDATE, INDEX, and DELETE statements.

 

 

2. What is a Column-level VPD ?

 

Column-level VPD enables you to enforce row-level security when a security-relevant column is referenced in a query. You can apply column-level VPD to tables and views, but not to synonyms. By specifying the security-relevant column name with the sec_relevant_cols parameter of the DBMS_RLS.ADD_POLICY procedure, the security policy is applied whenever the column is referenced, explicitly or implicitly, in a query.

 

 

3. Implementing a Column-level VPD

  • Create the security function:

CREATE OR REPLACE function SCOTT.TEST_VPD (

         objowner in varchar2,

         objname in varchar2 ) return varchar2 is

 

  return_val varchar2(900);

 

begin

   return_val := 'deptno = 10';

   return return_val;

end;

  • Apply this security function to a specific table (SCOTT.EMP). Scott run this PL/SQL block and must have EXECUTE on DBMS_RLS package.

begin

   dbms_rls.add_policy (

       object_schema => 'SCOTT',

       object_name => 'EMP',

       policy_name => 'VPD_TEST_POLICY',

       function_schema => 'SCOTT',

       policy_function => 'TEST_VPD',

       statement_types => 'select, insert, update, delete',

       sec_relevant_cols => 'sal,comm');

end;

 

Test the rule:

Virtual Private Database

If there are any errors during the SELECT, INSERT, UPDATE, DELETE statement please check the last udump file.

 

 

4. What is the Column-level VPD with Column Masking Behavior ?

 

If a query references a security-relevant column, then the default behavior of column-level VPD restricts the number of rows returned. With column masking behavior, which can be enabled by using the sec_relevant_cols_opt parameter of the DBMS_RLS.ADD_POLICY procedure, all rows display, even those that reference security relevant columns. However, the sensitive columns display as NULL values.

 

 

5. Implementing Column-level VPD with Column Masking Behavior

  • Create the security function:

CREATE OR REPLACE function SCOTT.TEST_VPD (

         objowner in varchar2,

         objname in varchar2 ) return varchar2 is

 

  return_val varchar2(900);

 

begin

   return_val := 'deptno = 10';

   return return_val;

end;

  • Apply this security function to a specific table (SCOTT.EMP). Scott run this PL/SQL block and must have EXECUTE on DBMS_RLS package.

begin

  dbms_rls.add_policy (

       object_schema => 'SCOTT',

       object_name => 'EMP',

       policy_name => 'VPD_TEST_POLICY',

       function_schema => 'SCOTT',

       policy_function => 'TEST_VPD',

       statement_types => 'select',

       sec_relevant_cols => 'sal,comm',

       sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS );

end;

Test the rule:

 

Virtual Private Database

If there are any errors during the SELECT, INSERT, UPDATE, DELETE statement please check the last udump file.

 

 

6. Policy Types

 

The correct use of policy types can increase the performance of VPD by caching the output of the policy function and applying it to subsequent queries without executing the policy function again. The POLICY_TYPE parameter of the DBMS_RLS.ADD_POLICY procedure is used to set one of the five policy types:

  • STATIC - The return value of the policy function is cached and reused repeatedly for an individual object. By definition the return value of the policy function must be static.
  • SHARED_STATIC - The same as STATIC but the resulting predicate can be applied to several objects.
  • CONTEXT_SENSITIVE - Used when policy is based around local application context. The result of the policy function is cached and reused. The policy function is only executed again when the value of the application context changes.
  • SHARED_CONTEXT_SENSITIVE - The same as CONTEXT_SENSITIVE but the resulting predicate can be applied to several objects.
  • DYNAMIC - The policy function is executed for every SQL statement.

Example:

 

begin

  dbms_rls.add_policy (

     object_schema => 'SCOTT',

     object_name => 'EMP',

     policy_name => 'VPD_TEST_POLICY',

     policy_type => DBMS_RLS.STATIC,

     function_schema => 'SCOTT',

     policy_function => 'TEST_VPD',

     statement_types => 'select, insert, update, delete',

     sec_relevant_cols => 'sal,comm');

end;

 

7. How to drop a Column-level VPD

 

begin

  dbms_rls.drop_policy (

      object_schema => 'SCOTT',

      object_name => 'EMP',

      policy_name => 'VPD_TEST_POLICY');

end;

 

In-Oracle.com  -> Oracle DBA

-> Virtual Private Database (in Oracle 10g)

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

 

     Copyright (c) 2011-2015  www.in-oracle.com  |  Disclaimer: The views expressed on this web site are my own and do not reflect the views of Oracle Corporation. You may use the information from this site only at your risk.