1

I have a SQL Server 2008R2 question regarding making a single row in an odd way.

situation: I have a student records database in which ~ 100 students might in-process in a given day. They enter a good deal of PII. Unfortunately, they frequently have need to save the data and come back later to update or complete their registration because they need one or another piece of information. At that point, they need to be able to go back to their original incomplete entry and add whatever information they need to add. This might occur at any point during the initial week of the course, depending on their schedule, and the difficulty they have in retrieving whatever information they need to complete the database.

Problem: I have a number of groups of people who need to be able to read and modify the student information. Various staff members need to be able to pull some of this information, course managers typically need to be able to read it for compiling statistical reports, and instructors need to access it all the time to add grades, etc. The students MAY need to access it later in the course to update information, but this is not typical. My main problem is that I need the students to have read and write access to THEIR ROW only, while instructors et al. need to be able to read and write to all the rows. With AD integration, everything works EXCEPT that one student could potentially open another student's "file" (row) and view their personal information.

How can I lock one row to one student while still allowing administrative staff and instructors read and potentially write access? I am a one man show, so I need to automate as much of this as possible, and SQL is not my strong suit. I considered using either certificates (we have our own internal CA) and/or AD userIDs, but I'm not sure how to implement it, or if that is really the best choice.

5
  • A Google search for SQL Server row level security returns a plethora of results.
    – joeqwerty
    Mar 14, 2015 at 23:35
  • This type of access control is usually handled at the application level, e.g. each student's record would have a unique identifier such as a database identity column (int/bigint) or GUID, and that identifier is not exposed to the student but is used as a parameter to any update/insert stored procedures. Usually storing it as a session variable is sufficient for web apps. It's not too difficult to tighten security even further by hashing that value and validating the hash when updating.
    – dartonw
    Mar 15, 2015 at 0:01
  • OK, so from what I'm reading, I could essentially have two different front-ends - one for the students, which limits their ability to interact with any row that <> their user ID, and the staff/instructors would access through a different front-end that is not limited by this restriction? Mar 15, 2015 at 0:13
  • Yes, that would be one way of doing it. Ideally, you would build in multiple levels of access control and have permissions on objects, filtered by some configurable criteria, such as one's own record identifier or records to which they have some relation (instructor:student, instructor:course).
    – dartonw
    Mar 15, 2015 at 17:48
  • After searching around a bit more, I found this: sqlserverlst.codeplex.com link which seems to build in the row level security. It isn't exactly what I was looking for, but I think I can use the classification settings to create instructor and student level access. I'll experiment with it and see if this is the solution. If nothing else, looking at the structure it builds should help me figure out how to implement my own solution. Mar 16, 2015 at 11:19

1 Answer 1

0

In addition to dartonw's comment you could consider to use only stored procedures and views for database access instead of raw access. You can set permissions on the SPs and Views for specific users. You can then use specific views and SPs to obtain different sets of results.

Yet I would agree with other commenters that row level security on that SQL Server Edition should be handled on the application level rather than the db level.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .