本节简单介绍了Oracle VPD。VPD用于实现精细化的权限管理。
About Oracle Virtual Private Database
What?
VPD的做法:VPD自动添加where子句到SQL语句上
影响的对象:table、view、synonym
涉及的语句:select、insert、update、index和delete
不支持:DDL
基本做法示例:
Benefits
Using Oracle Virtual Private Database with an Application Context
function
What
How
CREATE OR REPLACE FUNCTION hide_sal_comm (
BEGIN
— 设置敏感列输出
创建数据表,设定谓词为:username=’测试’ 创建函数 创建策略(select) 测试策略 创建策略(select、insert、update、delete) 测试策略
SELECT
FROM OE.ORDERS;
—>应用VPD
SELECT FROM OE.ORDERS
WHERE SALES_REP_ID = 159;
Security:不管用户如何访问数据,都可以确保精细化的访问控制策略得到执行。
Simplicity:只需要在table、view上操作一次即可
Flexibility:select、insert、update、delete都可以有自己的策略
SELECT
FROM orders_tab
—>
SELECT FROM orders_tab
WHERE custno = SYS_CONTEXT (‘order_entry’, ‘cust_num’);
Components of an Oracle Virtual Private Database Policy
用于产生where子句(谓词)
输入参数:schema、object name
输出参数:where子句(有效的)
Configuring an Oracle Virtual Private Database Policy
function需与object进行绑定,可通过配置policy实现绑定。
policy本身用于管理VPD function,同时可以进行精细化访问控制,比如指定SQL语句类型或者策略影响的特定列。
Oracle提供了DBMS_RLS来进行策略管理:ADD、DROP、ENABLE/DISABLE…
例子:
SELECT fname, lname, ssn FROM emp;
—>
SELECT fname, lname, ssn FROM emp
WHERE ssn = ‘my_ssn’;
v_schema IN VARCHAR2,
v_objname IN VARCHAR2)
RETURN VARCHAR2 AS
con VARCHAR2 (200);
BEGIN
con := ‘deptno=30’;
RETURN (con);
END hide_sal_comm;
/
DBMS_RLS.ADD_POLICY (
object_schema => ‘scott’,
object_name => ‘emp’,
policy_name => ‘hide_sal_policy’,
policy_function => ‘hide_sal_comm’,
sec_relevant_cols => ‘sal,comm’);
END;
/
BEGIN
DBMS_RLS.ADD_POLICY(
object_schema => ‘scott’,
object_name => ‘emp’,
policy_name => ‘hide_sal_policy’,
policy_function => ‘hide_sal_comm’,
sec_relevant_cols =>’ sal,comm’,
sec_relevant_cols_opt => dbms_rls.ALL_ROWS);
END;
/
Tutorials
drop table t_vpd_1;
create table t_vpd_1(id int,username varchar2(20),name varchar2(30));
insert into t_vpd_1(id,username,name) values(1,'test','name1');
insert into t_vpd_1(id,username,name) values(2,'张三','name1');
insert into t_vpd_1(id,username,name) values(3,'测试','测试名称1');
insert into t_vpd_1(id,username,name) values(4,'测试','测试名称2');
commit;
CREATE OR REPLACE FUNCTION func_testvpd_1(
schemaname IN VARCHAR2,
tablename IN VARCHAR2
)
RETURN VARCHAR2
IS
ret VARCHAR2 (400);
BEGIN
ret := 'username = ''测试''';
RETURN ret;
END func_testvpd_1;
/
BEGIN
DBMS_RLS.DROP_POLICY (
object_schema => 'test',
object_name => 't_vpd_1',
policy_name => 'policy_t_vpd_1'
);
END;
/
BEGIN
DBMS_RLS.ADD_POLICY (
object_schema => 'test',
object_name => 't_vpd_1',
policy_name => 'policy_t_vpd_1',
function_schema => 'test',
policy_function => 'func_testvpd_1',
statement_types => 'select'
);
END;
/
-- 查询
select * from t_vpd_1;
-- 插入
TEST-orcl@DESKTOP-V430TU3>create table t_vpd_2 as select * from t_vpd_1 where 1=2;
Table created.
TEST-orcl@DESKTOP-V430TU3>insert into t_vpd_2(id,username,name) select * from t_vpd_1;
2 rows created.
TEST-orcl@DESKTOP-V430TU3>delete from t_vpd_1;
4 rows deleted.
TEST-orcl@DESKTOP-V430TU3>
TEST-orcl@DESKTOP-V430TU3>update t_vpd_1 set name = 'test';
0 rows updated.
BEGIN
DBMS_RLS.DROP_POLICY (
object_schema => 'test',
object_name => 't_vpd_1',
policy_name => 'policy_t_vpd_1'
);
END;
/
BEGIN
DBMS_RLS.ADD_POLICY (
object_schema => 'test',
object_name => 't_vpd_1',
policy_name => 'policy_t_vpd_1',
function_schema => 'test',
policy_function => 'func_testvpd_1',
statement_types => 'select,insert,update,delete'
);
END;
/
insert into t_vpd_1(id,username,name) values(1,'test','name1');
insert into t_vpd_1(id,username,name) values(2,'张三','name1');
insert into t_vpd_1(id,username,name) values(3,'测试','测试名称1');
insert into t_vpd_1(id,username,name) values(4,'测试','测试名称2');
commit;
-- 查询
TEST-orcl@DESKTOP-V430TU3>select * from t_vpd_1;
ID USERNAME NAME
---------- -------------------- ------------------------------
3 测试 测试名称1
4 测试 测试名称2
-- 插入
TEST-orcl@DESKTOP-V430TU3>drop table t_vpd_2 ;
Table dropped.
TEST-orcl@DESKTOP-V430TU3>create table t_vpd_2 as select * from t_vpd_1 where 1=2;
Table created.
TEST-orcl@DESKTOP-V430TU3>insert into t_vpd_2(id,username,name) select * from t_vpd_1;
2 rows created.
TEST-orcl@DESKTOP-V430TU3>update t_vpd_1 set name = 'test';
2 rows updated.
TEST-orcl@DESKTOP-V430TU3>delete from t_vpd_1;
2 rows deleted.
TEST-orcl@DESKTOP-V430TU3>
免责声明:
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
软考中级精品资料免费领
- 历年真题答案解析
- 备考技巧名师总结
- 高频考点精准押题
- 资料下载
- 历年真题
193.9 KB下载数265
191.63 KB下载数245
143.91 KB下载数1142
183.71 KB下载数642
644.84 KB下载数2755