这篇文章主要讲解了“Oracle VPD的相关功能有哪些”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“Oracle VPD的相关功能有哪些”吧！

测试用的数据表使用Oracle的示例Schema Scott中的emp和dept：

SCOTT-orcl@DESKTOP-V430TU3>descempNameNull?Type-----------------------------------------------------------------------------EMPNONOTNULLNUMBER(4)ENAMEVARCHAR2(10)JOBVARCHAR2(9)MGRNUMBER(4)HIREDATEDATESALNUMBER(7,2)COMMNUMBER(7,2)DEPTNONUMBER(2)SCOTT-orcl@DESKTOP-V430TU3>descdeptNameNull?Type-----------------------------------------------------------------------------DEPTNONOTNULLNUMBER(2)DNAMEVARCHAR2(14)LOCVARCHAR2(13)SCOTT-orcl@DESKTOP-V430TU3>setpagesize100SCOTT-orcl@DESKTOP-V430TU3>SELECTe.deptno,d.dname,ENAME,JOB,SAL,COMM2FROMempe,deptd3WHEREd.deptno=e.deptno;DEPTNODNAMEENAMEJOBSALCOMM---------------------------------------------------------------10ACCOUNTINGKINGPRESIDENT500010ACCOUNTINGCLARKMANAGER245010ACCOUNTINGMILLERCLERK130020RESEARCHFORDANALYST300020RESEARCHSMITHCLERK80020RESEARCHJONESMANAGER297530SALESJAMESCLERK95030SALESTURNERSALESMAN1500030SALESMARTINSALESMAN1250140030SALESWARDSALESMAN125050030SALESALLENSALESMAN160030030SALESBLAKEMANAGER285012rowsselected.

过滤允许范围外的行
假设我们希望SALES部门只能看到自己部门的数据，不能看到其他部门的数据，按照上一节介绍的内容，可以创建相应的函数，添加相应的访问策略即可。
创建函数

SCOTT-orcl@DESKTOP-V430TU3>CREATEORREPLACEFUNCTIONhide_sal_comm(2v_schemaINVARCHAR2,3v_objnameINVARCHAR2)45RETURNVARCHAR2AS6conVARCHAR2(200);78BEGIN9con:='deptno=30';10RETURN(con);11ENDhide_sal_comm;12/Functioncreated.

添加策略

SCOTT-orcl@DESKTOP-V430TU3>BEGIN2DBMS_RLS.DROP_POLICY(3object_schema=>'scott',4object_name=>'emp',5policy_name=>'hide_sal_policy');6END;7/BEGIN*ERRORatline1:ORA-28102:policydoesnotexistORA-06512:at"SYS.DBMS_RLS",line59ORA-06512:atline2SCOTT-orcl@DESKTOP-V430TU3>BEGIN2DBMS_RLS.ADD_POLICY(3object_schema=>'scott',4object_name=>'emp',5policy_name=>'hide_sal_policy',6policy_function=>'hide_sal_comm');7END;8/PL/SQLproceduresuccessfullycompleted.

查询验证

SCOTT-orcl@DESKTOP-V430TU3>SELECTe.deptno,d.dname,ENAME,JOB,SAL,COMM2FROMempe,deptd3WHEREd.deptno=e.deptno;DEPTNODNAMEENAMEJOBSALCOMM---------------------------------------------------------------30SALESALLENSALESMAN160030030SALESWARDSALESMAN125050030SALESMARTINSALESMAN1250140030SALESBLAKEMANAGER285030SALESTURNERSALESMAN1500030SALESJAMESCLERK9506rowsselected.

返回的数据均为SALES部门中的数据。

涉及到敏感列时，过滤允许范围外的行
假设这一次我们希望在查询某些敏感列时才过滤，而不查询这些列时就不过滤。VPD通过在添加策略时指定sec_relevant_cols实现。
添加策略

SCOTT-orcl@DESKTOP-V430TU3>BEGIN2DBMS_RLS.DROP_POLICY(3object_schema=>'scott',4object_name=>'emp',5policy_name=>'hide_sal_policy');6END;7/PL/SQLproceduresuccessfullycompleted.SCOTT-orcl@DESKTOP-V430TU3>SCOTT-orcl@DESKTOP-V430TU3>SCOTT-orcl@DESKTOP-V430TU3>BEGIN2DBMS_RLS.ADD_POLICY(3object_schema=>'scott',4object_name=>'emp',5policy_name=>'hide_sal_policy',6policy_function=>'hide_sal_comm',7sec_relevant_cols=>'sal,comm');8END;9/PL/SQLproceduresuccessfullycompleted.SCOTT-orcl@DESKTOP-V430TU3>

查询验证

--不涉及敏感列SCOTT-orcl@DESKTOP-V430TU3>SELECTe.deptno,ENAME,d.dname,JOB2FROMempe,deptd3WHEREd.deptno=e.deptno;DEPTNOENAMEDNAMEJOB-------------------------------------------10KINGACCOUNTINGPRESIDENT10CLARKACCOUNTINGMANAGER10MILLERACCOUNTINGCLERK20FORDRESEARCHANALYST20SMITHRESEARCHCLERK20JONESRESEARCHMANAGER30JAMESSALESCLERK30TURNERSALESSALESMAN30MARTINSALESSALESMAN30WARDSALESSALESMAN30ALLENSALESSALESMAN30BLAKESALESMANAGER12rowsselected.--涉及敏感列1SELECTe.deptno,d.dname,ENAME,JOB,SAL,COMM2FROMempe,deptd3*WHEREd.deptno=e.deptnoSCOTT-orcl@DESKTOP-V430TU3>/DEPTNODNAMEENAMEJOBSALCOMM---------------------------------------------------------------30SALESALLENSALESMAN160030030SALESWARDSALESMAN125050030SALESMARTINSALESMAN1250140030SALESBLAKEMANAGER285030SALESTURNERSALESMAN1500030SALESJAMESCLERK9506rowsselected.

不涉及敏感列时，返回所有行，而涉及敏感列时，则返回可访问范围内的行，过滤范围外的行。

涉及到敏感列数据时，脱敏敏感数据
最后，假设我们希望在查询某些敏感列时不过滤，但不能显示数据，而只能输出NULL。VPD通过在添加策略时指定sec_relevant_cols和sec_relevant_cols_opt实现。
添加策略

SCOTT-orcl@DESKTOP-V430TU3>BEGIN2DBMS_RLS.DROP_POLICY(3object_schema=>'scott',4object_name=>'emp',5policy_name=>'hide_sal_policy');6END;7/PL/SQLproceduresuccessfullycompleted.SCOTT-orcl@DESKTOP-V430TU3>SCOTT-orcl@DESKTOP-V430TU3>BEGIN2DBMS_RLS.ADD_POLICY(3object_schema=>'scott',4object_name=>'emp',5policy_name=>'hide_sal_policy',6policy_function=>'hide_sal_comm',7sec_relevant_cols=>'sal,comm',8sec_relevant_cols_opt=>dbms_rls.ALL_ROWS);9END;10/PL/SQLproceduresuccessfullycompleted.

查询验证

SCOTT-orcl@DESKTOP-V430TU3>SELECTe.deptno,ENAME,d.dname,JOB,SAL,COMM2FROMempe,deptd3WHEREd.deptno=e.deptno;DEPTNOENAMEDNAMEJOBSALCOMM---------------------------------------------------------------10KINGACCOUNTINGPRESIDENT10CLARKACCOUNTINGMANAGER10MILLERACCOUNTINGCLERK20FORDRESEARCHANALYST20SMITHRESEARCHCLERK20JONESRESEARCHMANAGER30JAMESSALESCLERK95030TURNERSALESSALESMAN1500030MARTINSALESSALESMAN1250140030WARDSALESSALESMAN125050030ALLENSALESSALESMAN160030030BLAKESALESMANAGER285012rowsselected.

可以看到，允许范围内（SALES部门）的行，SAL和COMM都可以正常显示数据，而范围外的数据全部为NULL。

感谢各位的阅读，以上就是“Oracle VPD的相关功能有哪些”的内容了，经过本文的学习后，相信大家对Oracle VPD的相关功能有哪些这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！