Oracle的VPD介绍

butter7372

　　1、什么是VPD?
　　虚拟专用数据库 (VPD) 提供了角色和视图无法提供的行级访问控制。对于互联网访问，虚拟专用数据库可以确保在线银行的客户只能看到他们自己的帐户。Web 托管公司可以在同一Oracle 数据库中维护多个公司的数据，但只允许每个公司查看其自身数据。
　　在企业内部，虚拟数据库可在应用程序部署方面降低拥有成本。可以在数据库服务器一次实现安全性，而不用在访问数据的每个应用程序中分别实现安全性。因为是在数据库中实施安全性，所以不管用户访问数据的方式如何，安全性较以前更高。访问即席查询工具或新报表生成程序的用户不再能绕过安全环节。
　　2、VPD是如何工作的？
　　将一个或多个安全策略与表或视图关联后，就可以实现虚拟专用数据库。对带安全策略的表进行直接或间接访问时，数据库将调用一个实施该策略的函数。策略函数返回一个访问条件（WHERE 子句），即谓词。应用程序将它附加到用户的 SQL 语句，从而动态修改用户的数据访问权限。
　　你可以通过编写一个存储过程将 SQL 谓词附加到每个 SQL 语句（用于控制该语句的行级别访问权限）来实施 VPD。例如，如果 John Doe（他属于 Department 10）输入 SELECT * FROM emp 语句，则可以使用 VPD 添加 WHERE DEPT = 10 子句。这样，您便可以通过对查询进行修改来限制访问某些行的数据。
　　3、简单案例
　　二、使用VPD的示例：
　　1）未进行权限控制时，执行的查询语句,可以看到所有商品在2002年的销售额：
　　SELECT year, prod_category, sum(sales)
　　FROM sales_mv
　　WHERE year = '2002';
　　2）如果加上了VPD策略,限制访问指定范围内的产品（例如仅能访问'VIDEOS','RADIOS'），
　　再执行上述的查询语句，会自动加上限制条件--AND prod_category in('VIDEOS','RADIOS')，最后实际执行的语句为：
　　SELECTyear, prod_category, sum(sales)
　　FROM sales_factWHERE year = '2002'
　　AND prod_category in ('VIDEOS','RADIOS'); --自动加上的限制条件
　　操作过程
　　1：确定数据库对象及互相之间的关系
　　1）相关表和视图
　　数据库中有两类表，代码表和数据表，数据表均有一个“机构代码”字段（DM_JG），
　　其“前七位”等于用户代码表的“机构代码”
　　建立脚本：
　　CREATE TABLE dm_users
　　( user_dm VARCHAR2(11) NOT NULL,
　　jg_dmVARCHAR2(11) NOT NULL
　　);
　　CREATETABLE DIM_JBXX
　　(pk VARCHAR2(50) NOT NULL,
　　data VARCHAR2(11)
　　jg_dm VARCHAR2(11) NOT NULL); --www.6688.cc
　　CREATEVIEW VIEW_JBXX as select * from DIM_JBXX
　　;
　　2）相关用户，并为其授权
　　相关用户有三个：
　　VPD:数据表和VPD对象的属主，允许访问全部数据；
　　1234502 ：数据访问用户，其用户名为其单位代码的前7位，访问受限用户
　　建立脚本：
　　CONNECT sys/password@www.6688.cc ASSYSDBA;
　　CREATE USER VPD IDENTIFIED BY VPD
　　DEFAULT TABLESPACE VPD TEMPORARY TABLESPACE temp;
　　GRANT connect, resource TO VPD;

　　CREATEUSER 1234502>　　DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
　　GRANT connect, resource TO 1234502;
　　GRANT EXECUTE ON DBMS_RLS TO PUBLIC;
　　CONN VPD/VPD@www.6688.cc
　　GRANTSELECT, INSERT ON V_JBXX TO 1234502, 1234512;
　　2：定义安全策略（Security Policies）的目标
　　在数据库里，每个用户分配一个机构代码，在这里直接使用该代码的前7位作为用户名，相关的数据表均有一个“机构代码”字段
　　安全策略目标：每个用户仅能浏览和操作“机构代码”与自己的机构代码相同的数据记录。
　　例外情况：vpd用户可以不受限制访问数据
　　3：创建应用环境(Application Context)
　　应用环境是一个包含系列环境变量名称和值的值对的集合，是存储用户的环境变量的空间；
　　系统提供了默认的环境USERENV,含有当前会话相关的默认的变量,如用户名、主机和应用程序名等
　　1)为创建VPD的context和contextpackage的用户授权,
　　CONNECT sys/password@www.6688.cc ASSYSDBA;
　　GRANT create any context, create public synonym TO VPD;
　　CONNECT VPD/VPD@www.6688.cc;
　　2)创建应用环境 CTX_VPD_SEC
　　CREATE CONTEXT CTX_VPD_SEC USING VPD.PKG_VPD_SEC;
　　4：创建VPD应用程序包,在包里增加设置环境变量的函数、存储过程
　　这个包的作用是提供设置环境变量的工具（函数或存储过程），供on login触发器或应用程序等调用。
　　1) 创建VPD应用程序包PKG_VPD_SEC的包头
　　CONNECT VPD/VPD@www.6688.cc;
　　CREATE OR REPLACE PACKAGE VPD.PKG_VPD_SEC AS
　　PROCEDURE Set_Context;
　　END PKG_VPD_SEC;
　　2）创建VPD应用程序包的包体
　　CREATE OR REPLACE PACKAGE BODY VPD.PKG_VPD_SEC IS
　　PROCEDURE Set_Context IS
　　lv_user VARCHAR2(11);
　　lv_jg_dm VARCHAR2(11);
　　BEGIN
　　DBMS_Session.Set_Context('CTX_VPD_SEC','SETUP','TRUE');
　　v_ouser := 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 dm_users
　　WHERE user_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;
　　END PKG_VPD_SEC;
　　/
　　SHOW ERRORS
　　3)授权其他用户可以访问并运行 PKG_VPD_SEC 包:
　　GRANT EXECUTE ON VPD.PKG_VPD_SEC TO PUBLIC;
　　CREATE PUBLIC SYNONYM PKG_VPD_SEC FOR VPD.PKG_VPD_SEC;
　　4)建立用户登陆触发器，每次登录后首先设置自己的应用环境变量
　　CONNECT sys/password@www.6688.cc ASSYSDBA;
　　CREATE OR REPLACE TRIGGER VPD.Set_Security_Context
　　AFTER LOGON ON DATABASE
　　BEGIN
　　VPD.PKG_VPD_SEC.Set_Context;
　　END;
　　/
　　SHOW ERRORS
　　5：创建VPD应用程序-安全策略包，创建策略函数，为简便起见仍使用PKG_VPD_SEC
　　这是实现策略的函数，该函数从应用环境取得变量值，形成查询语句的一部分
　　1)建立安全策略( Security Policies)包的包头，简便起见修改PKG_VPD_SEC
　　--In order for the context package to have any effect on the users interactionwith the database,
　　--we need to define a Security_Package for use with the security policy.
　　--This package will tell the database how to treat any interactions with thespecified table:
　　CONNECT vpd/vpd@www.6688.cc;
　　CREATEOR REPLACE PACKAGE VPD.PKG_VPD_SEC AS
　　PROCEDURE Set_Context;
　　FUNCTIONUser_Data_Insert_Security(Owner VARCHAR2, Objname VARCHAR2)
　　RETURN VARCHAR2;
　　FUNCTIONUser_Data_Select_Security(Owner VARCHAR2, Objname VARCHAR2)
　　RETURN VARCHAR2;
　　END Security_Package;
　　/
　　2)修改包体，增加安全策略函数:
　　CREATEOR REPLACE PACKAGE BODY Security_Package IS
　　PROCEDURESet_Context IS
　　......
　　END Set_Context;
　　FUNCTIONUser_Data_Select_Security(Owner VARCHAR2, Objname VARCHAR2)RETURN VARCHAR2 IS
　　Predicate VARCHAR2(2000);
　　BEGIN
　　Predicate := '1=2';
　　IF (SYS_CONTEXT('USERENV','SESSION_USER') = 'VPD') THEN
　　Predicate := NULL;
　　ELSE
　　Predicate := 'JG_DM = SYS_CONTEXT(''CTX_VPD_SEC'',''JG_DM'')';
　　END IF;
　　RETURN Predicate;
　　END User_Data_Select_Security;
　　FUNCTIONUser_Data_Insert_Security(Owner VARCHAR2, Objname VARCHAR2) RETURN VARCHAR2 IS
　　Predicate VARCHAR2(2000);
　　BEGIN
　　Predicate := '1=2';
　　IF (SYS_CONTEXT('USERENV','SESSION_USER') = 'VPD') THEN
　　Predicate := NULL;
　　ELSE
　　Predicate := 'JG_DM = SYS_CONTEXT(''CTX_VPD_SEC'',''JG_DM'')';
　　END IF;
　　RETURN Predicate;
　　END User_Data_Insert_Security;
　　END Security_Package;
　　/
　　SHOW ERRORS
　　3）授权所有用户可以访问并运行安全策略包 PKG_VPD_SEC:
　　GRANTEXECUTE ON VPD.PKG_VPD_SEC TO PUBLIC;
　　--CREATE PUBLIC SYNONYM PKG_VPD_SEC FOR VPD.PKG_VPD_SEC;
　　6：将策略函数应用到一个表或视图上
　　使用Oracle 提供的DBMS_RLS包来实现安全策略的应用管理。
　　例如：
　　CONNECT VPD/VPD@www.6688.cc;
　　BEGIN
　　DBMS_Rls.Add_Policy('VPD', 'VIEW_JBXX', 'USER_DATA_INSERT_POLICY',
　　'VPD', 'PKG_VPD_SEC.USER_DATA_INSERT_SECURITY',
　　'INSERT', TRUE);
　　DBMS_Rls.Add_Policy('VPD', 'VIEW_JBXX', 'USER_DATA_INSERT_POLICY',
　　'VPD', 'PKG_VPD_SEC.USER_DATA_INSERT_SECURITY',
　　'SELECT');
　　END;
　　或者：
　　execute DBMS_RLS.ADD_POLICY(object_schema=>'VPD',object_name=>'V_FACT_data',policy_name=>'USER_DATA_INSERT_POLICY',
　　function_schema=>'VPD',policy_function=>'PKG_VPD_SEC.USER_DATA_INSERT_SECURITY',
　　statement_type=>'select',update_check=>FALSE,enable=>true)
　　)
　　注意事项：
　　1）DBMS_RLS包最初的运行权限须由sys用户授予VPD用户。
　　2）插入失败的报错信息：
　　ORA-28115: policy with check option violation