在ORACLE数据库上创建VPD的完整pl/sql脚本

本文链接：https://blog.youkuaiyun.com/josonchen/article/details/45698923

本文提供了一个完整的 Oracle VPD (Virtual Private Databases) 的 PL/SQL 脚本实例，通过设置上下文和策略函数实现精细访问控制，确保用户只能访问与其机构代码匹配的数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

本文是一个经过严格调试过的在ORACLE数据库上创建VPD完整pl/sql脚本。其他请参照作者的另一篇文章《在ORACLE数据库上创建VPD（Virtual Private Databases）安全策略管理的实战记录》:http://www.chq.name/content/view/284/49/
This is the complete pl/sql script for Oracle VPD policy. the References : http://www.chq.name/content/view/284/49/

在ORACLE数据库上创建VPD的完整pl/sql脚本
the complete pl/sql script for Oracle VPD policy

作者：陈海青(joson chen)
网站：www.chq.name
日期：2007.05.24

版权声明：转载或引用本网版权所有之内容须注明“转自（或引自）chq.name (Junit fans)”字样,
并标明本网站网址http://www.chq.name

--关于用户的说明：About the user
--
--      1):VPD:数据表和VPD对象的属主，允许访问全部数据；the owner of data
--      2):1234502 ：数据访问用户，其用户名为其单位代码的前7位，访问受限用户;the guest user
--其他请参照作者的另一篇文章《在ORACLE数据库上创建VPD（Virtual Private Databases）安全策略管理的实战记录》
--References : http://www.chq.name/content/view/284/49/

--1:建立数据表和视图--create table/view
CONNECT VPD/passwd@www.chq.name

    CREATE TABLE dm_users
   ( user_dm          VARCHAR2(11) NOT NULL,
     jg_dm            VARCHAR2(11) NOT NULL,
   );

CREATE TABLE DATA_JBXX
(pk     VARCHAR2(50) NOT NULL,
   data   VARCHAR2(11)
   jg_dm VARCHAR2(11) NOT NULL);

create or replace view VPD.VO_JBXX
as
  select *
  from VPD.DATA_JBXX
   ;

--2:create user and grant right

   CONNECT sys/password@www.chq.name AS SYSDBA;
   GRANT EXECUTE ON DBMS_RLS TO PUBLIC;
   CONNECT VPD/VPD@www.chq.name
   GRANT SELECT ON VPD.VO_JBXX TO "1234502"
   ;

--3: create context
CONNECT sys/password@www.chq.name AS SYSDBA;
GRANT create any context, create public synonym TO VPD;
CONNECT VPD/passwd@VPD1735;
CREATE CONTEXT CTX_VPD_SEC USING VPD.PKG_VPD_SEC;

--4: create PKG_VPD_SEC
CONNECT VPD/passwd@VPD1735;
create or replace package PKG_VPD_SEC is

-- Author : 陈海青 (www.chq.name)
-- Created : 2007-5-24
-- Purpose : VPD应用程序-安全策略包，创建策略函数,提供了对数据库的精密访问控制（graind access control (FGAC)）.
-- 安全策略目标：每个用户仅能浏览和操作“机构代码”与自己的机构代码相同的数据记录。
-- 例外情况：vpd用户可以不受限制访问数据

PROCEDURE Set_Context;

FUNCTION User_Data_Insert_Security(Owner VARCHAR2, Objname VARCHAR2)
RETURN VARCHAR2;

FUNCTION User_Data_Select_Security(Owner VARCHAR2, Objname VARCHAR2)
RETURN VARCHAR2;

end PKG_VPD_SEC;
/


create or replace package body PKG_VPD_SEC
-- Author : 陈海青 (www.chq.name)
-- Created : 2007-5-24 15:26:00
-- Purpose : VPD应用程序-安全策略包，创建策略函数,提供了对数据库的精密访问控制（graind access control (FGAC)）.
--           安全策略目标：每个用户仅能浏览和操作“机构代码”与自己的机构代码相同的数据记录。
--           例外情况：vpd用户可以不受限制访问数据

is
PROCEDURE Set_Context IS
    lv_user VARCHAR2(11);
    lv_jg_dm VARCHAR2(11);
BEGIN
    DBMS_Session.Set_Context('CTX_VPD_SEC', 'SETUP', 'TRUE');
    lv_user := SYS_CONTEXT('USERENV', 'SESSION_USER');
    DBMS_Session.Set_Context('CTX_VPD_SEC', 'DM_USER', lv_user);
    BEGIN
      SELECT substr(JG_DM, 1, 7)
        INTO lv_jg_dm
        FROM VPD.dm_users
       WHERE CZRY_DM = lv_user;
      DBMS_Session.Set_Context('CTX_VPD_SEC', 'JG_DM', lv_jg_dm);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        DBMS_Session.Set_Context('CTX_VPD_SEC',
                                 'JG_DM',
                                 substr(lv_user, 1, 7));
    END;
    DBMS_Session.Set_Context('CTX_VPD_SEC', 'SETUP', 'FALSE');
