locked
SQL Azure sync framework and security RRS feed

  • Question

  • I am considering usin the sql azure sync framework in a multi-tenant environment.  ie  The Customer table has a CustomerId (Guid) and most other tables to be synchronised have a foreign key relationship to the Customer table.
    I am happy that other customers GUIDs are secret and impossible to guess.
    What things will I need to do to make the system reasonably secure?
    Monday, February 1, 2010 1:46 PM

Answers

  • As the article pointed, filtering should not be implemented really for managing security. In your case, if somehow I can guess the GUID, then your data for one customer can be compromised.
    Maybe you can build another level of authentication above the data layer and see what logins have what access and also implement some stored procs that can access only customer specific data that they are allowed to.
    This posting is provided AS IS with no warranties, and confers no rights
    • Marked as answer by simon831 Monday, February 8, 2010 12:17 AM
    Monday, February 8, 2010 12:15 AM

All replies

  • I am not clear on what you are asking. Are you saying that certain customer table rows and other related rows from other tables need to be viewable only by certain logins and not by others?


    This posting is provided AS IS with no warranties, and confers no rights
    Wednesday, February 3, 2010 5:47 AM
  • The sync framework documentation says that you should not rely on GUIDs to provide security. And I was, assuming that the sync framework would download only information for the current customer was was logged in (via CustomerId (guid)).  And sql injection attacks would be protected from with guids being impossible to guess.
    Wednesday, February 3, 2010 7:49 AM
  • I still do not completely understand your scenario. Are you filtering your data based on this GUID and assuming that the other users cannot guess this GUID and hence cannot access that data. Maybe a link to the doc page you refer would be helpful and some more insight into your scenario may help clarify.
    This posting is provided AS IS with no warranties, and confers no rights
    Thursday, February 4, 2010 7:50 AM
  • Yes, I am filtering data based on a guid. It seems reasonable to assume that a guid cannot be guessed. Theres no financial transactions involved, so all I need is a reasonable level of security.

    The page I have been reading is this:
    Do not rely on filtering for security. The ability to filter data based on a client or user ID is not a security feature. 
    In other words, this approach cannot be used to prevent one client or peer from reading data that belongs to another client or peer.
    This type of filtering is useful only for partitioning data and reducing the amount of data that is synchronized.
    http://msdn.microsoft.com/en-us/library/bb726013(SQL.105).aspx
    Sunday, February 7, 2010 5:14 PM
  • As the article pointed, filtering should not be implemented really for managing security. In your case, if somehow I can guess the GUID, then your data for one customer can be compromised.
    Maybe you can build another level of authentication above the data layer and see what logins have what access and also implement some stored procs that can access only customer specific data that they are allowed to.
    This posting is provided AS IS with no warranties, and confers no rights
    • Marked as answer by simon831 Monday, February 8, 2010 12:17 AM
    Monday, February 8, 2010 12:15 AM