Controlling Data Modification at row level

[Bomza]

Hi,
I'm writing an application that involves data that has a set of users
that are allowed to perform certain operations on it.
i.e. Only the row owner can modify a row, but there is a set of users
who can view it.

At the moment, I've started to implement this by calling a UDF at the
beginning of each stored procedure that validates that the user is
allowed to call the procedure on that particular row (trusting a higher
teir to verify the user), and throws an error if they are not.

I don't particularly like this solution, as I need a UDF for each
procedure, and will have to re-write the udf's if the access rules
change (which they might).

Can anyone suggest a method of implementing a more generic row
permissions system?

Cheers,
Ben

[Simon Hayes]

"Bomza" <be******@hotmail.com> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...

Hi,
I'm writing an application that involves data that has a set of users
that are allowed to perform certain operations on it.
i.e. Only the row owner can modify a row, but there is a set of users
who can view it.

At the moment, I've started to implement this by calling a UDF at the
beginning of each stored procedure that validates that the user is
allowed to call the procedure on that particular row (trusting a higher
teir to verify the user), and throws an error if they are not.

I don't particularly like this solution, as I need a UDF for each
procedure, and will have to re-write the udf's if the access rules
change (which they might).

Can anyone suggest a method of implementing a more generic row
permissions system?

Cheers,
Ben

Unfortunately there's no built-in or generic solution, so you need to
implement something yourself. Here's an alternative view-based approach,
which might give you some more ideas:

http://vyaskn.tripod.com/row_level_s..._databases.htm

Simon

[QueryBuilder]

Views are of good use when situations like this.
In your table create a field called userID and assing the userID to
each row that has modifying permission. Create a view like this for
egsample.....

Create view <name>
AS
Select col1, col2, col3
from <table>
where userid = sUser_sName().

Then update the table by updating this view so that only those records
that are visible to that user can update these records on the
destination table.....!

Also create another view where userID<> sUser_sName() and grant only
view permission to the users... So they cant update the table thru this
view...!

This is just a guess. I have not tried it myself. So feel free to
comment on this..!!

Good luck..!

[Bomza]

They're both interesting ideas, and are probably a fair bit faster than
the way I came up with. I like the idea of being able to throw an
error when a client does something illegal rather than just doing
nothing and as I'm using ADO.NET, my "authorising" UDF method provides
a neat way of doing it. At worst the UDF does an EXISTS on a SELECT
statement, so its not a totally horrible method.

It must be a fairly common problem, so when I'm finished I might turn
it into a generic framework where you can grant and evoke various
permissions to any users on any rows, but at the moment I've just got
to get this working :) I think its all been addressed in SQL 2005
anyway...

[Erland Sommarskog]

Bomza (be******@hotmail.com) writes:

It must be a fairly common problem, so when I'm finished I might turn
it into a generic framework where you can grant and evoke various
permissions to any users on any rows, but at the moment I've just got
to get this working :) I think its all been addressed in SQL 2005
anyway...

Not really. There is no particular support in SQL 2005 for row-level
security either.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Andy O'Neill]

"Bomza" <be******@hotmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
<<>>

It must be a fairly common problem, so when I'm finished I might turn
it into a generic framework where you can grant and evoke various
permissions to any users on any rows, but at the moment I've just got
to get this working :) I think its all been addressed in SQL 2005
anyway...

I usually address this in the front end design.
Something like.
All users see everything in one screen is read only.
There are links butons or whatever take them to another screen to do
updates.
The update screen is only available to supervisors or when you pick data is
maintained by your team.

This does of course rely on users not trying to break the system by
connecting up using odbc or something.
But.
I suspect others use the same sort of approach and that's why there aren't
loads of solutions available.

--
Regards,
Andy O'Neill