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
Best Answer
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?
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
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.
Is the application user the dbo, or just belongs to the schema?
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.
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
