Oracle vs PostgreSQL DBA（21）- Oracle VPD

来源：这里教程网时间：2026-03-14 19:44:13 作者：

本节简单介绍了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>
```