oracle行列级权限控制（VPD）

oracle行列级权限控制（VPD）

一、背景

在数据访问中一般的访问权限控制是通过创建视图实现的，基于某个基础表创建不同的视图，将视图的查询权限赋予特定的数据查询方，使得不同的用户只能访问到特定的数据。

那么有没有办法让不同的用户访问同一张表的时候，只能看到自己权限范围内的数据，不通过视图实现，显然是有的。

虚拟专用数据库（VPD）指的是，通过在数据库里进行配置，从而让不同的用户只能查看某 个表里的部分数据。VPD分为以下两个级别。

• 行级别：在该级别下，可以控制某些用户只能查看到某些数据行。比如，对于销售数据表sales 来说，每个销售人员只能检索出他自己的销售数据，不能查询其他销售人员的销售数据。

• 列级别：在该级别下，可以控制某些用户不能检索某个表的某个列的值。比如用户HR 下的 employees 表中，含有工资（salary）列，由于该列比较敏感，因此不让其他用户查询该列的值。 其他用户检索该列时，会发现其值全都为空（null ）。

二、行级权限控制

1、创建测试表

CREATE TABLE PDM.TEST_VPD(
CAN_SELECT NUMBER
,NO_SELECT NUMBER
);

2、插入测试数据

INSERT INTO PDM.TEST_VPD VALUES(1，1);
INSERT INTO PDM.TEST_VPD VALUES(2，1);
INSERT INTO PDM.TEST_VPD VALUES(1，2);
INSERT INTO PDM.TEST_VPD VALUES(2，2); 

3、创建政策函数

创建策略函数，作为行级权限的逻辑判断。当用户访问该表时获取当前用户的信息，用来判断外部访问用户，根据访问的用户不同设置不同的访问权限 限制在ADM_NRT访问PDM.TEST_VPD表时，默认添加对用户完全透明的访问条件NO_SELECT = 2

CREATE OR REPLACE FUNCTION F_LIMITED_QUERY_LINE(S_CHEMA IN VARCHAR2
,S_OBJECT IN VARCHAR2)
RETURN VARCHAR2
AS
VAR_O_RE VARCHAR2(50);
VC_USERID VARCHAR2(100);
BEGIN
     --获取上下文信息
     SELECT SYS_CONTEXT(‘USERENV’,’SESSION_USER’)
     INTO VC_USERID
FROM DUAL;
    --做行级访问限制
     IF TRIM(VC_USERID) = 'ADM_NRT' THEN
        VAR_O_RE := 'NO_SELECT = 2';
     ELSE
        VAR_O_RE := NULL;
     END IF;
     
     RETURN VAR_O_RE;
END;
/

4、定义FGAC规则

限制访问PDM用户下的表TEST_VPD，政策名称为LIMITED_QUERY_LINE，政策函数为PDM用户下的F_LIMITED_QUERY_LINE，政策类型为SELECT类型，启用该FGAC规则

BEGIN
    DBMS_RLS.ADD_POLICY(OBJECT_SCHEMA => 'PDM'
        ,OBJECT_NAME => 'TEST_VPD'
        ,POLICY_NAME => 'LIMITED_QUERY_LINE'
        ,FUNCTION_SCHEMA => 'PDM' 
        ,POLICY_FUNCTION => 'F_LIMITED_QUERY_LINE'
        ,STATEMENT_TYPES => 'SELECT'
        ,ENABLE => TRUE);
END;
/

5、查询策略

SELECT POLICY_NAME,SEL,INS,UPD,DEL,IDX,CHK_OPTION,ENABLE 
FROM USER_POLICIES;

6、去掉访问限制

BEGIN
    DBMS_RLS.DROP_POLICY(
    	OBJECT_SCHEMA => 'PDM'
       ,OBJECT_NAME => 'TEST_VPD'
       ,POLICY_NAME => 'LIMITED_QUERY_LINE'
    );
END;
/

三、列级权限控制

1、创建政策函数

CREATE OR REPLACE FUNCTION F_LIMITED_QUERY_LINE(S_CHEMA IN VARCHAR2
,S_OBJECT IN VARCHAR2)
RETURN VARCHAR2
AS
VAR_O_RE VARCHAR2(50);
VC_USERID VARCHAR2(100);
BEGIN
     --获取上下文信息
     SELECT SYS_CONTEXT(‘USERENV’,’SESSION_USER’)
     INTO VC_USERID
FROM DUAL;
    --做行级访问限制
     IF TRIM(VC_USERID) = 'ADM_NRT' THEN
        VAR_O_RE := '1 = 2';
     ELSE
        VAR_O_RE := NULL;
     END IF;
     
     RETURN VAR_O_RE;
END;
/

2、定义FGAC规则

SEC_RELEVANT_COLS 要屏蔽的列（用逗号隔开）

SEC_RELEVANT_COLS_OPT =>DBMS_RLS.ALL_ROWS说明对所有记录屏蔽该列

BEGIN
    DBMS_RLS.ADD_POLICY(OBJECT_SCHEMA => 'PDM'
        ,OBJECT_NAME => 'TEST_VPD'
        ,POLICY_NAME => 'LIMITED_QUERY_COL'
        ,FUNCTION_SCHEMA => 'PDM'
        ,POLICY_FUNCTION => 'F_LIMITED_QUERY_LINE'
        ,STATEMENT_TYPES => 'SELECT'
        ,SEC_RELEVANT_COLS =>'CAN_SELECT'
        ,SEC_RELEVANT_COLS_OPT =>DBMS_RLS.ALL_ROWS
        ,ENABLE => TRUE);
END;
/