Oracle vs PostgreSQL DBA（21）- Oracle VPD

2024-04-02 19:55

短信预约 -IT技能 免费直播动态提醒

本节简单介绍了Oracle VPD。VPD用于实现精细化的权限管理。

About Oracle Virtual Private Database

What?
VPD的做法：VPD自动添加where子句到SQL语句上
影响的对象：table、view、synonym
涉及的语句：select、insert、update、index和delete
不支持：DDL

基本做法示例：
SELECT FROM OE.ORDERS;
—>应用VPD
SELECT FROM OE.ORDERS
WHERE SALES_REP_ID = 159;

Benefits
Security：不管用户如何访问数据，都可以确保精细化的访问控制策略得到执行。
Simplicity：只需要在table、view上操作一次即可
Flexibility：select、insert、update、delete都可以有自己的策略

Using Oracle Virtual Private Database with an Application Context
SELECT FROM orders_tab
—>
SELECT FROM orders_tab
WHERE custno = SYS_CONTEXT (‘order_entry’, ‘cust_num’);

Components of an Oracle Virtual Private Database Policy

function
用于产生where子句（谓词）
输入参数：schema、object name
输出参数：where子句（有效的）
Configuring an Oracle Virtual Private Database Policy

What
function需与object进行绑定，可通过配置policy实现绑定。
policy本身用于管理VPD function，同时可以进行精细化访问控制，比如指定SQL语句类型或者策略影响的特定列。
Oracle提供了DBMS_RLS来进行策略管理：ADD、DROP、ENABLE/DISABLE…
How
例子：
SELECT fname, lname, ssn FROM emp;
—>
SELECT fname, lname, ssn FROM emp
WHERE ssn = ‘my_ssn’;
CREATE OR REPLACE FUNCTION hide_sal_comm (
v_schema IN VARCHAR2,
v_objname IN VARCHAR2)
RETURN VARCHAR2 AS
con VARCHAR2 (200);
BEGIN
con := ‘deptno=30’;
RETURN (con);
END hide_sal_comm;
/
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’);
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

创建数据表，设定谓词为：username=’测试’

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; /

创建策略(select)

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.

创建策略(select、insert、update、delete)

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>