Denying certain users from updates

Question

question

Eric Templet asked Mar 2, '10

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

update trigger

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-03-02T14:12:48Z

Kev Riley answered Mar 2, '10

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.

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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

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

1 ·

Eric Templet · Mar 02, 2010 at 02:25 PM

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?

0 ·

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

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

0 ·

Answer 2 · 2010-03-02T14:28:40Z

CirqueDeSQLeil answered Mar 2, '10

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

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Eric Templet · Mar 02, 2010 at 02:36 PM

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.

0 ·

CirqueDeSQLeil · Mar 02, 2010 at 03:27 PM

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

0 ·

Answer 3 · 2010-03-03T08:58:12Z

Fatherjack answered Mar 3, '10

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.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 4 · 2010-03-03T20:35:45Z

Mister Magoo answered Mar 3, '10

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

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question