Example of using VPD with an Application Context

SQL> CREATE OR REPLACE PROCEDURE vpdtest_context_procedure(object_type IN VARCHAR2)                                                                                                           
  2  IS                                                                                                                                                                                       
  3  BEGIN                                                                                                                                                                                    
  4     DBMS_SESSION.SET_CONTEXT('vpdtest_context', 'object_type', object_type);                                                                                                              
  5  END;                                                                                                                                                                                     
  6  /                                                                                                                                                                                        
                                                                                                                                                                                              
Procedure created.                                                                                                                                                                            
                                                                                                                                                                                              
SQL>                                                                                                                                                                                          
SQL> CREATE CONTEXT vpdtest_context using vpdtest_context_procedure;                                                                                                                          
                                                                                                                                                                                              
Context created.                                                                                                                                                                              
                                                                                                                                                                                              
SQL>                                                                                                                                                                                          
SQL> CREATE TABLE vpdtest_table AS SELECT * FROM dba_objects;                                                                                                                                 
                                                                                                                                                                                              
Table created.                                                                                                                                                                                
                                                                                                                                                                                              
SQL>                                                                                                                                                                                          
SQL> BEGIN                                                                                                                                                                                    
  2     DBMS_RLS.ADD_POLICY(object_name => 'vpdtest_table',                                                                                                                                   
  3                         policy_name => 'vpdtest_policy',                                                                                                                                  
  4                         policy_function => 'vpdtest_policy_function');                                                                                                                    
  5  END;                                                                                                                                                                                     
  6  /                                                                                                                                                                                        
                                                                                                                                                                                              
PL/SQL procedure successfully completed.                                                                                                                                                      
                                                                                                                                                                                              
SQL>                                                                                                                                                                                          
SQL> CREATE OR REPLACE FUNCTION vpdtest_policy_function(schema VARCHAR2,                                                                                                                      
  2                                                     tab VARCHAR2) RETURN VARCHAR2                                                                                                         
  3  IS                                                                                                                                                                                       
  4  BEGIN                                                                                                                                                                                    
  5     RETURN 'OBJECT_TYPE = sys_context( ''vpdtest_context'', ''object_type'' )';                                                                                                           
  6  END;                                                                                                                                                                                     
  7  /                                                                                                                                                                                        
                                                                                                                                                                                              
Function created.                                                                                                                                                                             
                                                                                                                                                                                              
SQL>                                                                                                                                                                                          
SQL> EXEC vpdtest_context_procedure('TABLE');                                                                                                                                                 
                                                                                                                                                                                              
PL/SQL procedure successfully completed.                                                                                                                                                      
                                                                                                                                                                                              
SQL> SELECT DISTINCT object_type FROM vpdtest_table;                                                                                                                                          
                                                                                                                                                                                              
OBJECT_TYPE                                                                                                                                                                                   
-----------------------                                                                                                                                                                       
TABLE                                                                                                                                                                                         
                                                                                                                                                                                              
SQL>                                                                                                                                                                                          
SQL> EXEC vpdtest_context_procedure('VIEW');                                                                                                                                                  
                                                                                                                                                                                              
PL/SQL procedure successfully completed.                                                                                                                                                      
                                                                                                                                                                                              
SQL> SELECT DISTINCT object_type FROM vpdtest_table;                                                                                                                                          
                                                                                                                                                                                              
OBJECT_TYPE                                                                                                                                                                                   
-----------------------                                                                                                                                                                       
VIEW                                                                                                                                                                                          

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s