Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Summary
Relational databases usually do not implement row- (or record-) level access control commonly offered by document-oriented databases; a relational database user can read or modify all or none of the records in a table.
You can implement record-level modification controls with table triggers: An AFTER UPDATE or AFTER DELETE trigger checks whether the user is trying to modify a record she's not authorized to modify based on your business rules. Usually the records should only be modified by their authors and table-wide editors, but you could also decide to implement per-record editor field to specify a different set of editors for each record; this setup would be very handy if you'd try to implement a tightly controlled review process.
Record-level access controls are harder to implement. You have to deny all users' access to the original table, as any user with SELECT permission could retrieve all data from the table. The access to the table data should be controlled by a view that returns only those records the user is authorized to see. In most cases, a non-privileged user should access her own records and the records where she's listed as an authorized reader.
In large-scale deployments, you should not rely on granting permissions to individual users; using database roles to specify table/view permissions and controlling users' role membership scales better. You might also want to extend my solution to allow more than one writer/reader per record. The best approach would be to create an auxiliary table that would store multiple reader/writer values for each row in the original table.
For more information on SQL Server, visit the following InformIT resources:
Microsoft SQL Server 2005 Unleashed
InformIT SQL Server Reference Guide, hosted by Buck Woody