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?"
Just say no and hope that it sticks. Seriously. I find that so many people in the workforce noadays don't know how to say that simple word. No.
Sometimes its hard to make a case for it if management at your company thinks that you are being unreasonable. However if you are a reasonable person and skilled in your profession, management should trust you to do your job. I'm of the opinion that if management can't trust employees in their area of expertise and to give good advice, then it is not a good place to work. My first tech job became this way, the new management that came along had a distrust of us and it made everything sour. Anyways, that's getting away from your question.
But being a sysadmin, I think you have to stand up for your opinion when the time is right to do so. People who aren't in the know always have requests like this to grant more access, make things easier, keep the customer's demands first. Its your job to draw a line in the sand that says you can't go past that point. Some people don't like that, but honestly it doesn't matter. Rules are there for a reason. They are guides to providing good service for all customers, not just one.
The last thing you want is users writing ad-hoc queries against your live data. Replicate the data to a reporting database and let them abuse that.
You don't want them "pawing" through your database, but you don't give any reasons why that is a bad idea. If you can't come up with any, you're not going to get very far in your argument. If it is a read-only view of only the data they should be able to see, what is the harm?
No, seriously. Answer that question, and you have a basis for your argument. If you don't have an answer besides "it makes me feel dirty," you've lost.
How are they going to mess up your database with read-only access? They could run intensive queries, I guess. But unless you've got million+ row tables that are being accessed concurrently by tens of clients, this shouldn't be much of a problem.
Anyway, just enable logging and look through what they've been doing in case it's anything stupid. I used to work for a large insurance firm and we'd get a call minutes after doing against the database we shouldn't.
For the love of science do not give them access to your production database, they WILL screw it up, even with just read access.
Here is the psudocode from their SQL:
Select * from everything join everything where non-indexed column like '%'
you need to make them a COPY of the data that they are allowed to access on a seperate database (preferably a seperate server). Most reasonable replication suites allow you to do things like this.
How about just a simple "No." The database, while containing data pertitnent to your customer, is still your\your companies property. Simply tell them that access to that level, or in fact any level beyond what is alreayd granted to them as a customer, is for you and\or your employees only. Just because he's a customer, doesn't grant him unfettered access to your company or it's property, whether that property is physical or electronic.
Mirror the database to a 2nd server and provide them read access to that. It has several advantages.
1) You don't have to worry about them causing problems in the production database.
2) You can optimize the replica for read access. A read only database can generally perform MANY times better than one that has to be optimized to support read/write and especially if it is highly transactional.
Granted, it costs you a bit in hardware and setup time, etc. But if you're really nervous about it, then it should do the trick. Given the limited load on the replica and its read only nature it should be able to live on limited hardware, like maybe an older server that you have hanging around. Plus you don't have to worry about reliability either. If the thing blows up no data is lost.
"Malo periculosam, libertatem quam quietam servitutem." -- Jefferson
Don't use your work e-mail address when you call your clients "stupid" in a public forum?
(It's two a.m. here, I bet somebody'll point out some completely idiotic assumption I made in about two seconds. Oh well, so it goes.)
just now when said like that.
I am not sure why a DBA doesn't know this, but just create read only views
Seriously - are you really a DBA, or just someone that got stuck DBAing? This situation is dealt with at every place I have ever worked, without exception.
You could also create a Cube. This might be 24 hours old, but I don't know who many transactions we are talking about here.
Be sure you can track all logins, and log what they do.
They are not your tables, get that out of your mind. They are the companies. All you can do is write a report explaining the risks to management, and be sure the users know they are liable when they make a mistake. Then set up views.
Yes, if they screw up you will be the one to fix it, that's your job. At least you can wave off any fault.
The Kruger Dunning explains most post on
You are supporting them, so make it happen.
Yes, bad queries can run amuck, which is why you give them access to a slaved reporting instance of the DB.
Your tables are not precious, and they're not even yours, they are your customers. Let them run their queries on the reporting database, never the production DB.
Regards,
Website Hosting
Agree, just say NO. If they absolutely insist, replicate the tables that they need to see to a second server.
BTW have they offered to pay for all of the consulting time that they are going to request in understanding your schema and formulating their queries? Has management planned on the increase in personnel that your team is going to need to respond to these requests?
Finally, if you expose the schema to outside users, you are effectively making this your API. If you want to change your schema in the future, you are going to be breaking all of the legacy queries that you customers have written.
Statesman
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.
"I don't like them pawing through my database" makes me think that you're embarrassed by the database structure, and don't want people to see how screwed up it is. If that's the reason, then maybe it's time to fix things.
If it's just some weird possessiveness thing, then get over it. It's not your data. It belongs to your company. It's their servers, their programs and their data. If they want to give access, it's their decision, not yours.
Otherwise, a good reason not to allow direct access is performance. Amateurs doing queries against the "real" database can kill the server if they're not doing it correctly. My recommendation is to provision an entirely separate database server with a regularly-updated version of the data (perhaps even a "fixed" version if my first point is in play) and let them go wild on that.
Sometimes it's best to just let stupid people be stupid.
Wow, you're right. Next week "Ask Slashdot: How to find a DBA job after being fired from EDS"
Just say no and hope that it sticks. Seriously. I find that so many people in the workforce noadays don't know how to say that simple word. No.
:-)
Hey, I have a consulting firm that would be willing to work with the client to ensure they have that database access.
What we could do is give them the query access via their own public synonym space, and build it into our SLA that we are not responsible for downtime due to their querying. We would also bundle some support costs into the agreement.
This is my sig.
"Absolutely, we would be delighted to provide you with this high-value ad-hoc access system. In order to protect our valuable operational infrastructure this will require the installation of a separate datawarehouse. Provisioning this system will cost $X, the monthly charge for maintenance of the system, the population of the datawarehouse and the provision of secure access will cost $Y"
The advantage of this approach:
1. It makes you look helpful and willing to accomodate your customers
2. It makes it clear what some of the issues are
3. If you set the values of $X and $Y at the correct values you can generate significant additional revenue for your business
4. If you set the values of $X and $Y just a little higher, the answer equates to "No".
Win-Win.
The business rule here is:
"This is a complex Oracle database, and yes, even read-only access can cause major problems. These problems are prevented by accessing the data through the approved application.
If you would like full query access, you will need to provide an Oracle-trained staff member to perform that work. And even then, all warranties on the system are off.
Our preferred solution to your business requirements in this case is for you to submit queries for approval and/or integration into the front-end application. If there are strict deadlines involved, please let us know and we can try to accommodate those.
Please understand this isn't an issue of control, but simply of us trying to maintain a high level of quality of service. It may seem like read-only access is safe, but it is not. If you would like further clarification of this reasoning, please contact us and we would be happy to arrange a presentation."
If they want a presentation, you show them how poor queries can crash the database or cause unacceptable performance problems and misunderstood results.
If the customer does not understand the structure of the data, they can get bad answers that are disastrous. What if the data has the same amount under several categories and the customer decides to add all the categories together to get a total and then makes a business decision based on that answer? I've seen it happen!
"Who's paying for this?"
DBA time ain't exactly cheap, and setting this all up, the needed SysAdmin time to get the firewall/proxy port issues worked out/certificates setup, etc...
Figure 40 hours of time for the DBA and 30 hours for the Sysadmin..
160 and 240 per sound good?
That's $ 13,500.00
You guys ain't planning on doing 10 grand worth of work for FREE, were you?
Technology -- No Place For Wimps! Grateful Dead and Jerry Garcia Chatroom -- http://www.wemissjerry.org
Try not to squelch their enthusiasm to explore. It will likely mean new business opportunities for your company.
If you are really "hiding" information as its owner and not "protecting" information as its custodian, then you should consider a different business model, one where the value is placed on the data and not the service.
I am constantly reminded to look away from the monitor for for a few minutes and talk to people by this great quote from an interview of famous programmers I found on slash a few years(?) ago.
The next big thing in computer programming will be eclipsed by the next-next big thing in programming, and so on, and so on. I'm kinda tired of the endless search for the big things, because while doing it people tend to forget about the real issues: getting the fundamentals right. We need to get a whole lot better at talking with our customers, focussing on delivering value, and taking pride in what we do. A developer who can do these things can deliver great software with any tool set, and won't need to worry about tracking the fads and fashions.- Dave Thomas, Author or The Pragmatic Programmer -