Use of the Virtual Private Database VPD in the Oracle handbook Series

Oracle handbookSeriesVirtual Private Database VPDThe usage is what we will introduce in this article. VPD, Oracle Virtual Private Database, that is, Oracle Virtual Private Database, refers to the application of some policies, this allows users to access only the data that is allowed to be accessed. The principle is relatively simple. Oracle automatically adds a Where clause for the statement submitted by the corresponding user according to the policy to control the data that the user can access and operate on.

First, we prepare several tables for demonstration and insert a small amount of test data:

 
 

  
  
CREATETABLEvpdsample_clothing(  
  
  
clothing_idNUMBER,  
  
  
typeVARCHAR2(30),  
  
  
brandVARCHAR2(30),  
  
  
descriptonVARCHAR2(100)  
  
  
);  
  
  
--  
  
  
INSERTINTOvpdsample_clothingVALUES(10002,'jacket','ABC','autumnstyle');  
  
  
INSERTINTOvpdsample_clothingVALUES(10003,'t-shirt','XYZ','summerstyle');  
  
  
commit;  
  
  
CREATETABLEvpdsample_books(  
  
  
book_idNUMBER,  
  
  
nameVARCHAR2(30),  
  
  
authorVARCHAR2(20)  
  
  
);  
  
  
--  
  
  
INSERTINTOvpdsample_booksVALUES(10005,'CountryDriving','PeterHessler');  
  
  
INSERTINTOvpdsample_booksVALUES(10006,'Lifewithoutlimits','NickVujicic');  
  
  
commit; 
 
 

The two tables above simulate a simple inventory. There are two types of items in the Library: clothing and books. Here we need to satisfy the uniqueness of the IDs in the two tables through sequence ).)

 
 

  
  
CREATETABLEvpdsample_users(  
  
  
user_nameVARCHAR2(20),  
  
  
user_privilegeNUMBER  
  
  
);  
  
  
--  
  
  
INSERTINTOvpdsample_usersVALUES('Jack',1);  
  
  
INSERTINTOvpdsample_usersVALUES('Rose',2);  
  
  
COMMIT; 
 
 

This table stores user permission information. The permission is the permission ID field in the vpdsample_privileges table .)

This table stores the permission information corresponding to each permission ID, that is, the permission on the object clothing or books .)

Step 2: create a context that is actually a context namespace ]). You can simply understand context as a container defined in the memory. In this container, we can define several key-value pairs, these key-value pairs can be shared within a certain range, such as in the same session or in the same Oracle instance)

First, use the system user to log on and grant the Creator the corresponding permission: grant create any context to user1; then use: create or replace context vpd using pkg_vpdsample accessed globally; here we have created a context named 'vp', followed by a PLSQL package name. For security reasons, Oracle requires you to specify a package name when creating the context, it indicates that subsequent modifications to some context can only be made through the stored procedure in this package, but cannot be directly modified through dbms_session.set_context. When the context is created, the package does not exist and does not cause compilation errors.

The last 'accesssed globally 'is an optional option. If this option is not added, the range of the context is a session. If this option is specified, the context can be shared across the database instance.

To delete a context, you must grant the following permissions:

 
 

  
  
grant drop any context to user1;  
  
  
drop context VPD; 
 
 

Step 3: Create a package named pkg_vpdsample above. The functions of each function and stored procedure in the package will be given one by one:

 
 

  
  
CREATEORREPLACEPACKAGEpkg_vpdsample  
  
  
IS  
  
  
PROCEDUREenable_vpd;  
  
  
PROCEDUREdisable_vpd;  
  
  
PROCEDUREset_context(p_user_nameINVARCHAR2);  
  
  
FUNCTIONgen_vpd_predicate(p_column_nameINVARCHAR2)RETURNVARCHAR2;  
  
  
FUNCTIONapply_vpd_clothing(p1invarchar2,p2invarchar2)RETURNVARCHAR2;  
  
  
FUNCTIONapply_vpd_books(p1invarchar2,p2invarchar2)RETURNVARCHAR2;  
  
  
END;  
  
  
 
  
  
CREATEORREPLACEPACKAGEBODYpkg_vpdsampleIS  
  
  
PROCEDUREenable_vpdIS  
  
  
BEGIN  
  
  
DBMS_SESSION.set_context(namespace=>'VPD',  
  
  
attribute=>'ENABLE',  
  
  
value=>'1');  
  
  
END;  
  
  
 
  
  
/*======================*/  
  
  
PROCEDUREdisable_vpdIS  
  
  
BEGIN  
  
  
DBMS_SESSION.set_context(namespace=>'VPD',  
  
  
attribute=>'ENABLE',  
  
  
value=>'0');  
  
  
END;  
  
  
 
  
  
/*======================*/  
  
  
PROCEDUREset_context(p_user_nameINVARCHAR2)IS  
  
  
l_privilegeVARCHAR2(10);  
  
  
BEGIN  
  
  
SELECTuser_privilege  
  
  
INTOl_privilege  
  
  
FROMvpdsample_users  
  
  
WHEREuser_name=p_user_name;  
  
  
DBMS_SESSION.set_identifier(client_id=>l_privilege);  
  
  
END;  
  
  
 
  
  
/*======================*/  
  
  
FUNCTIONgen_vpd_predicate(p_column_nameINVARCHAR2)RETURNVARCHAR2IS  
  
  
l_vpd_flagVARCHAR2(1);  
  
  
l_privilegeVARCHAR2(10);  
  
  
BEGIN  
  
  
l_vpd_flag:=NVL(SYS_CONTEXT('VPD','ENABLE'),'0');  
  
  
IFl_vpd_flag=0THEN 
  
  
RETURNNULL;  
  
  
ELSE  
  
  
l_privilege:=SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER');  
  
  
IFl_privilegeISNULLTHEN  
  
  
RETURN'1=2';  
  
  
ELSE  
  
  
RETURNp_column_name||'IN(SELECTobject_idFROMvpdsample_privilegesWHEREprivilege_id='||l_privilege||')';  
  
  
ENDIF;  
  
  
ENDIF;  
  
  
END;  
  
  
 
  
  
/*======================*/  
  
  
FUNCTIONapply_vpd_clothing(p1invarchar2,p2invarchar2)RETURNVARCHAR2IS  
  
  
BEGIN  
  
  
RETURNgen_vpd_predicate('clothing_id');  
  
  
END;  
  
  
 
  
  
/*======================*/  
  
  
FUNCTIONapply_vpd_books(p1invarchar2,p2invarchar2)RETURNVARCHAR2IS  
  
  
BEGIN  
  
  
RETURNgen_vpd_predicate('book_id');  
  
  
END;  
  
  
END; 
 
 

Enable_vpd, disable_vpd: These two stored procedures are used to set a custom attribute 'enable' under context vpd. Table 1 enables vpd, and Table 0 disables vpd, because accessed globally is specified when this context is created, these attributes can be accessed across sessions. The two stored procedures call dbms_session, so the corresponding permissions must be granted: grant execute on dbms_session to user1;

Set_context: the context is mentioned above, and dbms_session.set_context is used in enable_vpd and disable_vpd to set Custom Attributes. In addition to the custom context, oracle also provides a pre-defined context 'userenv', which contains attributes defined for intervention, such as 'client _ identifier '. We do not use set_context to set this attribute, instead, use dbms_session.set_identifier ().

In this example, the stored procedure finds the permission ID of the user based on the input user name, and saves this ID as client_identifier to the context 'userenv' for later use. In actual applications, this stored procedure should be called by external applications. Applications can call this stored procedure to write the context after login verification is complete.

Gen_vpd_predicate: generates a correct where clause based on the input field name. Here, we first determine whether vpd is enabled. If the vpd. enable attribute is not set, we also think that vpd is enabled. Then determine whether the userenv. client_identifier attribute has been set. If this attribute is not set, a where clause always false is returned to prevent users from viewing data. In the last generated statement, find the permission table vpdsample_privileges Based on the obtained client_identifier, that is, the user's permission ID.

Apply_vpd_clothing, apply_vpd_books: visible by name. These two functions are used in two different tables, because the clothing table and the fields used for permission verification in the book table have different names. In addition, you can see that the two functions that will be used for vpd have two seemingly useless parameters p1 and p2, which are the requirements of the vpd interface. The first one is used to receive the schema name, the second is used to receive table/view/synonym names. We define that the function must meet the interface requirements. Of course, you can have additional parameters besides p1 and p2.

After all preparations are complete, call the dbms_rls package provided by Oracle to apply the vpd policy. before calling the policy, grant the user the corresponding permissions: grant execute on dbms_rls to user1;

Policy_name can be customized.

ENABLE_POLICY () is used to enable or disable a vpd policy, but only one vpd policy on a table can be enabled or disabled at a time, to enable/disable the vpd policy for all tables at one time, you can follow the preceding procedure .) And set context: Then we query the vpdsample_clothing table, and only the clothing information with ID 10002 is returned. Similar to vpdsample_books, only the book information with ID 10005 is returned. Users can be changed. to delete a vpd policy, use:

 
 

  
  
begin  
  
  
dbms_rls.drop_policy(object_name=>'VPDSAMPLE_CLOTHING',policy_name=>'POL_CLOTHING');  
  
  
dbms_rls.drop_policy(object_name=>'VPDSAMPLE_BOOKS',policy_name=>'pol_books');  
  
  
end;  
  
  
 
  
  
begin  
  
  
pkg_vpdsample.set_context('Rose');  
  
  
end;  
  
  
 
  
  
begin  
  
  
pkg_vpdsample.set_context('Jack');  
  
  
end;  
  
  
 
  
  
begin  
  
  
pkg_vpdsample.enable_vpd;  
  
  
end; 
 
 

Here is an introduction to the Virtual Private Database VPD of the Oracle handbook series. I hope this introduction will be helpful to you!