Limiting Record Deletions

Question

Post reply

Limiting Record Deletions

Eric Weiss

SSC Enthusiast

Points: 142
More actions
February 10, 2005 at 7:24 am

#60692

I am looking for a way to limit users ability to deleting their own records from a table. There is a user ID stored in the table. Our system allows certain users link to SQL Server using Access or even QA with delete permission on the table. So far I've come up with three possible solutions: use a stored procedure, use a view or use a trigger. What has been your experience with performance? We could end up protecting 100 tables so do you have any tricks that might help?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

I've never actually done this but a view seems to be pretty easy to implement :

Select col1, col2, id from dbo.TableX where user_id =

dbo.FnTranslateUNameToUid(user_name())

where FnTranslateUNameToUid would simply translate the user's login name to it's user id

Charles Otten Default port Points: 1475 More actions · Answer 2

Views would be my suggestion. Give them the ability to delete records from the view, but not delete permissions on the table.

But why would users have this kind of access to the data? I understand why developers would need it, but a wide open front end can open your database up to alot of issues (accidental deletes, inability to track source of data changes, users who know just enough to be dangerous, etc.)

I would suggest a better built front end in a true programming language (VB.Net, C#, Java, etc.)

If the phone doesn't ring...It's me.

Eric Weiss SSC Enthusiast Points: 142 More actions · Answer 3

I agree that a good front end would be the best way to go, but this is a legacy system with 500 tables, 300 stored procedures and limited documentation. We have a front end that violates every design rule in the book. I've suggested a re-engineering, but that is several years away.

Charles Otten Default port Points: 1475 More actions · Answer 4

Look at it as job security. Then my suggestion is to use views that do not allow deletions (where needed) and also some triggers could help. But be careful with the triggers as you could run into some pretty big blocking scenarios if you get a large rollback.

The best is stored procedures but that may also take along time to manage. Since the stored procedures could do all the business logic check before the actual delete SQL is done.

If the phone doesn't ring...It's me.