Slashdot Mirror
Keeping Customer From Accessing My Database?
cyteen02 writes "We run a data processing and tracking system for a customer in the UK. We provide a simple Web site where the customer can display the tracking data held in our Oracle database. From these screens they can query based on a combination of 15 different data fields, so it's pretty flexible. We also provide a csv report overnight of the previous day's data processing, which they can load into their own SQL Server database and produce whatever reports they want. Occasionally they also want one-off specific detailed reports, so we write the SQL for that and send them the results in an Excel format spreadsheet. This all ticks along happily. However they have now asked for direct read-only access to our Oracle database, to be able to run ad-hoc queries without consulting us. As a DBA, my heart sinks at the thought of amateurs pawing through my database. Unfortunately, 'because you are stupid' is not considered a valid business reason to reject their request. So can any Slashdotters assist me in building my case to restrict access? Have you experienced a similar situation? Have you had to support this sort of end user access? How would you advice me to keep my customer away from my precious tables?"
I've run into this myself.
I simply say that "Due to other client data being in the same database I am unable to allow you access. Since doing so would violate the privacy and security of their data, I sure that you would understand why I can't do that. I'm also sure how you would feel if the roles were reversed and how you would feel if another client asked for direct access and could see or read your data."
Usually this takes care of the problem. If not, tell them how much it will cost to set up a stand alone database that only contains their data and then give them some unreasonable amount. If they agree, then you just made your company a nice chunk of change. You then set up the database, and the scripts to replicate the data to back it up (when the client hoses themselves) and move on. When the call comes in that they hosed their database, you charge them for the time to restore from the back-up times a factor of two or three, and again, you've set yourself as the goldenchild for your company by making them money.
-Goran
Carpe Scrotum - The only way to deal with your competition.
This space for rent.
I would second this notion. This is the classic way to solve this problem. Modern databases provide many mechanisms to periodically push over changes to another cloned copy of the database. The advantage of doing this is you can do interesting things to the cloned data that you would be unwilling to do to a live database. For example, you can create temporary reports that get stored in another temporary database table and which in turn allow other reports to be created from this derived table. Oracle lets you do this pretty easily by creating "views". There is a whole industry built around this approach called "data cubing" with specialized tools to let you construct more involved data mining types of queries based on massaging the data in interesting ways.
So can any Slashdotters assist me in building my case to restrict access?
I think he was asking us to help him flesh out his argument, ie give him some reasons to back up his intuition. Well intentioned or not, your response is like telling someone who's asking for directions that they are lost.
Well.. maybe. Or Maybe not. But Definitely not sort of.
Oracle has a different concurrency model to older versions of MS-SQL. There are no read locks.
In Soviet Amerika the ballot boxes YOU!
Further down this thread people start mentioning the silly query overloading the server issue. Now this is a real issue but it can be made to work either way you want. We had a similar request from a customer several years ago but we were not so opposed to giving them read only access if it could be done safely. We choose to set up a separate replicated server that they could query directly. If they wipe out the server with a silly query, who cares since it only effects them. The work involved in setting this up, its maintenance and hosting were all chargeable thereby making us more profit. This keeps them happy, the management team happy and me happy since my company operates a profit sharing scheme.
If you still are unable to see the benefit of giving them access then the best bet might be an intellectual property argument. Depending on whether you or they own the IP of the system you provide you may be able to argue that the database structure is a proprietary work and that exposing it would be against company policy in that regard.
Somewhere I used to work had a less than optimal database structure we all inherited from the previous developers who build the system. We knew how bad the design was but changing it was a huge job that we could not make the time to do as we were busy on paid work for other clients. We successfully avoided letting the customer see how awful the design was until the contract ended (it was a fixed term job that could not be extended) by making the IP argument.
I dont read
creating views limited to their data is a great, easy solution for limiting rows within the same table.
looking at it from a longer term perspective, if you think that this ability is something many of your customers would use, you can look in to what oracle calls Virtual Private Database (VPD). VPD allows you to set rules on tables that, on the server, adds a predicate to all SQL ran against that table. Essestially, it forces a WHERE clause of your choosing for all select, insert, update, delete statements.
if you tie that to a lookup table, say of logged on user's CompanyID#, any statement they send can only ever affect that subset of rows.
'delete * from orders' from Ted@Acme Inc (ComanyID#=5) would turn into: 'delete * from orders where companyid#=5'
fun stuff