Restriction in the number of records updated

Question

Restriction in the number of records updated

Luiz-458831

SSCommitted

Points: 1904
More actions
July 8, 2009 at 8:35 am

#223413

Does anybody know if it is possible to create a login/user that restricts the number of records deleted/updated ?
Something like this:
update tblCustomers set Name='John Kirby' where CustomerID=1 -- allow this
update tblCustomers set Name='John Kirby' -- don't allow this
So, in the example above, the first update works because it affects only one row, but the second must be blocked because it affects multiple rows.
This would be a login/user restriction.
Does anybody know if MSSQL2005 has this capability or if there is a way to implement this?
Thanks a lot,
Luiz.

Viewing 15 posts - 1 through 14 (of 14 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

There isn't a native way to do that. Can be done through triggers pretty easily, but not through a login/account/group permission.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Lowell SSC Guru Points: 323504 More actions · Answer 2

another way to consider is to take away access to the tables themselves, and only grant access via stored procedures.

then you simply make sure the stored proc will execute onyl agaisnt a single row,

I would guess there is always situations where you need to update multiple rows, but separate procs, where you know which procs would do mass row updates would work.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Animal Magic SSChampion Points: 13964 More actions · Answer 3

another really nasty way which in theory would work would be to put an update trigger on the table itself and count the number of rows in the "inserted" table. if its greater than one then roll back the transaction.

You would have to also work out a way of just limiting this restriction to certain user groups etc.

its a horrible way but the best i can think of at short notice.

if people cant be trusted to put a where clause in their sql statements maybe they shouldnt have access 🙂

edit : just realised thats what GSquared said 🙂

Luiz-458831 SSCommitted Points: 1904 More actions · Answer 4

Thank you all for your suggestions.

I was also thinking about using triggers but I don't want to create a trigger for each table in the database. It's not that it would be a lot of work to create a new trigger for every table, because I suppose this process can be automated, but this could hurt the database performance.

So I guess I'll just live with the risk.

Thanks again,

Luiz.

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 5

SQL ORACLE

One Orange Chip

Points: 27807

July 10, 2009 at 9:01 am

#1023115

You may try to set ROWCOUNT on the user's SSMS.

Richard M. SSCertifiable Points: 7401 More actions · Answer 6

Luiz (7/10/2009)
So I guess I'll just live with the risk.

... and good backup policy 🙂

_______________________________________________________________________
For better assistance in answering your questions, click here[/url]

GSquared SSC Guru Points: 260824 More actions · Answer 7

Auditing the data changes can eliminate/mitigate the risk. I wrote a couple of articles for SSC on that subject, about a year ago.

Making sure all access to the tables is via stored procedures that police correctly for what rows to update can eliminate the risk, so long as NOBODY accesses the tables directly. That includes devs and admins.

Backups and being able to do a point-in-time restore can also mitigate the risk.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Luiz-458831 SSCommitted Points: 1904 More actions · Answer 8

SQL ORACLE (7/10/2009)
You may try to set ROWCOUNT on the user's SSMS.

What do you mean?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Luiz (7/10/2009)
SQL ORACLE (7/10/2009)
You may try to set ROWCOUNT on the user's SSMS.
What do you mean?

Regarding SET ROWCOUNT for SQL Server 2005 Books Online:

Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server. Avoid using SET ROWCOUNT together with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. Also, for DELETE, INSERT, and UPDATE statements that currently use SET ROWCOUNT, we recommend that you rewrite them to use the TOP syntax. For more information, see DELETE (Transact-SQL), INSERT (Transact-SQL), or UPDATE (Transact-SQL).

Luiz-458831 SSCommitted Points: 1904 More actions · Answer 10

Well, if users forgets to use a where clause, chances are that they will forget to use ROWCOUNT (or TOP) as well.

Thanks, anyway.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

Why are you allowing users to write SQL directly against the database any way? Such access should be through an application or API (such as stored procedures).

Luiz-458831 SSCommitted Points: 1904 More actions · Answer 12

They are not regular users. They are new developers and made some mistakes. The same mistake that a DBA can make. So I'm just exploring alternatives.

All suggestions here are welcome.

Luiz.

Jack Corbett SSC Guru Points: 184393 More actions · Answer 13

Luiz (7/10/2009)
They are not regular users. They are new developers and made some mistakes. The same mistake that a DBA can make. So I'm just exploring alternatives.
All suggestions here are welcome.
Luiz.

Developers should only have select permissions in production and does it really matter if multiple rows are deleted in a development system?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 14

Luiz (7/8/2009)update tblCustomers set Name='John Kirby' where CustomerID=1 -- allow this
update tblCustomers set Name='John Kirby' -- don't allow this

This is because of people has unrestricted access to that poor affected database, isn't it?

Access to tables should happen exclusively thru tested and certified code, no person should be allowed to directly issue a query against production.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.