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?"
Presumably apart from server load it wouldn't be a big deal assuming the users are working on a read-only login?
Do they have a seat license to access the data?
No discounts.
You do not answer SQL questions.
Provide 2yr old data dictionary (just like MS does)
Exactly like WreckDiver said. Create a data warehouse that is populated on a regular basis (nightly seems a good idea), and let them touch the warehouse only. Keeps their paws off of your live database, and keeps your security in place, while giving them what they requested (just in a modified form).
Say no, it's not permissible under current UK legislation. If one customer accesses another's data, you will be in deep shit.
Tell your bosses that allowing your customers SQL access to your database could allow them to input inefficient queries that could grind your whole system to a halt. Then none of your customers will have access to the DB.
You could also say that if a security vulnerability is present in your DB vendor's product, it will be much more easily exploitable through direct SQL.
You could give them a database user that is confined to whatever database or tables you wish and can only do SELECTs. Actually you should really say too bad and if you want custom queries, you should pay us to write a front-end for what you need. This solves the access problem and the possible server load problem from whatever cross product queries they might come up with.
simple, fast homepage with your links: http://www.ngumbi.com/
Depends on the query. The right (or wrong) query could take hours to execute on a few hundred row tables.
Just create a pre-processor process that applies a set of rules to the incoming request prior to allowing it through to the database.
Undetectable Steganography? Yep, there's an app fo
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!
Coming from an ex-Oracle DBA/Administrator background myself, I recommend setting up something like the Oracle Application Server for external users to access the database in a read-only format. That way, through the web interface, you can limit what tables can be reviewed. You could even go as far as creating certain types of search SQL statements that could be run from the portal as well (i.e. via drop downs, buttons, etc). Depending on your Oracle release, you might also want to look into the Oracle Reports server environment as well.
Oracle Application Server Overview: http://www.oracle.com/appserver/appserver_family.html
Oracle Reports Server Overview: http://www.oracle.com/technology/products/reports/index.html
-- Michael
And that is to generate a nightly backup of the database (or even a continuous one-way replication of the database), run it in its own virtual machine, and give the customer access to the backup. It's really that simple. The last thing you want to do is to give the customer direct access to your production database system, no matter what kind of security oracle thinks they can provide it would be a big mistake.
If money (in the form of ridiculously expensive oracle licenses) is a concern, then just create a daily backup and run the copy in mysql for the customer.
-Matt
I've had customers like that as well. Honestly, since they're paying and it's their data I can see where they're coming from.
On the other hand, I usually have someone sign off on a MOU that they understand writing to their database outside of the application is inherently risky, and that they understand there will be additional charges for recovery work.
WHEN a customer mangles their data we get to test our recovery processes. So far, so good.
Also good to have multiple backups using multiple methods. I've had customers mangle multiple tables, so a full database restore with point in time recovery was best while other customers munged up one table, so a restore from export worked out better.
A Human Right
Some privilege escalation exploits require "just" a read-only login to server, and who knows what kind of vulnerability would black-hat guys discover tomorrow? So I'd recommend author to say NO to his customers.
My biggest problem with this is that if you give them unfettered access to your database, everything in it becomes an interface you need to support. If they build applications around that data, you can't even change a column name without (potentially) breaking their application. If you give them access to a small subset of tables, there is a smaller interface you need to support.
This is actually my favorite reason for using stored procedures (yes, above performance and security). It's a clear interface definition. Don't break the procedures and you're fine. If there is ad hoc sql running against your database, your interface is basically infinite.
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
As thinkgeek puts it.. SELECT * FROM USERS WHERE CLUE > 0 No rows returned.
First post = troll. Cleverly worded post designed to enrage others = flamebait.
BTW: You are already better than 99% of all DBA's I've ever worked with. You at least ALLOW custom queries in the first place, and even take the time to write them yourself. Your co-workers don't know how lucky they have it...
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
A well setup database CAN'T be brought down by any type of query, especially when you know ahead of time they might by running bad SQL. In Oracle you can rate limit I/O, cpu, temp, etc per user so just put in reasonable limits to what their user ID can do and put them in the lowest priority queue for each resource and unless your own work is already pushing your database server to its limit then they shouldn't be able to really affect production. We have run into this issue in our own environment where we have about 10% of our user base who have an Excel plugin that allows them to do completely unstructured queries, the user that they proxy through is simply thrown into the lowest priority queue and so while they might use 40% CPU if the box is idle they get almost nothing if it is hammered.
There are 4 boxes to use in the defense of liberty: soap, ballot, jury, ammo. Use in that order. Starting now.