【REST2SQL】14 基于角色的数据权限设计与实现

【REST2SQL】01RDB关系型数据库REST初设计
【REST2SQL】02 GO连接Oracle数据库
【REST2SQL】03 GO读取JSON文件
【REST2SQL】04 REST2SQL第一版Oracle版实现
【REST2SQL】05 GO 操作 达梦 数据库
【REST2SQL】06 GO 跨包接口重构代码
【REST2SQL】07 GO 操作 Mysql 数据库
【REST2SQL】08 日志重构增加输出到文件log.txt
【REST2SQL】09 给Go的可执行文件exe加图标和版本信息等
【REST2SQL】10 REST2SQL操作指南
【REST2SQL】11 基于jwt-go生成token与验证
【REST2SQL】12 REST2SQL增加Token生成和验证
【REST2SQL】13 用户角色功能权限设计

---

0 前言废话

0.1 常见的权限管理模型

• ACL ACL的全称是 Access Control List (访问控制列表)
  ACL 是最简单的权限管理模型之一。它基于对象与主体之间的关系来控制访问权限。ACL 将权限直接与用户或用户组相关联，管理员直接给用户授予某些权限即可。
  这种模型适用于小型和简单系统，其中权限控制较为简单，并且角色和权限的变化较少。

• RBAC(Role-Based Access Control,基于角色的访问控制)
  RBAC 是应用最广泛的权限管理模型之一。它通过定义角色和角色的权限集合来管理访问控制。用户被分配到角色，角色与权限相关联，从而精确地控制用户对系统资源的访问。
  RBAC 适用于大型系统，特别是那些需要灵活、可扩展的权限管理的场景。使用 RBAC 可以简化权限管理的复杂性并提高系统的安全性。
  RBAC权限管理的模式，最适合公司内部的管理系统，不适合对外互联网用户的系统。

• ABAC(Attribute-Based Access Control,基于属性的访问控制)
  ABAC(Attribute-Based Access Control,基于属性的访问控制),又称为PBAC(Policy-Based Access Control,基于策略的访问控制),或CBAC(Claims-Based Access Control,基于声明的访问控制)。ABAC 是一种基于属性的权限管理模型，它根据多个属性（如用户属性、环境条件、时间等）来进行访问控制决策。ABAC 通过定义策略来决定用户是否有权访问特定的资源。
  这种模型适合于需要更细粒度、动态和灵活的访问控制的场景。ABAC 在复杂的环境中可以提供高度的可配置性和可扩展性。

0.2 数据权限分级

• 系统级
• 对象级，表或视图
• 行级，表或视图的行
• -列级，表或视图的具体字段

1 数据权限设计

权限管理的核心是角色，角色上接用户，下接功能，功能关联数据。
权限管理的5大实体 ： 用户-角色=页面#数据，角色-数据对象关系
实体之间的关系：用户1对多角色，角色1对多页面，页面1对多数据，角色1对多数据。
实体及关系图如下：

此数据权限设计兼顾了灵活与通用。

• 支持一个用户多角色，多角色都关联同一数据对象时，用户的数据权限是多个角色数据权限的并集。比如一个用户 user1 分配了2个角色 role1 和 role2，则user1 可以查阅 role1 和 role2 所有数据。
• 根据角色的功能权限自动获取相关数据对象（视图或表），并在角色上定义数据权限约束 Where条件。

1.1 权限实体(表)设计

• 用户表 s_user (p_id, s_name, s_passw…)
• 角色表 s_role (p_id, s_name…)
• 页面表 s_menu(p_id, s_name,s_page…)
• 数据(对象)视图 user_tabs_views
• 角色-数据权限表s_role_data(pf_role,pf_data,s_where)
  数据对象包括表和视图。

-- Oracle 数据库的表和视图
CREATE OR REPLACE VIEW USER_TABS_VIEWS AS
SELECT TABLE_NAME,TABLE_TYPE,COMMENTS,tab_cnt(table_name) as rowcount
FROM user_tab_comments
order by table_type,table_name;
comment on table USER_TABS_VIEWS is '用户表及视图';

1.2 权限关系(表)设计

• 用户角色关系表 s_user_role (pf_user,pf_role…)
• 角色功能权限关系表 s_role_menu (pf_role,pf_menu…)
• 页面数据对象关系表 s_menu_data (pf_menu,pf_data…)
• 角色数据对象权限关系表 s_role_data (PF_ROLE,PF_DATA,s_where,s_note…)
  角色项下所有功能页面关联的数据对象的集合（页面和数据是多对多的关系，此集合不去除了重复的数据对象）

1.3 角色数据权限视图设计

其它视图参阅 【REST2SQL】13 用户角色功能权限设计

• 角色关联的数据对象视图，就是角色功能权限用到的数据对象。

	create or replace view role_data_v as
	select distinct m.pf_role, d.pf_data
	from S_MENU_DATA d
	left join s_role_menu m on m.pf_menu = d.pf_menu
	where m.pf_role is not null;
	comment on table ROLE_DATA_V is '角色关联的数据对象视图';

• 角色数据权限重置连接表，就是角色功能权限或功能数据对象发生变化时，角色当前数据权限和应该拥有的数据权限对照视图

create or replace view role_data_join_v as
select "CROLE","CDATA","HROLE","HDATA"
from ( select pf_role as crole, pf_data as cdata from S_ROLE_DATA  ) c
full outer join (select pf_role as hrole, pf_data as hdata from role_data_v  ) h
 on h.hdata = c.cdata  and h.hrole = c.crole;
comment on table ROLE_DATA_JOIN_V is '角色数据权限重置连接表';

1.4 角色数据权限更新的存储过程

角色-功能权限或功能-数据对象发生变化时，执行此存储过程变更角色的数据权限，增加或删除角色的数据对象权限。

	CREATE OR REPLACE PROCEDURE reset_role_data
	IS
	--PRAGMA AUTONOMOUS_TRANSACTION;
	BEGIN
	  
	    -- 这里放置处理数据的代码
	    NULL; -- 示例中，我们不执行任何操作
	
	    for rec in ( select crole,cdata,hrole,hdata from role_data_join_v ) loop
	      if rec.crole is null then
	        --当前角色插入数据对象
	        dbms_output.put_line('A 当前角色增加数据对象...');
	        insert into s_role_data (pf_role,pf_data) values (rec.hrole,rec.hdata);
	      end if;
	
	       if rec.hrole is null then
	         --当前角色删除数据对象
	         dbms_output.put_line('B 当前角色删除数据对象...');
	         delete from s_role_data where pf_role = rec.crole and pf_data = rec.cdata;
	      end if;
	    end loop;
	
	    --提交数据操作数据
	    --commit;
	
	END;

1.5 触发器设计

• 功能s_menu_data数据对象变化时触发器，功能页面数据对象变化时触发

	create or replace trigger s_menu_data_trigger
	  FOR INSERT OR DELETE on s_menu_data
	  compound TRIGGER
	/*
	  BEFORE STATEMENT IS -- 语句执行前触发（表级）
	  BEGIN
	    DBMS_OUTPUT.put_line('1、BEFORE STATEMENT .');
	  END BEFORE STATEMENT;
	
	  BEFORE EACH ROW IS -- 语句执行前触发（行级）
	  BEGIN
	    DBMS_OUTPUT.put_line('2、BEFORE EACH ROW .');
	  END BEFORE EACH ROW;
	
	  AFTER EACH ROW IS -- 语句执行后触发（行级）
	  BEGIN
	    DBMS_OUTPUT.put_line('3、AFTER EACH ROW .');
	  END AFTER EACH ROW;
	*/
	  AFTER STATEMENT IS -- 语句执行后触发（表级）
	  BEGIN
	    DBMS_OUTPUT.put_line('4、AFTER STATEMENT .');
	    reset_role_data;
	  END AFTER STATEMENT;
	
	end s_menu_data_trigger;

