July 8, 2009 at 8:35 am
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.
July 8, 2009 at 9:01 am
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
July 8, 2009 at 9:35 am
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
July 8, 2009 at 10:30 am
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 🙂
July 10, 2009 at 8:48 am
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.
July 10, 2009 at 9:01 am
You may try to set ROWCOUNT on the user's SSMS.
July 10, 2009 at 9:18 am
July 10, 2009 at 9:26 am
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
July 10, 2009 at 9:32 am
SQL ORACLE (7/10/2009)
You may try to set ROWCOUNT on the user's SSMS.
What do you mean?
July 10, 2009 at 9:41 am
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).
July 10, 2009 at 11:12 am
Well, if users forgets to use a where clause, chances are that they will forget to use ROWCOUNT (or TOP) as well.
Thanks, anyway.
July 10, 2009 at 11:16 am
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).
July 10, 2009 at 11:30 am
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.
July 10, 2009 at 11:53 am
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
July 10, 2009 at 1:38 pm
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.Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply