Denying certain users from updates

Question

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:

CREATE TRIGGER [dbo].[Restrict_Primary_Location_Update] ON [dbo].[Professional_Location] INSTEAD OF UPDATE AS BEGIN IF ( SELECT i.last_record_update_user FROM inserted i INNER JOIN professional_location (NOLOCK) pl ON pl.ProfLoc_Identity = i.ProfLoc_Identity WHERE i.primary_location <> pl.primary_location ) NOT IN(***-- the group of users that I approve to make changes***)

BEGIN RAISERROR(N'You have been warned not to alter the primary address from your PDA or in TBO. The address changes you have attempted have been rejected. If you are an outside representative this means that you can no longer sync your PDA until you call IT or undo your changes',16,1)

DECLARE @email varchar(400); DECLARE @rep_name varchar(200); DECLARE @message varchar(400);

 SET @email = 'etemplet@pamlab.com';

 SET @rep_name = (SELECT First_name + ' ' + Last_name FROM dbo.employee e
                             INNER JOIN inserted i ON i.last_record_update_user = e.emp_id)

         SET @message =  'ADDRESS CHANGE ATTEMPT: ' + @rep_name + ' just tried to change an address';

             EXEC msdb.dbo.sp_send_dbmail
             @profile_name = 'TargetBackOfficeAlerts', -- For actual DB
             --@profile_name = 'TBOUser', -- For Test DB
             @recipients = @email, 
             @body = @message,
             @subject = 'Address Change Attempt'

END ELSE BEGIN UPDATE professional_location SET Professional_ID = i.professional_id , Location_ID = i.location_id , Current_Status_Code = i.Current_Status_Code , DEA_Number = i.DEA_Number , DEA_Expiration =i.DEA_Expiration , DEA_Schedule = i.DEA_Schedule , State_License_State = i.State_License_State , State_License_Number = i.State_License_Number , State_License_Expiration = i.State_License_Expiration , Customer_ProfLoc_Number = i.Customer_ProfLoc_Number , Alternate_ProfLoc_ID = i.Alternate_ProfLoc_ID , Phone = i.phone , Fax = i.fax , Pager = i.pager , Cellular = i.cellular, Email = i.email, Gatekeeper_Name = i.Gatekeeper_Name , Staff_Name_1 = i.Staff_Name_1 , Staff_Name_1_Notes = i.Staff_Name_1_Notes , Staff_Name_2 = i.Staff_Name_2 , Staff_Name_2_Notes =i.Staff_Name_2_Notes , Validation_Date =i.Validation_Date , Validation_Source = i.Validation_Source , Primary_Location = i.Primary_Location , Profloc_Effective_Date = i.Profloc_Effective_Date , Validation_Status = i.Validation_Status, Validation_Reason = i.Validation_Reason , Validation_Eligibility = i.Validation_eligibility , SLN_On_File = i.SLN_On_File , SLN_On_File_SFA_notif_Sent = i.SLN_On_File_SFA_notif_Sent , SL_Val_Status_SFA_notif_Sent = i.SL_Val_Status_SFA_notif_Sent , SLN_Validation_Sent_Date = i.SLN_Validation_Sent_Date , SLN_With_Rep = i.SLN_With_Rep , SLN_With_Rep_Date = i.SLN_With_Rep_Date , Department_ID = i.Department_ID , Rx_Sampling_Count = i.Rx_Sampling_Count , Initial_Load_Status = i.Initial_Load_Status , Inactivation_Reason = i.Inactivation_Reason , Payment_Terms = i.Payment_Terms , Credit_Hold = i.Credit_Hold , Order_Limit = i.Order_Limit , Tax_Exempt = i.Tax_Exempt , Taxpayer_ID = i.Taxpayer_ID , Bill_To_Organization_Name = i.Bill_To_Organization_Name , Bill_To_Address1 = i.Bill_To_Address1 , Bill_To_Address2 = i.Bill_To_Address2 , Bill_To_City = i.Bill_To_City , Bill_To_State =i.Bill_To_State , Bill_To_Zip = i.Bill_To_Zip , Bill_To_Country = i.Bill_To_Country , Bill_To_Attention = i.Bill_To_Attention , On_File_CC_Type = i.On_File_CC_Type , On_File_CC_Number = i.On_File_CC_Number , On_File_CC_Exp_Month = i.On_File_CC_Exp_Month , On_File_CC_Exp_Year = i.On_File_CC_Exp_Year , On_File_CC_Cardholder_Name = i.On_File_CC_Cardholder_Name , Blanket_PO_Number = i.Blanket_PO_Number , Operator_Note = i.Operator_Note , OE_Extra1 = i.OE_Extra1 , OE_Extra2 = i.OE_Extra2 , OE_Extra3 = i.OE_Extra3 , On_File_CC_Security_Code =i.On_File_CC_Security_Code , DEA_Sched_1 = i.DEA_Sched_1 , DEA_Sched_2 = i.DEA_Sched_2 , DEA_Sched_2N = i.DEA_Sched_2N , DEA_Sched_3 = i.DEA_Sched_3 , DEA_Sched_3N = i.DEA_Sched_3N , DEA_Sched_4 =i.DEA_Sched_4, DEA_Sched_5 = i.DEA_Sched_5 , DDD_Outlet_Number = i.DDD_Outlet_Number , State_License_Status_Desc = i.State_License_Status_Desc , Sampleability_License_Status =i.Sampleability_License_Status , Sampleability_Expiration = i.Sampleability_Expiration , State_Grace_Period = i.State_Grace_Period , Adjudication_Sampleability = i.Adjudication_Sampleability , Adjudication_Code_Desc = i.Adjudication_Code_Desc , State_Lic_Import_Comment = i.State_Lic_Import_Comment , website_address = i.website_address, record_create_user = i.record_create_user, record_create_Date = i.record_create_Date, last_record_update_user = i.last_record_update_user, last_record_update_date = i.last_record_update_date, r_save_timestamp = i.r_save_timestamp

