利用Oracle VPD（虚拟专用数据库）实现类似EBS R12里的多OU屏蔽
EBS R11通过建立带有CLIENT_INFO过滤条件的视图来实现多OU屏蔽，而R12使用的则是VDP（Virtual Private Database）来实现，关联的package是MO_GLOBAL,ORG_SECURITY。
假设我们想让员工只能查看到本部门的所有员工信息：
没有应用VDP之前：
select * from scott.emp;-----------------------------------------------------------------------------7369 SMITH CLERK 7902 12/17/1980 00:00:00 800 207499 ALLEN SALESMAN7698 02/20/1981 00:00:00 1600 300 307521 WARD SALESMAN7698 02/22/1981 00:00:00 1250 500 307566 JONES MANAGER 7839 04/02/1981 00:00:00 2975 207654 MARTIN SALESMAN7698 09/28/1981 00:00:00 1250 1400 307698 BLAKE MANAGER 7839 05/01/1981 00:00:00 2850 307782 CLARK MANAGER 7839 06/09/1981 00:00:00 2450 107788 SCOTT ANALYST 7566 04/19/1987 00:00:00 3000 207839 KING PRESIDENT 11/17/1981 00:00:00 5000 107844 TURNER SALESMAN7698 09/08/1981 00:00:00 1500 0 307876 ADAMS CLERK 7788 05/23/1987 00:00:00 1100 207900 JAMES CLERK 7698 12/03/1981 00:00:00 950 307902 FORD ANALYST 7566 12/03/1981 00:00:00 3000 207934 MILLER CLERK 7782 01/23/1982 00:00:00 1300 10
应用VPD之后（员工查询的时候不需加任何条件，VPD会通过query rewrite自动附加对应的过滤条件）：
select * from scott.emp;-----------------------------------------------------------------------------7782 CLARK MANAGER 7839 06/09/1981 00:00:00 2450 107839 KING PRESIDENT 11/17/1981 00:00:00 5000 107934 MILLER CLERK 7782 01/23/1982 00:00:00 1300 10
什么是VPD？
VPD是由应用程序上下文（application context）和细粒度访问控制(fine-grained access control)共同实现的一套可以精确到行（RLS，row level security）和列的安全策略。
具体实现
这里以scott用户示例，首先创建application context：
CONNECT scott/tiger
为方便以后手动设置context，这里先加一个set_dept_info，因为如果dbms_session.set_context不放到context关联的package里面会报ORA-01031: insufficient privileges：
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1288401763279#3056702265218
创建application context所需的package：
CREATE OR REPLACE PACKAGE scott_context
AS
PROCEDURE set_deptno;
PROCEDURE set_dept_info (p_name IN VARCHAR2, p_value IN VARCHAR2);
END scott_context;
CREATE OR REPLACE PACKAGE BODY scott_context
AS
PROCEDURE set_deptno
IS
l_deptno NUMBER;
BEGIN
SELECT deptno
INTO l_deptno
FROM scott.emp
WHERE UPPER (ename) = SYS_CONTEXT ('USERENV', 'SESSION_USER');
DBMS_SESSION.set_context ('dept_info', 'deptno', l_deptno);
END set_deptno;
PROCEDURE set_dept_info (p_name IN VARCHAR2, p_value IN VARCHAR2)
AS
BEGIN
DBMS_SESSION.set_context ('dept_info', p_name, p_value);
END set_dept_info;
END scott_context;
赋予scott CREATE ANY CONTEXT权限：
CONNECT system/oracle
GRANT CREATE ANY CONTEXT TO scott;
创建application context：
CONNECT scott/tiger
CREATE CONTEXT dept_info USING scott.scott_context;
创建FGAC所需package：
CREATE OR REPLACE PACKAGE scott_security
AS
FUNCTION deptno_sec (a1 VARCHAR2, a2 VARCHAR2)
RETURN VARCHAR2;
END scott_security;
CREATE OR REPLACE PACKAGE BODY scott_security
AS
FUNCTION deptno_sec (a1 VARCHAR2, a2 VARCHAR2)
RETURN VARCHAR2
IS
l_predicate VARCHAR2 (2000);
BEGIN
l_predicate := 'deptno=SYS_CONTEXT(''dept_info'',''deptno'')';
RETURN l_predicate;
END deptno_sec;
END scott_security;
将FGAC的package与表关联：
CONNECT system/oracle
BEGIN
DBMS_RLS.add_policy (object_schema => 'scott'
,object_name => 'emp'
,policy_name => 'dept_policy'
,function_schema => 'scott'
,policy_function => 'scott_security.deptno_sec'
,statement_types => 'select'
);
END;
查看刚刚建好的VPD policy：
SELECT object_name
,policy_name
,sel
,ins
,upd
,del
,ENABLE
FROM all_policies;
-----------------------------------------------------------------------------
EMP DEPT_POLICY YES NONONOYES
将FGAC package的执行权限赋予public：
GRANT EXECUTE ON scott.scott_security TO public;
测试：
exec scott_context.set_dept_info('deptno',10);
select deptno from scott.emp;
DEPTNO
----------
10
10
10
通过LOGON触发器我们可以在用户登录的时候就直接设置好context值：
CREATE OR REPLACE TRIGGER scott.security_context
AFTER LOGON ON DATABASE
BEGIN
scott_context.set_deptno;
END;
REF：
1. <<Expert Oracle Database 11g Administration>> Page 578, Fine-Grained Data Access
2. http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1288401763279#3056702265218
页:
[1]