February 4, 2015 at 5:28 pm
For a fairly full description of what I'm trying to do, see my previous post at http://www.sqlservercentral.com/Forums/Topic1652109-3412-1.aspx.
In a nutshell, I need to restrict users from seeing student information from districts that they are not permitted. So there's a user table that contains the SQL login name and there's a status code. If they're 1, they're superuser and can see/edit everything. Otherwise there's an additional permission table. Originally I had a table called SeekerUsersDistricts that had SeekerUserID and DistrictID, one record per user/district. And that methodology worked just fine. My desire was to reduce I/O, which is probably pointless because I think the cache would handle it just fine, by having a parameter table that would have the user ID and a varchar(255) field that would contain "001,046,010," etc. It would build out the list of permitted districts through either a trigger or a scheduled job.
This is the code that doesn't work:
SELECT st.*
FROM Students AS st
INNER JOIN SeekerUsers AS t
ON SeekerUserDBLogin = SYSTEM_USER
AND (t.SeekerUserRole = 1
OR st.DistrictNum IN (SELECT PermittedDistricts
FROM dbo.SeekerUsersReportParams AS td
WHERE t.SeekerUserid = td.SeekerUserid)
)
GO
(no, I won't use a select * in production)
So the question is: (A) is there a way to do what I want to do, and (B) am I ridiculously overcomplicating things and I should be fine with the user ID/district# table.
It's difficult to estimate how this table will build out. We're anticipating 1,500-2,000 student records, 90ish district users who can only access their particular district's info, and 100 or so TVIs who work with multiple districts and thus would have multiple records in (B) of the preceding paragraph. But this view is going to be very central to the system and get hit LOTS.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
February 5, 2015 at 10:38 am
Unless someone comes up with something, I think I have a solution, albeit not what I was looking for.
If I stick with my original methodology of a lookup table rather than crunching the variables in to one field, I can make the student filter work at the SQL level. I can use the crunched district list in Access for reporting purposes, so sort of dividing part of the load. SQL Server still maintains core security, and Access has easier reporting.
The Access query is reliant on this SQL view:
alter view vwCurrentUserInfo as
SELECT u.SeekerUserID, u.SeekerUserDBLogin, u.SeekerUserNameFL,
urp.ReportDistrict, urp.ReportSchool, urp.ReportStudent,
urp.DateStart, urp.DateEnd, urp.PermittedDistricts
from SeekerUsers u
join SeekerUsersReportParams urp
on u.SeekerUserID = urp.SeekerUserID
where SeekerUserDBLogin = SYSTEM_USER
Then Access uses this view via links to SQL tables and views to produce a filtered district list:
SELECT dbo_lkupDistricts.*
FROM dbo_lkupDistricts, dbo_vwCurrentUserInfo
WHERE ((InStr([PermittedDistricts],[DistrictNum])>"0"));
This gives me a parameter record for report control for each user in the reporting system, and I can also use it for combo boxes elsewhere, for example, to restrict the districts seen by a user to include only the districts that they are assigned to. And since the database is not a 24/7 production, I can take the time to rebuild the User/District# lookup table every night to give it the best edge that I can.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
February 5, 2015 at 6:15 pm
Wayne West (2/4/2015)
This is the code that doesn't work:
SELECT st.*FROM Students AS st
INNER JOIN SeekerUsers AS t
ON SeekerUserDBLogin = SYSTEM_USER
AND (t.SeekerUserRole = 1
OR st.DistrictNum IN (SELECT PermittedDistricts
FROM dbo.SeekerUsersReportParams AS td
WHERE t.SeekerUserid = td.SeekerUserid)
)
GO
Are you saying that PermittedDistricts contains a comma delimited list?
If so, use DelimitedSplit8K[/url] to break it up into its component districits.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 6, 2015 at 1:16 pm
dwain.c (2/5/2015)
Are you saying that PermittedDistricts contains a comma delimited list?If so, use DelimitedSplit8K[/url] to break it up into its component districits.
I looked over Jeff's initial article but hadn't gotten to this particular one yet. I think I'm going to redo my approach, I think I'm overcomplicating things. Thanks for the link!
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply