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

Source: Internet
Author: User

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:

 
 
  1. CREATETABLEvpdsample_clothing(  
  2. clothing_idNUMBER,  
  3. typeVARCHAR2(30),  
  4. brandVARCHAR2(30),  
  5. descriptonVARCHAR2(100)  
  6. );  
  7. --  
  8. INSERTINTOvpdsample_clothingVALUES(10002,'jacket','ABC','autumnstyle');  
  9. INSERTINTOvpdsample_clothingVALUES(10003,'t-shirt','XYZ','summerstyle');  
  10. commit;  
  11. CREATETABLEvpdsample_books(  
  12. book_idNUMBER,  
  13. nameVARCHAR2(30),  
  14. authorVARCHAR2(20)  
  15. );  
  16. --  
  17. INSERTINTOvpdsample_booksVALUES(10005,'CountryDriving','PeterHessler');  
  18. INSERTINTOvpdsample_booksVALUES(10006,'Lifewithoutlimits','NickVujicic');  
  19. 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 ).)

 
 
  1. CREATETABLEvpdsample_users(  
  2. user_nameVARCHAR2(20),  
  3. user_privilegeNUMBER  
  4. );  
  5. --  
  6. INSERTINTOvpdsample_usersVALUES('Jack',1);  
  7. INSERTINTOvpdsample_usersVALUES('Rose',2);  
  8. 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:

 
 
  1. grant drop any context to user1;  
  2. 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:

 
 
  1. CREATEORREPLACEPACKAGEpkg_vpdsample  
  2. IS  
  3. PROCEDUREenable_vpd;  
  4. PROCEDUREdisable_vpd;  
  5. PROCEDUREset_context(p_user_nameINVARCHAR2);  
  6. FUNCTIONgen_vpd_predicate(p_column_nameINVARCHAR2)RETURNVARCHAR2;  
  7. FUNCTIONapply_vpd_clothing(p1invarchar2,p2invarchar2)RETURNVARCHAR2;  
  8. FUNCTIONapply_vpd_books(p1invarchar2,p2invarchar2)RETURNVARCHAR2;  
  9. END;  
  10.  
  11. CREATEORREPLACEPACKAGEBODYpkg_vpdsampleIS  
  12. PROCEDUREenable_vpdIS  
  13. BEGIN  
  14. DBMS_SESSION.set_context(namespace=>'VPD',  
  15. attribute=>'ENABLE',  
  16. value=>'1');  
  17. END;  
  18.  
  19. /*======================*/  
  20. PROCEDUREdisable_vpdIS  
  21. BEGIN  
  22. DBMS_SESSION.set_context(namespace=>'VPD',  
  23. attribute=>'ENABLE',  
  24. value=>'0');  
  25. END;  
  26.  
  27. /*======================*/  
  28. PROCEDUREset_context(p_user_nameINVARCHAR2)IS  
  29. l_privilegeVARCHAR2(10);  
  30. BEGIN  
  31. SELECTuser_privilege  
  32. INTOl_privilege  
  33. FROMvpdsample_users  
  34. WHEREuser_name=p_user_name;  
  35. DBMS_SESSION.set_identifier(client_id=>l_privilege);  
  36. END;  
  37.  
  38. /*======================*/  
  39. FUNCTIONgen_vpd_predicate(p_column_nameINVARCHAR2)RETURNVARCHAR2IS  
  40. l_vpd_flagVARCHAR2(1);  
  41. l_privilegeVARCHAR2(10);  
  42. BEGIN  
  43. l_vpd_flag:=NVL(SYS_CONTEXT('VPD','ENABLE'),'0');  
  44. IFl_vpd_flag=0THEN 
  45. RETURNNULL;  
  46. ELSE  
  47. l_privilege:=SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER');  
  48. IFl_privilegeISNULLTHEN  
  49. RETURN'1=2';  
  50. ELSE  
  51. RETURNp_column_name||'IN(SELECTobject_idFROMvpdsample_privilegesWHEREprivilege_id='||l_privilege||')';  
  52. ENDIF;  
  53. ENDIF;  
  54. END;  
  55.  
  56. /*======================*/  
  57. FUNCTIONapply_vpd_clothing(p1invarchar2,p2invarchar2)RETURNVARCHAR2IS  
  58. BEGIN  
  59. RETURNgen_vpd_predicate('clothing_id');  
  60. END;  
  61.  
  62. /*======================*/  
  63. FUNCTIONapply_vpd_books(p1invarchar2,p2invarchar2)RETURNVARCHAR2IS  
  64. BEGIN  
  65. RETURNgen_vpd_predicate('book_id');  
  66. END;  
  67. 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:

 
 
  1. begin  
  2. dbms_rls.drop_policy(object_name=>'VPDSAMPLE_CLOTHING',policy_name=>'POL_CLOTHING');  
  3. dbms_rls.drop_policy(object_name=>'VPDSAMPLE_BOOKS',policy_name=>'pol_books');  
  4. end;  
  5.  
  6. begin  
  7. pkg_vpdsample.set_context('Rose');  
  8. end;  
  9.  
  10. begin  
  11. pkg_vpdsample.set_context('Jack');  
  12. end;  
  13.  
  14. begin  
  15. pkg_vpdsample.enable_vpd;  
  16. 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!

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.