Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Oracle Virtual Private Database policy (VPD) tips

Oracle Tips by Burleson Consulting
August 25, 2015

In previous installments of this series on Oracle security, we examined Oracle grant security and grant execute security, noting the advantages and shortcomings of each approach. Now let's look at another Oracle security alternative, the virtual private database (VPD).


Using VPD policy security

Virtual private databases have several other names within the Oracle documentation, including row-level security (RLS) and fine-grained access control (FGAC). Regardless of the name, VPD security provides a whole new way to control access to Oracle data. Most interesting is the dynamic nature of a VPD. At runtime, Oracle performs these near magical feats by dynamically modifying the SQL statement of the end user:
 

  1. Oracle gathers application context information at user logon time and then calls the policy function, which returns a predicate. A predicate is a where clause that qualifies a particular set of rows within the table.
     
  2. Oracle dynamically rewrites the query by appending the predicate to users' SQL statements.


Whenever a query is run against the target tables, Oracle invokes the policy and produces a transient view with a where clause predicate pasted onto the end of the query, like so:

SELECT * FROM book WHERE P1


A VPD security model uses the Oracle dbms_rls package (RLS stands for row-level security) to implement the security policies and application contexts. This requires a policy that is defined to control access to tables and rows:.


VPDs are involved in the creation of a security policy, and when users access a table (or view) that has a security policy. The security policy modifies the user's SQL, adding a where clause to restrict access to specific rows within the target tables. Let's take a close look at how this works.

VPD security Application context

For the VPD to properly use the security policy to add the where clause to the end user's SQL, Oracle must know details about the authority of the user. This is done at sign-on time using Oracle's dbms_session package. At sign-on, a database logon trigger executes, setting the application context for the user by calling dbms_session.set_context. The set_context procedure can be used to set any number of variables about the end user, including the application name, the user's name, and specific row restriction information. Once this data is collected, the security policy will use this information to build the run-time where clause to append to the end user's SQL statement. The set_context procedure sets several parameters that are used by the VPD, and accepts three arguments:

dbms_session.set_context(namespace, attribute, value)

For example, let's assume that we have a publication table and we want to restrict access based on the type of end user. Managers will be able to view all books for their publishing company, while authors may only view their own books. Let's assume that user JSMITH is a manager and user MAULT is an author. At login time, the Oracle database logon trigger would generate the appropriate values and execute the statements shown in Listing A:

dbms_session.set_context('publishing_application', 'role_name', 'manager');

dbms_session.set_context('publishing_application', 'user_name', 'jsmith');

dbms_session.set_context('publishing_application', 'company', 'rampant_techpress');

dbms_session.set_context('publishing_application', 'role_name', 'author');

dbms_session.set_context('publishing_application', 'user_name', 'mault');

dbms_session.set_context('publishing_application', 'company', 'rampant_techpress');

Once executed, we can view these values with the Oracle session_context view. This data will be used by the VPD at runtime to generate the where clause. Note that each user has his or her own specific session_context values, shown in Listing B:

connect jsmith/manpass;


select
namespace, attribute, value
from
session_context;


NAMESPACE ATTRIBUTE VALUE
---------------- --------- ---------
PUBLISHING_APPLICATION ROLE_NAME MANAGER
PUBLISHING_APPLICATION USER_NAME JSMITH
PUBLISHING_APPLICATION COMPANY RAMPANT_TECHPRESS


connect mault/authpass;

select
namespace, attribute, value
from
session_context;


PUBLISHING_APPLICATION ROLE_NAME AUTHOR
PUBLISHING_APPLICATION USER_NAME MAULT
PUBLISHING_APPLICATION COMPANY   RAMPANT_TECHPRESS

Now let's see how this application context information is used by the VPD security policy. In Listing C, we create a security policy function called book_access_policy that builds two types of where clauses, depending on the information in the session_context for each end user. Note that Oracle uses the sys_context function to gather the values.

create or replace function
   book_access_policy
   (obj_schema varchar2, obj_name varchar2) return varchar2

 is                

 d_predicate varchar2(2000);

 begin              

 if sys_context('publishing_application','role_name')='manager' then
   d_predicate:=
      'upper(company)=sys_context(''publishing_application'',''company'')';

 else                  
   -- If the user_type session variable is set to anything else,
   -- display only this person's record                    --                  
   d_predicate:=
      'upper(author_name)=sys_context(''userenv'',''session_user'')';

 end if;       

 return d_predicate;       

 end;

 end; /


 DBMS_RLS.ADD_POLICY (
   'pubs',
   'book',
   'access_policy',
   'pubs',
   'book_access_policy',
   'select'
 );

Look at the code in Listing C carefully. If the user was defined as a manager, their where clause (d_predicate) would be:

where upper(company) = 'RAMPANT_TECHPRESS';

For the author, they get a different where clause:

where upper(author_name) = 'MAULT';


VPDs in action

We are now ready to show our VPD in action. In Listing D, we see very different results from an identical SQL query, depending on the application context of the specific end user.
 

connect jsmith/manpass;


 select * from book;



 Book                   Author                                  
 Title                  name          Publisher                 
 --------------------   ------------- --------------------      
 Oracle9i RAC           mault         Rampant Techpress         
 Oracle job Interview   dburleson     Rampant Techpress         
 Oracle Utilities       dmmoore       Rampant Techpress         
 Oracle Troubleshooting rschumacher   Rampant Techpress         
 Oracle10i DBA Features mault         Rampant Techpress         

 connect mault/authpass;

 select * from book;

It should be obvious that VPD is a totally different way of managing Oracle access than grant-based security mechanisms. There are many benefits to VPDs:

  • Dynamic security?No need to maintain complex roles and grants.
     
  • Multiple security?You can place more than one policy on each object, as well as stack them on other base policies. This makes VPD perfect for Web applications that are deployed for many companies.
     
  • No back doors?Users no longer bypass security policies embedded in applications, because the security policy is attached to the data.
     
  • Complex access rules may be defined?With VPD, you can use data values to specify complex access rules that would be difficult to create with grant security. You can easily restrict access to rows.

Of course, there are also some drawbacks to VPD security:

  • Difficult column level security?Because access is controlled by adding a where clause, column-level access can only be maintained by defining multiple views for each class of end user.
     
  • Requires Oracle IDs for every user?Unlike security that is managed externally, VPD requires that an Oracle user ID be defined for every person who connects to the database. This adds maintenance and overhead.
     
  • Hard to audit?It is hard to write an audit script that defines the exact access for each specified user. This problem becomes even more acute for shops that mix security methods.

Problems with mixing VPD and grant security

Now that we have established the areas of security and auditing, it should be clear that we must come up with a method to ensure that security methods are not mixed in an inappropriate way. By themselves, each of these security mechanisms provides adequate access protection, but when these methods are mixed, it can often be difficult (if not impossible) to identify the access for individual users. You'll have to decide whether the security benefits of VPD are worth the extra administrative method.

------------------------------------------------------------------------------------------------------------------------------

The Virtual Private Database (VPD) concept was first introduced in the Oracle version 8i and is one of the most common security features in the Oracle database evolution, serving as an interface between the PL/SQL functions and the database tables. The PL/SQL functions compute a WHERE condition predicate that is appended to the SQL statements which are executed on a database transparently, restricting the access to the rows and the columns within the queried table.

 

This feature is also called as Fine-Grained Access Control (FGAC) and has a remarkable difference between the data redaction process, which is discussed in the earlier section. While the data redaction concentrates only on the column level data security, the VPD can secure both row level and column level data.

 

The VPD creates a virtual private look of the database table to the users who are restricted to access only the portion of the data from the table that are authorized for them. Since a dynamic WHERE condition is appended to the SQL statements which are secured under the VPD implementation, the structure of the original SQL is modified by a change in its access path, which seems to appear in the explain plan. Thus, utmost care has to be taken while designing the predicate clause in a such a way that it does not take a big toll on the performance of the SQL statement being executed.

Advantages of VPD over FGA

Implementing VPD security policy to our database objects has the below advantages rather creating access controls all over our application.

 

1.      Simplicity: We can implement the VPD policy to a table, view, or synonym only once, rather repeating the implementation to all the database objects in our application.

 

2.      Security: By implementing the VPD policy, the security is placed on the objects at the database level and not at the object level. Thus, accidental modification of the data using ad-hoc tools is prohibited when the VPD is in place.

 

3.      Flexibility: The security policies can be created on a database column of a table or a view and can be applied to SELECT, INSERT, UPDATE, DELETE, and INDEX statements based on it. We can also have individual policies placed upon the above types of statements.

 

% Note: Oracle VPD policies do not support security over the DDL statements, such as TRUNCATE or ALTER.

Virtual Private Database Policy Components

To implement the VPD security in the Oracle database, we must first create a function to create a dynamic predicate clause and then a policy to combine this function and the objects we need to secure.

Function Definition and Implementation Rules

The first step in enforcing the security over the database object is to create a function (Procedures are not supported) defining the WHERE condition predicate that we want to implement.

 

The function created must satisfy the below behavior,

 

·         It must have two input parameters. One for the schema name and the other one for the object name with VARCHAR2 datatypes in their respective order mentioned. Even though these parameters are not needed by the function, those parameter values are supplied by the DBMS_RLS package during the policy creation.

 

·         It must have a VARCHAR2 return type, that returns the restriction condition to be placed in the WHERE predicate of a query.

 

% Note: The string WHERE must be avoided in the predicate return statement.

·         The WHERE clause predicate can either be simplistic (with a simple arithmetic operated predicate) or be complex (with a predicate containing an application context), but it should be a valid syntax.

 

·         The function body cannot select or transform the table on which the policy is about to be created.

VPD Policy Creation

After creating the function that defines the WHERE clause predicate, we must associate this function with the database object by creating a VPD policy.

 

We can create the policy using the procedures offered by the DBMS_RLS package (RLS stands for Row Level Security). During the policy creation, we can also configure the fine-grained access control, that is, the types of SQL statements and the columns the policy protects. The policy starts functioning once we try to access data from this database object.

 

The Procedures offered by the DBMS_RLS package for handling Individual Policies

Procedure Name

Description

ADD_POLICY

Creates/ Adds a policy to a database table, view, or a synonym.

ENABLE_POLICY

Enables/ Disables an existing policy.

ALTER_POLICY

Alters an existing policy to associate or disassociate its attributes.

REFRESH_POLICY

Re-parses all the cached statements associated with a policy.

DROP_POLICY

Drops an existing policy.

 

Procedures offered by the DBMS_RLS package for handling Grouped Policies

Procedure Name

Description

CREATE_POLICY_GROUP

Creates a policy group.

ALTER_POLICY_GROUP

Alters the group policy to associate or disassociate its attributes.

DELETE_POLICY_GROUP

Drops an existing policy group.

ADD_GROUPED_POLICY

Adds a policy to the specified policy group.

ENABLE_GROUPED_POLICY

Enables a policy within a group.

REFRESH_GROUPED_POLICY

Re-parses all the cached statements associated with a refreshed policy.

DISABLE_GROUPED_POLICY

Disables a policy within a group.

DROP_GROUPED_POLICY

Drops a policy from a group.

 

Procedures offered by the DBMS_RLS package for handling Application Contexts

Procedure Name

Description

ADD_POLICY_CONTEXT

Adds the context for the active application.

DROP_POLICY_CONTEXT

Drops the context for the application.

Oracle VPD Policy Types

There are totally 5 policy types provided by the Oracle Virtual Private Database to satisfy our needs such as for using them in the hosting environments.

 

Policy Type

Description

Usage Situation

Dynamic (Default)

This is the default type of policy type if the POLICY_TYPE parameter is omitted. The policy function is executed each and every time a VPD protected query is executed.

For example, in applications that use the time for restricting users to access the table data.

Static

The policy function is executed only once and is shared in the SGA for every other session to use.

This is useful when the predicate clause is common for all the queries executed on a VPD protected table. This results in better performance as the policy function is executed only once.

Shared_static

It is similar to the Static policy type, but this type can be shared among multiple schema objects.

This type is useful when we can re-use the same predicate to multiple schema objects. This is highly used in data warehousing environments.

Context_sensitive

The policy function is executed only when the associated application context attribute changes starting from R12.1. Prior to this release, the policy function is executed every time any attribute of any context is changed.

This policy type is used when we want to enforce more than predicate for different users or groups.

Shared_context_sensitivity

The predicate is shared in the UGA when an SQL statement is parsed, which then can be shared among different objects in a database.

This is mainly used in a data warehousing environment where we want to share a single predicate with multiple database objects.

Fine-grained Access Control Implementation Rules

We can create Oracle VPD policies for SELECT, INSERT, UPDATE, DELETE, and INDEX statements. To specify an SQL statement type for the policy, we must use the statement_types parameter in the ADD_POLICY procedure. If we want to specify more than one type of SQL statement, we must separate them with a comma enclosed with a single quotation. When we omit this parameter, by default Oracle specifies all the SQL statement types except INDEX.

 

% Note: The Database users, who were granted the EXEMPT ACCESS POLICY privilege, either directly or through a database role, are exempted from Oracle VPD enforcements.

We must consider the below rules before setting up the statement_types parameter.

 

·         When our application uses the MERGE statement, we must make sure that we set the statement_types parameter to INSERT, UPDATE, and DELETE or omit this parameter to set these statement types by default.

 

·         When the users have their statement_types parameter set to INDEX, they have the privilege to see the data from all rows, even if the other statement types are not set.

A Simple Oracle VPD Policy Creation

Before creating an Oracle VPD policy, let us examine the plan for the EMPLOYEES table.

 

EXPLAIN PLAN FOR SELECT * FROM employees;

 

The EXPLAIN PLAN FOR statement parses and records the formatted plan in the plan table that has been generated by Oracle for executing the query.

 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

 

The display function allows us to display the execution plan that is stored recently in the plan table.

Script Result:

PLAN_TABLE_OUTPUT

Plan hash value: 1445457117

 

-------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                       | Name               | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |                         |   107    |  7383 |     2   (0)        | 00:00:01 |

|   1 |  TABLE ACCESS FULL | EMPLOYEES  |   107   |  7383  |     2   (0)        | 00:00:01 |

-------------------------------------------------------------------------------------------------------------------------

 

In the above plan, we can see that all 107 rows of the EMPLOYEES table are returned from the query execution with a full table access.

 

The first step in creating an Oracle VPD is to create the policy function in a common user (In our case, say admin_user). Consider that all the employees have a database user created in their name (First_name||'_'||Last_name) for their purpose. For e.g., Alexander_Hunold is a username for a schema used by the employee- Alexander Hunold. Thus, the below function is created returning the department ID of the database user who calls it as its predicate statement. If the user accessing the EMPLOYEES table is not a valid employee, by default the predicate is returned as DEPARTMENT_ID is null, thus no employee details are displayed to them.

 

Note that even though the function does not need the schema name and the table name in its body functionality, we must create it with two input parameters of VARCHAR2 type as the VPD policy call expects the function like so.

 

CREATE OR REPLACE FUNCTION func_emp_vpd(

    ip_vc_schema_name IN VARCHAR2,

    ip_vc_table_name  IN VARCHAR2 )

  RETURN VARCHAR2

IS

  l_vc_return VARCHAR2(100);

BEGIN

  SELECT MAX('DEPARTMENT_ID = '||department_id)

  INTO

    l_vc_return

  FROM

    employees

  WHERE

    upper(first_name||'_'||last_name)=upper(USER);

  RETURN NVL(l_vc_return,'DEPARTMENT_ID is null');

END;

/

 

The Oracle VPD policy can be created using the ADD_POLICY procedure from the DBMS_RLS package. It requires the below parameters to be set for its creation,

 

·         OBJECT_SCHEMA parameter is for setting the schema of the object on which the policy is to be applied. In this case, a common user who administers the VPD object, ADMIN_USER is set for this parameter.

 

·         OBJECT_NAME parameter is for setting the object name on which the policy is to be applied. In this case, the table EMPLOYEES is set for this parameter.

 

·         POLICY_NAME parameter is for setting the user defined unique policy name for its creation and unique identification. In this case, the user-defined name POLICY_EMP is set for this parameter.

 

·         FUNCTION_SCHEMA parameter is for setting the schema name of the policy function created in the above section. In this case, a common user who administers the policy function, ADMIN_USER is set for this parameter.

 

·         POLICY_FUNCTION parameter is for setting the policy function created in the above section. In this case, the policy function FUNC_EMP_VPD is set for this parameter.

 

·         STATEMENT_TYPES parameter is for setting the fine-grained access control statement types. In this case, SELECT, INSERT, UPDATE, and DELETE statement types are set for this parameter. These statement types will be taken into consideration by default if we omit this parameter during the policy creation.

 

BEGIN

  DBMS_RLS.ADD_POLICY (

    object_schema    => 'admin_user',

    object_name      => 'employees',

    policy_name      => 'policy_emp',

    function_schema  => 'admin_user',

    policy_function  => 'func_emp_vpd',

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

   );

 END;

/

 

After creating the policy, the VPD goes into effect immediately. When we try to query the EMPLOYEES table, we get the results pertaining to the department that the querying database user is associated with.

 

For example,

 

1.      When we try to select the table data from the database user steven_king, we can see that the employees table returning all the employees corresponding to the employee King, Steven’s department ID (i.e., 90).

 

2.      When we try to select the table data from the database user nancy_greenberg, we can see that the employees table returning all the employees corresponding to the employee Nancy_Greenberg’s department ID (i.e., 100).

 

The below statement for retrieving the explain plan for the EMPLOYEES table is executed in the database user david_austin, whose department ID is 60.

 

EXPLAIN PLAN FOR SELECT * FROM employees;

 

The display function is then executed for displaying the stored plan details as shown below,

 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Script Result:

PLAN_TABLE_OUTPUT

Plan hash value: 223546141

 

--------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                          | Name               | Rows | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |                          |    10   |   690 |     2   (0)         | 00:00:01 |

|   1 | TABLE ACCESS BY

          INDEX ROWID                | EMPLOYEES   |    10   |   690 |     2   (0)         | 00:00:01 |

|*  2 |   INDEX RANGE SCAN   | IDX_DEPTNO   |    10   |          |     1   (0)         | 00:00:01 |

--------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

--------------------------------------------------------------------------------------------------------------------------

   2 - access("DEPARTMENT_ID"=60)

 

In the above plan, the policy function’s predicate information is available as a result of a VPD process to filter the data returned. In addition, Oracle optimizer also uses an index range scan on the predicate clause containing the index IDX_DEPTNO for increasing the performance of the query’s execution.

Column Level Oracle Virtual Private Database Policy

We can create a column-level Oracle VPD policy by setting up the SEC_RELEVANT_COLS parameter of the ADD_POLICY procedure in the DBMS_RLS package. Here, the security is applied only when the security relevant columns are accessed.

 

After creating the policy function for the Oracle VPD process (We can use the above-created policy function in this case), we must create the below policy with the parameter SEC_RELEVANT_COLS set to the columns EMAIL, PHONE_NUMBER, and SALARY from the EMPLOYEES table in a comma separated manner enclosed within single quotes.

 

BEGIN

  DBMS_RLS.ADD_POLICY (

    object_schema    => 'admin_user',

    object_name      => 'employees',

    policy_name      => 'policy_emp_column_level',

    function_schema  => 'admin_user',

    policy_function  => 'func_emp_vpd',

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

    sec_relevant_cols => 'email, phone_number, salary'

   );

 END;

/

 

When we query the EMPLOYEE table from the user Shelley_Higgins who is an employee in the department ID 110 as below,

 

SELECT employee_id, first_name, last_name, email, phone_number, salary, department_id

FROM

  employees;

 

The only rows that are displayed are the ones which the user has the privileges to access all columns in a row. Thus, we will be able to find all the employees belonging to the department ID 110 as shown below.

Script Result:

EMPLOYEE_ID

FIRST_NAME

LAST_NAME

EMAIL

PHONE_NUMBER

SALARY

DEPARTMENT_ID

205

Shelley

Higgins

SHIGGINS

515.123.8080

12008

110

206

William

Gietz

WGIETZ

515.123.8181

8300

110

 

But when we query the EMPLOYEE table omitting the security relevant columns from the selection list in any user as below,

 

SELECT employee_id, first_name, last_name, department_id

FROM

  employees;

 

We will be able to find all the rows in all the departments from the EMPLOYEE table without any security policy applied to them as below,

Script Result:

EMPLOYEE_ID

FIRST_NAME

LAST_NAME

DEPARTMENT_ID

200

Jennifer

Whalen

10

201

Michael

Hartstein

20

202

Pat

Fay

20

114

Den

Raphaely

30

                                                                                                                              

                                                                  

                                                                  

After creating the necessary application contexts, we must create the policy function with a dynamic predicate clause and the VPD policy for merging this function and the objects we intend to secure.

Creating the Policy Function

A policy function with two input VARCHAR2 parameters defining the secure object’s WHERE predicate is created below. Here, the database username is passed as the attribute parameter value to the SYS_CONTEXT function and its resulting department ID is returned as the WHERE clause predicate. When the SYS_CONTEXT function returns a null value, the Department_id is null predicate is returned by default. Thus, this policy function returns different department IDs for different users for which they have access to.

 

% Note: The application context namespace and the attribute name are case-insensitive when passed as input parameters to the SYS_CONTEXT function.

CREATE OR REPLACE FUNCTION func_emp_vpd(

    ip_vc_schema_name IN VARCHAR2,

    ip_vc_table_name  IN VARCHAR2 )

  RETURN VARCHAR2

IS

  l_n_dept_id NUMBER;

BEGIN

  SELECT

    sys_context('CONTEXT_VPD',user)

  INTO

    l_n_dept_id

  FROM

    dual;

  RETURN

  CASE

  WHEN l_n_dept_id IS NOT NULL THEN

    'Department_id = '||l_n_dept_id

  ELSE

    'Department_id is null'

  END;

END;

/

Creating the VPD Policy

After creating the policy function, VPD policy can be created using the ADD_POLICY procedure from the DBMS_RLS package as shown below.

 

BEGIN

  DBMS_RLS.ADD_POLICY (

    object_schema    => 'admin_user',

    object_name      => 'employees',

    policy_name      => 'policy_emp',

    function_schema  => 'admin_user',

    policy_function  => 'func_emp_vpd',

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

   );

 END;

/

 

Once the policy is created, when we try to access the EMPLOYEES table from the database user USER1 we tend to find all the employees belonging to the department ID- 10.

 

SELECT count(*) FROM employees;

 

Result:

 

1

 

Thus, when we try to access the EMPLOYEES table from the database user USER2, USER3, USER4, we tend to find all the employees belonging to the department IDs- 20, 30, 40 respectively.

Oracle VPD Data Dictionary Views

View Name

View Comments

*_POLICIES

Describes all the Oracle VPD security policies for objects.

*_POLICY_ATTRIBUTES

Describes all the application context namespaces, attributes, and the VPD policy associations

*_POLICY_CONTEXTS

Describes all the application contexts defined for the database objects.

*_POLICY_GROUPS

Describes all the Oracle VPD policy groups defined for the database objects.

*_SEC_RELEVANT_COLS

Describes the security relevant columns of the security policies for the database objects.

VPD Gotchas!

·         We should not use the FOR-UPDATE clause when querying VPD protected tables as the query might sometimes fail if they are too complex in nature.

 

·         We must rewrite our outer joins and ANSI operations in our SQLs as some views may not merge and some indexes may not be used. This problem is a known optimization limitation.

 

·         When we use flashback queries on a VPD protected object, then the current policies are applied to the old data, which may not result what we have expected.

 

·         We cannot apply VPD policies to the objects owned by the SYS user.

 

·         When we are exporting data using a direct path export operation, Oracle VPD policies are not enforced as the Oracle database reads data directly from the disk and not from the database where the policies are in effect.

 


Next in the series:
We've reviewed grant security, grant execute security, and VPD security. We're now ready to explore the auditing of Oracle security. Oracle offers several methods for auditing, including Oracle DDL and server error triggers, the Oracle audit SQL command, and Oracle's fine-grained auditing facility.
 
If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.