sql-server - 在 SQL Server 存储过程中组合查询

问题描述

我想在存储过程中结合用户及其角色的权限。为此，目前我正在运行许多查询。第一个获取用户拥有的角色。然后查询以获取每个角色的权限。然后查询以获取用户的权限。然后我将它们全部从代码中组合起来。我希望能够从存储过程中完成所有这些工作。我从未使用过存储过程，所以我什至不知道它是否可能。

希望这张图能帮助你理解我想要做什么。

我开始写一个，但它还没有接近完成或功能。

CREATE PROCEDURE get_privileges
    @userId INT
AS
    SET NOCOUNT ON;

    SELECT 
        role_id 
    FROM 
        user_role 
    INNER JOIN 
        role ON user_role.role_id = role.id 
    WHERE 
        user_id = @userId;

    -- For each role
    SELECT 
        privilege_id as id, name 
    FROM 
        role_privilege 
    INNER JOIN 
        privilege ON role_privilege.privilege_id = privilege.id 
    WHERE 
        role_id = ${roleId}

    SELECT 
        privilege_id as id, name 
    FROM 
        user_privilege 
    INNER JOIN 
        privilege ON user_privilege.privilege_id = privilege.id 
    WHERE 
        user_id = @userId;

我需要做什么才能完成该程序？

循环遍历第一个选择的结果。不知道如何做到这一点。
结合结果。发现这可以通过制作一个临时表然后将每个查询的结果集推送到它来完成。有没有更好的办法？

样品：

用户表

+----+----------+
| id | username |
+----+----------+
|  1 | caleb    |
+----+----------+

角色表

+------+----------+
| role |   name   |
+------+----------+
|    1 | admin    |
|    2 | standard |
+------+----------+

特权表

+-----------+---------------+
| privilege |     name      |
+-----------+---------------+
|         1 | CREATE_USER   |
|         2 | DELETE_USER   |
|         3 | ADMIN_VIEW    |
|         4 | STANDARD_VIEW |
|         5 | NOTHING       |
+-----------+---------------+

user_privilege 表

+---------+--------------+
| user_id | privilege_id |
+---------+--------------+
|       1 |            1 |
+---------+--------------+

用户角色表

+---------+---------+
| user_id | role_id |
+---------+---------+
|       1 |       1 |
+---------+---------+

角色特权表

+---------+--------------+
| role_id | privilege_id |
+---------+--------------+
|       1 |            2 |
|       1 |            3 |
|       2 |            4 |
+---------+--------------+

预期结果user_id = 1

+--------------+-------------+
| privilege_id |    name     |
+--------------+-------------+
|            1 | CREATE_USER |
|            2 | DELETE_USER |
|            3 | ADMIN_VIEW  |
+--------------+-------------+

标签： sql-serverstored-procedures

边注：

看？没有线交叉，+100 以提高可读性。

一种可能的解决方案（几乎是从自然语言到 SQL 的翻译）： 返回存在于用户权限列表或任何用户角色权限列表中的权限列表：

select *
from privilege p
where exists(
  select 1 from user_privilege up
  where up.user_id = @user_id
    and up.privilege_id = p.id
)
or exists(
  select 1 from user_role ur
  inner join role_privilege rp
  on rp.role_id = ur.role_id
  where ur.user_id = @user_id
    and rp.privilege_id = p.id
)

未优化，但可以作为理解解决方案的良好开端。

sql-server - 在 SQL Server 存储过程中组合查询

问题描述

解决方案

推荐阅读