时间戳

首页 > 解决方案 > 根据用户授予对表中数据的访问权限的更好方法？

sql - 根据用户授予对表中数据的访问权限的更好方法？

问题描述

我正在尝试为 API 设计一个系统，该系统根据与组表 Group 相关的权限表 Permissions 授予用户对数据表 Data 的访问权限。当用户（从数据表）发出数据请求时，我的 API 应仅根据数据表中已授予他们查看的列中的值从数据表返回行。

默认情况下，当通过我的 API 请求数据时，用户将无权访问任何行。但是，我想根据列中的值授予对数据的访问权限。

例如，如果我的数据表包含有关新闻文章的信息并具有列 title、news_source、posted_date 和其他类似列

  id | title    |  news_source          | posted_date | ...
-----+----------+-----------------------+-------------+------
 1   | ...      | NYTimes               | 2019-12-30  |  
 2   | ...      | BBC                   | 2019-12-30  |  
 3   | ...      | BBC                   | 2019-12-30  |
 4   | ...      | Washington Post       | 2019-12-30  |
 5   | ...      | NYTimes               | 2019-12-30  |
 6   | ...      | NYTimes               | 2020-01-01  |
 7   | ...      | Boston Globe          | 2020-01-01  |

在此示例中，我想授予组访问权限以仅从 NYTimes 获取数据，这些数据在 2020 年 1 月 1 日之后发布，等等...

为此，我实现了下面的架构

+-----+        +--------------+
|Group|<-------|Permission    |
+-----+        +--------------+
|name |        |group_id      |
|...  |        |column_name   |
+-----+        |text_value    |
               |date_value    |
               +--------------+

对于 Group，name 只是组的名称，椭圆表示其他一些不相关的列。在权限中，我有组的外键 (group_id)、我正在访问的数据表中列的名称 (column_name)，以及我授予访问权限的值 (text_value 或 date_value，具体取决于我的列) '我参考）。

现在，当用户请求数据时，我运行此 SQL 以应用权限（如果用户的组的 id = 1）。

SELECT * FROM Data d
    INNER JOIN Permission p1 ON p1.group_id = 1 AND p1.column_name = 'news_source' AND p1.text_value = d.news_source
    INNER JOIN Permission p2 ON p2.group_id = 1 AND p2.column_name = 'posted_date' AND p2.date_value >= d.posted_date;

这会起作用，但我想知道是否有更好的更有条理的方式来解决这个问题。我觉得这个模型在具有相同权限的多个组中会有很多冗余。

标签： sqlpostgresqlperformancedatabase-designdatabase-normalization

解决方案

推荐阅读