SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


User Access Puzzle


User Access Puzzle

Author
Message
Treehouse
Treehouse
SSC-Enthusiastic
SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)

Group: General Forum Members
Points: 122 Visits: 341
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?
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26313 Visits: 17553
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 Modens 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)
Steve Jones
Steve Jones
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: Administrators
Points: 63422 Visits: 19115
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
My Blog: www.voiceofthedba.com
Treehouse
Treehouse
SSC-Enthusiastic
SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)

Group: General Forum Members
Points: 122 Visits: 341
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.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26313 Visits: 17553
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.

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)
Treehouse
Treehouse
SSC-Enthusiastic
SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)

Group: General Forum Members
Points: 122 Visits: 341
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 Smile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search