-1

The scenario is that our client owns and manages a system (we wrote it) hosted at their clients premises. Their client is contractually restricted from changing any data in the database behind the system but they could change the data if they chose because they have full admin rights (the server is procured by them and hosted on their premises).

The requirement is to get notification if they change any data. For now, please ignore deleting data, this discussion is about amendments to data in tables.

We are using Linq to Sql and have overridden the data context so that for each read of the data, we compare a hash of the rows data against a stored hash, previously made during insert/update, held on each row in the table.

We are concerned about scalability so I would like to know if anyone has any other ideas. We are trying to get notified of data changes in SSMS, queries run directly on the db, etc. Also, if someone was to stop our service (Windows service), upon startup we would need to know a row had been changed. Any thoughts?

EDIT: Let me just clarify as I could have been clearer. We are not necessarily trying to stop changes being made (this is impossible as they have full access) more get notified if they change the data.

Lewis Harvey
  • 332
  • 2
  • 10
  • 1
    Whatever you do to block access in SQL Server you will not be able to stop them, as local admins on the server from: 1. stop SQL server, 2. install second instance of SQL Server including themselves in the dbadmins role, 3. attaching your database to the new instance. In the end they have physical access so can get at the data. Better to think about working *with* your clients and remember who is paying the bill. – Richard Nov 01 '13 at 13:25
  • I don't think you will be able to do this, they have access to the hardware. They can change this as they see fit and when it comes down to it all the security resides on the hardware. – Liam Nov 01 '13 at 13:26
  • Interesting comment Richard, it is our client that has requested we do this - I'd consider that working "with" our client. But thanks for the advice. – Lewis Harvey Nov 01 '13 at 13:28
  • 2
    Sounds like they are trying to push their network security onto you. I'd have nothing to do with it or else you (your company) will be liable to any breaches that they are responsible for. – Liam Nov 01 '13 at 13:30
  • I'm confused. In one paragraph you say "the requirement is to stop them changing any data" then in another you say that isn't the requirement. Edit, thats better :) – Andrew Nov 01 '13 at 13:35
  • Yes sorry Andrew, my mistake, I have reworded it. We are more interested in being notified (by our system or something else) if a change is made. – Lewis Harvey Nov 01 '13 at 13:37

1 Answers1

2

The answer is simple: to prevent the client directly manipulating the data, store it out of their reach in a Windows Azure or Amazon EC2 instance. The most they will be able to do is get the connection string which will then connect them as a limited rights user.

Also, if someone was to stop our service (Windows service), upon startup we would need to know a row had been changed.

You can create triggers which will write whatever info you want to an audit table, you can then inspect the audit table to determine changes made by your application and directly by the client. Auditing database changes is a well known problem that has been solved many times before, there is plenty of information out there about it.

for each read of the data, we compare a hash of the rows data against a stored hash

As you can probably guess, this is painfully slow and not scalable.

slugster
  • 49,403
  • 14
  • 95
  • 145