August 1, 2013 at 9:24 am
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?
August 1, 2013 at 10:25 am
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/
August 1, 2013 at 10:38 am
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.
August 1, 2013 at 11:43 am
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.
August 1, 2013 at 12:32 pm
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/
August 2, 2013 at 9:25 am
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