利用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 20 7499 ALLEN SALESMAN 7698 02/20/1981 00:00:00 1600 300 30 7521 WARD SALESMAN 7698 02/22/1981 00:00:00 1250 500 30 7566 JONES MANAGER 7839 04/02/1981 00:00:00 2975 20 7654 MARTIN SALESMAN 7698 09/28/1981 00:00:00 1250 1400 30 7698 BLAKE MANAGER 7839 05/01/1981 00:00:00 2850 30 7782 CLARK MANAGER 7839 06/09/1981 00:00:00 2450 10 7788 SCOTT ANALYST 7566 04/19/1987 00:00:00 3000 20 7839 KING PRESIDENT 11/17/1981 00:00:00 5000 10 7844 TURNER SALESMAN 7698 09/08/1981 00:00:00 1500 0 30 7876 ADAMS CLERK 7788 05/23/1987 00:00:00 1100 20 7900 JAMES CLERK 7698 12/03/1981 00:00:00 950 30 7902 FORD ANALYST 7566 12/03/1981 00:00:00 3000 20 7934 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 10 7839 KING PRESIDENT 11/17/1981 00:00:00 5000 10 7934 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