Combining queries in a SQL Server stored procedure

Question

I want to combine privileges from the user and their roles in a stored procedure. To do so currently I am running many queries. The first gets the roles the user has. Then a query to get the privileges for each role. Then a query to get the privileges for the user. Then I combine them all from the code. I would like to be able to do all that from a stored procedure. I have not ever worked with stored procedures so I do not know if it is even possible.

Hopefully the diagram will help you understand what I am trying to do.

I started to write one but it is not near finished or functional.

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;

What do I need to do to finish the procedure?

Looping through the results of the first select. No clue how to do this.
Combining the results. Found that this could be done by making a temp table and then pushing the result sets from each query to it. Is there a better way?

Sample:

user table

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

role table

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

privilege table

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

user_privilege table

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

user_role table

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

role_privilege table

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

Expected result for user_id = 1

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

You should be able to do this entire thing in a single query. Using loops and such is a not a great way to retrieve data. — Sean Lange
– Sean Lange, Commented Feb 20, 2019 at 22:29
@SeanLange Could you explain how I could do that. I just can't see how that would be don. — Caleb Bassham
– Caleb Bassham, Commented Feb 20, 2019 at 22:31

IVNSTN · Accepted Answer · 2019-02-21 07:41:34Z

1

sidenote:

see? no line crossings, +100 for readability.

One of possible solutions (almost a translation from natural language to SQL): Return list of priviledges which exist in user priviledges list or any of user role priviledges list:

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
)

not optimized but can be a good start for understanding the solution.

edited Feb 21, 2019 at 7:41

answered Feb 21, 2019 at 7:34

IVNSTN

9,3896 gold badges24 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Carlo Bos · Accepted Answer · 2019-02-21 00:33:45Z

0

You can try this:

CREATE OR ALTER PROCEDURE get_user_privileges
  @userId int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT p.[id] AS [privilege_id], p.[name]
    FROM [user] AS u
    JOIN [user_role] AS ur ON ur.[user_id] = u.[id]
    JOIN [role] AS r ON r.[id] = ur.[role_id]
    JOIN [role_privilege] AS rp ON rp.[role_id] = r.[id]
    JOIN [user_privilege]  AS up ON up.[user_id] = u.[id]
    JOIN [privilege] AS p ON p.[id] = up.[privilege_id]
    WHERE u.[id] = @userId 
    GROUP BY p.[id], p.[name] ;
END ;

Then you can execute this stored procedure as follows:

EXEC get_user_privileges @userId = 1

Hope this helps.

edited Feb 21, 2019 at 0:33

answered Feb 20, 2019 at 22:30

Carlo Bos

3,3132 gold badges21 silver badges32 bronze badges

2 Comments

Caleb Bassham Over a year ago

That is close to what I am wanting. It returns what role a user has and all of their user privileges, but it does not return any of the privileges that the roles have which is the main difficulty I have been having. All I want from this whole query is a list of privileges. I don't care at all if it cam from user or a certain role.

Carlo Bos Over a year ago

I've updated the answer to include a stored procedure that returns all the role-privilege associations.

Collectives™ on Stack Overflow

Combining queries in a SQL Server stored procedure

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related