January 16, 2015 at 5:08 pm
I'm undecided on how to implement something. Because the system that I'm building is holding student data, I have to implement a compartmentalized security model consisting of three levels: employees, districts, and TVIs (Teachers of the Visually Impaired). Employees can view and modify all data, districts can work with data only for students in for that particular district, and TVIs for the districts to which they are assigned, so TVIs can work with multiple district's data. I have an updateable view that matches SYSTEM_USER against a user table to find out of they're an employee (in which case they satisfy the WHERE condition as TRUE and proceed) or if they're a district or TVI they look up the district permission table to find out what districts they're permitted to view, and if that student is in that district, we're good.
Functionally there is no difference in permissions for reading student data between a TVI and district user, pending further clarification of the business rules for this system. It is important to keep them differentiated outside of the system. All users are in one table, so when someone tries to access students, three (possibly four) tables are involved at the basic level: students, DBusers, and DBuserpermissions. (My next design iteration to experiment with is to transform the permissions table in to one record that will be updated by a regularly running SP while doing the initial data load and a trigger after the system is up and running)
The current design (not yet finalized or implemented) has four bit flags: IsEmployee, IsDistrict, IsTVI, and IsInactive. Constraints limit values to Y/N and only one of the first three flags can be set. The IsDistrict and IsTVI fields have no business logic significance, they'll just apply to report listings. If IsInactive is set, the view's evaluation fails and no records are seen. Districts can add and remove TVIs to their district if they are set as active, only an employee can set or clear the IsInactive flag.
I think the reason why I initially designed the implementation as bit fields is that I don't often use them. I could as easily use a lookup table to code their role. It would actually require an additional byte of space since I intend to keep the Inactive bit flag for easier trigger logic and a more clearly defined status. The additional byte is insignificant as we're expecting maybe 250 or so records, but the additional lookup table would require another page or three in the database, again, insignificant. But it would add another table to join to determine what districts are permitted for a user, so a slight hit there.
So I'm a little undecided. The design using bit fields works just fine, though I haven't loaded the system with dummy records to see what performance plans look like. It is extremely unlikely that more user roles will be added to the system, though never say never. The design is fairly well agreed upon and fleshed out, and I don't know that changing the role to a lookup table clarifies things.
Thoughts? If anyone's interested, I can post the code that does the compartmentalization (keeping in mind that it's just a proof of concept).
-----
[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]
January 17, 2015 at 6:45 am
The first thing that comes to mind is the fact that you're going to write a lot of OR statements to switch through these bit fields. That bothers me from a performance perspective. Also, if you do find that you could use indexing, you're only going to be able to do some compound key type of index on these things and it still might not get picked up by the optimizer because the histogram will look useless. I'm inclined to go with a table because it allows for some degree of flexibility should another layer of security get added. Indexing is certainly going to be a little better. I generally fail towards normalizing things until I see that it's not going to work.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 17, 2015 at 11:07 am
Second vote for a table.
February 4, 2015 at 5:07 pm
Thanks for the replies, gents. I wanted to advance the project a bit and get to experiment before replying. Plus I lost last week to the flu.
So I've redesigned away from the flags. I added a status field because I still need to differentiate types of users. The lookup table has the values -1 for a user without a DB account (basically an address card), 0 for inactive, 1 for superuser (edit anything), 2 districts, 3 TVIs. This way I can see if the user = 1 to access all students, and for the purposes of student record security 2 and 3 are the same. 2 will have additional functionality at the application level. And I'll have the edit trigger on the user table delete user lookup permissions if their code changes to -1 or 0.
I'm pretty optimistic it'll work the way that I need it to.
-----
[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 3:47 am
Excellent. Make sure to look at the execution plans to see how the queries resolve based on the new design. Thanks for the follow-up post.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 5, 2015 at 6:48 am
I would separate the inactive flag from the other user roles. A user could be a TVI AND inactive, correct? Perhaps you may need to know what type of user was just marked as inactive. Without some other logging, that wouldn't be possible if "inactive" is a user role. And if someone was made active again, to which role should they belong?
Active/Inactive should be a status and not a role, in my opinion.
February 5, 2015 at 8:20 am
dlaughlin 24148 (2/5/2015)
I would separate the inactive flag from the other user roles. A user could be a TVI AND inactive, correct? Perhaps you may need to know what type of user was just marked as inactive. Without some other logging, that wouldn't be possible if "inactive" is a user role. And if someone was made active again, to which role should they belong?Active/Inactive should be a status and not a role, in my opinion.
Good point, and I agree with you semantically, it's more accurate to think of it as a status and not a permissions role. Having said that, we often make database design decisions based on what's easier to implement and easier for the users. In this case I think it's easier to change their status to inactive and have a trigger purge their access permissions because we can then have a superuser disable a TVI, for example, before news filters to the DBA level to disable or remove their server login. Since I'll have a full audit on what districts they've been assigned to, recreating them if they were 'inactivated' by mistake shouldn't be difficult.
It's difficult to say what role someone could return as after they've been made inactive, this database is a first for this school, so we're still wrangling with the business logic and processes behind the system as I experiment with and make refinements to the database design. We know that district special education directors (SPEDs) have unusually short tenures, so we're giving them District Name logins so they can pass that login information to the next SPED. Since all actions on students are fully audited, we don't really care if someone mucks up a record: we can point to District X at Time Y making these changes, and we have the information to restore the record to its original values. We don't care who the person was that screwed it up because regardless it's the district's responsibility. TVIs apparently are quite mobile: if a school or district has a sudden influx of visually impaired kids, they may hire a couple more temporarily who'll come in for a few hours once a week or so and that TVI may also provide service to kids at another district elsewhere in the state. They're highly mobile, and it's not unreasonable that they decide to take a semester or year off and their account would be disabled until they started working again. It's also possible that someone could transition between being a SPED to a TVI to an IRC coordinator or back and forth. We'll still have all of their contact info and all the history of where they've worked, but where they're now working might change radically when they change roles.
(IRC I think is Instructional Resource Coordinator, sort of a librarian position whom we send assistive material to. They're the -1's: we need the contact info, but they won't have database logins. Everyone else in the SeekerUsers table will have, or have had, a network/database login.)
It's a very weird system, and a lot of fun. I've never worked in education at the school level, I'm learning a lot of the peculiarities of the system and I'm making them define a lot of things that they'd never considered before.
-----
[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 3:07 pm
CELKO (2/5/2015)Read this for details:
https://www.simple-talk.com/sql/t-sql-programming/bit-of-a-problem/%5B/quote%5D
Thanks for the article, Joe. That and the comments were quite interesting.
-----
[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 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply