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 


 

 

 


 

 

 
 

VPD Policy Creation using an Application Context

Oracle PL/SQL tips by Boobal Ganesan

This is an excerpt from the book Advanced PL/SQL: The Definitive Reference by Boobal Ganesan.

Application contexts are introduced in Oracle version 8i to support VPD policies to set the WHERE clause predicate when querying the policy protected tables. Later in 9i, the global application contexts were introduced to support shared-session applications.

 

The application contexts are session variables which hold information about the database and the session in the form of key-value pairs that are identified by labels called as namespaces.

 

 

Fig. 8.3 Application Context Internal Architecture

 

The above figure depicts the relationship between namespaces (also called as the application contexts), keys and their respective values. We can create n number of namespaces which in turn can have more than one key which points to their respective values. There is no relationship between the application contexts, keys or values except for the ones inside their branch.

 

Fig. 8.4 System Defined Application Context

 

In the above example, the system-defined context USERENV is portrayed along with a few of its keys and their values.

 

Oracle stores these application context values in a secure data cache that is available either in the User Global Area (In the case of local contexts) or in the Shared Global Area (In the case of global contexts). Thus, these values are available throughout the session when they are set once. As the application contexts are stored in the cache, the performance of our applications increases. The application context values can be retrieved through the system defined function SYS_CONTEXT. This function accepts two parameters. They are the context name (namespace) and its input parameter (key).

 

% Note: The function doesn’t validate the context name or its input parameter. If they are not available, the function simply returns null as its value. However, if we tend to use the context USERENV, supplying a wrong input parameter may result in ORA-02003: invalid USERENV parameter exception.

There are totally three general types of application contexts available in the Oracle database and they are,

 

·         Local Application Contexts: These contexts are available only in the creator’s session and these are stored in the UGA part of the memory.

 

·         Global Application Contexts: These contexts can be accessed by all session users. Thus, these are stored in the SGA part of the memory.

 

·         Client-Session Based Application Contexts: This context uses OCI (Oracle Call Interface) functions on the client side to set the context parameters to perform the necessary security check for restricting the user access. This type of context is also stored in the SGA memory.

Components for Creating an Application Context

In our case, we must create the global application contexts for implementing them with the Oracle VPD policies. We must use the below components to create and use the global application context.

 

·         We must create an application context using the CREATE CONTEXT syntax. We have to make sure that the ACCESSED GLOBALLY clause is used during its creation such that the context can be accessed globally across all the session instances.

 

·         We must create a trusted procedure to set the attribute and the value for the context. During the context creation in the above step, the validity and permission of this procedure are not tested by the system. The system trusts that this procedure exists with valid permissions and then creates the context, thus these procedures are coined as trusted.

 

·         After the trusted procedure is created, we must run it once, preferably during the user login such that the context values are set way before the user starts to access the database tables. We can do this by creating a database startup trigger to execute the trusted procedure automatically during a database startup.

Creating the Globally Accessible Context

The prototype for creating a context is shown below,

 

CREATE OR REPLACE CONTEXT <Context_name> USING [Schema_name].[Trusted_procedure_name]

[[Initialized [Externally | Globally]] | [Accessed Globally]];

 

·         Context_name is the user-defined unique name for identifying the context created.

 

·         Trusted_procedure_name is the name of the trusted procedure that sets or resets the context attributes under the context name. For design flexibility, the validity and the existence of this procedure are not verified during the context creation.

 

·         Initialized clause allows other entities to initialize this context.

 

è Externally clause indicates that this context can be initialized using an OCI to establish a session.

 

è Globally clause indicates that this context can be initialized from the LDAP directory when a global user connects to the database.

 

·         Accessed Globally clause indicates that the application contexts set in this namespace are accessible by multiple sessions.

 

The context CONTEXT_VPD is created upon the trusted procedure PROC_VPD, which is yet to be created. This context is available for all the users connected to this database because of the ACCESSED GLOBALLY clause.

 

CREATE OR REPLACE CONTEXT context_vpd USING proc_vpd ACCESSED GLOBALLY;

Creating the Trusted Procedure

After the global context is created, we must create the trusted procedure. This procedure must contain a call to the DBMS_SESSION.SET_CONTEXT procedure sporting three input parameters for setting the context attributes and its values. The first parameter accepts the name of the context created in the above setup, the second parameter accepts a user-defined attribute name, and the third parameter accepts a value for the attribute created. The context set by this procedure will be reset once the session is terminated, thus we don’t have to strain ourselves to reset them.

 

In the below code snippet, four attributes and their respective values are set in the CONTEXT_VPD application context. Here, the attributes are the database user IDs and their values are the department IDs for which the corresponding database user has access to.

 

CREATE OR REPLACE PROCEDURE proc_vpd

IS

BEGIN

  dbms_session.set_context('CONTEXT_VPD', 'USER1', 10);

  dbms_session.set_context('CONTEXT_VPD', 'USER2', 20);

  dbms_session.set_context('CONTEXT_VPD', 'USER3', 30);

  dbms_session.set_context('CONTEXT_VPD', 'USER4', 40);

END;

/

 

When we try to execute the system provided function SYS_CONTEXT in this context and attribute,

 

SELECT sys_context('CONTEXT_VPD','USER1') FROM dual;

 

Result:

 

(null)

 

It results null as we have not yet set by executing the trusted procedure at least once.

 

EXEC proc_vpd;

 

Thus, when we re-query the SYS_CONTEXT function after setting the attributes and the values by executing the trusted procedure,

 

SELECT sys_context('CONTEXT_VPD','USER1') FROM dual;

 

Result:

 

10

 

We can see that the values corresponding to the input attribute of the particular context are displayed.

Creating the Database Startup Trigger

% Note: If there is an error in the database trigger or in the trusted procedure, it may be impossible to start the database. In that case, we must disable the database trigger until the issue is fixed.

In the below listing, a database startup trigger named TRG_VPD has created which fires as soon as the database is started. This trigger’s body contains the trusted procedure call, which sets the context attributes and the values for the entire user session.

 

CREATE OR REPLACE TRIGGER trg_vpd AFTER STARTUP ON DATABASE

  BEGIN

    proc_vpd;

  END;

  /

 

Need to learn to program with PL/SQL?  For complete notes on programming in PL/SQL, we recommend the book Advanced PL/SQL: The Definitive Reference by Boobal Ganesan.

This is a complete book on PL/SQL with everything you need to know to write efficient and complex PL/SQL code.

   
Oracle Training from Don Burleson 

The best on site "Oracle training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!

Oracle training
 
 



 

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 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational

 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster