Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-06-11 : 10:08:06
|
| Tom writes "Here's our problem:We need to allow users who add rows to various transaction tables to edit those rows. However, in addition, we need to allow these users to authorize access to their rows to other users as they see fit.Thus, our initial thought is to add five memo fields to the end of each row: creator, admin, read, change / deny. In these memo fieds, the creator could add / delete other user id numbers in a comma separated string. Admins could also add delete other users, but not the admin field or the creator field. Creators could add edit all the other fields.Nice thing about this is it's one operation to see if a user can access a row, retrieve the row and search the memo fields for the user's id.Another thought is to set up a single row level table with the following fields - row auto num / table name / and then the memo fields listed above. Two selects, one for the row, one for the access control row, compare user ids, and you're done. A bit slower, but it would allow you to easily produce reports on who is authhorized to see what.This can't be a unique problem, and I suspect we are improperly reinventing the wheel." |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-11 : 11:45:17
|
quote:
This can't be a unique problem
Maybe not, but it's pretty rare. SQL is not a row-oriented language, nor does it handle permissions for sub-sets of data. You either have permissions to the table or object holding the data or you don't. If there is data that should be kept secure from users, that data should be segregated into a separate table and that user's permissions revoked for that table. You'll have a hard time making this work in SQL Server and just about every other RDBMS I can think of; I *think* Ingres might have row-level permissions, but I'm not sure.One thing you have to look out for is that you cannot enforce this properly using tables. You'd have to create views from the base table(s) that enforces the user's access, based on SUSER_SNAME() of the equivalent user ID functions (see Books Online for more details). You'd then have to revoke every user's permission to the tables and grant them access only to the views. If they can access the tables directly, then they can get at everything and you have no security at all.Now, you *might* be able to get this to work using triggers, but there are a number of problems that are not easily solved. The trigger would let you find rows that the user should not be able to modify, delete, or add, but cannot prevent SELECT access. Also, if someone edits 10 rows but only has permission for 9 of them, you can't easily undo the 1 row that they can't modify; either all 10 get rolled back or all 10 get changed.Another thing is if people can view or browse through rows, but not change them, you really can't physically stop them from doing it except to revoke update permissions, and that would affect every row.If you're really serious about making this work, the only practical way is to treat SELECT operations separately from INSERT, UPDATE and DELETE. You can still use a view to restrict what people can see, but that view cannot be updated (revoke update permissions on it). Use stored procedures to handle all other operations. That allows you to strictly enforce these kinds of row permissions. |
 |
|
|
|
|
|
|
|
Another Row Level User Access Security Question