Shulou(Shulou.com)05/31 Report--

This article mainly explains "what are the related functions of Oracle VPD". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what are the related functions of Oracle VPD"?

The data table for the test uses emp and dept in Oracle's sample Schema Scott:

SCOTT-orcl@DESKTOP-V430TU3 > desc emp Name Null? Type-EMPNO NOT NULL NUMBER (4) ENAME VARCHAR2 (10) JOB VARCHAR2 (9) MGR NUMBER (4) HIREDATE DATE SAL NUMBER (7) COMM NUMBER (7) DEPTNO NUMBER (2) SCOTT-orcl@DESKTOP-V430TU3 > desc dept Name Null? Type-DEPTNO NOT NULL NUMBER (2) DNAME VARCHAR2 (14) LOC VARCHAR2 (13) SCOTT-orcl@DESKTOP-V430TU3 > set pagesize 100SCOTT-orcl@DESKTOP-V430TU3 > SELECT e.deptno D.dname, ENAME, JOB, SAL, COMM 2 FROM emp e, dept d 3 WHERE d.deptno = e.deptno DEPTNO DNAME ENAME JOB SAL COMM--10 ACCOUNTING KING PRESIDENT 5000 10 ACCOUNTING CLARK MANAGER 2450 10 ACCOUNTING MILLER CLERK 1300 20 RESEARCH FORD ANALYST 3000 20 RESEARCH SMITH CLERK 800 20 RESEARCH JONES MANAGER 2975 30 SALES JAMES CLERK 950 30 SALES TURNER SALESMAN 1500 0 30 SALES MARTIN SALESMAN 1250 1400 30 SALES WARD SALESMAN 1250 500 30 SALES ALLEN SALESMAN 1600 300 30 SALES BLAKE MANAGER 285012 rows selected.

Filter rows outside the allowed range

Suppose we want the SALES department to see only the data of its own department, but not the data of other departments. As described in the previous section, we can create the corresponding function and add the corresponding access policy.

Create function

SCOTT-orcl@DESKTOP-V430TU3 > CREATE OR REPLACE FUNCTION hide_sal_comm (2 v_schema IN VARCHAR2, 3 v_objname IN VARCHAR2) 4 5 RETURN VARCHAR2 AS 6 con VARCHAR2; 7 8 BEGIN 9 con: = 'deptno=30'; 10 RETURN (con); 11 END hide_sal_comm; 12 / Function created.

Add Policy

SCOTT-orcl@DESKTOP-V430TU3 > BEGIN 2 DBMS_RLS.DROP_POLICY (3 object_schema = > 'scott', 4 object_name = >' emp', 5 policy_name = > 'hide_sal_policy'); 6 END 7 / BEGIN*ERROR at line 1:ORA-28102: policy does not existORA-06512: at "SYS.DBMS_RLS", line 59ORA-06512: at line 2SCOTT-orcl@DESKTOP-V430TU3 > BEGIN 2 DBMS_RLS.ADD_POLICY (3 object_schema = > 'scott', 4 object_name = >' emp', 5 policy_name = > 'hide_sal_policy', 6 policy_function = >' hide_sal_comm') 7 END; 8 / PL/SQL procedure successfully completed.

Query verification

SCOTT-orcl@DESKTOP-V430TU3 > SELECT e.deptno, d.dname, ENAME, JOB, SAL, COMM 2 FROM emp e, dept d 3 WHERE d.deptno = e.deptno DEPTNO DNAME ENAME JOB SAL COMM--30 SALES ALLEN SALESMAN 1600 300 30 SALES WARD SALESMAN 1250 500 30 SALES MARTIN SALESMAN 1250 1400 30 SALES BLAKE MANAGER 2850 30 SALES TURNER SALESMAN 1500 0 30 SALES JAMES CLERK 9506 rows selected.

The data returned are all data from the SALES department.

Filter rows outside the allowable range when it comes to sensitive columns

Suppose this time we want to filter only when we query some sensitive columns, but not when we do not query those columns. VPD is achieved by specifying sec_relevant_cols when adding a policy.

Add Policy

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

Query verification

-- does not involve sensitive columns SCOTT-orcl@DESKTOP-V430TU3 > SELECT e.deptnojinEname, d.dname, JOB 2 FROM emp e, dept d 3 WHERE d.deptno = e.deptno DEPTNO ENAME DNAME JOB- 10 KING ACCOUNTING PRESIDENT 10 CLARK ACCOUNTING MANAGER 10 MILLER ACCOUNTING CLERK 20 FORD RESEARCH ANALYST 20 SMITH RESEARCH CLERK 20 JONES RESEARCH MANAGER 30 JAMES SALES CLERK 30 TURNER SALES SALESMAN 30 MARTIN SALES SALESMAN 30 WARD SALES SALESMAN 30 ALLEN SALES SALESMAN 30 BLAKE SALES MANAGER12 rows selected.-- involves sensitive column 1 SELECT e.deptno D.dname, ENAME, JOB, SAL, COMM 2 FROM emp e Dept d 3 * WHERE d.deptno = e.deptnoSCOTT-orcl@DESKTOP-V430TU3 > / DEPTNO DNAME ENAME JOB SAL COMM--30 SALES ALLEN SALESMAN 1600 300 30 SALES WARD SALESMAN 1250 500 30 SALES MARTIN SALESMAN 1250 1400 30 SALES BLAKE MANAGER 2850 30 SALES TURNER SALESMAN 1500 0 30 SALES JAMES CLERK 9506 rows selected.

When sensitive columns are not involved, all rows are returned, while when sensitive columns are involved, rows within the accessible range are returned and rows outside the range are filtered.

Desensitize sensitive data when it comes to sensitive column data

Finally, suppose we want to query some sensitive columns without filtering, but cannot display the data, but can only output NULL. VPD is implemented by specifying sec_relevant_cols and sec_relevant_cols_opt when adding a policy.

Add Policy

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

Query verification

SCOTT-orcl@DESKTOP-V430TU3 > SELECT e.deptno Ename, d.dname, JOB, SAL, COMM 2 FROM emp e, dept d 3 WHERE d.deptno = e.deptno DEPTNO ENAME DNAME JOB SAL COMM--10 KING ACCOUNTING PRESIDENT 10 CLARK ACCOUNTING MANAGER 10 MILLER ACCOUNTING CLERK 20 FORD RESEARCH ANALYST 20 SMITH RESEARCH CLERK 20 JONES RESEARCH MANAGER 30 JAMES SALES CLERK 950 30 TURNER SALES SALESMAN 1500 0 30 MARTIN SALES SALESMAN 1250 1400 30 WARD SALES SALESMAN 1250 500 30 ALLEN SALES SALESMAN 1600 300 30 BLAKE SALES MANAGER 285012 rows selected.

As you can see, rows within the allowable range (SALES department), SAL and COMM can display data normally, while the data outside the range is all NULL.

Thank you for your reading, the above is the content of "what are the relevant functions of Oracle VPD". After the study of this article, I believe you have a deeper understanding of what the relevant functions of Oracle VPD have, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.