By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,837 Members | 1,195 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,837 IT Pros & Developers. It's quick & easy.

Controlling Data Modification at row level

P: n/a
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

Jul 23 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a

"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
Jul 23 '05 #2

P: n/a
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..!

Jul 23 '05 #3

P: n/a
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...

Jul 23 '05 #4

P: n/a
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
Jul 23 '05 #5

P: n/a
"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
Jul 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.