Role-based access control query

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)

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

• Ordering your joins can make your code much easier to read, and often avoid nested subqueries.

• 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.

StackExchange Code Review Q#64006, answer score: 4