Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2004
    Posts
    31

    Unanswered: Selective Updates based on User

    We have a Table that contains a schedule. The schedule has certain pieces of information that are required to be updated by certain members of the
    organization, specifically Shift, Start Date, and Line. Each record also has a column for the customer that it is for.

    The schedule has a Column, [CSR] which lists the name of the person who is responsible for updating it. Format of that is domainname\johndoe

    What we would like to do is block domainname\johndoe from being able to update or insert on any of the records that he is not responsible for.

    So if he attempts to change the shift for example, it denies the change and possible pops up an error indicating that the change was blocked because he is not the responsible party.

    Input in this is through a query which views the table data in MS Access. It cannot be moved to a Form without some serious redesign, so I cannot put the block in there. I assumed that this could be blocked with a Trigger that checks the [CSR] column, matches it to the logged in user through the suser_sname(suser_sid()) and then either allows or denies the update.

    So is this a viable idea, or should I explore the move to an input form and make the change there in MS Access via VBA.

    At first we started with just an audit trail, but I started to think that the audit trail would be needed only for records that should be allowed to be changed and that we should deny changes to those who are not allowed to make them.

    Below is the audit trail trigger, I figure that the change should occur somewhere within that first IF statement, or make the first IF the second, and the first should be the permission check.

    At this point I am stuck as to how to block the updates. I tried using Deny but that was not working. Filtering the results by only showing the logged in user their records is not an option as they need visibility to the entire schedule.

    This is for SQL 2000 SP4/Windows 2000 SP4

    Code:
    CREATE TRIGGER audit_mschange
    ON dbo.T_PP_Table_2 
    FOR update AS
    	IF (UPDATE([Start Dt]) OR Update(shift) or Update
    (comments) 
    	or Update(status) or Update (line))
             BEGIN
      
    	      INSERT INTO T_MS_RB_AUDIT
    	        
    (ord_no,line_tank_old,line_tank_new,shift_old,shift_new,comments_old,comments_new,status_old,status_new,trx_dt,trx_username,
    start_dt_old,start_dt_new)
    
    	   	SELECT ins.[PP Ord No],
    	            del.line,ins.line,
    		    del.shift,ins.shift,
    		    del.comments,ins.comments,
    	            del.status, ins.status,
              	    getdate(),
    		    suser_sname(suser_sid()),
    		del.[Start Dt],
    		ins.[Start Dt]
    		 FROM inserted ins, deleted del
    		 WHERE ins.[PP Ord No]=del.[PP Ord No] AND
    		       ins.autonumber=del.autonumber AND
    			(ins.status <> del.status OR ins.[Start Dt] <>
    del.[Start Dt] or ins.status <> del.status
    or ins.comments <> del.comments or ins.line <>
    del.line )
    END

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    To block the updates you could utilise RAISERROR()

    Or alternatively you run updates like this
    Code:
    UPDATE myTable
    SET     myField = 'new value'
    WHERE  id = 1
    AND user = suser_sname(suser_sid()) 
    This will simply not let the update happen for any other user.
    George
    Home | Blog

  3. #3
    Join Date
    Mar 2004
    Posts
    31
    Thanks for the nudge in the right direction. The RAISERROR() worked.

    Here is the new trigger and it was just tested and works but a little too good.
    It seems to blocking all updates even when it should allow them.

    Code:
    CREATE TRIGGER audit_msdeny
    ON dbo.T_PP_Table_2
    FOR UPDATE
    AS
    DECLARE @@CSR nvarchar,
       @@CSR_BACKUP nvarchar,
       @@CSR_PACKETTES nvarchar
    SELECT @@CSR = ins.CSR, 
       @@CSR_BACKUP = ins.CSR_BACKUP, 
       @@CSR_PACKETTES = ins.CSR_PACKETTES
    FROM dbo.T_PP_Table_2 PP,inserted ins 
    WHERE PP.[PP Ord No] = ins.[PP Ord No] AND PP.autonumber = ins.autonumber
    IF (@@CSR <> suser_sname(suser_sid())) or (@@CSR_BACKUP <> suser_sname(suser_sid())) or (@@CSR_PACKETTES <> suser_sname(suser_sid())) and (UPDATE([Start Dt]) OR Update(shift) or Update (comments) 
    	or Update(status) or Update (line))
    BEGIN
       RAISERROR ('You are not responsible for this customer.  NO Change Allowed', 16, 1)
       ROLLBACK TRANSACTION
    END

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This code:
    Code:
    DECLARE @@CSR nvarchar,
       @@CSR_BACKUP nvarchar,
       @@CSR_PACKETTES nvarchar
    SELECT @@CSR = ins.CSR, 
       @@CSR_BACKUP = ins.CSR_BACKUP, 
       @@CSR_PACKETTES = ins.CSR_PACKETTES
    FROM dbo.T_PP_Table_2 PP,inserted ins 
    WHERE PP.[PP Ord No] = ins.[PP Ord No] AND PP.autonumber = ins.autonumber
    ...assumes that only one record will be inserted/updated at a time. Your trigger needs to be coded using set-based operations to handle multi-record transactions.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Mar 2004
    Posts
    31
    I am not quite sure what that means. I do not think that more then one record would need to be updated at a time. Do you mean single record in a single column, or multiple changes within a particular record row?
    Last edited by Rex Deckard; 11-28-07 at 11:06.

  6. #6
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    simple example. this will update all rows in a table at once as there is no where clause. your trigger would be broken in such a case.

    update MyTable set MyColumn='bleh'

  7. #7
    Join Date
    Mar 2004
    Posts
    31
    Ok, I think I am tracking here.

    But I am not sure where to start the change. I am new to Triggers.
    It sounds like the entire thing needs to be redone.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What version of SQL Server are you using?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Mar 2004
    Posts
    31
    Quote Originally Posted by blindman
    What version of SQL Server are you using?
    This is for SQL 2000 SP4/Windows 2000 SP4

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I suspect the reason that your trigger is refusing to allow modifications is due to your use of the UPDATE() function. Contrary to what you might think, this function does not actual indicate whether a column's value was changed. It only determines whether the column was listed in the update statement. So, if you update column MYCOL with the value "A" to "A", UPDATE(MYCOL) will return true, even though no change occured.
    Since most applications read and write entire records rather than just the portions of the record that were modified by the user, UPDATE() is high on my list of the most useless and misleading SQL functions.
    You need to actually compare the values in the INSERTED and DELETED tables to see if any change took place, if you insist on doing this in a trigger. IMO, you are better off restricting access to the table through stored procedures and implementing your security checks there.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Mar 2004
    Posts
    31
    Quote Originally Posted by blindman
    I suspect the reason that your trigger is refusing to allow modifications is due to your use of the UPDATE() function. Contrary to what you might think, this function does not actual indicate whether a column's value was changed. It only determines whether the column was listed in the update statement. So, if you update column MYCOL with the value "A" to "A", UPDATE(MYCOL) will return true, even though no change occured.
    Since most applications read and write entire records rather than just the portions of the record that were modified by the user, UPDATE() is high on my list of the most useless and misleading SQL functions.
    You need to actually compare the values in the INSERTED and DELETED tables to see if any change took place, if you insist on doing this in a trigger. IMO, you are better off restricting access to the table through stored procedures and implementing your security checks there.
    Ok, I have done that in other Triggers, the comparison between Ins and Del, so I will have a crack at that.

    If that does not work, then I will start looking at Stored Procedures.

    Thanks for all of your input. Though I might be back.

  12. #12
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by blindman
    IMO, you are better off restricting access to the table through stored procedures and implementing your security checks there.
    The blindman speaks truth...

    IMO, triggers are best left to things like audit trails and logging. They should have minimal logic.

    Regards,

    hmscott
    Have you hugged your backup today?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •