Keeping Customer From Accessing My Database? 567
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?"
A simple suggestion (Score:4, Insightful)
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.
Re:A simple suggestion (Score:5, Informative)
Re:A simple suggestion (Score:5, Insightful)
Re:A simple suggestion (Score:5, Insightful)
I don't see what the problem is....just set up a role with select privs. only on that customer's table(s). If you have all the customers' data mixed in the same tables, then create a view on their data and grant select only on that. Or...maybe look into Oracle's granular level permissions you can set up?
Comment removed (Score:4, Interesting)
Re:A simple suggestion (Score:4, Insightful)
Re:A simple suggestion (Score:4, Interesting)
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
Re:A simple suggestion (Score:5, Interesting)
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.
Re:A simple suggestion (Score:5, Insightful)
However, when you start talking about load issues, that's where things that are feasible in MySQL just aren't in Oracle. Presuming this DBA is running Oracle EE, he'll be paying $40k/CPU (or technically, $40k/2 cores). That means for him to replicate onto another box for load issues will cost him an extra $40k just for a simple dual-core machine. Or $45k say, hardware isn't completely free.
If he wants that load balancing to happen automatically rather than telling clients which machine to log into, then Oracle has a much better product than MySQL's cluster. Unfortunately, MySQL's cluster is virtually free, while Oracle RAC is over $500k. At the same price, I would have chosen RAC over Cluster, but with that kind of price difference...
So, I think it basically comes down to load issues. Scaling up an Oracle install is unaffordable without a great business case and expecting random clients to not bring the server to its needs (granting them unlimited CPU) won't work - especially on a server which no doubt has limited cores - while not granting unlimited CPU will lead to all sorts of confused issues logged about queries failing.
There are plenty of solutions. Replicate onto Postgres (it supports Oracle's syntax so would be a better choice then MySQL). Create some nice star schemas and export via Discover or similar, replicate onto a machine that the client supplies and pays for licencing of, etc. Ditching Oracle EE and going SE might be enough too, the EE features are nice but not when they prevent business growth. Writing a custom SQL Server integration and syncing daily is probably only a few hours work and good enough for a DB up to about a TB if daily sync is fresh enough. That's just off the top of my head, I'm sure there are more options.
Re:A simple suggestion (Score:4, Insightful)
There's too much of an instinct in IT to think of the systems as "yours", and protect your little kingdom. It's a computer, not your children.
The only thing I'd like to add is, if the customer wants this kind of access (and I'd agree about the replication system), then it's going to cost something. This kind of thing isn't cheap, and it should be free. Estimate some reasonable setup costs, as well as maintenance costs, add some kind of profit margin to each, and present it to the customer. Let THEM decide if it's worth the price.
This is a business decision. There's really no reason the technology can't provide you a level of protection from the customer access to the system. The difference is they have to be willing to pay for that access.
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.
Heh. I'm sure you can fool a non-developer with a line like that, but anyone that knows anything about software development knows that schema is about worth nothing, and you're just trying to hide something.
Re:A simple suggestion (Score:5, Insightful)
I had a system that was breaking about once a week and I was getting some real heat from the managment to keep it running. It was usually on a Saturday when it failed and sometimes on a Thursday night. I eventually started suspecting that someone was messing with it because there were no logs of anything and none of the usual "something isn't acting right" when it would bork. I even started replacing things that I knew couldn't be the cause of the problems but it was about the only thing I haven't done yet. I wiped and reloaded the system 3 times, each time holding off on all the updates in case one of them was causing the problems. After about a month, I changed the passwords and took an IP camera in and set it by the terminal. Turns out that one of the members of the cleaning crew was on site alone during those nights. He would get the password from the sticky note on the wall of the management's office (why it was there, I will never know) and run a counterstrike/half life server from the server. He would then turn all the services off that he thought nobody needed, half ass his work then pull a laptop out and spend the next 4-6 paid hours playing games over it.
After this came to light, I found out that another client I had been attempting to get a contract with talked to the managment of this place and got a bad review specifically because of this server having repeated issues that I couldn't fix. After the real problem was known, the client called me up and gave me the contract I was looking for and specifically mentioned that he was worried because of all the trouble I was having with the servers at the other place.
It isn't just that one time either. I have sites that we totally lock down and reimage the profiles each night so any unapproved changes to the systems is removed at the end of each shift. I find that I am having to look for reasons to show up to those sites and make sure everything it working right. I also have sites where power users are present without any restrictions and I am constantly being called in to fix something. In fact, If I can keep people away from IE and outlook (express), convince them to not install anything not directly related to their work, make sure an up to date anti virus scanner is present, I don't have too many problems outside of hardware failures and stuff outside our control.
We protect the system like they are our children because our reputations are on the line. In many situations, our reputation determines out pay or potential pay. It stops us from doing productive things when we have to fix over people's mess up's that they attempt to hide so they don't look bad. Even if you can always blame it on someone else, you still end up looking bad because your always blaming someone else.
Re:A simple suggestion (Score:4, Insightful)
I don't think there's much doubt about why your typical sysadmin distrusts a lot of people. Whether it's a sensible policy is a different question. What happens when you start distrusting otherwise reasonable people and it hampers their ability to do their job is that those reasonable people will do whatever is necessary to circumvent your protections. Unless you are literally operating in something like a military base or a bank vault, there's always something.
Or, you could just support the competent people in doing their jobs, and save the distrust for those who earn it.
Re:A simple suggestion (Score:5, Funny)
You hit the nail on the head.
Re:A simple suggestion (Score:4, Insightful)
Most people are complete fucking idiots. And frighteningly, many of them THINK they know what they're doing.. and if you go ahead and let them, and they fuck things up, they'll tend to fall back on the one thing they ARE good at.
Blaming someone else.
(and if they're management, that's about the time you're forced to promote them so they have less opportunity to create huge fuckups)
Re:A simple suggestion (Score:4, Informative)
Re: (Score:3, Insightful)
Users are reaching the database through applications across many layers of security.
So my concern with the question hear would not be some ignorant user bringing down my system with a wayward query. Good database management will make that pretty much impossible. My concern would be - how do they gain
Re:A simple suggestion (Score:5, Insightful)
Oracle has plenty of security and control mechanisms to ensure that a user can't starve the system of resources if you know how to use them.
Re: (Score:3, Insightful)
You also dont have a license to grant them access to the schema. If they query the database directly, any back end changes would break the reports
These 4 word incantation to dispell project... (Score:5, Insightful)
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?
Re:A simple suggestion (Score:5, Insightful)
Re:A simple suggestion (Score:5, Informative)
select bt.* from big_table bt, bigger_table bbt, biggest_table bbbt where bt.id=bbt.bt_id order by non_indexed_column;
Perhaps a good left outer join tossed in there to really thrash the drives.
Re:A simple suggestion (Score:4, Informative)
Re:A simple suggestion (Score:5, Funny)
Re:A simple suggestion (Score:5, Informative)
Re:A simple suggestion (Score:5, Insightful)
Re:A simple suggestion (Score:5, Insightful)
I have been toying with the idea of a shadow database that they can have live access to but which is only updated, never queried, by the main system. This is another possibility for your customer, and provides fresh income for you and your team as you develop this "new product".
Re: (Score:3, Insightful)
Unless there was a negotiation up front with specific line items t
Re:A simple suggestion (Score:5, Insightful)
Re:A simple suggestion (Score:5, Insightful)
This happened to me. No choice in the matter. And when it came time to build version 2 and make some internal changes we couldn't because the customers had grown accustomed to the schema being a certain way.
Re:A simple suggestion (Score:5, Informative)
And when it came time to build version 2 and make some internal changes we couldn't because the customers had grown accustomed to the schema being a certain way.
That's what Views are for. They keep using the "old table", which is now a view. You put the changes in the "new table". You don't need to change your code either, except in the instances that required you to change it in the first place.
What's the customer's name? (Score:5, Insightful)
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.
Re:What's the customer's name? (Score:5, Insightful)
He seems to think 2-8 GB is a big database. If the customer wants some custom report, he thinks emailing someone who writes custom SQL and sends them an excel spreadsheet the next day is a process that "ticks along happily". If your customer is asking for direct SQL access so they can bypass you and do stuff themselves, your process is not ticking along happily.
Re:A simple suggestion (Score:5, Informative)
Re:A simple suggestion (Score:4, Informative)
Oracle supports standby databases that can be locked into read-only modes. You can even change how often the data is updated, releiving your main access system the burden of direct customer interaction.
Worst case scenario is that the customer chokes the box to its knees and then THEY can't access your data, but it doesn't affect your operations at all.
Since you should most certainly be charging for this service, the profits reaped should more than pay for the small investment in hardware.
Plus, if your main DB does go down in flames, you may be able to requisition the read-only DB as a temporary main of down time becomes excessive.
Re:A simple suggestion (Score:4, Insightful)
There are perfectly good technical solutions to the problem. As a DBA I would just point out to management what the issues are and suggest the obvious solution (data replication). If management really wants to tell the customer to take a hike, then that is up to them. But at the very least you the DBA don't end up being the nay sayer.
Re: (Score:3, Insightful)
That's customer service! lol. Think about it, the data is probably required for the customer's business process. So saying 'no' is tantamount to 'you can't run your business', and the customer will become an ex-customer just like that.
Um, no. Actually think about it. The customer is already in business and has been for some time without being able to do this. It is therefore an immediately obvious fact that it is not required for the customer's business process.
Re: (Score:3, Insightful)
In any case, it is irrelevant whether the data is actually required for the customer's business process. The GP's point is valid because it is what the customer believes that matters.
Besides, this is an opportunity to make more money from this customer. Why would you say no?
Re: (Score:3, Insightful)
Re: And that's the kind of attitude... (Score:3, Insightful)
Providing improvements to business processes where technically possible, affordable, and not illegal is the number one job, just above securing company data. Because if you can't do number one, there isn't much worth securing for number 2.
Is a requested enhancement required for a company? Probably not. Is it important to provide continuous improvement and efficiency to keep the company competitive? Frequently it is.
I'm all for standing up and saying w
Re:A simple suggestion (Score:5, Insightful)
The key is to try to steer the customer to another direction. Often they want silly things like this because they don't know the alternatives. Engage the customer and find out what they are doing, and toss out a better solution. In the end, you will both be happier.
If you do end up having to give them RO access, I would be sure to write some method into their user interface that restricts wildcards. You don't want somebody doing the oracle equivalent of
echo "select * from huge_table" | cat > querry.sql; mysql -u user -p huge_db < querry.sql | grep value
Sounds silly but I saw a colleague write a script that did something about like that.
Re:A simple suggestion - data can be dangerous! (Score:4, Insightful)
Re:A simple suggestion (Score:5, Insightful)
Saying ``no'' for business reasons is great; saying ``no'' because there's no good way to handle it technically is a -bad- reason.
From my experience, sysadmins who overuse the `no' response are a buncha pricks who can't do anything (you know something is wrong when seemingly simple requests meet a ``no'' response or take days to complete [something that would take you a minute with command line access to the server]---big corps are full of such folks).
As for one possible solution: with read only access, they can't mess things up; most seasoned db admins can ensure that Oracle handles things gracefully---even from stupid read-only users.
Another solution may be to setup a mirror box, and let'em have at it. Mirror the data every day or so. If they screw it up -somehow-, everything will be reset in 24 hours anyway.
Re:A simple suggestion (Score:5, Insightful)
What they don't want is this:
Someday, probably a Monday, you will get a page. The production website is timing out and they've traced it to the database. A look at the Oracle dashboard indicates that several "ad hoc" queries have been running for the past 4 hours, have blown the cache, and are churning up 99% of the read I/O on the database. You kill the queries. Some customer's ad hoc reports fail, they want to know why and they're especially irritated because they've been waiting 4 hours for the result. The other customers are upset that the main website is unavailable.
What they do want is a sandbox to play in.
The production database server is not a sandbox. It's a system of schema and queries there are all designed and QA'ed to meet an SLA. A certain amount of cache is required; a certain amount of logical I/O is possible; queries do their work within those boundaries. The tp99 is under 100ms because it was designed that way. And the schema is not even convenient for reporting. Maybe it happens to be convenient today, but that is a coincidence and things will soon change.
Everyone would be much happier if the sandbox operation could be managed separately. OTOH, improvements to the reporting schema could be made without requiring a dev+QA iteration on the production website.
Whatever you do, don't agree to support this feature without getting sign off on the additional hardware and software you need to run that sandbox. If you can't get that, get sign off on a new, weakened SLA for the main production application that will be impacted.
Re:A simple suggestion (Score:4, Insightful)
If your database isn't designed for separation of customer data, and your data structure needs to be somewhat reorganized, you are going to come up with a fairly large number. Mention that there will be downstream issues, and that it's partial system redesign.
Present options; you know what they are, you know what they will cost as far as your time is concerned. Is this account worth bringing someone on to take over some of your duties, or paying you overtime at double time for the next two or three months? Saying, "No, I don't wanna," is fairly ineffective; showing people (in numbers) why you're uneasy about the issue might help.
Reporting Database (Score:5, Insightful)
Re: (Score:3, Interesting)
Re:Reporting Database (Score:5, Informative)
Re:Reporting Database (Score:5, Informative)
that the adhoc query account has limited access and limited resource priveleges.
However, the customer is bound to complain when Oracle will refuse to run their
heinous query.
Re:Reporting Database (Score:4, Insightful)
Re:Reporting Database (Score:5, Interesting)
Re:Reporting Database (Score:5, Funny)
You just violated the MS-SQL license.
Re:Reporting Database (Score:5, Informative)
Re:Reporting Database (Score:4, Interesting)
Re:Reporting Database (Score:5, Interesting)
Why? (Score:5, Insightful)
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.
Re:Why? (Score:5, Interesting)
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.
Re:Why? (Score:5, Insightful)
Re:Why? (Score:5, Informative)
2) Who is liable if the customer queries affect the performance of other processes/services (lack of index usage, expensive queries, etc)?
Re:Why? (Score:5, Insightful)
You currently have a choke point where you can make sure this is well-defined: up-front before they use canned reports, or ad-hoc when they request new ones; when they start writing their own, it needs to be clear that they're on their own, and that they should *probably* at least ask for help along the way to make sure they get what they want (get the right answer), and that what they want makes sense for what they need (ask the right question), and that they understand the limitations of the data (missing data, small sample sizes, small list of codes, known-dirty data...)
Re: (Score:3, Informative)
First, just say "no".
Only if they demand an answer, give them as many reasonable reasons as you can think of, all at once, saying "no external connections; security policy" (only if true; and it should be... just because your DB has passwords doesn't mean it doesn't have vulnerabilities) and/or "poten
You seem to be the problem (Score:5, Insightful)
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.
Re: (Score:3, Interesting)
Re:You seem to be the problem (Score:5, Interesting)
Oracle Seat License (Score:3, Interesting)
No discounts.
You do not answer SQL questions.
Provide 2yr old data dictionary (just like MS does)
Partial data replication (Score:5, Insightful)
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.
Like Nancy Reagan Used To Spout; "Just Say No". (Score:4, Insightful)
Use a read only replica (Score:5, Insightful)
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.
Re:Use a read only replica (Score:5, Insightful)
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.
Cost? What cost? Oh, you mean the profit that you'll make from charging the end user for time and "overhead" in setting up the replication?
That's only a cost to the requesting end user! It's all profit for you!
Re: (Score:3, Informative)
They will either decide not to due to costs (you win) or pony up the money (you win again).
Remember your time is (or at least should be) billable.
Why not? (Score:3, Insightful)
Why not set up an account that has read only access? Why not create a view of the table that shows only the columns they need? It'll be good customer service and relations. Just remember, your company can be replaced and if you don't give them the service they want they'll get it somewhere else.
Suggestion (Score:5, Funny)
(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.)
ummm.... (Score:5, Funny)
Re: (Score:3, Funny)
Becasue you are stupid is a valid reason (Score:5, Insightful)
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.
Re:Becasue you are stupid is a valid reason (Score:5, Insightful)
The person also wrote that the customer did not want 24 hour old data (you really didn't bother to read the post did you?) so your cube is a useless idea.
If you think it's a good idea to give clients direct access to your production database then please send me over your resume so I can make sure it goes on our "Never, ever hire this person" board.
Oracle DBA (Score:3, Funny)
Anyhow, you never give end users direct access to tables. Never. Users who create nice cross joined tables will bring your system down to a crawl Create data dumps to ancillary database -- one per company in this case. And that is if your management won't back you up in just saying "no". I know this much, and I am NOT a DBA.
You have some other problems... (Score:5, Insightful)
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,
Before you give them access (Score:3, Funny)
Don't do it (Score:4, Insightful)
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.
Pre-process all data requests. (Score:3, Interesting)
Just say no (Score:5, Interesting)
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
Re: (Score:3, Insightful)
Re:Just say no (Score:4, Interesting)
Sheesh, have a reason, at least (Score:5, Insightful)
"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.
Not to state the obvious, but... (Score:4, Insightful)
If the only reason to refuse them access is that you "don't like the idea", you should come up with a proper reason you feel that way, and if you can't, you should change your opinion - or risk gaining a reputation as an arrogant, arbitrary obstructionist.
The customer can have anything... (Score:3, Insightful)
Pretty clearly, running ad-hoc decision support queries against a transactional database is going to add an undetermined amount of load on that system. So your customer has a few options -
1. Upgrade your systems to support more load. Obviously, they want to still do their data processing in addition to any queries. If they're willing to cover the costs of the upgrade to insure the current level of service, then there shouldn't be a problem.
2. If the data doesn't have to be real time (within a few seconds), you should be able to replicate the data on a separate box for ad-hoc queries while active processing is done on the main database. Again, they need to foot the bill for this replicated server, but it may not need to be as beefy as the production box (depends a great deal on your scale/size).
3. Find a 3rd party to host the data for the customer and have the customer pay the 3rd party directly. Obviously, there may be some development and support cost of maintaining the data feed, but that way the customer understands the actual cost of that capability.
Now, I don't know the competitive and political environment that you're in. Are there competitors that may have a similar product to yours that allow live queries? Sometimes requests like this are simply to provde justification for a switch.
The customer needs to cover the cost (Score:3, Insightful)
Make a list of bad things that can happen (Score:4, Informative)
Tell you boss how someone with read only access could affect the rest of the users, and you should be on the right track.
Only one safe way to do it (Score:4, Interesting)
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
Say Yes. (Score:5, Insightful)
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.
How To Do It (Score:5, Insightful)
"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.
Just Who's Data is It? (Score:3, Insightful)
Is it the customer's data (and how do you plan to keep one customer out of another competitor's data of they both have this access?) in the first place and you're just storing it for them? If so, then there's a strong case that they should be allowed access to their own data.
Or is it your company's data that you provide to them on request? In that case they have no rights to anything beyond what you're already providing to them.
Why do you REALLY not want them to do it? (Score:4, Insightful)
You use a combination of views custom to their needs and access restrictions on tables to ensure they only see their data.
You don't grant them any permission to write to any table or view.
You configure their user so it can't starve the system of resources so they can't disrupt everyone else that uses the system.
Oracle is made for this sort of thing. If you were talking about MySQL or PostgreSQL, it'd be a little different as they aren't nearly as mature.
Being able to configure Oracle to do this stuff is why you get to be called a 'DBA', since you know, DBAs administer DBs.
Now
Theres a reason Oracle costs a fortune and people still use it over open source alternatives, its MADE for these sort of problems.
If you don't want them wasting your CPU power for their queries, thats a fine reason. What are they willing to pay to get special access to the data? Its going to cost you time and energy to create a user for the database that has proper permissions, they definately need to pay for that.
The easy way to say "No" (Score:4, Insightful)
First, you have to start by saying "Yes". Then you let the customer decide on his own that he really doesn't want what he just asked for.
Your problem is that the customer has come to you with a business problem. Someone, somewhere, has decided that it might be fun to have full access to SQL queries instead of those boring prepared reports they have been getting. Chances are that it came up in a meeting when one of the customer's own technical people was trying to explain why he couldn't deliver the Moon, two pieces of cake and a pony upon request.
"Why can't we get more detailed data than this?"
"Um, because it's not in the report. We would need, uh, raw SQL access to the database to get that and that's not going to happen."
"Why not?"
"Um, because those guys won't let us."
The idea built up momentum until it got to you. So far all that they can see is that they have business reasons for wanting data and all they need to do is ask you for it so they can have it. Now you have very understandable technical reasons why you do not want unwashed, barely literate knuckle dragging Neanderthals who don't know the difference between an outer join and an outside straight from being able to touch your database. Unfortunately they are _technical_ reasons and not _business_ reasons and business trumps technical. Think of business reasons as being 'Paper' and technical reasons as 'Rock'. Oh, and some kid ran off with the scissors, so that's all you have to play with. The only way to win at that game is to keep choosing 'Paper' until your opponent gets bored and leaves.
So how do you do that? You forget about the technical problems and explain the simple _business_ costs involved in resolving them. Your customer needs access to a database? Fine. Naturally they can't just use the main database for that. They will need their own dedicated reporting database server. (Ching!) With software licenses. (Ching! Ching!) And storage. (Ching!) Plus administrative overhead, datacentre costs, additional bandwidth, and so on. (Ching ching ching!) These are all things which you will need to provide to the customer in order to give them their pony^W own database to play with. All they need to do is pay for them.
Suddenly the technical problem of "No you can't play with the main database server" turns into "Of course you can have that if you pay $X up front and $Y additional annually", which is a business problem. Write up a rough quote, send it to the customers, and let them decide for themselves if their sudden whim of making their own queries justifies the actual costs involved in having it. If you have any alternative suggestions such as how you could provide additional canned reports or develop a slightly more flexible set of queries which they could use, feel free to attach estimates for the real costs of those projects too.
The key here is to make sure you tell them that you would be quite happy to provide any of the solutions you have offered. If they're smart, and your estimation of the costs of replicating your entire DB server are accurate, they should be able to talk themselves into doing the right thing without any further encouragement from you. If, on the other hand, they do decide that it's worth that much to them, and you're smart, then you should be in a good position to sell them that additional service.
Trust, Rapport, and good business (Score:4, Insightful)
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 [stifflog.com] I found on slash [slashdot.org] a few years(?) ago.
- Dave Thomas, Author or The Pragmatic Programmer -
DO YOUR JOB (Score:4, Insightful)
Re: (Score:3, Insightful)
Re: (Score:3, Interesting)
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
Re:Yeah (Score:4, Funny)
One that works for EDS, who are famous for their incompetence. Next question!
Professional Responsibility, It's not just theory! (Score:3, Insightful)
Also while some advocate "just say no." It doesn't work for drugs, and it won't do squat for a client. Especially a mildly informed client that thinks (but thinks they "KNOW") damn near anything in software can be achieved w/ some effort. (IMHO it can) If you take that route, at least show them how it would be less economical.
For instance, a client just last