查詢分層 RBAC 場景

我有一個帶有分層基於角色的訪問控制 (RBAC) 數據模型的 SQLite 數據庫。

在此模型中，使用者被授予對各種資源執行某些操作的權限，這些權限可以是直接的，也可以是被分配了權限的一個或多個角色的成員。此外，可以將角色分配給其他角色，從而可以定義角色層次結構。

架構大致如下：

（來自如何設計基於分層角色的訪問控制系統 - StackOverflow）

除了 Role_Role 連接表而不是 Roles 表上的“父”列之外，類似於以下內容：

（來自與上圖相同的文章）

連接表上也沒有“授予”列；連接表中存在記錄意味著它被授予。

獲取給定使用者的所有權限的查詢是什麼樣的？我假設會涉及使用遞歸 CTE，但我不確定。

**編輯：**我有以下遞歸 CTE 用於獲取其他角色繼承的角色：

WITH RECURSIVE
 deep_roles(role_id, parent_id, depth) AS (
   SELECT role_id, parent_id, 1 FROM role_roles WHERE role_id = ?
   UNION ALL
   SELECT rr.role_id, rr.parent_id, dr.depth+1
     FROM deep_roles dr, role_roles AS rr
    WHERE rr.role_id=dr.parent_id
      AND dr.depth < 10
 )
SELECT DISTINCT role_id, parent_id FROM deep_roles;

不確定這是否是我需要的唯一 CTE，但這是一個開始。

這就是我想出的——感謝**@CL。**提示。我把它變成了一個視圖，所以我可以針對特定使用者查詢它。

CREATE VIEW user_permissions_deep AS
SELECT DISTINCT user_id, permission_id
FROM (
   SELECT urd.user_id user_id, pr.permission_id permission_id, urd.role_id role_id, urd.parent_id parent_role
   FROM (
       WITH RECURSIVE
         deep_roles(user_id, role_id, parent_id, depth) AS (
           SELECT ru.user_id, rr.role_id, rr.parent_id, 0 depth
           FROM role_roles rr
           INNER JOIN role_users ru ON rr.role_id = ru.role_id
           UNION
           SELECT dr.user_id, rr.role_id, rr.parent_id, dr.depth+1
           FROM deep_roles dr, role_roles rr
           WHERE rr.role_id=dr.parent_id
           AND dr.depth < 10
         )
       SELECT user_id, role_id, parent_id FROM deep_roles
       WHERE depth > 0
       UNION
       SELECT user_id, role_id, null parent_id
       FROM role_users
   ) AS urd
   INNER JOIN permission_roles pr
       ON pr.role_id = urd.role_id
       OR pr.role_id = urd.parent_id
   UNION
   SELECT user_id, permission_id, null role_id, null parent_id
   FROM permission_users
) AS upd
INNER JOIN permission p ON upd.permission_id = p.id;

我發現我必須保持深度檢查，否則如果角色繼承鏈中有循環，循環將永遠不會退出。

如果有人對如何改進有任何建議，請告訴我！

引用自：https://dba.stackexchange.com/questions/102104