END Set_Context;

FUNCTION User_Data_Select_Security(Owner VARCHAR2, Objname VARCHAR2)
    RETURN VARCHAR2 IS
    Predicate VARCHAR2(2000);
BEGIN
    Predicate := ' 1=2 ';
    IF (SYS_CONTEXT('USERENV', 'SESSION_USER') in( Owner)) THEN
      Predicate := NULL;
    end if;
    IF upper(Objname) in('VO_JBXX') then
      Predicate := 'N_JG_DM like SYS_CONTEXT(''CTX_VPD_SEC'',''JG_DM'')||''%''';
    else
      Predicate := 'JG_DM like like SYS_CONTEXT(''CTX_VPD_SEC'',''JG_DM'')||''%''';
    end if;
    RETURN Predicate;
END User_Data_Select_Security;

FUNCTION User_Data_Insert_Security(Owner VARCHAR2, Objname VARCHAR2)
    RETURN VARCHAR2 IS
    Predicate VARCHAR2(2000);
BEGIN
    Predicate := '1=2';
    IF (SYS_CONTEXT('USERENV', 'SESSION_USER') in( Owner,'VPD','VPDUSER','IOFFICE')) THEN
      Predicate := NULL;
    end if;
    IF upper(Objname) in('VO_JBXX') then
      Predicate := 'N_JG_DM like SYS_CONTEXT(''CTX_VPD_SEC'',''JG_DM'')||''%''';
    else
      Predicate := 'JG_DM like like SYS_CONTEXT(''CTX_VPD_SEC'',''JG_DM'')||''%''';
    end if;
    RETURN Predicate;
END User_Data_Insert_Security;
end PKG_VPD_SEC;
/
GRANT EXECUTE ON VPD.PKG_VPD_SEC TO PUBLIC;
CREATE PUBLIC SYNONYM PKG_VPD_SEC FOR VPD.PKG_VPD_SEC; -- for use on login trigger
/
--5:Apply Security Policies to Tables
BEGIN
DBMS_Rls.Add_Policy('VPD', 'VO_JBXX', 'USER_DATA_INSERT_POLICY',
                      'VPD', 'PKG_VPD_SEC.USER_DATA_INSERT_SECURITY',
                      'INSERT', TRUE);
DBMS_Rls.Add_Policy('VPD', 'VO_JBXX', 'USER_DATA_SELECT_POLICY',
                      'VPD', 'PKG_VPD_SEC.USER_DATA_INSERT_SECURITY',
                      'SELECT');
END;
/
--or
execute DBMS_RLS.ADD_POLICY(object_schema => 'VPD',object_name => 'VO_JBXX', policy_name => 'USER_DATA_SELECT_TEST_POLICY',                            function_schema => 'VPD',                            policy_function => 'PKG_VPD_SEC.USER_DATA_INSERT_SECURITY',                            statement_types => 'SELECT',                            update_check => false,                            enable => true,                            static_policy => true )
;
/
/

--5:建立用户登陆触发器，每次登录后首先设置自己的应用环境变量
--5:Create Login Trigger
CONNECT sys/password@www.chq.name AS SYSDBA;

CREATE OR REPLACE TRIGGER VPD.Set_Security_Context
AFTER LOGON ON DATABASE
BEGIN
PKG_VPD_SEC.Set_Context;
END;
/
SHOW ERRORS

--6:调试,使用 1234502 用户
--6:for debug,use user 1234502
CONNECT 1234502/password@www.chq.name;
select VPD.PKG_VPD_SEC.USER_DATA_select_SECURITY('VPD','VO_JBXX') from dual
;
select VPD.PKG_VPD_SEC.USER_DATA_insert_SECURITY('VPD','VO_JBXX') from dual
;
select * from VPD.VO_JBXX;

---7:扩大授权,对增加的每张表
---7:add another table/view

--1）:create view
create or replace view VPD.VO_JBXX2
as
select *
  from VPD.DATA_JBXX2
;
/
--2):Add_Policy
BEGIN
DBMS_Rls.Add_Policy('VPD', 'VO_JBXX2', 'USER_DATA_INSERT_POLICY',
                      'VPD', 'PKG_VPD_SEC.USER_DATA_select_SECURITY',
                      'INSERT', TRUE);
END;
/
--3):GRANT use vpd user
CONNECT vpd/password@www.chq.name;
GRANT SELECT ON VPD.VO_JBXX2 TO "1234502";
/
GRANT EXECUTE ON VPD.PKG_VPD_SEC TO PUBLIC;
/

--4: 如果修改了VPD.PKG_VPD_SEC，要重新授权，用sys用户
CONNECT sys/password@www.chq.name AS SYSDBA;
GRANT EXECUTE ON VPD.PKG_VPD_SEC TO PUBLIC;
/
--5:测试结果
CONNECT 1234502/password@www.chq.name;
select count(*) from VPD.VO_JBXX;