|
I have a table with a column that I need to restrict who can update that column. I need everyone to be able to read the column, but only certain people to be able to make an update on it. I wrote a TRIGGER that worked great... until I put it into production, at our home office I have about 50 people reading and writing to that table constantly. My trigger quickly began to generate Deadlock and brought our system to a screeching halt. Specifically, I want to restrict who can change data (DML) if the user ID in the inserted table is a certain not a member of a certain group of people and not allow them to write their changes. Below is the Trigger I wrote that was causing the Deadlocks:
(comments are locked)
|
|
Remove all permissions from the table. Make all changes through stored procedures, and only allow execute permission on procs that update the column in question to those users/roles that are allowed. That is a great suggestion... with one problem, the mechanism that writes to this table is from a vendor application program. I don't have control over how their program writes to the table. Now, I realize I could use a stored procedure to undo/prevent any changes that an unauthorized user will make, but then that will also lock the table, right?
Mar 02 '10 at 02:25 PM
Eric Templet
ahhh!.. is the vendor open to feature requests?
Mar 02 '10 at 02:35 PM
Kev Riley ♦♦
+1 to Kev for that thought. We can hope!!!
Mar 03 '10 at 08:50 AM
Fatherjack ♦♦
(comments are locked)
|
|
Is this for SQL 2000, 2005 or 2008? Regardless of the version, I would create database roles for starters. Grant the permissions necessary to the roles, and then add users to the role. Kev has a good suggestion in applying permissions solely through stored procs. If all of your code is in procs, that will work nicely. If you are running 2005 and above, you can define permissions at the column level. For the role that you just created, deny update permission on the column in question.
To deny permissions on a table column -
http://msdn.microsoft.com/en-us/library/ms187965(SQL.90).aspx Thanks again, a great suggestion that I tried already. Here is the really crazy part, the application executes its reads/writes as dbo. Can I block dbo from the column? By the way, I am using 2005.
Mar 02 '10 at 02:36 PM
Eric Templet
Is the application user the dbo, or just belongs to the schema?
Mar 02 '10 at 03:27 PM
CirqueDeSQLeil
(comments are locked)
|
|
Given your responses to the two other answers I would look to improve the trigger in the short term (you never know the vendor may be willing to take on ideas for development). I would get the users that you have in your I would also alter the trigger to dump the data into a logging table and then, as a separate process (Agent Job), run a proc that fires the email based on rows found in there, that way the trigger is not hanging on the email process to complete in order to complete its transaction.
(comments are locked)
|
|
I have some issues with the way this trigger is written, so have "psuedo" re-written it for you to take a look at - without the actual tables I may have made typos...
(comments are locked)
|

