本節簡單介紹了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>
```

Oracle vs PostgreSQL DBA（21）- Oracle VPD

相關文章