Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

User Access Puzzle Expand / Collapse
Author
Message
Posted Thursday, August 1, 2013 9:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 6:58 AM
Points: 36, Visits: 290
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 @UserID INT = 12345

--this is very fast
SELECT *
FROM Employees e
JOIN UserAccess a ON a.UserID = @UserID
AND e.DepartmentID = a.DepartmentID


--this is very slow
SELECT *
FROM Employees e
JOIN UserAccess a ON a.UserID = @UserID
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?
Post #1479994
Posted Thursday, August 1, 2013 10:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:36 PM
Points: 13,418, Visits: 12,283
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 = @UserID

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 = @UserID

UNION ALL

SELECT *, 'Office'
FROM Employees e
JOIN UserAccess a ON e.OfficeID = a.OfficeID
WHERE a.UserID = @UserID

UNION ALL

SELECT *, 'Region'
FROM Employees e
JOIN UserAccess a ON e.RegionID = a.RegionID
WHERE a.UserID = @UserID

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1480024
Posted Thursday, August 1, 2013 10:38 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 7:45 PM
Points: 33,264, Visits: 15,424
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1480028
Posted Thursday, August 1, 2013 11:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 6:58 AM
Points: 36, Visits: 290
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 = @UserID

UNION ALL

SELECT EmployeeID
FROM Employees e
JOIN UserAccess a ON e.OfficeID = a.OfficeID
WHERE a.UserID = @UserID

UNION ALL

SELECT EmployeeID
FROM Employees e
JOIN UserAccess a ON e.RegionID = a.RegionID
WHERE a.UserID = @UserID
)
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.
Post #1480057
Posted Thursday, August 1, 2013 12:32 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:36 PM
Points: 13,418, Visits: 12,283
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1480086
Posted Friday, August 2, 2013 9:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 6:58 AM
Points: 36, Visits: 290
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:

JOIN	vAccess a ON a.EmployeeID = e.EmployeeID AND a.UserID = @UserID

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 :)
Post #1480463
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse