x

Denying certain users from updates

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
more ▼

asked Mar 02, 2010 at 12:40 PM in Default

Eric Templet gravatar image

Eric Templet
11 1 1 1

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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.

more ▼

answered Mar 02, 2010 at 02:12 PM

Kev Riley gravatar image

Kev Riley ♦♦
54.3k 47 49 76

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 ♦♦
+1 to Kev for that thought. We can hope!!!
Mar 03, 2010 at 08:50 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Mar 02, 2010 at 02:28 PM

CirqueDeSQLeil gravatar image

CirqueDeSQLeil
4.9k 10 11 15

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)
10|1200 characters needed characters left

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.

more ▼

answered Mar 03, 2010 at 08:58 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

(comments are locked)
10|1200 characters needed characters left

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  &lt;&gt; 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  &lt;&gt; 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&gt;0
    ROLLBACK TRAN

-- and replace the original transactions with the same number so that any external commits/rollbacks work
WHILE @tc&gt;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
more ▼

answered Mar 03, 2010 at 08:35 PM

Mister Magoo gravatar image

Mister Magoo
1.8k 2 3 5

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x133
x121

asked: Mar 02, 2010 at 12:40 PM

Seen: 1270 times

Last Updated: Mar 02, 2010 at 01:20 PM