| Author |
Topic |
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
 Posted - 2012-06-11 : 14:05:46
|
| Hi all, How can I restrict "Update" statement as not to be executed without "Where" clause, on all tables of a database for certain users. For example, I've a databases with the name of "test" having 10 tables. I want to restrict two users "A" and "B" that they must specify Where clause while executing Update query. Many thanks!CheersMIK |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2012-06-11 : 14:11:52
|
| A typical security scheme would be:Create parameterized Update stored procedures for tables in these DBs. Grant execute priv on those SPs to those users. Then remove direct access to those tables for those users.Be One with the OptimizerTG |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Sachin.Nand
2937 Posts |
Posted - 2012-06-11 : 15:07:31
|
| One way would be create a DML trigger for update/delete and in the trigger query the sys.dm_exec_sessions cross apply with the sys.dm_exec_sql_text based on the @@spid and check whether the text for that session id has a where clause in it.If no then rollback the transaction.After Monday and Tuesday even the calendar says W T F .... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-06-11 : 15:12:10
|
quote:
Originally posted by Sachin.Nand
One way would be create a DML trigger for update/delete and in the trigger query the sys.dm_exec_sessions cross apply with the sys.dm_exec_sql_text based on the @@spid and check whether the text for that session id has a where clause in it.If no then rollback the transaction.
The problem with a solution like that is how expensive it'll be. Depending upon how big the transaction is, or in other words how big the table is, this could be a very costly trigger.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2012-06-11 : 15:37:17
|
| The understanding I have from OP's requirement is that he/she wants to prevent update/delete ad-hoc queries being fired without predicates say for example using SSMS.Also I am not sure how would the SP solution would force some users to use a where clause and others not.After Monday and Tuesday even the calendar says W T F .... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-06-11 : 16:05:34
|
| The stored procedure approach guarantees what code the user can run as the user would not have any permissions to the tables.So the stored procedure(s) would have to be coded to handle the queries the user needs to do. A stored procedure approach is the best solution for what the OP wants to do. It'll take a bit of coding, however it isn't an expensive operation in terms of the transaction.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2012-06-11 : 16:32:32
|
That still doesn't answer my question.How are you going to force users to use a where clause in an update query ?The trigger wont be expensive cause it would be conditional.Something like thisCREATE TRIGGER MyTrigger ON MyTable FOR DELETE,UPDATEAS BEGIN DECLARE @loginame varchar(50) SELECT @loginame=login_name FROM sys.dm_exec_sessions WHERE session_id = @@spid IF @loginame ='Login which does not have permission to use update/delete without where clause' ---Check the querytext whether it has where clause in it.If not then rollback.END After Monday and Tuesday even the calendar says W T F .... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-06-11 : 16:39:10
|
| You are missing my point completely about stored procedures. The user would not write the stored procedure, the OP would or some other SQL developer but not these users. Then the users would have to use those to make their updates.Yes the trigger would be expensive as the rollback time adds to the transaction time. Take an update of a large table. Let's say they accidentally updated the entire table and it has millions of rows in it. Let's say that query took 30 seconds. You now have to roll it back, so it's going to take 60 seconds or so total. An update like that would do an exclusive lock on the table, so we've about doubled the time the table is locked.With the stored procedure approach, it never would take the 30 seconds as they wouldn't have been allowed to do that query.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-06-11 : 16:39:51
|
quote:
Originally posted by Sachin.Nand
That still doesn't answer my question.How are you going to force users to use a where clause in an update query ?The trigger wont be expensive cause it would be conditional.Something like thisCREATE TRIGGER MyTrigger ON MyTable FOR DELETE,UPDATEAS BEGIN DECLARE @loginame varchar(50) SELECT @loginame=login_name FROM sys.dm_exec_sessions WHERE session_id = @@spid IF @loginame ='Login which does not have permission to use update/delete without where clause' ---Check the querytext whether it has where clause in it.If not then rollback.END
Your trigger code is not why it would be expensive. It has to do with the transaction.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2012-06-11 : 17:22:12
|
| I quite understand the issues wrt to triggers and there are ways to get around this.One way would be to change the trigger to an Instead of trigger which would update/insert for the same table only if the query text contains a where clause for a particular user.So the whole insert and rollback wont happen in the first place if the query does not have where clause.I am still trying to understand how a stored procedure would suffice the requirement.Say a table has 20 columns and user can update the underlying table using 10 where conditions.So my SP would take 10 parameters just for the where condition.So when user wants to update the table with only one where condition he would have to pass NULL values to the other 9 parameters.Sounds interesting...After Monday and Tuesday even the calendar says W T F .... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-06-11 : 17:56:56
|
| I'm not saying that a stored procedure is going to be quick to write, I'm saying it's the best solution. I wouldn't even recommend one stored procedure, I'd likely use multiples. There's probably only a few columns they need to update. I'd discuss with the users exactly what their update requirement is and get them into stored procedures.An INSTEAD OF trigger does get around the transaction issue, however a trigger fires for every user and not just these users you are trying to get to stop forgetting the where clause. I can think of two problems with this approach: 1. you can't use an instead of delete or update trigger on a table that has delete or update action on the foreign key. I've got this all over the place on my systems. 2. you'll need to add logic so that certain users can do the update or whatever without a where clause, such as a DBA needing to fix data. I don't about you, but my developers have bugs in their code that sometimes require manual intervention to fix. If I remove the trigger even for just a few seconds, those other users could slip in with their bad query.I like to come up with the best possible solution that handles all requirements, and the stored procedure approach does that. It requires some coding though.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2012-06-12 : 00:04:51
|
| An instead of trigger wont work on foreign keys that has cascading action set on them else it wont be much of an issue.If cascading is the case then the other way round is to set read snapshot isolation enabled on the database and implement the trigger(the rollback one). So there wont be any noticeable blockings on the tables which have exclusive locks.The reason I casted by doubt on the SP solution from the start was because at my job each day on an average 50 to 60 ad hoc update queries with an seemingly endless permutations and combinations are fired by the developers and me to fix data issues that come up each day(we are into stock trading so we get lot of 3rd party data from various sources).I definately would have my a** kicked up by themAfter Monday and Tuesday even the calendar says W T F .... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-06-12 : 12:24:01
|
| I like your idea of switching isolation levels, but the OP needs to fully understand it. You generally have to code for the isolation level change or be aware that you are getting data that could be in the process of being changed. We use RCSI on all of my systems here, the ones where I'm primary DBA on. RCSI works beautifully on these systems and without any needed code changes. An ecommerce system would require changes to use it. Imagine an inventory system, someone is purchasing the last item, and another person comes along and is reviewing it and it still says one is available. I definitely see your point about stored procedures about it being a cumbersome task to write them all. But it's what solves this problem. The stored procedures could use dynamic SQL, but then direct table access is required which doesn't solve the problem. I wonder if you can do the execute as thing with dynamic SQL in a stored procedure. That might solve this whole thing.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
|
Restriction on the where clause
Tara Kizer