Selective blocking of Inserts and Updates

[rpk740]

Not sure if the subject makes sense, but it was the best I could think of.

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.

I have built a cross reference table that contains the customer and the NT User that is responsible for that customer, example 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 customer column, checks the crossreference 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.

[ck9663]

The answer to your question is, Yes. That would be one way of doing it.

Can you try to modify the "query which views the table data in MS Access" to retrieve only data for the specific user by adding "WHERE table.UserResponsible = system_user' ?

--CK

[rpk740]

The answer to your question is, Yes. That would be one way of doing it.

Can you try to modify the "query which views the table data in MS Access" to retrieve only data for the specific user by adding "WHERE table.UserResponsible = system_user' ?

--CK

The problem with that approach is that we need the full schedule to be visible that way changes can be made with visibility to other records that have been changed.

If I limit the schedule to just those records that I am responsible for, and I want to change a date, I would not be able to see that someone already has that date booked since it would not show on my schedule.

It is not an issue that the same date could be booked, since the schedule still requires that the planners review it and make the necessary adjustments. But full visibility is the idea.

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.

This is for SQL 2000 SP4/Windows 2000 SP4

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
 

[ck9663]

The problem with that approach is that we need the full schedule to be visible that way changes can be made with visibility to other records that have been changed.

If I limit the schedule to just those records that I am responsible for, and I want to change a date, I would not be able to see that someone already has that date booked since it would not show on my schedule.

It is not an issue that the same date could be booked, since the schedule still requires that the planners review it and make the necessary adjustments. But full visibility is the idea.

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.

This is for SQL 2000 SP4/Windows 2000 SP4

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
 

i guess the trigger will be your choice. specially if this table can be modified in SQL Analyzer and similar tools. if the only way to access the table is through your GUI, i'd still recommend to pass the burden on the front-end and not at the back-end.

try adding a flag on the view that signify if the record is read-only or read-write mode. once clicked, your GUI checks if the user can actually edit the record or not. if not, prompt for a dialog box. if he can, proceed to edit mode...