VPD = Virtual Private Database。同义词有RLS : Row Level Security， FGAC: Fine Grained Access Control。
用于行级访问控制。假设有需求，只有用户'SCOTT'能访问emp表所有记录，其他人只能访问manager以下员工的记录。
CREATE FUNCTION emp_policy(schema_in IN VARCHAR2,object_in IN VARCHAR2)
RETURN VARCHAR2
IS
l_return_value VARCHAR2(32767);
BEGIN
IF SYS_CONTEXT('USERENV', 'SESSION_USER') = 'SCOTT' THEN
l_return_value := '1=1';
ELSE
l_return_value := 'JOB NOT IN (''PRESIDENT'',''MANAGER'')';
END IF;
RETURN l_return_value;
END;
BEGIN
DBMS_RLS.ADD_POLICY (
object_schema => 'SCOTT',
object_name => 'EMP',
policy_name => 'EMP_POLICY',
function_schema => 'SCOTT',
policy_function => 'emp_policy',
statement_types => 'SELECT, INSERT, UPDATE, DELETE'
);
END;我们测试一下：
[oracle@odilab ~]$ sqlplus scott/a123456
SQL*Plus: Release 11.2.0.1.0 Production on Sun Jan 19 22:52:20 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$scott@ORCL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7935 HUIYE PRESIDENT (null) 03-NOV-13 9999 (null) 10
7369 SMITH CLERK 7902 17-DEC-80 800 (null) 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 (null) 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 (null) 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 (null) 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 (null) 20
7839 KING PRESIDENT (null) 17-NOV-81 5000 (null) 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 (null) 20
7900 JAMES CLERK 7698 03-DEC-81 950 (null) 30
7902 FORD ANALYST 7566 03-DEC-81 3000 (null) 20
7934 MILLER CLERK 7782 23-JAN-82 1300 (null) 10
15 rows selected.
$scott@ORCL> connect scott1/a123456
Connected.
$scott1@ORCL> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 (null) 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 (null) 20
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 (null) 20
7900 JAMES CLERK 7698 03-DEC-81 950 (null) 30
7902 FORD ANALYST 7566 03-DEC-81 3000 (null) 20
7934 MILLER CLERK 7782 23-JAN-82 1300 (null) 10
10 rows selected.
我们也可以在session中声明自己的context，并给context赋值，最终由context中的值决定l_return_value。如下所示：
CREATE OR REPLACE CONTEXT MY_CONTEXT USING MY_CONTEXT_PKG;
CREATE OR REPLACE PACKAGE my_context_pkg IS
PROCEDURE SET_MAX_DEPT;
END MY_CONTEXT_PKG;
CREATE OR REPLACE PACKAGE BODY my_context_pkg IS
PROCEDURE set_max_dept
AS
l_max_deptno NUMBER;
BEGIN
CASE SYS_CONTEXT('USERENV', 'SESSION_USER')
WHEN 'SCOTT' THEN l_max_deptno := 9999;
WHEN 'HR' THEN l_max_deptno := 10;
WHEN 'OE' THEN l_max_deptno := 20;
ELSE l_max_deptno := 0;
END CASE;
DBMS_SESSION.SET_CONTEXT('my_context', 'max_deptno', l_max_deptno);
END set_max_dept;
END my_context_pkg;
CREATE TRIGGER tr_set_max_dept AFTER LOGON ON DATABASE
BEGIN
scott.my_context_pkg.set_max_dept;
END;
CREATE FUNCTION DEPT_POLICY(SCHEMA_IN IN VARCHAR2,OBJECT_IN IN VARCHAR2)
RETURN VARCHAR2
IS
l_return_value VARCHAR2 (32767);
BEGIN
CASE SYS_CONTEXT('my_context', 'max_deptno')
WHEN 10 THEN l_return_value := 'DEPTNO <= 10';
WHEN 20 THEN l_return_value := 'DEPTNO <= 20';
WHEN 30 THEN l_return_value := 'DEPTNO <= 30';
WHEN 40 THEN l_return_value := 'DEPTNO <= 40';
WHEN 9999 THEN l_return_value := '1=1';
ELSE l_return_value := '1=0';
END IF;
RETURN L_RETURN_VALUE;
END MY_POLICY;查询和删除policy
dba|all|user_policies视图保存了与VPD相关的policy
select object_owner, object_name, policy_name, pf_owner, function, enable, static_policy, policy_type
from all_policies;DBMS_RLS.DROP_POLICY (object_schema IN VARCHAR2 NULL, object_name in varchar2,policy_name IN VARCHAR2);Add_policy的Policy_type参数
- STATIC - The return value of the policy function is cached and reused repeatedly for an individual object. By definition thereturn value of the policy function must be static.
- SHARED_STATIC - The same as STATIC but the resulting predicate can be applied to several objects.
- CONTEXT_SENSITIVE - Used when policy is based around local application context. The result of the policy function is cached and reused. The policy function is only executed again when the value of the application context changes.
- SHARED_CONTEXT_SENSITIVE - The same as CONTEXT_SENSITIVE but the resulting predicate can be applied to several objects.
- DYNAMIC - The policy function is executed for every SQL statement.
create or replace function dept_policy(schema_in in varchar2,object_in in varchar2)
RETURN VARCHAR2
IS
begin
dbms_application_info.set_client_info(userenv('client_info')+1);
RETURN '1=1';
END;
exec dbms_rls.drop_policy('SCOTT','DEPT','DEPT_POLICY');
BEGIN
DBMS_RLS.ADD_POLICY (
object_schema => 'SCOTT',
object_name => 'DEPT',
policy_name => 'DEPT_POLICY',
function_schema => 'SCOTT',
policy_function => 'DEPT_policy',
statement_types => 'SELECT, INSERT, UPDATE, DELETE',
policy_type => dbms_rls.dynamic --
);
END;
$scott@ORCL> select policy_name, function, enable, static_policy, policy_type from all_policies;
POLICY_NAME FUNCTION ENA STA POLICY_TYPE
------------------------------ ------------------------------ --- --- ------------------------
DEPT_POLICY DEPT_POLICY YES NO DYNAMIC
EMP_POLICY EMP_POLICY YES NO DYNAMIC
exec dbms_application_info.set_client_info(0);
select /*my4*/ * from dept;
select userenv('client_info') from dual;
Policy_Type为dynamic时，每次执行这条Query返回20条记录，调用2次policy function - dept_policy。根据Oracle文档每次SQL parse和execution各调用一次policy function。
同样的policy_function，假如在add_policy时policy_type => dbms_rls.static时。同样的Query也会返回20条记录，第一次执行调用一次policy function，以后不再调用，因为policy function的结果缓存在SGA中。这符合我们的预期。
Static
static要求policy function返回值是一个常量，考虑如下VPD配置：
CREATE OR REPLACE FUNCTION EMP_POLICY(P_SCHEMA IN VARCHAR2, P_OBJECT IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
dbms_application_info.set_client_info(userenv('client_info')+1);
RETURN 'ename = SYS_CONTEXT (''USERENV'', ''SESSION_USER'')';
END;
BEGIN
DBMS_RLS.ADD_POLICY (
object_schema => 'SCOTT',
object_name => 'EMP',
policy_name => 'EMP_POLICY',
function_schema => 'SCOTT',
POLICY_FUNCTION => 'emp_policy',
STATEMENT_TYPES => 'SELECT, INSERT, UPDATE, DELETE',
policy_type => dbms_rls.static
);
END;
Elapsed: 00:00:00.01
$clark@ORCL> select ename from scott.emp;
ENAME
----------
CLARK
Elapsed: 00:00:00.00
$clark@ORCL> connect scott/xxx
Connected.
$scott@ORCL> select ename from scott.emp;
ENAME
----------
SCOTT
Elapsed: 00:00:00.01
SQL_ID 5nwx8yqg94xdy, child number 1
-------------------------------------
select ename from scott.emp
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 6 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ENAME"=SYS_CONTEXT('USERENV','SESSION_USER'))
Column level security
有个需求：emp表中dept=10（总裁们所在部门）的工资信息不希望被查询到。
CREATE OR REPLACE FUNCTION EMP_POLICY(P_SCHEMA IN VARCHAR2, P_OBJECT IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(USERENV('client_info')+1);
RETURN 'deptno=10';
END;
BEGIN
DBMS_RLS.ADD_POLICY (
object_schema => 'SCOTT',
object_name => 'EMP',
policy_name => 'EMP_POLICY',
function_schema => 'SCOTT',
POLICY_FUNCTION => 'emp_policy',
STATEMENT_TYPES => 'SELECT, INSERT, UPDATE, DELETE',
POLICY_TYPE => DBMS_RLS.STATIC,
sec_relevant_cols => 'sal'
);
END;
$scott@ORCL> select deptno, ename, sal from emp;
DEPTNO ENAME SAL
---------- ---------- ----------
20 SMITH 800
20 JONES 2975
20 SCOTT 3000
20 ADAMS 1100
20 FORD 3000
Elapsed: 00:00:00.00
$scott@ORCL> select deptno, ename from emp;
DEPTNO ENAME
---------- ----------
10 HUIYE
20 SMITH
30 ALLEN
30 WARD
20 JONES
30 MARTIN
30 BLAKE
10 CLARK
20 SCOTT
10 KING
30 TURNER
20 ADAMS
30 JAMES
20 FORD
10 MILLER
15 rows selected.
Elapsed: 00:00:00.01
可以看到，只要用户试图查找deptno=10的员工的薪水，那么deptno=10的行就不显示。也就是说，用户查询sal时，policy function给出的predicate就会被加到sql中。
Data Masking
CREATE OR REPLACE FUNCTION EMP_POLICY(P_SCHEMA IN VARCHAR2, P_OBJECT IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(USERENV('client_info')+1);
RETURN 'deptno=10';
END;
BEGIN
DBMS_RLS.ADD_POLICY (
object_schema => 'SCOTT',
object_name => 'EMP',
policy_name => 'EMP_POLICY',
function_schema => 'SCOTT',
POLICY_FUNCTION => 'emp_policy',
POLICY_TYPE => DBMS_RLS.STATIC,
SEC_RELEVANT_COLS => 'sal',
sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS
);
END;
$scott@ORCL> select deptno, ename, sal from emp order by deptno;
DEPTNO ENAME SAL
---------- ---------- ----------
10 HUIYE 9999
10 MILLER 1300
10 KING 5000
10 CLARK 2450
20 JONES (null)
20 SMITH (null)
20 FORD (null)
20 ADAMS (null)
20 SCOTT (null)
30 ALLEN (null)
30 TURNER (null)
30 JAMES (null)
30 WARD (null)
30 BLAKE (null)
30 MARTIN (null)
可以看到deptno=10的员工的薪水照常显示，其他部门的都显示为null。 看来，data masking不作用于policy function返回的predicate包含的数据。
VPD的性能
测试了三种情况：
- 不使用VPD，SQL手工添加predicate再执行
- 使用VPD，policy_type=static
- 使用VPD，policy_type=dynamic
Policy function如下：
create or replace FUNCTION EMP_POLICY_1(P_SCHEMA IN VARCHAR2, P_OBJECT IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
dbms_application_info.set_client_info(userenv('client_info')+1);
RETURN 'deptno = SYS_CONTEXT (''my_context'', ''deptno'')';
end; 下面给出三次测试的执行计划：
SQL_ID cj605kcb4s1t6, child number 0 ------------------------------------- select /*3*/ count(*) from emp2 where deptno=sys_context('my_context','deptno') Plan hash value: 2889146209 ------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | ------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 37905 (100)| | 1 |00:00:20.14 | 137K| 137K| | 1 | SORT AGGREGATE | | 1 | 1 | 3 | | | 1 |00:00:20.14 | 137K| 137K| |* 2 | TABLE ACCESS FULL| EMP2 | 1 | 2857K| 8370K| 37905 (2)| 00:07:35 | 1998K|00:00:10.93 | 137K| 137K| ------------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("DEPTNO"=TO_NUMBER(SYS_CONTEXT('my_context','deptno')))SQL_ID 39sknfv3wmb37, child number 0 select /*3*/ count(*) from emp0 Plan hash value: 3474440072 ------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | | 3800 (100)| | 1 |00:00:19.91 | 13699 | 13695 || 1 | SORT AGGREGATE | | 1 | 1 | 3 | | | 1 |00:00:19.91 | 13699 | 13695 ||* 2 | TABLE ACCESS FULL| EMP0 | 1 | 1998K| 5853K| 3800 (2)| 00:00:46 | 1998K|00:00:10.33 | 13699 | 13695 |------------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id):--------------------------------------------------- 2 - filter("DEPTNO"=TO_NUMBER(SYS_CONTEXT('my_context','deptno')))------------dynamic------------SQL_ID b82cfwj1skv3d, child number 0-------------------------------------select /*4*/ count(*) from emp0 Plan hash value: 3474440072 ------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | | 3800 (100)| | 1 |00:00:17.17 | 13699 | 13695 || 1 | SORT AGGREGATE | | 1 | 1 | 3 | | | 1 |00:00:17.17 | 13699 | 13695 ||* 2 | TABLE ACCESS FULL| EMP0 | 1 | 1998K| 5853K| 3800 (2)| 00:00:46 | 1998K|00:00:08.90 | 13699 | 13695 |------------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id):--------------------------------------------------- 2 - filter("DEPTNO"=TO_NUMBER(SYS_CONTEXT('my_context','deptno'))) 可以看到性能差异很小。------------static------------
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
