ORACLE HANDBOOK系列之八:VPD
VPD,Oracle Virtual Private Database,即【Oracle虚拟专用数据库】,通过应用一些策略,使得用户只能访问被允许访问的那部分数据。其原理相对简单,Oracle根据策略自动为相应用户提交的语句添加Where句,从而控制用户可以访问和操作的数据。
首先我们准备演示用的几张表,并插入少量的测试数据:
clothing_id NUMBER,
type VARCHAR2(30),
brand VARCHAR2(30),
descripton VARCHAR2(100)
);
--
INSERT INTO vpdsample_clothing VALUES(10002,'jacket','ABC','autumn style');
INSERT INTO vpdsample_clothing VALUES(10003,'t-shirt','XYZ','summer style');
commit;
CREATE TABLE vpdsample_books(
book_id NUMBER,
name VARCHAR2(30),
author VARCHAR2(20)
);
--
INSERT INTO vpdsample_books VALUES(10005,'Country Driving','Peter Hessler');
INSERT INTO vpdsample_books VALUES(10006,'Life without limits','Nick Vujicic');
commit;
user_name VARCHAR2(20),
user_privilege NUMBER
);
--
INSERT INTO vpdsample_users VALUES('Jack',1);
INSERT INTO vpdsample_users VALUES('Rose',2);
COMMIT;
privilege_id NUMBER,
object_id NUMBER
);
--
INSERT INTO vpdsample_privileges VALUES(1,10002);
INSERT INTO vpdsample_privileges VALUES(1,10005);
INSERT INTO vpdsample_privileges VALUES(2,10003);
INSERT INTO vpdsample_privileges VALUES(2,10006);
COMMIT;
(这个表存储每个权限ID对应的权限信息,即对哪些对象(服装或图书)有权限。)
第二步,我们要创建一个context(实际上是【context名称空间】)。可以简单地把context理解为一个定义在内存中的容器,在此容器中我们可以定义若干个键值对,这些键值对可以在一定的范围内被共享(比如同一个session中,或者同一个Oracle实例中)
首先,使用system用户登录,赋予创建者相应的权限:
grant create any context to user1;
然后通过:
CREATE OR REPLACE CONTEXT VPD USING pkg_vpdsample ACCESSED GLOBALLY;
这里我们创建了一个叫’vpd’的context,’using’后面的是一个PLSQL package的名字,出于安全性考虑,Oracle需要你在创建context时指定一个包名,表示后续对些context的修改只能通过此包中的存储过程进行修改,不能通过dbms_session.set_context()直接进行修改。创建context时,package不存在并不会导致编译错误。
最后’accessed globally’是一个可选项,如果未添加此项,表示此context使用范围是某一session;如果指定了此项,则表示该context可以在整个数据库实例范围内共享。
欲删除context同样需要赋予相应的权限:
grant drop any context to user1;
drop context VPD;
第三步,建立一个package(即上面的pkg_vpdsample),包中的各个函数及存储过程的作用会随后逐一给出:
IS
PROCEDURE enable_vpd;
PROCEDURE disable_vpd;
PROCEDURE set_context(p_user_name IN VARCHAR2);
FUNCTION gen_vpd_predicate(p_column_name IN VARCHAR2) RETURN VARCHAR2;
FUNCTION apply_vpd_clothing(p1 in varchar2,p2 in varchar2) RETURN VARCHAR2;
FUNCTION apply_vpd_books(p1 in varchar2,p2 in varchar2) RETURN VARCHAR2;
END;
CREATE OR REPLACE PACKAGE BODY pkg_vpdsample IS
PROCEDURE enable_vpd IS
BEGIN
DBMS_SESSION.set_context(namespace => 'VPD',
attribute => 'ENABLE',
value => '1');
END;
/*======================*/
PROCEDURE disable_vpd IS
BEGIN
DBMS_SESSION.set_context(namespace => 'VPD',
attribute => 'ENABLE',
value => '0');
END;
/*======================*/
PROCEDURE set_context(p_user_name IN VARCHAR2) IS
l_privilege VARCHAR2(10);
BEGIN
SELECT user_privilege
INTO l_privilege
FROM vpdsample_users
WHERE user_name = p_user_name;
DBMS_SESSION.set_identifier(client_id => l_privilege);
END;
/*======================*/
FUNCTION gen_vpd_predicate(p_column_name IN VARCHAR2) RETURN VARCHAR2 IS
l_vpd_flag VARCHAR2(1);
l_privilege VARCHAR2(10);
BEGIN
l_vpd_flag := NVL(SYS_CONTEXT('VPD', 'ENABLE'), '0');
IF l_vpd_flag = 0 THEN
RETURN NULL;
ELSE
l_privilege := SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER');
IF l_privilege IS NULL THEN
RETURN '1=2';
ELSE
RETURN p_column_name || ' IN (SELECT object_id FROM vpdsample_privileges WHERE privilege_id=' || l_privilege || ')';
END IF;
END IF;
END;
/*======================*/
FUNCTION apply_vpd_clothing(p1 in varchar2, p2 in varchar2) RETURN VARCHAR2 IS
BEGIN
RETURN gen_vpd_predicate('clothing_id');
END;
/*======================*/
FUNCTION apply_vpd_books(p1 in varchar2, p2 in varchar2) RETURN VARCHAR2 IS
BEGIN
RETURN gen_vpd_predicate('book_id');
END;
END;
enable_vpd,disable_vpd:这两个存储过程用于设置context vpd下的一个自定义属性’enable’,1表是启用vpd,0表禁用vpd,由于我们在创建此context时指定了accessed globally,所以这些属性是可以被跨session访问的。这两个存储过程调用了dbms_session,因此需要被赋予相应的权限:grant execute on dbms_session to user1;
set_context:上面我们提到了context,并且在enable_vpd与disable_vpd中使用了dbms_session.set_context来设置自定义的属性,其实除了这样自定义的context外,Oracle还提供了预定义的context ’userenv’,该名称空间下有若干预定义的属性,比如’client_identifier’,设置此属性我们不使用set_context,而是使用dbms_session.set_identifier()。
本例中此存储过程根据传入的用户名,查找到该用户的权限ID,并将此ID作为client_identifier保存到context ’userenv’中,以便后续使用。
在实际应用中,此存储过程应该是由外部的应用程序调用的,应用程序可以在登录验证完成后,调用此存储过程写入context。
gen_vpd_predicate:根据传入的字段名称生成一个语法正确的where子句。这里首先判断了是否启用了vpd,未设置vpd.enable属性的也认为的已经启用了。随后判断是否已设置userenv.client_identifier属性,如果未设置,则返回一个始终为false的where子句以防止用户查看数据。最后生成的语句中,根据取到的client_identifier(即用户的权限ID)查找权限表vpdsample_privileges得到该用户有权限的所有对象ID。
apply_vpd_clothing, apply_vpd_books:由名字可见,这两个函数将被用于两个不同的表,因为服装表与图书表所用于权限验证的字段有着不同的名字。另外,大家可以看到这两个将被用于vpd的函数都有两个看似没用的参数p1与p2,这是vpd接口的要求,第一个用于接收schema名,第二个用于接收table/view/synonym名称,我们定义函数必须符合接口的要求。当然除了p1,p2外,你可以有自己额外的参数。
一切准备完毕,开始调用Oracle提供的dbms_rls包应用vpd策略,在调用之前,需要赋予用户相应权限:
grant execute on dbms_rls to user1;
然后:
dbms_rls.add_policy(object_name => 'VPDSAMPLE_CLOTHING',policy_name => 'POL_CLOTHING',policy_function => 'PKG_VPDSAMPLE.APPLY_VPD_CLOTHING');
dbms_rls.add_policy(object_name => 'VPDSAMPLE_BOOKS',policy_name => 'pol_books',policy_function => 'pkg_vpdsample.apply_vpd_books');
end;
其中policy_name可以自定义。
随后我们启用vpd:
pkg_vpdsample.enable_vpd;
end;
(需要说明的是,DBMS_RLS包本身有ENABLE_POLICY()方法用于启用或禁用一个vpd策略,但它只能一次启用/禁用一张表上的一个vpd策略,为了一次性启用/禁用所有表上的vpd策略,可以采取类似上面的做法。)
并设置context:
pkg_vpdsample.set_context('Jack');
end;
随后我们查询vpdsample_clothing表,只返回了ID为10002的服装信息;查询vpdsample_books也类似,只返回了ID为10005的图书信息。
可以更换用户:
pkg_vpdsample.set_context('Rose');
end;
删除vpd策略则使用:
dbms_rls.drop_policy(object_name => 'VPDSAMPLE_CLOTHING',policy_name => 'POL_CLOTHING');
dbms_rls.drop_policy(object_name => 'VPDSAMPLE_BOOKS',policy_name => 'pol_books');
end;
![]() |
关注作者:欢迎扫码关注公众号「后厂村思维导图馆」,获取本人自建的免费ChatGPT跳板地址,长期有效。 原文链接:https://www.cnblogs.com/morvenhuang/archive/2011/08/23/2151030.html 版权声明:本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须在文章页面给出原文链接,否则保留追究法律责任的权利。 |