Stop Clients Changing Data in Database Directly

[leeneilson]

Hi,

I was wondering how people deal with stopping users changing data in databases directly.

Here is the scenario:

You have created a great application that you sell to clients, some of these clients prefer to host it internally on their own servers. These clients may have access to all settings on the servers and know how to use their chosen SQL server well.

To stop these clients copying your code or breaking it etc, you decide to encrypt the php using such products as Zend Guard or ionCube PHP Encoder.

The problem now is that as they have access to the databse. They may be able to look at the structure and data and figure out how to put data in and modify it directly.

Over time these clients may want new systems to work with your application, but not want to pay you to develop these links.

So they get someone else to develop applications that change the data in your systems database. Then as they didn't understand the databse fully the data in the database gets corrupted and the application encounters errors.

Then you get a support call from them to fix this.
They may not tell you about the other application and swear to you that your application must have errors in it.

I know of some people who add database triggers to log queries, however as they have access to the database this sort of thing could be covered up before they seek suppport.

I was wondering if anyone is dealing with this sort of situation at the moment and how they go about tackling it.

Also if anyone has any ideas of how this can be done well.

[r937]

my advice is to help them by providing complete documentation of the database to make it easier for them to know when they make changes whether it will affect your application code

[DaveMaxwell]

Unfortunately, once you sell the web app to them, it's theirs to do with as they see fit. And it is their data, so they have the right to change it however and whenever they like.

Like Rudy says, providing complete and full documentation will help, and though you may not get additional work at that time, you may in the future.

Also, what type of support agreement do you have in place? Is there a time frame/cost structure defined? You don't need to support something forever, so define a baseline support structure, then offer additional support later for additional cost.

[leeneilson]

Thanks for your comments.

I wouldn't want to supply documentation on the database as I wouldn't want to encourage people to mess with it.
Most of our clients would not want to know about the database, and if they do want to play with it they may not necessarily ask us about it first.

As the web application is sold to lots of customers the ownership of the code itself is written in the contract as remaining our property, though the data is theirs as you mentioned.

The support contract is renewable each year, this includes bug fixes and free updates of the system.
Some of our clients have been running the system for several years so far.

If they chose not to renew this then the code remains on their server encrypted and no futher support is offered.
If they wish to have certain data extracted in a specified format, then we offer this at the end of the contract for a small fee.

The support contract has no constraints upon it, however we do not want to support issues resulting from users messing up the database and data links.

I was thinking about the use of a checksum in each row, but that would mean reading all the values and calculating this each time.

Then I was thinking of encypting all data in the system, but also this has a big performance issue.

I was hoping that there might be people out there that have a better solution for us to use.
I have found loads of information on encypting php code, but none on protecting the database.

[r937]

you charge your users for extracting their own data from the database?

that's actually a really good incentive to make them want to "mess with it"

[Jake Arkinstall]

I agree with Rudy on this one.

It's their application, let them do what they want with it.

If they want to mess with the database structure, by all means let them!

If you want to look at it from the businessman point of view, it allows you to charge a fee for fixing it for them.

[DaveMaxwell]

you charge your users for extracting their own data from the database?

that's actually a really good incentive to make them want to "mess with it"

Exactly, and charging them for something which belongs to them is incentive (at least to me) to never use you again. I've worked for companies which took the same type of approach and each one of them ended up losing work because of it. It's their data, not yours. Especially if it's hosted on their servers.

Have you actually run into this problem, or is it theoretical in nature? Because you're heading into murky waters here, and I would (strongly) advise you to tread lightly.

You are better off providing them with either the database structure, or an API which allows you to control how the data gets into the system - which would be a more user friendly (and more profitable) approach to take.