Role-based access control query

Question

I use SQL Server, and though not pertinent here, Railo's CFML engine.

I'm not great at SQL Joins, but I worked through this one and got the result set that I want. This particular SQL will only ever return 10 to 20 records, so even if it's a weak method, I'd probably never see a lot of grief from it since it's on an admin page to edit user profiles.

However, obviously it's best to learn early to improve, before habits set in. It's conceivable, and likely, that I'll encounter a similar situation in the future where the potential size of the result could be several times larger.

It's also possible that there's a faster way to retrieve the same results.

The goal of this page is to list out the user details, and a list of roles along the side with checkboxes. That's easy to code. I'm not asking for help with that.

This is the SQL that I came up with, and I'll explain my db layout below it.

select r.roleID, r.roleTitle, pu.*, 'constant' as qc from roles r
    left outer join (select u.*, p.roleID as xrole from permissions p
         left join users u on p.userID = u.userID
              and p.userID = 2) pu on pu.xrole = r.roleID
         and pu.userID = 2
order by len(username) desc

(Both occurrences of the number 2 are merely to test, this will naturally be replaced with a variable in my .cfm page)

I have three tables:

• Users - typical users table (UserID, Username, other personal details
• Permissions Table (UserID, RoleID)
  • Permissions, likely obvious, links the other two tables. If a User doesn't have an entry matching their userID and the role they're trying to access, can't access the role.
• Roles (RoleID, RoleTitle)

Notes - I'm not sure if I would naturally always get a row with userdata first, so I added an order by clause. - 'constant' as qc is added so that I have one column that has a reliable value so that I can always group by that (cfoutput grouping, not sql grouping, I then cfoutput-group by roleID for the checkboxes for roles)

Answer 1

No, this s not the best way for several reasons:.

First, with this CTE test data common to all three runs:

with Users as ( select * from (values
        (1,'Pieter')
       ,(2,'Betty')
       ,(3,'Robert')
       ,(4,'Fred')
       ,(5,'George')
       ,(6,'Anne')
   )users(UserID,Username)
),
Roles as ( select * from (values
        (100,'Guest')
       ,(101,'Public')
       ,(  1,'Admin')
       ,(102,'Editor')
   )Roles(RoleID,RoleTitle)
),
Permissions as ( select * from (values
        (1,101)
       ,(2,101),(2,102)
       ,(3,101),(1,102)
       ,(4,101)
       ,(5,100)
       ,(6,1)
       ,(7,101),(1,102)
       ,(8,101)
   )Permissions(UserID,RoleID)
)

Your suggested SQL with results is:

select r.roleID, r.roleTitle, pu.* from roles r
    left outer join (select u.*, p.roleID as xrole from permissions p
         left join users u on p.userID = u.userID
              and p.userID = 2) pu on pu.xrole = r.roleID
         and pu.userID = 2

roleID      roleTitle UserID      Username xrole
----------- --------- ----------- -------- -----------
100         Guest     NULL        NULL     NULL
101         Public    2           Betty    101
1           Admin     NULL        NULL     NULL
102         Editor    2           Betty    102

Note that the same result is obtained if we tidy the SQL somewhat to this:

select 
    r.roleID, r.roleTitle, 
    u.UserID, u.UsrName 
from permissions p
join users       u on u.UserID = p.UserID
                  and p.UserID = 2
right join roles r on p.RoleID = r.RoleID
;

roleID      roleTitle UserID      Username
----------- --------- ----------- --------
100         Guest     NULL        NULL
101         Public    2           Betty
1           Admin     NULL        NULL
102         Editor    2           Betty

1. Ordering your joins can make your code much easier to read, and often avoid nested subqueries.
2. Always list all field names (except possibly hen referencing a subquery which already lists them). This helsp to prevent your code from breaking with minor schema changes, such as additional fields or a re-ordering of the physical layout of the fields.

Answer 2

I had discovered an issue (SQL wasn't bugged, just a failure in my understanding) with my query.

The query, and the accepted answer's query (he answered what I asked very well), failed when the user has no associated entries in the Permissions table. It returned results for every role, but all the user data was empty, so I couldn't use it as a single query to get user profile & role information.

I came up with a solution

select r.roleID, r.roleTitle, ut.*, pu.* from (select * from users where userID = 3) ut, roles r
left outer join (select u.username as un, u.userID as ux , p.roleID as xrole from permissions p
inner join users u on p.userID = u.userID
    and p.userID = 3) pu on pu.xrole = r.roleID
    and pu.ux = 3

And I modified Pieter's solution so that it fixes the same "problem" my original query had, since he surely seems to know what he's talking about and he says his solution is more elegant.

select r.roleID, r.roleTitle, pu.*, ut.* from (select * from users where userID = 3) ut, roles r
left outer join (select u.userid ux, u.username as un, p.roleID as xrole from permissions p
     left join users u on p.userID = u.userid
          and p.userID = 3) pu on pu.xrole = r.roleID
     and pu.ux = 3

I simply wanted to share it if anyone else should find themselves in a similar situation.

All occurrences of the number 3 in both queries is where you would provide your variable. 3 was just the user-with-no-permissions I was testing against (1 and 2 had permissions).