Using SQL to search for a set in a one-to-many relationship

Question

Given the tables:

role: roleid, name
permission: permissionid, name
role_permission: roleid, permissionid

I have a set of permissions, and I want to see if there is an existing role that has these permissions, or if I need to make a new one. Note that I already know the permissionid, so really the permission table can be ignored - I just included it here for clarity.

Is this possible to do in a SQL query? I imagine it would have to be a dynamically-generated query.

If not, is there any better way than the brute force method of simply iterating over every role, and seeing if it has the exact permissions?

Note, I'm looking for a role that has the exact set of permissions - no more, no less.

Answer 1

You can select all roles that have the subset of permissions you are looking for. Count the number of permissions and see if it's exactly equal to the number of permissions you need:

select r.roleid 
from role r
where not exists (select * from role_permissions rp where rp.roleid = r.roleid and rp.permissionid not in (1,2,3,4)) -- id of permissions
   and (select count(*) from role_permissions rp where rp.roleid = r.roleid) = 4 -- number of permissions

Answer 2

Maybe use a subquery along the lines of ...

SELECT * FROM role r
WHERE r.rolid = (SELECT x.roledid 
                 FROM role_permission 
                 WHERE x.permissionid in (1,2,3,4);

Sorry, haven't validated this, but having just spent an hour debugging PHP code for another question I feel the need for a glass of red wine.

Answer 3

This is an old sql trick (works in Oracle, at least):

SELECT roleid FROM role_permission t1
WHERE NOT EXISTS (
(SELECT permissionid FROM role_permission t2 WHERE t2.roleid = t1.roleid
 MINUS
 SELECT permissionid FROM role_permission WHERE roleid = 'Admin')
UNION
(SELECT permissionid FROM role_permission t2 WHERE roleid = 'Admin'
 MINUS
 SELECT permissionid FROM role_permsission t2 WHERE t2.roleid = t1.roleid)
)

Also not validated. Red wine always sounds good.

Answer 4

You basically need to check if there is a role that has the exact number of distinct permissions as you are checking for.

I have checked this stored procedure on SQL Server 2005 and it returns only those role ids that have an exact match of permission ids to those in the passed in list of comma separated permission ids -

CREATE PROC get_roles_for_permissions (@list nvarchar(max)) -- @list is a comma separated list of your permission ids
AS
SET NOCOUNT ON

BEGIN

DECLARE     @index INT, @start_index INT, @id INT
DECLARE     @permission_ids TABLE (id INT)           

    SELECT @index = 1 
    SELECT @start_index = 1
    WHILE @index <= DATALENGTH(@list)
    BEGIN

        IF SUBSTRING(@list,@index,1) = ','
        BEGIN
                SELECT @id = CAST(SUBSTRING(@list, @start_index, @index - @start_index ) AS INT)
                INSERT INTO @permission_ids ([id]) VALUES (@id)
                SELECT @start_index = @index + 1
        END
        SELECT @index  = @index + 1
    END
    SELECT @id = CAST(SUBSTRING(@list, @start_index, @index - @start_index ) AS INT)
    INSERT INTO @permission_ids ([id]) VALUES (@id)

SELECT 
r.roleid 
FROM
role r 
INNER JOIN 
role_permission rp 
ON r.roleid = rp.roleid
INNER JOIN
@permission_ids ids
ON
rp.permissionid = ids.id
GROUP BY r.roleid
HAVING(SELECT COUNT(*) 
       FROM role_permission 
       WHERE roleid = r.roleid) = (SELECT COUNT(*) FROM @permission_ids)

END

Example Data

CREATE TABLE [dbo].[role](
    [roleid] [int] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](50)
    )

CREATE TABLE [dbo].[permission](
    [permissionid] [int] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](50)
    )

CREATE TABLE [dbo].[role_permission](
    [roleid] [int],
    [permissionid] [int]
    )

INSERT INTO role(name) VALUES ('Role1')
INSERT INTO role(name) VALUES ('Role2')
INSERT INTO role(name) VALUES ('Role3')
INSERT INTO role(name) VALUES ('Role4')

INSERT INTO permission(name) VALUES ('Permission1')
INSERT INTO permission(name) VALUES ('Permission2')
INSERT INTO permission(name) VALUES ('Permission3')
INSERT INTO permission(name) VALUES ('Permission4')

INSERT INTO role_permission(roleid, permissionid) VALUES (1, 1)
INSERT INTO role_permission(roleid, permissionid) VALUES (1, 2)
INSERT INTO role_permission(roleid, permissionid) VALUES (1, 3)
INSERT INTO role_permission(roleid, permissionid) VALUES (1, 4)
INSERT INTO role_permission(roleid, permissionid) VALUES (2, 2)
INSERT INTO role_permission(roleid, permissionid) VALUES (2, 3)
INSERT INTO role_permission(roleid, permissionid) VALUES (2, 4)
INSERT INTO role_permission(roleid, permissionid) VALUES (3, 3)
INSERT INTO role_permission(roleid, permissionid) VALUES (3, 4)
INSERT INTO role_permission(roleid, permissionid) VALUES (4, 4)

EXEC get_roles_for_permissions '3,4' -- RETURNS roleid 3

Answer 5

Having made a hash of my first answer to this question, here is a slightly left field alternative which works but does involve adding data to the database.

The trick is to add a column to the permission table that holds a unique value for each row.

This is a fairly common pattern and will give precise results. The downside is you have to code your way around hiding the numerical equivalents.

id int(10) 
name varchar(45) 
value int(10)

Then the contents will become:

Permission:           Role                   Role_Permission
id  name  value       id  name               roleid permissionid
--  ----  -----       --  ----               ------ ------------
1   Read     8         1   Admin                1          1
2   Write   16         2   DataAdmin            1          2
3   Update  32         3   User                 1          3
4   Delete  64                                  1          4
                                                2          1
                                                2          3
                                                2          4

Then each combination of roles gives a unique value:

SELECT x.roleid, sum(value) FROM role_permission x
inner join permission p
on x.permissionid = p.id
Group by x.roleid

Giving:

roleid   sum(value)
------   ----------
    1          120       (the sum of permissions 1+2+3+4 = 120)
    2          104       (the sum of permissions 1+3+4 = 104)

Now where did I leave that corkscrew...