• 角色s_role_menu功能权限变更时触发器，角色功能权限变化时触发

	create or replace trigger s_role_menu_trigger
	  FOR INSERT OR DELETE on s_role_menu
	  compound TRIGGER
	
	/*
	  BEFORE STATEMENT IS -- 语句执行前触发（表级）
	  BEGIN
	    DBMS_OUTPUT.put_line('1、BEFORE STATEMENT .');
	  END BEFORE STATEMENT;
	
	  BEFORE EACH ROW IS -- 语句执行前触发（行级）
	  BEGIN
	    DBMS_OUTPUT.put_line('2、BEFORE EACH ROW .');
	  END BEFORE EACH ROW;
	
	  AFTER EACH ROW IS -- 语句执行后触发（行级）
	  BEGIN
	    DBMS_OUTPUT.put_line('3、AFTER EACH ROW .');
	  END AFTER EACH ROW;
	*/
	
	  AFTER STATEMENT IS -- 语句执行后触发（表级）
	  BEGIN
	    DBMS_OUTPUT.put_line('4、AFTER STATEMENT .');
	    reset_role_data;
	  END AFTER STATEMENT;
	
	end s_role_menu_trigger;

1.6 用户-数据权限

• 用户-角色关系视图

create or replace view user_role_v as
select r.p_id as r_id,r.s_name ,s.pf_user,u.p_id as u_id,
  decode(length(s.pf_role),4,1,0) as b_yn
 from s_role r
cross join s_user u --先做一个用户与角色的笛卡尔交叉，再连接用户角色表
left join s_user_role s on s.pf_user = u.p_id and s.pf_role = r.p_id
order by u.p_id,r.p_id
;
comment on table USER_ROLE_V is '用户角色勾选';

• 用户-数据视图

create or replace view user_data_v as
select distinct u.u_id, u.r_id, --多角色同一个数据对象，用户的数据权限用 or 连接
       d.pf_data, d.s_where
  from user_role_v u --用户-角色关系视图
  left join s_role_data d -- 角色-数据表
    on d.pf_role = u.r_id
 where d.pf_data is not null and u.pf_user is not null
 ;
 
 comment on table USER_data_V is '用户-数据权限';

1.7 用户-数据权限字符串生成函数

根据用 用户 userid 和 数据对象 dataid 查询user_data_v 视图生成数据权限字符串，此字符串作为 sql 语句的 Where的一部分。

CREATE OR REPLACE FUNCTION GET_DATA_WHERE (userid varchar2,dataid varchar2) RETURN varchar2 is
  -- 获取userid的dataid的数据权限Where字符串
  where_str varchar2(512) := ' ';
BEGIN

  for curs in (select s_where,r_id from user_data_v where u_id = userid and pf_data = upper(dataid) ) loop
    --DBMS_OUTPUT.PUT_LINE(curs.r_id || ':' || curs.s_where);
    -- 循环组合where字符串,用 or 连接
    if curs.s_where is null then
      --定义了数据对象，权限where为空的,用 1=1 表示没有数据权限控制
       where_str := ' ';
    else
       where_str := where_str || curs.s_where || ' or ';
    end if;
  end loop;

  if instr(where_str,'or') > 5  then
    where_str := substr(where_str,1,length(where_str) - 3); --去除最后的 or
  else
    --没有定义的数据对象(游标返回行数为0)返回null
    where_str := null;
  end if;
  
  return where_str;
END GET_DATA_WHERE;

2 数据权限的后端实现

基于以上的用户数据权限设计，后端验证 token 时返回用户的帐号 userid ,根据用户的 userid 和 数据对象 dataid 显示数据的访问范围。

2.1 创建数据权限函数 getDataWhere

// 获取用户 userid 的数据对象 dataid 的数据权限 where 字符串
func getDataWhere(userid string, dataid string) string {
	dataID := strings.ToUpper(dataid) // 数据对象名改为大写
	sSql := "select get_data_where('" + userid + "','" + dataID + "') as s_where from dual"
	result := Icrud.SelectData(sSql)

	var swhere []map[string]string
	err := json.Unmarshal([]byte(result), &swhere)
	if err != nil {
		panic(err)
	}
	//fmt.Println(swhere)
	s_where := swhere[0]["S_WHERE"]

	return s_where
}

2.2 重构token验证函数

增加返回 token 里的 userid

// token 验证函数
func vToken(w http.ResponseWriter, tokenString string) (string, int) {
	tokenmap := make(map[string]interface{})
	tokenmap = token.ValidateTokenHandler(w, tokenString)
	if tokenmap["status"] != http.StatusOK {
		// 抛出错误信息
		httpResWriter(w, tokenmap)
		return "", 401
	}
	// 返回userid 和 200
	return tokenmap["userid"].(string), 200
}

2.3 Token验证时保存Userid

REST请求时验证token，代码片段：

switch req["RESTorSQL"] {
	case "REST":
		// 是否配置了需要token验证
		if config.Conf.OpenToken {
			// token有效性验证
			tokenString := req["Authorization"].(string)
			userid, status := vToken(w, tokenString)

			if status != 200 {
				return
			}
			req["Userid"] = userid //token里的userid
		}

2.4 Where 条件增加数据权限

// 根据 userid 和 数据对象 加数据权限，数据库函数 get_data_where实现，返回的where 用 and 连接
	if config.Conf.OpenToken {
		userid := req["Userid"].(string)

		dataWhere := getDataWhere(userid, resName) // 数据权限 where
		urlWhere := qry["Where"]                   // url 上的 where
		if len(dataWhere) > 3 {
			if len(urlWhere) > 3 {
				// 如果有数据权限约束就加上
				qry["Where"] = qry["Where"] + " and (" + dataWhere + ")"
			} else {
				qry["Where"] = dataWhere
			}
		}
	}

3 前端实现

3.1 页面-数据对象设置

创建一个页面-数据对象设置页面即可。这个需要开发人员来设置，只有开发人员才知道页面用到那些数据，当然不进行数据权限约束的数据对象是不需要设置的。

3.2 角色-数据对象权限设置

创建角色-数据对象权限设置页面即可。就是定义sql查询语句的where条件，这个也由开发人员设置。

3.3 用户-数据权限查询

创建查询用户的数据权限页面

4 实操演练

4.1 设置页面-数据对象

4.2 设置角色-数据权限

1. 新建一个角色 YSSH
2. 设置功能权限
   
3. 设置角色-数据权限
   

4.3 查询用户-数据权限

4. 设置用户-角色
5. 查询用户-数据权限
   

4.4 用户登录

4.5 企业管输需求

如上图，列表只显示本企业的数据，企业ID的列选也只有本企业可选了。
后台的SQL查询语句类似如下：

select *
  from dd_xuqiu
 where to_char(date_t, 'YYYY-MM-DD') >= '2022-07-10' 
   and qy_id = nvl('', qy_id)
   and yz_id = nvl('', yz_id)
   and (qy_id = 'Q0010')

其中 qy_id = ‘Q0010’ 就是权限控制条件。

5 跋尾

此数据权限基于管输管理平台设计，管输平台主要角色有总部、企业、油库、港口、货代等，不用角色的数据权限都不同，还有很大差别，此设计完全满足数据权限的管理。如果参与的主体很多年的话，可能会有角色组合爆炸。
对于有层级关系的数据也基本能实现数据权限的控制。不需要构建组织机构表，但要用角色实现组织机构关系。

---

本文完