Oracle vs PostgreSQL DBA（21）- Oracle VPD

function
用于產(chǎn)生where子句（謂詞）
輸入?yún)?shù)：schema、object name
輸出參數(shù)：where子句（有效的）

Configuring an Oracle Virtual Private Database Policy

What
function需與object進(jìn)行綁定，可通過(guò)配置policy實(shí)現(xiàn)綁定。
policy本身用于管理VPD function，同時(shí)可以進(jìn)行精細(xì)化訪問(wèn)控制，比如指定SQL語(yǔ)句類(lèi)型或者策略影響的特定列。
Oracle提供了DBMS_RLS來(lái)進(jìn)行策略管理：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;
/

— 設(shè)置敏感列輸出
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

創(chuàng)建數(shù)據(jù)表，設(shè)定謂詞為：username=’測(cè)試’

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,'測(cè)試','測(cè)試名稱1');
insert into t_vpd_1(id,username,name) values(4,'測(cè)試','測(cè)試名稱2');
commit;

創(chuàng)建函數(shù)

CREATE OR REPLACE FUNCTION func_testvpd_1( 
  schemaname IN VARCHAR2,
  tablename  IN VARCHAR2
 )
 RETURN VARCHAR2
 IS
  ret VARCHAR2 (400);
 BEGIN
  ret := 'username = ''測(cè)試''';
  RETURN ret;
 END func_testvpd_1;
/

創(chuàng)建策略(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;
/

測(cè)試策略

-- 查詢
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.

創(chuàng)建策略(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;
/

測(cè)試策略

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,'測(cè)試','測(cè)試名稱1');
insert into t_vpd_1(id,username,name) values(4,'測(cè)試','測(cè)試名稱2');
commit;
-- 查詢
TEST-orcl@DESKTOP-V430TU3>select * from t_vpd_1;
        ID USERNAME             NAME
---------- -------------------- ------------------------------
         3 測(cè)試                 測(cè)試名稱1
         4 測(cè)試                 測(cè)試名稱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>