利用Oracle VPD（虚拟专用数据库）实现类似EBS R12里的多OU屏蔽

darkyinliang

　　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      SALESMAN  7698 02/20/1981 00:00:00 1600     300      307521 WARD       SALESMAN  7698 02/22/1981 00:00:00 1250     500      307566 JONES      MANAGER   7839 04/02/1981 00:00:00 2975              207654 MARTIN     SALESMAN  7698 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     SALESMAN  7698 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 NO  NO  NO  YES
　　
　　将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