locked
Row Level Permissions and Synchronization RRS feed

  • Question

  • I have a table with row level permissions. The permissions are stored in a seperate table (ie, foriegn key reference). I have set bidirectional synchronization with the table.

     

    For normal updates/adds and deletes it works fine. If I change a row level permission the client may require an add or delete of the row. The row is not physically deleted on the server (therefore not in the _tombstone table). I don’t want to have every row from the server synched with the client unless they have permission to see it.

     

    Is there a nice way to handle using the framework?

     

    I am struggling to find a suitable workaround.

    • Moved by Max Wang_1983 Thursday, April 21, 2011 10:18 PM forum consolidation (From:SyncFx - Technical Discussion [ReadOnly])
    Friday, January 4, 2008 4:31 AM

Answers

  • Hi Joel,

     

    I believe this issue you are referring to is commonly called a territory realignment problem which can be a difficult problem to solve.  It is called that because this is something you would commonly see in sales applications where sales reps are assigned a certain set of customers (based on a permissions table governing which contacts they are allowed to see). 

     

    Then when the sales rep territory changes you need to remove the old customers and add the new ones (without physically removing the customers from the server side).  The following is just one example which I hope you find useful for your scenario. 

     

    The following incremental download query will downloads only the current customers where information has changed since the last successful download. It will also only bring down the customers assigned to a specific sales rep.  Note, I put this together pretty quickly so hopefully at least you will get the concept.

     

    SELECT cust_id, Customer.name, Customer.rep_id
    FROM Customer key join Permissions
    WHERE Customer.LastUpdated >= @sync_min_timestamp 
    AND Permissions.UserID = <USERID PARAMETER>
    AND Customer.Active = 1

     

    The following tombstone delete sql statement would bring down only customers where the information has changed since the last sync.  It will also remove the customers that this sales rep is no longer assigned to. 

     

    SELECT cust_id
    FROM Customer key join Permissions
    WHERE Customer.LastUpdated >= @sync_min_timestamp 
    AND ( Permissions.UserID != <USERID PARAMETER> OR Customer.Active = 0 )

     

    In this example if a row is physically deleted from the customer table on the server side they will not be selected in this result and as such will not be deleted on the remote side.  Instead, customers are marked as inactive. When territories are realigned, this script will delete those customers that are not assigned to the sales rep anymore.

     

    Liam

     

    Friday, January 4, 2008 7:48 PM

All replies

  • Hi Joel,

     

    I believe this issue you are referring to is commonly called a territory realignment problem which can be a difficult problem to solve.  It is called that because this is something you would commonly see in sales applications where sales reps are assigned a certain set of customers (based on a permissions table governing which contacts they are allowed to see). 

     

    Then when the sales rep territory changes you need to remove the old customers and add the new ones (without physically removing the customers from the server side).  The following is just one example which I hope you find useful for your scenario. 

     

    The following incremental download query will downloads only the current customers where information has changed since the last successful download. It will also only bring down the customers assigned to a specific sales rep.  Note, I put this together pretty quickly so hopefully at least you will get the concept.

     

    SELECT cust_id, Customer.name, Customer.rep_id
    FROM Customer key join Permissions
    WHERE Customer.LastUpdated >= @sync_min_timestamp 
    AND Permissions.UserID = <USERID PARAMETER>
    AND Customer.Active = 1

     

    The following tombstone delete sql statement would bring down only customers where the information has changed since the last sync.  It will also remove the customers that this sales rep is no longer assigned to. 

     

    SELECT cust_id
    FROM Customer key join Permissions
    WHERE Customer.LastUpdated >= @sync_min_timestamp 
    AND ( Permissions.UserID != <USERID PARAMETER> OR Customer.Active = 0 )

     

    In this example if a row is physically deleted from the customer table on the server side they will not be selected in this result and as such will not be deleted on the remote side.  Instead, customers are marked as inactive. When territories are realigned, this script will delete those customers that are not assigned to the sales rep anymore.

     

    Liam

     

    Friday, January 4, 2008 7:48 PM
  • Thanks, tried the example and it works well.

    Monday, January 7, 2008 10:15 PM