FROM inserted i INNER JOIN professional_location pl ON pl.ProfLoc_Identity = i.ProfLoc_Identity

END

END

Answer 1

0

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.

answered Mar 02, 2010 at 02:12 PM

Kev Riley ♦♦
65.8k ● 48 ● 63 ● 81

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, 2010 at 02:25 PM Eric Templet

ahhh!.. is the vendor open to feature requests?

Mar 02, 2010 at 02:35 PM Kev Riley ♦♦

to Kev for that thought. We can hope!!!

Mar 03, 2010 at 08:50 AM Fatherjack ♦♦

(comments are locked)

Answer 2

0

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.

GRANT <permission> ON <schema>.<tablename>(<columnname>) TO <GRANTEE>

<permission> = SELECT | UPDATE | REFERENCES

To deny permissions on a table column -

DENY <permission> ON <schema>.<tablename>(<columnname>) TO <GRANTEE>

http://msdn.microsoft.com/en-us/library/ms187965(SQL.90).aspx

answered Mar 02, 2010 at 02:28 PM

CirqueDeSQLeil
5.5k ● 11 ● 13 ● 20

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, 2010 at 02:36 PM Eric Templet

Is the application user the dbo, or just belongs to the schema?

Mar 02, 2010 at 03:27 PM CirqueDeSQLeil

(comments are locked)

Answer 3

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 IN (... ...) into a table so you can join on it rather than look up in a text string

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.

Answer 4

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...

CREATE TRIGGER [dbo].[Restrict_Primary_Location_Update] ON [dbo].[Professional_Location] AFTER UPDATE AS


  First lets make this trigger multi-row safe by not assuming one row per update

 
  and test for any problems - if no problems, then just exit
  IF NOT EXISTS (
      SELECT TOP 1 1 
      FROM inserted AS i 
      JOIN deleted AS d 
      ON d.ProfLoc_Identity = i.ProfLoc_Identity 
      WHERE i.primary_location  <> pl.primary_location 
      AND i.last_record_update_user NOT IN (SELECT allowed_user FROM Allowed_Users WHERE table_name='professional_location')
      -- I took the liberty of assuming you are happy to put your allowed users list in a table keyed on table name
      )
  RETURN 

 
  If you reach here then the update is not allowed and you can fail the update

 
  But first you can store some data to later handle the email


 
  I put these in a table variable so that the rollback does not remove them
  DECLARE @log TABLE(whatever columns you need for the email)
  INSERT @log (your, column, list)
  SELECT your, column, list -- whatever columns you need in @log
  FROM inserted AS i 
  JOIN deleted AS d 
  ON d.ProfLoc_Identity = i.ProfLoc_Identity 
  WHERE i.primary_location  <> pl.primary_location 
  AND i.last_record_update_user NOT IN (select allowed_user from Allowed_Users where table_name='professional_location')

 
  Rollback the update(s)
  DECLARE @tc INT
  SET @tc = @@TRANCOUNT
  
    if we are in a transaction - which we should be but safety first! - then roll it back
IF @tc>0
    ROLLBACK TRAN
    
      and replace the original transactions with the same number so that any external commits/rollbacks work
    WHILE @tc>0
    BEGIN
  BEGIN TRAN
  SET @tc=@tc-1
    END

     

    

   

  

 
  Store the email info in a table
  INSERT new_log_table(same columns as @log)
  SELECT your, column, list FROM @log

 
  Now you can return an error
  RAISERROR(N'You have been warned not to alter the primary address from your PDA or in TBO. The address changes you have attempted have been rejected. If you are an outside representative this means that you can no longer sync your PDA until you call IT or undo your changes',16,1)

GO


  stored proc to be run from sql agent job


 
  will read new_log_table and send the emails out of context of the update

	Follow @Ask_SSC
	Follow Ask SSC on Facebook
	Find Ask SSC on Google+
	Find us on LinkedIn

Denying certain users from updates

Follow this question

Related Questions