User Access Puzzle

  • We have a list of employees, and want to allow users of our system to be able to access a subset of these employees based on certain rules. The "rules" are nothing more than specific foreign key values that may be assigned to an employee. Here is a simplified example:

    Here's the employee table. One employee per row. DepartmentID, OfficeID, and RegionID are foreign keys to other tables

    CREATE TABLE Employees (

    EmployeeID INT,

    FullName NVARCHAR(50),

    DepartmentID INT,

    OfficeID INT,

    RegionID INT

    )

    And here's the users table. In the real world, we have password etc. here

    CREATE TABLE Users (

    UserID INT,

    FullName NVARCHAR(50)

    )

    Now we define which users have access to which employees. By default, users have ZERO rows defined here, and thus have access to all employees. UserID and only one value (department, office, or region) will be supplied per row. A user can have multiple rows to provide access to multiple departments, and/or multiple offices, and/or multiple regions:

    CREATE TABLE UserAccess (

    UserID INT,

    DepartmentID INT,

    OfficeID INT,

    RegionID INT

    )

    So now lets query the employees for a user with an ID of 12345.

    DECLARE @user-id INT = 12345

    --this is very fast

    SELECT *

    FROM Employees e

    JOIN UserAccess a ON a.UserID = @user-id

    AND e.DepartmentID = a.DepartmentID

    --this is very slow

    SELECT *

    FROM Employees e

    JOIN UserAccess a ON a.UserID = @user-id

    AND (e.DepartmentID = a.DepartmentID

    OR e.OfficeID = a.OfficeID

    OR e.RegionID = a.RegionID)

    Assuming the employee definition is fairly common and straightforward, I am wondering if my approach is the best means of representing the user access in a defined database structure... or if I am even on the right track. Maybe all I need is an index (or 2, or 3) on the user access table...?

    Is there a more efficient way to go about this?

  • I can see some issues with what you have here. It starts with normalization. You really should only have 1 of those UserAccess values per row. I am guessing that only 1 column per row is not null? I would either split this into three tables or add a new column to identify which type of UserAccess it is.

    If you split out the access into 3 tables your query might look something like this.

    SELECT *

    FROM Employees e

    JOIN DepartmentAccess a ON e.DepartmentID = a.DepartmentID

    join OfficeAccess oa on e.DepartmentID = oa.DepartmentID

    join RegionAccess ra on e.DepartmentID = ra.DepartmentID

    WHERE e.EmployeeID = @user-id

    Another option might be to use a UNION ALL for the three types. Something like this.

    SELECT *, 'Dept' as AccessType

    FROM Employees e

    JOIN UserAccess a ON e.DepartmentID = a.DepartmentID

    WHERE a.UserID = @user-id

    UNION ALL

    SELECT *, 'Office'

    FROM Employees e

    JOIN UserAccess a ON e.OfficeID = a.OfficeID

    WHERE a.UserID = @user-id

    UNION ALL

    SELECT *, 'Region'

    FROM Employees e

    JOIN UserAccess a ON e.RegionID = a.RegionID

    WHERE a.UserID = @user-id

    You will notice in all of those I moved the filtering to the where clause instead of on the join. It will work the same but I find it a lot easier to read.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • I'd also note your logic is always going to be split this way. Since someone has access with no rows, you have to account for that separately in your logic and query without restrictions if the user doesn't have rows. If they have rows, as Sean noted, you'll need to find better ways to query, or index better. You never mentioned indexes, which might solve the problem.

    I've done this before, but we usually ensured that we added rows to the table for all departments/offices/employees so that things were always joined.

  • Thanks guys, all good points.

    I think the kicker here is that a user may have access to departments and/or offices and/or regions. It's the "ORs" that are killing the query.

    I like the approach of three tables, but I can't do inner joins since a user may have access to some offices, but not to any regions. (I must also note that users are different than employees, so Sean's first query above would need some minor adjustment). I understand about normalization, but if I store an ID and the type of ID separately, then I lose referential integrity on the ID column, so I don't think that's a viable option.

    HOWEVER!!! The union is interesting, and my prelim tests seem to be resoundingly successful.

    My really big challenge here is that there are over 100 stored procs already written that do not yet filter based on this defined user access (the user access is a new business requirement after 10 years!) and so whatever we decide as a solution will need to be incorporated into them. We'll pass the requesting user's ID to the procs, and then process the data using the user access filtering.

    So maybe I create a temp table or a CTE using Sean's union approach, and then do an inner join against that temp table/CTE within each of the queries. Something like this, maybe:

    WITH Emps AS (

    SELECT EmployeeID

    FROM Employees e

    JOIN UserAccess a ON e.DepartmentID = a.DepartmentID

    WHERE a.UserID = @user-id

    UNION ALL

    SELECT EmployeeID

    FROM Employees e

    JOIN UserAccess a ON e.OfficeID = a.OfficeID

    WHERE a.UserID = @user-id

    UNION ALL

    SELECT EmployeeID

    FROM Employees e

    JOIN UserAccess a ON e.RegionID = a.RegionID

    WHERE a.UserID = @user-id

    )

    SELECT *

    FROM Employees e

    JOIN Emps x ON e.EmployeeID = x.EmployeeID

    And yes indexing will certainly play a part.... Now I just have to figure out how to allow users with no access defined to be able to access all the employees. Maybe add a "AND WHERE EXISTS (SELECT * FROM Emps)" to the join.

  • Maybe instead of creating the cte in over 100 procs you could create a view. Then you just have to add a join in your other queries. This would give you the advantage that if the user access changes a bit all you have to do is adjust the view.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean, that's another great idea. I tried it this morning, and actually have everything modularized and tidy.

    My view performs the necessary unions, and I only need to add ONE LINE to all the existing procs... a line that is instantly evident in its purpose as well:

    JOINvAccess a ON a.EmployeeID = e.EmployeeID AND a.UserID = @user-id

    Thanks for helping me think through this and for your excellent suggestions.

    Now, off to establish some usage patterns and begin looking at some indexes 🙂

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply