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.
It doesn't matter if there's anything in the act that applies. Just lie. Say that uyour legal advisor is worried about breeaches of the act.
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)
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.
Have some automated process periodically export the data to a disconnected data source (the exact one is up to you, something like MS Access or an Excel sheet depending on your exact needs) and give the user access to that.
If "No" doesn't work, which is my first choice, could you replicate the database and let them work from that? I would avoid letting them hit the live db at all costs.
create a one way mirror of the database, let them access the mirror, that way when they pooch it you can reload...but charge them through the nose to do so, claim is as "you have to recover the data" or some such nonsense.
-Ours is the wisdom of Solomon, the magic of Merlyn, the fall of Icaris.
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
Say no, it's not permissible under current UK legislation. If one customer accesses another's data, you will be in deep shit.
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.
"My precious tables"?
This is valid business mentality?
Why don't you just give them read-only access, and if they do f**k up, then you have a perfectly valid argument. Otherwise, you're just being a jealous geek.
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.
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.)
A simple solution would be to tell them that you do not have the ability to do that. You could say that your database stores data for all of your customers and that it's not possible to give them direct access without compromising the security of the other customers' data.
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
First of all, I am not a lawyer. YMMV, etc...
Not sure what sort of information they're requesting access to, but if any data at all is of a sensitive nature, by supplying a password to them even in a read-only capacity increases your liability if that information is stolen through unauthorized access. Basically, if a third-party somehow acquires that username/password to steal a dump of every firstName, lastName, DOB, and social security number from the database, you could expose yourself to legal trouble for enabling the access in the first place.
I would err on the side of caution and explain you would rather third-party access not exist at all than open yourself up to the risks of unauthorized use of credentials.
I thought Oracle DBA's were all uber-brilliant? Everyone I have met has told me so.
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.
See my journal for slashdot ID's by year. Mine created in 2005. http://slashdot.org/journal/289875/slashdot-ids-by-year
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/
You can.. uh.. build them a view.
Maybe you shouldn't be in that job if you are asking.
You could go with a denial due to the increased resources that the ADHOC queries will produce. Both CPU and memory used will increase as they do their (most likely) not optimized queries. They won't have access to the rest of the jobs being run on the server so they could be performing a query at a time where usually the resources are free for an existing complicated job. You could even appeal to paranoia with exposing the server odd sql attacks. I'm not sure of the licensing restrictions on Oracle, but maybe they would be violating that if their ADHOC queries were coming from dozens of different users.
Basically they could break the server or increase the licensing costs.
Unless you want an excuse for a beefier server... then this might be worth turning around.
Good luck.
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
Make a replica of the database available for this type of queries, you can argue that uncontrolled queries can impact your operations and that an isolated environment is required.
Send them a Statement of Work with this costing scenario as your response.
Giving the wrong people access to your database leads to unfortunate questions such as:
"Did you intend to remove/change that data?" (which is never taken well) or "Why is the program/site/etc. no longer working?" and my personal favourite "Why am I still working here?"
At the very least, CYA and say no. If your boss overrides you, then well, you might want to preemptively ask question #3.
Be sure to refactor the tablenames. The customer will catch-on when the "Moron" flag in your users table is set to 1 for their login.
What do they really want it for? What sort of adhoc queries do they imagine they need to run?
Odds are they only need access to a couple of tables... I'd consider creating a couple copies of those tables for them, and set it to update daily from the 'real' ones (with just their data of course), and then give them read only access to that. They are happy, and you are happy, hell if its small enough / simple enough, you could trhow up a mySQL or postgresql server somewhere to host 'their' data for them, and they won't even be able to touch your real server.
Not saying this is a good idea or even feasible... but it may be possible to satisfy their request without opening up your database to them.
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
... and the proper setup can both satisfy the customer, and keep you happy. It does require work though, as well as investment. Create a datamart. Put it on separate hardware (depending on how much you want to spend, and since you use oracle, you have the option of implementing redundancy). If all the data within the given instance is specific to this one customer, then replication is an option. Otherwise, you'll probably have to create an ETL process (many platforms/apps available for this), unless their data is segragated to a specific set of tables. Create a read-only account on the datamart, punch a hole in your firewall for the specific IPs they'll be accessing it with (depending on how far you want to go, and how sensitive the data is, there are more secure/portable options here, such as VPN).
I realize this is a lot of effort/$$$ to get done, not sure if you can convince the customer to offset, but it's what I would propose. Good luck.
I'm not quite sure what you mean by customer, but it shouldn't make too much difference. If there is data they don't need, the best bet is usually confidentiality.
"Unfortunately, due to confidential information, we cannot give you full access to the database"
Your role is probably to serve the customers, so if it's not a privacy concern I doubt there's much you can do (depending on your organization's structure). You can however say "We cannot provide technical support for ad hoc queries, nor can we ensure quality data. If you have any errors/trouble, you will have to submit a full request per current policies". For easy stuff, this will cut down on your workload, while still covering you and your concerns.
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.
Microsoft Reporting Services' Report Designer Ad-Hoc report tool will allow them the access they need, without allowing them to write to the database. Also, you design the model with an Oracle back-end in visual studio, allowing you to have precise control over how they see the database.
http://www.microsoft.com/sql/technologies/reporting/default.mspx
http://visualstudiomagazine.com/features/article.aspx?editorialsid=1300
Sounds like the data actually belongs to your customer.
And apart from the replication to another server as mentioned, it sounds like you are being a tad childish. For example: "This is my ball, and I'm going home with it..."
The government has a defect: it's potentially democratic. Corporations have no defect: they're pure tyrannies. -Chomsky
Oracle provides user profiles to control virtually everything the user can do within the database, including CPU time and connect time. With it, you should be able to closely control what these ad-hoc queries can do, and avoid problems.
"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.
My company provides SQL-backed software, and we have gone through similar problems. Normally we support Crystal Reports and have someone give them basic training in it. If they cause a problem, we'll cut the access, and they know it.
Now the truly scary part is when they want WRITE access to their database. They are already restricted so they only have access to their own data, so confidentiality of other customers' data isn't an issue. However, they can massively destroy their database under bad circumstances. In one case a customer did a wide-open update to a very large table--they were fortunate that it filled the transaction log and it was rolled back, otherwise their only recovery would have been a database restore. Even then the rollback took hours...
What's the worst that could happen if you issue them full read access? I work at a major isp, and I just gave a customer full read access to our client database server (which I am typing this from). As you can see, everything is operating perfe
Let me translate that into non-Oracle DBA terms:
"Whine! Whine! Whine! I hold the key! Me! This is magic! Whine!"
Another translation into Customerese:
"It's my data AND I WANT TO SEE IT, NOW!"
Seriously, what non-ego-driven reason do you possibly have from not allowing your customers to access their property, aka "their data"?
Dewey, what part of this looks like authorities should be involved?
that limit access to what is visible in the base tables according
Let them know that you can't support their scripts, and if you need to change something internal to the database, it may break what they've put together without your knowledge.
If they're okay with that, then I second the notion of giving them a mirror they can play with - they *are* your customers, after all. Don't promise them anything you can't promise them, but do give them what they want.
Give them access to that database.
Salut,
Jacques
Depending on just how heavy the queries are going to be, and how standards-compliant you can require things to get, you may be able to have the database replicated onto a lighter-weight server. This would have the bonus "selling point" of being faster than the main system, and have the "selling point" at your end that a read-only cache is both a standard and secure method of providing the required service.
It's a small world and it smells funny; I'd buy another if it wasn't for the money; Take back what I paid (SoM)
If it's their data, then why not give them what they want?
If there's data for other customers in the same DB, you could always create a set of views and give them access to them.
In the end, it's read only access that they're looking for. As long as there's an agreement whereby they accept liability if unauthorized access is made using the account you provide, you should be covered.
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.
"Goodness me, how unlike the FBI to abuse the trust of the American public." -- The Onion
...that they're willing to pay for.
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.
If no is not a sufficient answer and you can not come up with a legitimate business case the a replicated DB would be my choice.
Possible business case reasons:
No way to prevent access to other customers info without significant development time and expense
Server may not be able to handle the expanded load
Not enough licenses for the server
If you have to
Charge for creating the replicated data
charge for development time to provide access
charge for service of maintaining replicated data
write read only views to the data restricting what they actually have access to.
You could create one or more views that restrict what the user can see. That way you don't have to reveal the structure of the tables.
I have been in the position of your users and can relate to their situation, but I am also sensitive to yours.
As others have said, the best option is to create a copy of your production DB and put it on a different server and refresh it weekly or every couple of days (e.g. Sunday and Wednesday). The copy doesn't have to be the entire DB, just a subset. That way, even if they do something stupid (e.g. bad join) they won't crash production.
Always try to result to a higher authority like 91 Degrees said and say that you aren't authorized to provide that access. No need to lie about legal stuff though. The initiated will look that up and call you out. Not a good scenario.
Explain to the higher-ups, in detail, how this customer request can (and will) impact your other customers. Then tell them the solution is to replicate the database onto another server, which will be the one the customer will be given access to (as others have said). But make sure the customer foots the bill for purchasing and running that new server.
#DeleteChrome
Wow, you're right. Next week "Ask Slashdot: How to find a DBA job after being fired from EDS"
And let them break that, and pay to maintain it as well.
Just make up a bunch of BS about SOX and SAS-70 compliance. Tell them your hands are tied from government regulations. It works every time!
As long as they ask for a strictly R/O access, why not let them have it? If I hear "my precious tables", I immediately have to think "unprofessional administrator". Do you have to worry about sensitive contents or are you ashamed of the mess, which most production databases converge into?
Easy, straightforward solution (with some implied assumptions, btw): I would create a set of views with SELECT privileges granted to a new role, let's say ADVANCED_CUSTOMER. Then create a new db user, grant him the role and there he goes.
More advanced solution: I would set up a new database, into which the original data would be replicated. Possibly with some time lag, so the advantage of fresh data remains for you.
Anyway, as a customer, I would just state what I want ("this and that set of data"), and the provider's job would be to make it happen. Preferably in some interface neutral way, e.g. SOAP.
Use Oracle Data Guard to replicate the database and let them query on the rerplicated database.
Use Oracle RAC and have your application use one server on the node and they can use the other.
Use CPU_PER_CALL to limit their impact.
Honestly this isn't a Slashdot question you need to be on somewhere like http://asktom.oracle.com.
You have many options to allow these users the access they desire but so far no strong reasons to deny them. So go for budget. Write up a RAC or Data Guard proposal, show them the additional initial and then ongoing cost, and let that put them off.
If you're concerned about them writing bad queries and eating up server resources because of it, that is a valid reason. They cannot be reasonably expected to know how to write good SQL. However, that's the only good reason you can give. They are the customer, it's their data, and it's reasonable for them to want access to it.
This is a no-win situation unless you can come up with a replication of the data they can play with as they desire. If you provide the access, then they may wind up writing bad, slow-running SQL. If you don't provide access, then you will wind up getting stuck responding to their every query requests, which presumably has financial implications.
Bottom line: the scope of your relationship is expanding, and perhaps you need a discussion with them about the economic impact (i.e., "this is going to cost you").
If you really want to make a case to management, write out all the worst case scenarios for your management (I know that there should only be one WORST case scenario, but work with me here). If you can list the things that can go wrong, you might be able to help. I once worked in an environment where a developer wrote a query that locked a database for four hours until we killed it. One of the DBAs was able to re-write his query so that it completed in under 10 seconds. His access was then revoked.
Tell you boss how someone with read only access could affect the rest of the users, and you should be on the right track.
Still, with a plan, you only get the best you can imagine. I'd always hoped for something better than that. -CP
I had a similar situation. In my case, I was more concerned they'd see the schema. I created views of denormalized data that they'd be interested in accessing, then granted rights to only these views. They were never the wiser.
sig: pv qid
They are the customer. They paid for the database. Give them full access to it. And when they muck it up, charge them a very high rate to fix things.
Just create some views on a copy of the database. They wouldn't know if the view is incomplete.
Let them only read the views created for them.
In general, isn't it funny how views are available for just this reason, and invcorporated into all the big databases, and yet, when it comes time to use the feature, everybody gets nervous.
Similar to using VM appliances instead of adding another package to a machine, another process or service, "just to be safe".
Stephanhttp://stephan.sugarmotor.org
I'm a DBA
:-)
You should not report off of a production Database.
Obviously, you will need to have a feed that looks like a report to the reporting DB but this is NOT a report, it is a feed.
Reporting will harm performance and reliability of the performance of the production db.
Also, reporting off of a simple copy of the production db is generally undesirable, generally you want to have a warehouse/flattenning/sumarization of some kind for reporting.
That's the stock reasons to keep this stuff seperated, its done me well for 20 years.
If they want access, give it to them. Let them know up front that any damage done to the database because of their queries will be billed at 4X the normal rate, or some other suitably large number.
Everyone's happy.
Check out JoshJitsu.info for Brazilian Ji
Instead of giving them open access to feed SQL to the database, see if they would be open to using a PHP (or other web) screen where they could check off specified field names, add conditions (like, , and a value, etc.).
You could then construct well-behaved SQL statements and kill requests that your code defines as unreasonable.
If an interactive screen is not acceptable, then consider a facility where they feed you a SQL string and then you use PERL (or other program of choice) to extract simple queries that you can then re-feed to Oracle. Any sufficiently complex or burdensome query could trigger emails to your DBA who could then gently provide guidance to your customer.
Live Long and Prosper - Thanks Leonard. You are missed.
Why don't you ask what sort of queries they want to run and instead build a web service?
They won't have access to the database directly.
Tell them to fuck off and get another supplier. I'm sure they aren't worthy of your precious tables.
For crying out loud, what kind of DBA is unable to create views and restrict access to tables? I mean, come on, Oracle can restrict access to specific rows if you need to, that's both it's beauty and hideous ugliness.
Deleted
Cartesian joins!
Customer is paying the bills, get it?
What you should do, though, as you give it up, is that, you should get the right to waive performance guarantees you have in your SLA with them, because of their querying. Also, get your support costs for handholding them worked into the deal.
That's fair. If the customer is monkeying with their database, and their site goes down because they did a massive cartesian join, then, you can't help that now, can you?
This is my sig.
And let them go crazy.
If it's only this customer's data in the database, nothing else, then just give them one or more users with read-only permissions. They'll be able to look all they want, but they won't be able to change anything or muck anything up. No danger there, and no problems with exposure of data because it's all their data anyway. The only problem comes if/when they ask for write access, and the case for denying them there depends on what they're asking and how the database is set up (if for various reasons it's not got a strong integrity constraint setup in place, the risk of corruption is a good enough reason technically to deny access unless the customer also wants to take over complete administrative responsibility for the database).
If there's data in the database that's not the customer's, eg. tracking data for other customers or additional data for your organization that's not supposed to be visible to the customer, cite the need to restrict access to that data and the inability to do that if you give the customer direct read access to the database. You may end up having to build a custom schema/view that exposes only what they're allowed to see, but that's a fairly significant undertaking and should be billed to someone.
I know for a fact that EDS has got that whole nickel & dime process down to a science, so I don't see why you need to post this on Slashdot. Hopefully that is not your real name & email address in the original post.
ÕÕ
Sell them a copy of the data, the whole thing. That is what they get, they don't get access to your database, which I suspect is what you want to protect. You sell them a copy, and let them figure out how to get Oracle etc working for them.
Or, you can sell them that as a service as well.
In other words, your database is yours, not theirs. If they want direct access, you'll sell them a copy of it, and let them have at it.
The other option is to clone the database, and give them access to the cloned database. You keep and maintain the existing database away from them. If they screw up theirs, then they can buy another clone of yours.
If they are as bad as you seem to think, then it should be quite lucrative for you, keep selling pristine copies.
Agent K: A *person* is smart. People are dumb, stupid, panicky animals, and you know it.
The customer (remember, the dupes who pay your salary) perceives that there's something they want that you aren't giving them. This is either 1. An opportunity to charge them for their new feature extension, or 2. You evidently don't know exactly what the customers are doing with the data. When the multinational 3M company was much smaller, they sold their Scotch-brite green non-scratch pads in various sizes. Once a customer asked if they can get a 2-foot width uncut. The salesman asked why, and the customer replied that they used the pads on their custodial floor strippers. The salesman said "We make those already, ready to stick to your floor polishers!" Then the salesman immediately calls R&D and says, "Can we make these?" The answer was yes, and the entire janitorial division of 3M was born. That's why you want to find out what the customer wants to do with your, errr, *their* data.
In MySQL at least, you can grant SELECT only access, prevent locks, limit runtime, etc, on a per user/host basis... wouldn't this be applicable here?
"Thanks for all the money you paid to us. We've used it to buy off ISO among other things" -Microsoft
Frame it in business terms.
... and on huge amounts of extra tech support because of things the customer breaks.
Giving users read only access will work, with these caveats:
- It may require the investment of quite a bit of developer time ($$) to tighten the security model to isolate restricted parts of the database thoroughly.
- It may require the purchase of new hardware and new software licenses to support the increased load. If the customer has a skilled DBA and the database is well documented this may not be an issue. In practice, however, they'll always end up writing slow and inefficient queries that the DBA must spend time tracing and fixing ($$) or that force the addition of extra capacity.
- Once you give a user read access, they will demand that you introduce more and more levels of restricted access priveleges into the DB structure. This not only costs DB admin time ($$) but also potentially reduces the security of the database by increasing its complexity.
If you give them write access you can expect to spend lots of money:
- Adding lots of extra capacity to cover inefficient queries
- On downtime when they break the database
- On adding excessively fine-grained security policy to the database
-
This is true even in a well designed database with exisitng quite strict access limits and lots of internal checking. The only time write access might even be *remotely* sane is when everything is done through (paranoid) stored procedures anyway. If that's so, it's not much different to offering them access at a web service level instead.
On a database that's not so well designed or is designed for use only by 100% trusted clients (so it omits some sanity checking and priveleges structure) you're in for a nightmare without end.
In other words: Giving the customer direct DB access will cost you money. If you do it, make sure you're prepared to add capacity (and charge the customer accordingly) and dedicate DBA time for direct customer support. Consider giving them access only to a cloned reporting DB. If you offer write access to the main DB do so only through very restricted interfaces or you will face large increases in tech support costs, downtime, etc.
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.
What your customer really need is a OLAP software to query the database. Open query access may result in wrong queries structure that grab wrong data and result in a wrong interpretation. With an OLAP software you can put roles in what relationship is allowed, what indicators and so on.
I work in social services, and my job is to be the liaison between my team and our database vendor. The biggest complaint from my staff, and from other people I've talked to who use databases from other vendors, is that we can't get the data we need the way we need it when we need it. From the customer perspective, your job is to make it happen so that we can do what we need to do for our jobs. In the bigger picture, from the customer perspective, this is why we hate database vendors. My experience is that database vendors have a database that works the way it works and too damn bad if you want something to work differently or you need to access or enter something they don't already have a way for you to access or enter. Is your job to provide a service, or is your job to be a BOFH? Am I a customer or am I a lowly peon who has been graced with the chance to use your sacred product?
When your customers can write their own queries, what's stopping one of them from
1. Copying the entire database for their own? CCBA anyone?
2. Query about their competitors who also use your services.
3. Give your competitors access to your full system. You'd be a fool not to think that one of your customers hasn't given access to your systems to one of your competitors sales people. Now image if the whole system is open.
III.IIVIVIXIIVIVIIIVVIIIIXVIIIXIIIIIIIIVIIIIVVIII
Let them download backups of the entire db that they can restore and query on their own server. Since you're not using an open source db it may not be compliant to give them direct client access to the db anyway, but what you can tell them is that you don't want rogue queries to corrupt the data or hose the performance.
It's not a perfect solution, but perhaps you could create a read-only view that is customized for each client that wants this capability? That would keep their grubby paws off of other clients' data and also abstract (and obscure) them from knowing the actual underlying data model.
Proudly supporting the Libertarian Party.
Low priority, read-only views on a limited number of shared connection types.
Queries, even bad ones, are throttled, lower priority than standard work, and they can only open 10 connections, for example.
Even this will impact production performance, but it shouldn't interfere with local transactions, at least.
You could also make disconnected views, which are refreshed at a single point in time.
Which is one step away from a replicated DB. This offers the best hope, as they could update certains tables and you oculd scrub/replicate back if you wanted.
> Presumably apart from server load it wouldn't be a big deal assuming the users are working on a read-only login?
Server load can be a big deal. All it takes is a couple of bad joins and some dirty math (can you tell I'm not a DBA?) to start thumping disk or processors in new and interesting ways. That's bad enough when your own DBA does it, and indescribably worse when a customer does it. Read-only access does nothing to solve this problem.
Personally, I wouldn't be letting non-developer-types write queries, period. If they're paying you for data processing and reporting, then provide them with data processing a reporting. If they just want you to host their instances, then charge them accordingly.
sqlnet-level access arrangements have a tendency to both snowball and become entrenched. Five years ago, my employer (a data processing a reporting shop) gave sql access to a single user at a large customer of long standing to our otherwise web-only application. Five years later, every last user at that customer expects access, and the security requirements are having a severe negative impact on our ability to build out a more robust and flexible network infrastructure.
Your shop may be able to support query access for your current customer base, but will you be able to do it in five years when you have ten times the customers? What about when you want to, say, start collocating your servers? What happens when a new customer comes on - do you give them their own instance and eat that overhead, or do you give the SQL customer access to a potential competitor's data? Your sales or customer support folks should have to answer all of those questions before they give a green light to the customer.
hang brain.
This is precisely why Oracle created Resource Manager. Figure out how much it would cost you to acquire the proper licenses (if you don't already have them), add hardware to handle the added load, and set up and administer the resources. Add your profit markup. Present the cost to the customer.
If the customer balks, you got your wish. If your customer puts up the money, you make profit. win-win.
The masses are the crack whores of religion.
You might suggest to your management installing some business intelligence software. This would allow your customers to poke around the data in an ad hoc way but without the absolute freedom to create chaos that direct SQL gives. Check out SourceForge for some free alternatives. I like the look of OpenI, but can't claim much knowledge in this area.
have them execute a stored procedure on a view that contains information pertaining to their userid only. stored procedures are like batch files. views can have certain parts of tables but not others (like a virtual table)
Contrary to popular belief, Unix is user friendly. It just happens to be particular about who it makes friends with.
Basically they are asking you for an arbitrary amount of computer power whenever they want it, for free. You shouldn't allow this.
If you provide access to some interface for running SQL queries against databases you host, come up with a way for charging them for the resources those queries take to run. If you don't, you're just asking them to write SQL queries that compute 1 billion digits of Pi.
Otherwise, work with them to get the data feeds they need to run those queries locally using their own resources. If they're worried about cost have them use MySQL or something like that.
I repeat, do not allow them to run arbitrary queries without some way of making sure that you aren't providing them with potentially infinite resources for free.
Need a Python, C++, Unix, Linux develop
Since you're using Oracle, there some ways to prevent access to other parts of the schema and prevent run-on queries from going nuts.
;-)
Create a user for them, specifically for these ad-hoc queries. This user should have no disk quotas on any tablespace, since they're not writing any data. This user should only have the 'Create session' privilege.
Second, if your customer data is all in big_honkin_schema, create some views for them. Create the views in another schema and grant them select only.
Third, if you're REALLY worried about them "touching your precious tables"[1] then create them materialized views instead. Might have to adjust disk quotas of course.
You can also set up user profiles to limit resource consumption ( number of sessions, maximum CPU time, et. al. ).
[1] Since when did Gollum become a DBA?
A Human Right
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 am sorry. We can not provide you with that kind access due to SOX compliance issues."
"I am sorry, but we can not provide you with that access because then we would not be able to provide our customers with the confidentiality they expect."
"I am sorry, but we can't do that. Doing so would mean we would have to give access to all our clients, some of whom may use that access to spy on their rivals."
There is no "-1 offended" or "-1 you don't agree with me" mod options for a reason.
stage the data elsewhere in a read-only copy that is updated daily. Let them paw through that. I do that, and it works well.
You don't really have to set a separate reporting database. Just set up some materialized views and give them read-only access to those views. You can control what you want them to have access to, by way of your select statements in those views, refresh the views on your decided schedules, and since they won;t be hitting the live tables, the performance impact will be lessened.
You will have to worry about Oracle client licensing for their connection, if you haven't already bought and paid for that.
But if you just give them actual, SQL-level access, aside from all the security and performance implications, you're also setting yourself up to be even more rigid than you may already be with respect to schema changes. You shouldn't even be touching the SQL directly yourself, when you can wrap it in an API.
Now, security and performance... Performance issues can be demonstrated as easily as a 'SELECT * FROM SOME_HUGE_TABLE' -- and you could add a few joins without conditions (or with very broad conditions) to make it even worse.
Security issues shouldn't need a demonstration. Just calmly point out that most variants of SQL are Turing-complete, and that you're very sorry, but customers are NOT allowed to run executable code on your production server.
Finally, worst-case, give them a dump of all the data they need, in a format which they can easily import into their own Oracle database. Explain that it's the closest they'll get, unless they want to use your API.
Don't thank God, thank a doctor!
I would never give a customer direct query access to any database critical to the rest of my business.
... if they want more they should pay more.
... and bad queries are the problem of their DBA.
... just to protect your asse(t)s.
A possible solution is to replicate the data tables they need onto a separate physical server that they can hammer all they want. Pass the cost of the server on to them as a one time "setup fee", and away they go with whatever obscene table joins their interns want to write. Additionally charge them for maintaining the extra service
If you don't want to spring for another oracle license, throw postgres or mysql at the problem.
If the dataset is small enough, another solution is to simply export data tables to the client and let them expend time and resources importing into their own database system. They can then hammer their database all they want
I'd pull the trigger on your Legal team in either scenario
"Glory is fleeting, but obscurity is forever." - Napoleon Bonaparte
It seems I've made a career of your question. The first 2 obvious possibilities have been suggested to death already. ("Just say No", and "build a copy for them", but no-one says at whose expense for this new copy).
I'm going to go out on a limb and guess you are (or will soon be) told "we have to service the customer - find a way."
Since you are using Oracle, you do have some options to let them in without killing the performance for everyone else.
1. User profiles... you can limit a particular user(s) and cut them off based on idle time, cpu usage, long-running queries, etc.
2. Resource manager... when the CPU hits 100% Oracle can automatically start playing traffic cop and let the connections you designate as "important" get most of the CPU time, while your hungry-end-user-hobbitses get reduced to a small percent of CPU time.
3. What you didn't ask, but stands out... security. Is this ONLY data for this customer... maybe no big deal. Does this database contain data for OTHER customers? If so then you've got some data security to take care of. Virtual Private Database may be useful here, if that is the case. Even if VPD is overkill in your case, the concept of "least privilege" is always good - don't let them see any more data than you are obligated to let them see.
4. Auditing is a good thing to consider also... log when they come in (via oracle auditing, or with just a logon trigger), log how long their queries run. When the system slows down be able to pinpoint the session that is the culprit. In doing so you may find the real slowdown comes from poorly-written-application-X instead of scary-adhoc-user. 5. Of course, be able to tell if they are killing your system or not. Monitor (Statspack/AWR for starters) when all is well and when there is a problem so you know what "normal" looks like so you don't chase the wrong activity when things slow down.
Why, oh why, didn't I take the Blue Pill?
"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.
You're handing them the keys. You're not allowed to do that. You're obligated to assess the implications and give them to a corporate officer, because you're transferring assets. Really. Don't make this decision on your own. It's your organization's assets and if they're going to be given away, an officer needs to know this.
Should accessibility be granted, then you need user controls that don't kill your CPU or generate queries from hell. RO access is one thing, sucking data and porting into MySQL or another db is trivial, so be prepared for other strange problems that you customer might try and impose on you. You need to clearly constrain the access, if in a congenial way.
---- Teach Peace. It's Cheaper Than War.
- Wrong formatted SQL Statements or Queries generated by reportint tools (e.g. Crystal Reports) can crash, or at leas slow down, the database
- The database structure is too complicated
- That way you can't ensure the protection of privacy
If they still want it:- Give them access to a replicated server/database (to ensure the stability of the production system) AND charge them for this extra service
- Ensure they are only using the tools YOU specify. For example: If you want that they only use Crystal Reports, a nice Trigger ensures they're using just that.
And hey... If the customer is too annoying you still could play BOFHYou are correct being concerned about the data. That's why they pay you the big buck no?
But the tables are not yours. Does the cleaning lady own the phone because she dusts it?
Why not just post them a CD with the database on it. You can always password protect it to keep it safe. I see you work for EDS. Their customers include... http://www.eds.com/about/locations/uk/
Oh arse
I would see if setting up a REST interface might do it for them. It's not the low level SQL access they asked for, but with a library like ActiveResource they can work with it. Ethically, it's not much different for you than exporting a CSV or something.
chris@xanadu:~$ whatis /.
/.: nothing appropriate.
Being a sys admin I know where you are coming from on this. I get weird requests all the time, and most of the time it is because some vendor has been feeding crap in the ears of my user base. Usually most of the time you just have to tell them why it is a bad idea, and also try to speak to them in terms they understand (I.E. overhead costs) Let them know that this will take some of their employees time up, and that is why they pay you for your service. So in essence they are paying for the service twice then. You know by letting their employee fuss about getting the data, writing the queries, etc. Chances are they have some dipshit kid who is running his mouth out of inexperience and telling them that he can do more comprehensive searches and return the results faster if they will cut you out of the loop. usually the money arguement makes sense to them, because it is in fact a good deal of time that their employee can be more productive doing his own job and generating revenue. Remember as an IT folk, you are overhead, they figure you into the cost of doing business. You are less expendable than a FTE and they are paying you for the exact thing they now want to take over. You make a good enough arguement about the money of it and management will always buy it, because profit is the name of the game.
If they insist on it try the PCI line. Most folks lap that up and just nod their heads in agreement. Then ask them if they need more focused data and better queries. Because this kid probably spit in their ear about saving money let them know the time involved in writing an effective query and how much of it their employees will spend mining all this data. Really to me it sounds like they aren't getting exactly what they need to get, so they think by bringing it in house they will find the data they need. What really needs to happen is you need to sit down with them and get a better idea of their needs and make them feel like you'll go the extra mile and that is why they pay for you to do it.
First, I would have to ask, what does your contract/agreement that you have with the customer say? It may be spelled out in there that you will provide them X,Y,Z services but the access they are presently asking for is not covered and not allowed at the price they are currently paying. If it doens't cover it, you have to make a case for why ti doens't cover it and proceed on.
If they say, well, how much extra will it cost to give us this option that we want, then you figure out how much it will cost to make sure they can not screw you up and give them a new contract to sign for the option they want. If you can not make the data secure on your end where they can not significantly screw your stuff up, then make the price of the contract high enough to cover 10 times (or some reasonable saftey factor) the number of likely incidents they will create. I.E. If you could guess they may screw the stuff up 10 times a year and it would take you 3 days to fix it, then do the math and say, 10 times* 3 Days, * Hourly Rate* 10 (safety factor) and tell them that is the anual cost to have the feature they are asking for. You may also want to include a clause that if they screw up your data base, additional extra charges can be levied at a certain rate to fix it in case they mess it up way more. They want an option, figure out what it is worth to you to give them that option and then make them pay it.
If you can't make it secure/don't want to, then as someone else said, tell them no, it can't be done and explain it, but that can be difficult. It is usually pretty easy to discourage a customer by saying, not in scope and that will cost a lot of extra money and they will usually say, nevermind.
We don't allow any end-user accounts of any kind on our primary database servers for a number of reasons, but one of the important ones is that a significant number of vulnerabilities that have surfaced for Oracle involve privilege escalation. Many require that a user have the ability to log in to the database before they can exploit the vulnerability. Keeping the unwashed masses out of your database is a good idea, if you'd like it to remain your database.
Against stupidity, the Gods themselves contend in vain. --Friederich Schiller
if your boss really wants this he should then by you a new server and setup a read only DB on that server all at his expence
Well, if you don't want to just outright say no, you could make up a fake excuse.
"Someone else was stupid once, and ruined it for everyone, and now we have a policy not to allow access to anyone."
Use something like BI Publisher, or other reporting tools that come with the Oracle Database.
Or, make an APEX application from the data that you want them to be able to see. APEX apps take almost no time to create.
Create an account for them that only gives them access to their data. They will only see data that they have access to.
Realistically, if you want to make your own life a lot easier - give them self-serv access. It will make you better able to focus on other, more important things.
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 customers have $$$, get them to fund a web application that does ad hoc queries based on user selection of tables, columns, and values entered in a nice HTML form.
You build the queries dynamically. You control access to which tables and columns users can base their queries on, including select lists for allowed values.
That way you make some $$$ and satisfy your users. Everybody wins as long as the application you deliver is a good one. Make sure to include the ability to save queries (public and/or private).
I have faced this type of requirement too. Either create a schema that has read-only rights or strictly filter out keywords and other troublesome things such as --, TRUNC, DELETE, etc. Good luck!
you should never give access to run adhoc queries on a live production transaction based system without expecting severe producution performance problems. that in itself should be a winner argument. however they are your customer so try to identify what their true business requirement is. offer to give them more complete automated data feeds that you control which they can load into their sql server database. offer to provide them an additional reporting database, for more money, that has a snapshot of production data. essentially that is what their own sql server is supposed to be for, but apparently for some reason its not meeting their needs.
I'm currently on the other side of this problem. We utilize a 3rd party service to collect metrics on visitors to our website. The interface we have to query the data stinks. Any request for custom reports cost extra money, take a long time and are bound to be error prone. We have the capacity to replicate the data on our own dataservers, but the provider does not offer us the option of downloading the raw data. The should do this for us, and you should do it for your customer.
Don't tell your customer no; find a way to meet their needs without compromising the integrity of your platform. That's how a good service business is run.
If they still want it, you just didn't propose a high enough cost. Double it and try again.
politicians are like babies' nappies: they should both be changed regularly and for the same reasons
It's not shadowing, but similar, where you work on a subset of the data (a subset that consists of the whole dataset, essentially a replication, but not in real time)
GOD the name escaping is killing me. DB was SO long ago. Anyone?
How much is your data worth? Back it up now.
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.
"It's the height of ridiculousness to say for those 9 lines you get hundreds of millions."
Just allow them to specify the WHERE condition, validate it using regular expressions, determine which tables are involved, build the full select on your end and use in a nested select that does pagination so that no more than a fixed number of records are selected. Expose only this API function. Alternatively, log all the queries and write a contract that says you terminate their contract and they owe damages if they screw up.
Oracle is more than capable of dealing with this situation.
... figure out the real reasons you don't want them to have access to the database and make your case based around that, if you have one. Don't try to BS your way out of it with a 'its insecure' or 'its dangerous' excuse, cause in those cases the fault lies in you, and they may very well have a DBA that can point that out. If you want to use those excuses, you shouldn't have used an enterprise class RDBMS that has been capable of dealing with these requests for years.
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.
Persistent Volume manager for Kubernetes - https://github.com/dwimsey/openshift-pvmanager
I'd say it's probably time for the customer to just manage their own data. If they want access now, they'll want control soon. Unless this is your only customer you need to cut them loose. In the long run everyone will be happier with that.
(I'm not a lawyer, but I envision the conversation going something like this...)
"Sure, I can open up the database for you.
But if I open up the database to you, please understand that it poses a massive security risk, and I advise you against requesting that I do this. If you compel me to make the data available, you will no longer be able to hold me responsible for data which are copied, stolen, corrupted, or rendered inaccessible by any means, including unanticipated malicious attacks or by your own incompetence. Also, please be aware that I reserve the right to bill you for any expenses that may be incurred if your actions or the actions of others render the data inaccessible or corrupt.
Please sign here to indicate your acceptance of these criteria."
If the customer is an outside customer and access the DB over the internet, then why would you allow access. Either you will need to create a login (with VPN, etc) for your network or open ports to a DB server. Hint, option 2 is real bad! Tell them security standards don't allow for this. I would not even ask or entertain the idea of opening up the firewall for this. That is a disaster waiting to happen. If you do allow this, be ready to hear about yourself on the news for customer information leaks, etc. You know, the kind of story you hear and think to yourself... "What a dumbass, I would hate to be in his shoes! What was he thinking?"
--
My parents went to Slashdot and all I got was this lousy sig.
1) Who owns the data in the database? If it is just that customer then why shouldn't they have access?
2) Are they aware of the potential performance hit if they screw a query up on the production server? If so and they accept the risk then let them...
3) Do they want read only access? If so why not provide an access to a shadow database via odbc - explain that the data is, say 24 hours out of sync, with the live system and let them play in what's effectively become their sandbox (IE put the shadow database on a seperate server, do not allow access other than for mirroring from the production server, do not allow any acess from the shadow server to other parts of your network, restrict heavily what they can do to the minimum they need)...
4) Is there a risk that by gaining knowledge of the internals of the database etc that they could over time stop outsourcing their database needs? if so tell your boss that, if they will see proprietry schema's etc which are your companies IPR then tell him that etc...
--- Users are like bacteria -> Each one causing a thousand tiny crises until the host finally gives up and dies.
Yeah, that happens all the time. You get 2 marketing folks that typed a SQL statement once in their life and think they can do your job better than you. Ignore the veiled insult and give them what they want (they'll come back crawling for help later).
1. Ideally, if you provide them with an OLAP system with enough dimensions and drill-down abilities, you might find that... they don't need SQL access anymore.
2. They ask for it, ok: replicate the damn thing! They get access to the replicated database and can run whatever the hell they want. Put that on a separate server so if some douche runs a horrible query it doesn't ruin your primary server and voila: no worries about bad stuff happening and you're really giving them what they're asking for, though arguably, I'd still say I prefer and always implemented #1 myself and mostly avoided giving them #2
You need a defined interface with your customer. It's like an API. So they know what to expect from you and you know what to expect from them.
If you have a high level interface which the customer can access (this could even mean email), then you are at liberty to, for example, change the structure of your database to make it more efficient. The customer doesn't need to know.
If you expose the contents of the database, you're no longer at liberty to change it when you want. Any changes you do make will impact the customer, requiring justification and *support*.
This might be bad for both you and the customer in the long run.
Maybe you could *for an additional fee* provide another way to access the data for custom queries? This could be copying/transforming it into another database which you can give them.
You could find out exactly what the user needs and set up views accordingly and give them permission to only access those views
If the database is very interesting than you are putting your self a risk by giving unrestricted access to the database. A client could easily download the entire database and make their own. This creates the risk of duplicate data as well as giving the database to potential competitors if a client was to defect.
Where I work, we are hampered by half-assed pregenerated reports and wonky query tools. This becomes a major time-waster. I've sat through ridiculous training classes of report generation tools just to get a negative answer to the question: "Does this let you access and edit the generated SQL?". A negative answer means the tool is basically worthless.
Finally, I got access to telnet in to the box that holds the database, and ability to run queries (and SAS). This finally allows us to analyze data without horrendous lag times, and actually makes the db admin's job much easier. You can set permissions so they can't overwrite data. Don't be overprotective.
Preferably, a fee large enough that if they pay it, you can afford to replicate your database in a sandbox for them to screw up at leisure.
I'll echo the other posts warning about Data Protection Act and server loading issues, and add the concern of unauthorized access to the database and/or third-party attacks on it.
"My strength is as the strength of ten men, for I am wired to the eyeballs on espresso."
I agree that in general I would not like to do this sort of thing. But if you absolutely have to do this just create a view of the data they need. It restricts their access and is by definition read only. --The Dan
the problem is called "Operational Risk" rather than 'because you are stupid' and that's a completely valid accepted reason.
How much do they know about your database layout anyway? How much time will you be spending explaining 'what's in table ??' or 'what does that field mean?' Who will pay for that time?
At worst create a few views for them with proper access control and with reasonable indexing so whatever they hit the views with can't bring the dataserver to its knees.
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!
There are so many fucking repetitive replies on this post it's sickening.
"linux is just DOS with a UNIX like syntax" -- Galactic Dominator (944134)
There is a very simple argument against giving them direct access to your internal database. Presumably they are going to be writing queries and running reports which that are going to want to run often. Once they have spent this time creating queries and formatting reports based on your tables who is responsible to update them when you make schema changes to your database? It would be MUCH better to provide a secondary database which at first might simply be a replication of your database. This gives you the flexibility to change your schema for your database without breaking any of the queries or reports they have spent alot of time and money creating. This is a very effective argument becuase it shows them how NOT querying your database directly is actually beneficial to them. Telling them or your boss no is a bad idea. Showing them a better solution to thier problem is a good idea and will be much more likely to meet with success. Don't present it as a limitation, present it as a benefit.
They'll want read only access for simple reports..then they'll start integrating their systems with yours and expect you to retain your current DB schema forever.
A customer of ours supplies software to the large TV networks and they've insisted on direct access to our customers underlying DB. Guess what? Our customer's hands are now tied since any changes they make will break their customers queries...even though they wanted the upgrades in the first place!
Just tell them that 9/11 changed everything and that's why they can't get access.
This is a standard contract issue.
As long as you have put your [ database | web server | automobile | children ] in my care, I am 100% responsible for its safety.
You may have your [ database | web server | automobile | children ] back at any time. At that moment, I relinquish all responsibility and will not be held liable for any damage that results. You agree to accept full responsibility for its safety.
If you take over responsibility and then wish to return control to me, I will charge $150 an hour to inspect and verify the integrity of the database that is being returned to my care. I may refuse to assume responsibility for the database being returned to me if it has been altered in any way.
It sounds like you need to look into a reporting tool: Business Objects, MS Reporting Services, whatever. Copy/replicate the data to a separate server to keep the users away from production, and then let them do what they like on there.
There are good reporting tools that be web-based, write the SQL for them, detect and prevent cross joins, handle logical security (client X can't see client's Y data etc.), use intelligent caching, export to Excel/CSV/PDF etc.
It may not be the easiest or cheapest thing to set up, but on the other hand it will probably be a lot more reliable and smarter than anything you can hack together yourself. And since you're providing an additional service to your customers, you can bill them for it.
--pondlife
... if "no" simply does not work, discuss the costs of creating and maintaining a replication server which their custom queries will run so that none of their queries will affect the stability and performance of your main production database.
We were all warned a long time ago that MS products sucked, remember the Magic 8 Ball said, "Outlook not so good"
My first question would be: Who owns the data? If it is the customer's data and just your systems holding it, then there's no problem (barring that systems are put in place to keep a badly written READ call from bringing down everyone's systems). If it is your company's data and they are just paying for access to it, then giving them direct Read control is essentially giving them a copy of the database. I would wager that, if the latter situation is the case, your company views their database as an important asset and having a client download the entire thing would make management queasy.
My sci-fi novel, Ghost Thief, is now available from Amazon.com.
It's your job to inform your manager whether or not it's a good or a bad Idea to do whatever new someone wants to do w/in your sphere of influence.
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 night wanted 75G of data recovered to be burned to DVD. I wasn't going to go buy a column of dvds, and swap disks all night, while trying to divide it up all pretty. so I said no problem, 4 hrs at 75$/hr, or you can go buy an external drive at costco for 150$ and I'll do this for 1 hr @ $75/hr saving you $75 dollars.
It wasn't too long before he said "I'll drop the external drive off on Monday."
There's tons of reasons to do something or not, but if you send it out to Management in these terms, you'll have the best response.
1. Economics
2. Your Liability
3. Decreased (or for a positive, Increased) RVU (more time taken to do less work)
4. Their Liabilty
5. Their increased/decreased RVU
6. Solve the problem, and make sure you make money off of it. Tell your boss that they should front the cost for a project for you to build a shadow server for them to work on. Think how much it'd cost to set it up, make it functional, then support it for 5 years. Write it out as a $ figure, and propose it. Become your own vender, sit the server at clients site, hope their bandwidth costs for shadowing the data is not too high, and reap in the $
How much is your data worth? Back it up now.
Step 1. Learn how to DBA
Step 2. Learn how to write a service contract
Step 3. Learn how to recognize a sales opportunity.
It is rather apparent from your summary that you have not yet mastered any of the above three steps. At this point, "I" would recommend to your customer that they seek services from another firm.
Modding Trolls +1 inciteful since 1999
This is a pretty common problem. I'd suggest picking up a good book on (**** warning, buzzwords incomming****) SOA/ESB solutions. There are plenty of good, proven patterns to help solve this problem.
Here's a good start:
http://books.google.com/books?id=dH9zp14-1KYC
http://manning.com/rademakers/
"A mind is a terrible thing to taste."
Point out the staggering number of security vulnerabilities Oracle has fixed, has not yet fixed and is likely to discover as a reason for not permitting remote access via TNS. Demonstrate how simple it is to cripple an Oracle database with a couple of cartesian joins.
Work up a proposal to establish a read-only replica + isolated subnet + secure VPN to address the above problems. If the cost doesn't shut them down you end up with shiny new hardware.
Don't use phrases like "my precious tables". You sound like a dork.
I think everyone is looking at it like DBAs... think of it like a sysadmin/network admin for a second.
HOW is this company going to access the server directly? Are you going to hand the oracle server's a55 out onto the internet directly? Are they going to use a secure VPN tunnel? Do you have the hardware for the VPN?
I think the bigger question (and better answer for MGMT) is HOW would they get access if they are not physically on location, as otherwise you are opening gaping security holes, let alone what level of access they have on the DB.
Hopefully this gets modded up rather than down.
The common solution for that kind of dilemma is to create a read-only copy of the DB, refreshed from actual data nightly, which the customer has access to. This will allow you run it either on a separate box entirely; or at minimum set a max on the CPU(s) allocated to this new DB -- so that your production app doesn't fall to its knees the first time some ninny performs a Cartesian join.
Make it available as a premium service and charge for CPU usage. If they write a screwy runaway query, you can retire early!
-- Don't Tase me, bro!
It's just a part of your Business Rules that only internal DBAs are allowed to run queries against the database.
"It's not like your minds are as open as the source you love..." - Me to the majority of Slashdot.
Firstoff, I agree that the client shouldn't get direct access to the database server. They probably will mess things up. One way to get around this is with realtime data replication. There is software, among other people by Datamirror (ask Wikipedia) that does realtime database replication and transformation. This means that you can duplicate only those tables/columns you want and even manipulate data as it's being replicated. Nifty stuff. You could set this up between your live server and a secondary server for the customer. The upshot of this would be that the customer can fool around on the secondary server, see only what you want him to see and if it crashes the server won't take your business with it.
Hope this helps,
Coward 312-213
"they have now asked for direct read-only access to our Oracle database, to be able to run ad-hoc queries without consulting us"
You meant...
"they have now asked for direct read-only access to our Oracle database, to be able to run ad-hoc queries without PAYING us".
There, fixed that for ya.
Now it's all clear to me. I've had clients like this. Write them an ad-hoc report generator, bill them accordingly. This should take a year at least, to get their requirements and meet the 19th version of the requirements some time in November. Then you should get them to sign off on the final version by next March or so.
I suppose you could expand the Web gizmo to allow more than 15 fields, but where's the fun in that?
Alternatively, you could farm this out to an Elbonian consulting firm and take a cut.
I'm so glad I don't do that any more. Made me feel dirty charging clients for their own data. My former boss is in real estate now, for somewhat obvious reasons...
deleting the extra space after periods so i can stay relevant, yeah.
I'd recommend using Application Express (used to be called html_db) / Discoverer. Create the reports they need - and give them access to that. It's web based, you can tune the query and you can even schedule it to repopulate the cache on a daily or hourly basis etc. Might also want to look at using Oracle Portal too if you have multiple clients that you want to do it for. The less people with direct access to your database the better!
Respond with an offer for daily/weekly/monthly cleansed extracts. Give them the possible fields you would let them have, ask them to tell you which they want and write and SQL extract script to make the dump, compress it andemail it to them for $XXX.00 per extract. If you REALLY want to be helpful, import the extract into a read only MySQL hosted on a locked down linux box that only allows connections from their IP with a specific userid. Tell them the SLA is whatever they want to pay for. If they don't want to pay, they don't get to play.
IBM's DB2 has a feature that can estimate the cost of a query. DB2 Query Patroller takes advantage of this feature and allows you to define rules such that expensive queries against your database can be rejected, given lower priority, or scheduled for a later time, for example.
I know you're using Oracle, but perhaps it supports something similar?
Your real name wouldn't happen to be SmeeQL, would it?
If "disco" means "I learn" in Latin, does "discothèque" mean "I learn technology"?
And create a consumer group limiting their impact to the overall functioning of the data store... Under no circumstances can they access base tables (unless you implement virtual private databases). With views, you don't have to worry about malformed joins, or peeking at data they shouldn't be able to see....
- install a new oracle on a dedicated box. If there data set is small, you can do it for free with XE (express edition). If not, you will have to pay $750 (list price) for SE1 (standard edition one).
- set up oracle replication so that you publish changes from the main server to the new server
- do these tables have data for multiple customers? If so, create views to filter other customers out. From your description it sounds like this customer has its own dedicated schema, so that's not a problem.
- make the schema read-only.
- turn on auditing so you can keep a log of their queries. This will be quite useful to them!
- disclaim that the internal table structure may change. If they want some guarantees regarding the table structure, that can be taken into account with the views.
The nice thing is that (a) you can charge for all this, and (b) it makes the customer happy. I imagine they want to do excel query directly on the tables, so you can imagine this would make their life a lot easier.
Gratuitous career advice... drop the BOFH attitude, instead think of how you can make an awesome environment for your customers.
just because they have readonly access, doesn't mean they can't bring down your system. I mean, what if they write a bad query that joins incorrectly and produces a huge cartesian join. not only that, but if there's more than one customer's data in there, what's the stop them from querying other client's records. maybe one way is to give them a page on a web server that would scrub their sql queries, and maybe append something like "and customerid=blah"
I've actually been on the customer side of this debate MANY times, and I always think if at all possible the customer should have read only access to the database containing their data if at all possible. You say you frown on the idea of customers getting directly into the database, but if it's a properly setup relational database and you have a decent schema or layout, even if technical and not for lay persons, that's all most folks need, even if you provide it 'as-is'. In today's world, you'd be surprised how many folks have database experience in small offices or how many offices have resources to help them get the data they need if given the ability. Becuase no matter what tools you provide, someone within the company will always ask for data the canned report writers can not provide.... and charging for custom reports when there IS someone within the office who's DB savvy enough to create the reports they need just seems a rip-off. so my post might not give you want you're wanting ot hear, but if at all possible, grant read only access on an as-is basis if it's technically possible.
The strongest technical argument I can think of is that this breaks encapsulation: you are exposing your internal data structures to your customer, which could make it much more difficult to change them in the future should this be necessary.
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.
You may not be able to justify completely excluding them. However, if you have any data that could in any way be linked to specific individuals, you may be able to achieve your goals by putting onerous liability requirements. Require them to sign a 'transfer of risk or liability' contract or form in which they assume liability and responsibility for any financial or criminal data breach, leakage, etc. Also can you require signed confidentiality or legal contract form each employee who will be accessing the data. Tell them you will happily give access but each individual will have their own unique username and password for auditing and issue tracking purposes, and write in the contract that sharing these passwords will result in revocation of priviliges. Possibly modify your contract or financial relationship to include billing for additional issues resulting from 'I tried this query and now the database isn't responding for anytone'. If you turn the issue around in such a way that their managers or company has to carry the financial impact, you will at least be able to minimize the number of people who have access to the data.
Views still don't necessarily limit run-away queries. If some kind of time limit cannot be put on them, then perhaps considered creating say 3 views: one that requires a date range, with a max range of 1 month (checked with triggers), another that requires a specific product category, and another requires a specific location. That way you know they are using at least one indexed column (date, product category, or location). You may have to create a set of these for say sales transactions and product catalog. Thus, the sales transaction view would have about 3 views and the product view would also have about 3 views, each one based on a required parameter for an indexed column.
Table-ized A.I.
Stop paying Oracle, use MySQL, setup a dedicated slave for the client to abuse. end of story.
Limit their account to read only. Give it access to specific tables and, if possible, specific columns. Using quotas (or the proc resource equivelent), limit the amount of CPU time their queries are given. Also, add a max query time to the account so they can tie up the DB with a lot of slow queries. Then, track resource usage by the user account so you can decide as things progress how efficient your rules are or are not.
To quote Nancy Reagan, "Just say NO!" Seriously, your files are proprietary and private. If you could find a dummy Excel file that you could send them, that might help them and would certainly save you. If you don't have an old dummy file for them, maybe you could hire a data entry temp to create on or look online.
Seriously, get over it. What is the problem with a customer accessing customer data in a database? Provide read only access along with the table schema so they know what to query for and call it done.
The only problem you need to watch is to make sure they don't cause performance problems on your server with huge convoluted queries. If that is a problem then you limit them to certain times of the day/week when the load is lighter.
I have worked on several projects where the application we wrote utilized a database. In all cases a document describing the schema was made available so the customer could generate reports as needed.
we had the exact same situation here at the company i work for. the one who stopped it was our network admin that refused for obvious security reasons to open any path to the databases for external traffic. and that stuck. cheers
Can I light a sig ?
I like to stick with the new paradigm in data management:
STITT
Which stands for: Stab them in the throat.
Its your DB, make 'em back off.
worst case.... make a shadow database and create some very restrictive views.... best case, create a webservice where they can get the data to load into their own database to play with.
Just create a read only VIEW that limits them to what records and columns they should be allowed to access and let them replicate the results to their own server for playing around with. Optionally you can create a stored procedure to track what they are looking at, limit the number of times per day, or even time of the day when they have access to that data. In fact, why not just hook the stored procedure into paypal so they can make a direct payment to your company each time they hit your database! That should at least make them a little more thoughtful about beating on your database too hard.
I understand that you may want to keep them from doing queries with 19 table joins (yes, I've seen that before) and such, giving your database machines performance issues but remember, it's their data... the next thing they may be asking you for is complete backups/dumps of the data (so they can use machines/services where they can do their queries), but that may be OK for you, too.
The real question is do you have a good reason not to give the customer what they want?
IF you do(concern for performance, concerns about amateurs misinterpreting data, bandwidth since they will have to come in from outside the LAN, concern they will copy everything and then no longer need your company's services...) then express it concisely and it should be no problem. If you have no concern that you can express, then you should probably give it to them.
In fact, it is generally a good rule of them to give the customer what they want if the request is reasonable. So I would go a step further and say that even if you have reasonable concerns, you should look at ways of addressing or mitigating them and proposing those to management too. For instance, if your concern is bringing the server's performance down, perhaps set up replication to another server dedicated to the customer (with the customer providing the money for this additional service and its costs of course). If your concern is misinterpretation, that can be **Partially** mitigated with good documentation, which you probably should have anyway. If the concern is accessing proprietary information, use proper security so they cannot, or to be even more paranoid give them access to only a replicated server which will only have the information they are allowed to have.
What about your license with Oracle? Will that allow such access? Their licenses tend to be restrictive about this type of thing.
A friend gave me a piece of advice that has served me very well. He said to "never say NO, always give the customer a choice".
In this case, many posters have already pointed out the obvious problems with security of their and other customers' data on the main server, and the problems with badly formed queries trashing your performance.
But, instead of saying "no you cannot acess the main server to enter your queries", tell them that you understand their need, explain the issues, provide options X, Y, and Z to meet their need, and provide rough costs and schedules for each. (Of course, none of these options involve actually running queries on the main server.)
The options might inclulde setting up a replica reporting-only server in your data center, shipping the data to them periodically (assuming that is reasonable based on data ownership, size, available transfer techniques, etc.), and/or setting up a replica reporting-only server in their site. Of course, you'll come up with better options since you know the business and the customer, but the core idea is find a solution and bring it to them.
The customer may not like the costs or the schedule, but they are much more likely to remain your customer than if you just said "NO". The best case is that they buy one of your solutions, which is a win for everyone.
Just setup a secure SOAP or XML data service. They can get live data from the server, and you can put limits in the queries to protect your servers from melting.
It might be 'your database', but it sounds like it the 'their data'. Give it to them. Whats wrong with read-only access? My guess is it boils down to fear of:
a) working yourself out of a job. Why would they need you as the bitkeeper if they can write their own queries.
or
b) someone who knows better might tell you why your schema sucks.
In either csse, sharing the data might be a hard thing to do, but is in your long-term best interests... do you want to be maintaining reports forever? If you are designing a lousy database, wouldn't you want the education to do it right?
I think your customer has a legitimate need, which is to be able to get answers out of data that you store and manage for them. Although your customer thinks direct access is the best solution to get these answers, it may not really help them because non-DBA users often don't understand the database structure or the SQL syntax that's necessary to make use of the data.
I'm with Kirix, and we currently have a similar situation with one of our clients: they have a backend database that they don't want their users to access, but their end-users want the ability to do ad-hoc analysis on the data.
Our solution was to set up a nightly job to extract out relevant portions of the data into CSV files and post them to a secure website. The users can then open the CSVs directly by clicking on their hyperlinks in Kirix Strata, and then manipulate them directly any way they want with a simple interface. As a result, 1) the users get what they really need, which is the ability to do ad-hoc reporting on the data, 2) the database remains secure, and 3) the solution is easy to extend by making additional data available via new hyperlinks on the secure website.
In your case, this may work well, as long as the individual data tables you post are reasonably sized (less than 1 million records).
A simple and good reason for not allowing everyone to run SQL on your database would be that a badly formed query can bring down your database to its knees. Which, from what you've described would rather impair your business.
I hope this will help convince your managers to choose between "restricted access" and "no access anyway because it's dead".
In some instances, we simply mirror the data on a second reporting server that provides and then provide read access to the users.
But one thing I haven't seen mentioned so far is use of a BI tool - like Oracle's OBIEE+. This, of course, requires up front development but in the end empowers the user, no?
Technical reasons aside (there's technically no reason you can't do this,) the main reasons business/risk exposure reasons. If you make money off running reports for them, why the hell would you want to let them do it themselves?
Also, if you're not skilled enough with Oracle security to be able to do this; it's not going to happen overnight. Just say you don't have the proper security structures in place to allow for that kind of access.
Besides, do you want to be the one who has to explain to the rest of your customers that your database access got 0wned because "Special client A" had a vulnerable intranet web application they wrote to query the DB without your permission that allowed the hacker to query your database and run some form of Oracle privelege escalation to get access to 20 million credit card numbers? You can't trust your clients to be as careful with security as you are.
Since you can't control the security on their end, you can't trust them. Financial companies have external compliance audits to iron out this kind of thing; is that what you're going to do for this customer? Or is the prospect of a $250,000 security audit every 2 years enough to scare them off?
Out of the box? No. Thats the answer. Slightly longer answer....if they really want all the info in the database and access to it when ever they like they can buy your company for it.
Elsewise you should not give them access to something that could very well impact your company's ability to business with other customers.
It is actually quite a ridiculous request on their part.
...that makes the IT department a liability.
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 what kind of resources and safeguards are necessary to provide an enhancement so the company can decide how to expense the upgrade. But just saying ninny, ninny, boo, boo, you can't have it is ridiculous.
As one of the few (in my workplace) non-IT people that actually does know how to write an SQL query, and does want to manage my data quickly than some poorly-written propriety software (desktop or web) will permit, I routinely ask for read-only Oracle access to the pertinent databases. It's read-only; what's the harm? Makes my life easier, and makes me more efficient.
--Jim (me)
depend on your problem. Free Queries against a live database a no-no - so set up a sync link of whatever flavour you like. Either host it yourself, or to a db on their site (guess it depends what the load and your link is like). If you're trying to 'hide' the internal workings of your db, then just create some views that match the daily reports you send them and sync those. Not entirely sure what your problem is..
Sometimes these decisions are made above your pay grade. Just interject your own creativity and reasoning in between and hope it all works for the best. Here is what I would propose if I were you: Pre-parse and re-write the query before submitting it to the database. Some checks: Make sure it starts with SELECT,and does not contain ALTER, UPDATE, DELETE, TRUNCATE, etc. in the query string. Remove all ";" semicolons to prevent them from stacking commands. If a ; is found, strip everything beyond that. Require them to log in and establish their credentials somehow. Let's establish them and make a session var equal to their login uuid or something. Substring search their query "WHERE" clause, and interject a "and customer_id = %cust_id%" to restrict the returned results to just the stuff they are supposed to see. If none of that flies, you can offer your resignation, saying that you can't support it, and if they insist, bail out of there.
I'm surprised this question is even posted.
My answer is: duh, make a copy of the database, do not encourage them to fail.
On a different note, it is a shame that slashdot accepts this story and not something more important such as the forensic evidence behind wtc7's collapse.
I think a lot of these comments missed the point. It's not that he's not thinking of saying no. It's that he doesn't know HOW to say no or what reasons to give. So telling him to "just say no" isn't sufficient and was a no brainer from the beginning.
I think the arguments I saw that were best boiled down to the fact that you are providing an information service, and that you're not selling the data. And the other thing is that they need to know the downfalls of somebody inexperienced bringing the whole server to a crawl.
Pancakes. Oh I blew it.
Seriously, not all of your customers are "stupid" and it's damn right that "because you're stupid" is not a valid reason.
What makes your tables so "precious" anyway? My guess is you have some superior design that you want to keep away from all the "stupid people" (e.g. everyone besides you).
Anyway, you could make a shadow copy of your precious tables and create some views to exclude information that you don't want returned. You know, offer a compromise that allows them to get at most of the data without being able to adversely affect the main database.
Even a stupid guy like me can figure that one out.
"Alcohol, Tobacco, Firearms, and Explosives" should be a convenience store, not a government agency.
One reason for security is competitive advantage. In your case you could define that as the value add on the collecting of the data. Yes at a low level you can make the information available to them in a safe a secure manner. This thread is full of good ideas. It sounds like you are looking for a reason not to share the data and from a competitive advantage point of view, you collect the data and make a subset of it available to them. You may have other customer information in the same database, you may have data fields that you don't want to share, etc. In any event if it's your information and you must provide what you want to share with them.
You can also offer "more data" for a cost and pass along the additional work you'll have to do to protect the information.
good luck.
This is where a Business Intelligence tool such as Oracle Discoverer, Business Objects, or Cognos come into play, as well as a Data Warehouse. Store a copy of the data in the data warehouse, and provide them access to the BI tool, which will allow you to control access, preset joins on tables, etc.
Remind them that simply because they don't have to use the waterfountains for coloreds anymore doesn't mean you two can coexist in the database together.
Then put a white sheet over the server and a noose from the CDROM tray.
And you are 100% right.
Well, and as alternatives the OP could proxy his database thru SQLrelay... or replicate his client's tables in other database, giving access to that one.
But he should never forget (and neither should the GPP) that the customer is always right!!!
It's better to be the foot on the boot than the face on the pavement. ~~ tkx Kadin2048
The customer is always right, and if you don't agree with the customer, you are obviously wrong. Period.
It's better to be the foot on the boot than the face on the pavement. ~~ tkx Kadin2048
I would create an Interactive Report in Oracle APEX (Application Express, formerly HTMLDB). This will allow you to restrict everyone that logs into the application to their own data. It will also allow them to customize their own report, hopefully reducing the number of one-off requests that you get.
Sounds like you need a Data Warehouse for your reporting needs. Using a multi-dimensional db model (star schema) you can de-normalize your data and create an easy to use reporting database to allow your customers to access. Most of the time your customers will not need up to the minute real time statistics; so you can use a multitude of ETL (Extract Transform Load) tools available to 'load' your warehouse each night, only loading the data they need to see. You can save space by aggregating your data and any modern fast sql db can be used to store the warehoused data (MySQL, PostgreSQL if you want to save some money). You can then set user perms on that separate database and it makes reporting MUCH easier. Makes it really easy to design cubes (ie Mondrian), create easy to query reports, and it reduces the load on your transactional db servers. There are many resources for building warehouse and data marts (Oracle Warehouse builder, Pentaho (open source), etc) Using 'slowly changing dimensions' methodology, you can track changes to your data as well without bloating your transactional db servers (say your customer changes mailing addresses, product price changes,etc).
All of you who are advising kdawson to say no, pause for a moment and think of it from the other point of view. What if *you* were the customer, and you had hired a company to provide some data management service for you? Wouldn't you feel justified in demanding full and unrestricted access to your own data? If your service provider refused, how long would it take you to start getting quotes from competing providers?
The customer owns the data and should have whatever level of access they want. Your job as custodian and manager of that data is to make that happen, using whatever reasonable means are necessary to enable access while ensuring that there is no risk of damaging the service, including training the customer if necessary as well as the other technical solutions people have suggested. But don't say no.
(I am in this situation personally, as the customer, and the service provider in my case would have lost us as a customer long ago if it weren't for some higher-level business alliance factors that keep us locked in.)
The same truth that I tell my clients:
"I could never guarantee reliable service and performance if other people have access to my systems. I have other clients, and I can't put myself in a position where one client can hinder others. That's why your system runs so well, because no one else has access to it."
Look, you're talking about an SQL database with a whole whack of records. Read only means they can't change information without some kind of hack. But nothing stops them from accidentally running a query that returns eighteen trillion records. We've all done it at least ten times by accident. Oops, missed a join condition.
Unless you're going to start playing with permissions that restrict the complexity of the SQL statements being run. . .
I would not say "no". I would provide them a quote on the cost to impliment their request. For security reasons, you probably need to grant them a VPN of some sort. Just opening a readonly port to an oracle system over the internet would probably be a bad idea. In addition, to protect other customers data, you may want to tell them they need a dedicated oracle system. Plus you probably need additional Oracle licenses, now that they are not using web. Plus they will need a reporting tool. And the hours it will take to impliment. In effect, you are telling them that "just giving read-only access" is not a trivial request. By forcing them to foot the bill, the decision for the value of having this is all on them. What do you care if your customer wantts it or not, it is their money and they can justify how they spend it. We people ask me "can we do..." and I tell them "with enough time and money we can do anything".
What you should consider are ways to provide the service or information, and the costs of each option. If the solution is to give them their own DB server with a copy of their data (so they can screw up its usage as much as they want), then tell them how much that will cost (include the cost of resources to transfer the data to the DB server). If there are less hardware-intensive solutions then find a price for each option. Let the customer choose how much they want to pay for the various services; you won't care too much because you'll profit from any of their options...unless you underestimate the cost of what they chose.
you forgot one thing, they are in fact "stupid and ugly".
You can create a replicated copy of your data filter by only this specific customer data and only give them access to that database, hopefully you will put this db on an other server...
This should help you keep your data and CPU safe from your customers...
The moment they ask for direct access it means as a sysadmin (or the company) you've not anticipated their needs fast enough.
Think about it: the real reason they want access is to fill a need that is not being provided now by the current mechanisms you control. A good sysadmin would have already met that need -- before such requests come in.
I've had too many sysadmins throw hurdles like that (even my own subordinates). Guess what, eventually it turns out DBAs become the easiest link in the chain to replace. As someone here mentioned, the data is not yours, so it's not yours to horde. So find a way to make it work.
On the plus side, this may be your opportunity to show how to make it all work -- by managing the risks. Don't listen to too many sysadmins, especially entrenched stick-in-the-mud types. Rise above it all.
Good luck.
I am shocked I tell you!
I ask such a simple request (and very politely too I might add) and next thing I know you're turning to Slashdot for advice!
Too late buddy! You've aired my laundery in public and I'll never do business with you again!
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.
Simple. Just export each customers data as an MS Access Database. This way they have a high-end DB engine (MS Access) at their disposal that can handle all of their needs.
Why limit your customers to the limitations of Oracle? They can achieve a lot more with MS Access.
MS Access was built for a REASON. It is _far_ more flexible than Oracle. Any day.
If the customer(s) need more power, they can simply import the Access DB into SQL Server and have an even higher-end DB.
In all my years doing VB6 and now state-of-the-art VB.Net, I have never seen a benefit of Oracle. I have built many _Enterprise_ applications with VB6 and Access that blow Oracle out of the water. The apps I built with Access could easily handle 10 or more concurrent users. Try that with Oracle.
Build a data export tool for their data only. Then they request the data dump, download it, and install it on their servers to massage. Simple. This solves the data privacy issues, lowers the security hole, reduces potential runaway query problems, and makes the end user responsible for figuring out their own queries. Done.
Having just experienced a STREAMS/CDC nightmare, I'd suggest using some other mechanism for synchronizing your databases. STREAMS issues almost took down a production database...and none of the issues were seen in the test system, which was pretty much identical to the production system.
There are large numbers of patches required (10.2), and there's a large amount of training your DBAs will need to be able to babysit STREAMS replication.
Just do an exp/imp every day. Realtime isn't worth that much aggravation.
Note: I wasn't the dba, I was a stakeholder. Ugh.
If the customers are allowed to ad-hoc query the entire database, they may just as well have a copy to keep for themselves... So why not export the data (or replicate it out) and give it to them? You can be nice and offer to host their database server in your datacenter, but let them be responsible for running it. If they balk at the work of doing that, charge them to perform that service.
Thank you, Mike1024.
Any sufficiently simple magic can be passed off as mere advanced technology.
Seriosuly, download Kettle http://kettle.pentaho.org/ and create a transformation of the required data only into a new DB on a seperate server (while you are at it, make it a star or snowflake schema). Give them all the access they want telling them that the data will be updated regularly but is not live. If you need live data, I think Oracle has a product that creates a direct image of the DB live (i.e. one row changes in DB1, changes in DB2 etc). I think this is expensive though (isn't Oracle always?). Karem
When all is said and done, nothing changes...
I woudn't. I would suggest that you pull your head out of your ass and start trying to figure out how to give them the access they need. Consider, for instance, views that encapsulate common joins in order to ease the construction of more complex queries. Consider, even, a schema consisting entirely of views designed to make it easy for them to get what they want out of their data. Consider, as others have pointed out, resource limits or replication.
I assume that you do not talk directly to the customer, right? Well, whomever manages that relationship is missing out on possibly making more money for your company. Here is what you say, "Malformed queries on a production database can severly disrupt performance and reliability. To best meet your request we will offer to provide a mirror of the production db in which you will be granted read only priviledges.". You can help your sales staff come up with a quote for this. :)
Thank me later.
Instead of looking for reasons to say "no", you should put together a reasonable, first-blush estimate of what it would take you to say "yes" (in terms of putting together the necessary security, administrative controls, etc. to reasonably allow this without mixing customer data, allowing customers to starve the system, etc.), figure out what you'd have to charge the customers to make it worthwhile for you to do that, and tell them that they can have what they want -- if they pay the price (for which you will be able to provide the justification for the cost -- not necessarily a deatiled breakdown, but an overview.)
Then, they'll either say "no", and you don't have the hassle, or they'll say "yes" on terms that are worthwhile to you.
Explain to your customers that exposing your database, even in a read-only and limited scope, opens the potential for THEIR data to be access/hacked by a third party.
Emphasize that you are not willing to be held responsible should that be compromised.
Play the security card and stick with it. Don't get into fine details, keep it high level.
to access your DB in that way.
Right. Tell them that you have an obligation to your suppliers and other customers and marketing folks to maintain data security. You can protect this via onion layers on your own web portal and data base set up. But once the DB can potentially be migrated outside, you cannot assure your stakeholdrs you have responsibility for it.
Moreover, if they based financial decisions on their own queries you cannot guarentee them results.
Some drink at the fountain of knowledge. Others just gargle.
They're your customers. I assume they will get what they want in the long term. So better make it happen with you than the competition, unless the risk for you is too high. Whatever you need to make it safe for everyone, your customers will need to pay for it.
Once they've asked the question and you haven't told them directly that you would prefer them not to, it's too late to find 'excuses'.
Many have already hit on the most plausible way to handle this situation, ie. give them their own sandbox to play in if you are afraid they'll upset yours.
When it comes right down to it, it is the customer's data and as such the customer should have access to it in any way they wish. However, that access should not impact your ability to maintain the system/service for ALL users which includes management all the way down to the data processing clerks. Giving them a copy to muck around with seems to be the best way to address both sides of the issue.
Never bathe in hot oil and Bisquick.
I have this this happen b4. Here are the reason you keep the customer out of the data. Confidentiality & Security: Unless you are an expert in DB security who's to say that one customer won't "accidentally" access another customers data. Poorly formed queries: I worked for a company that had a VERY large online database application, our own programmers crashed the live system by pushing code containing poorly formed queries that included, of all things, never ending loops. Bog Down: Your system will show signs of stress when all your customers start banging on it from every direction trying to pull thier EOM's, EOW's, and the ever tiedious EOY using the before mentioned poorly formed queries. My friend the solution here is to remain steadfast in your current course. If the customer really wants to write their own queries, provide them the data structure of the data that can be queried, let them send the query to you to proof and then run it. You can then charge them for Query Analysis, and whatever else you can think of. Unless they know what they are doing, they'll give up after the first try.
--AD
What you have here is an opportunity for you to win.... (Cool Hand Luke +/-) It seems to me that your CLIENT has a new REQUIREMENT and YOU and your BOSS have an opportunity to PROVIDE a SOLUTION for a very reasonable price! (unless the current contract says that they can do this already) So why not setup what your CLIENT WANTS in a way that they do not "mess up" with "your database"? By the way unless you personally paid for the collection of the data, IT AIN'T YOURS. If you want to test my point go to your boss and tell him/her that you are taking your data home, and see what look you get as your butt is dragged out the door.
Limit what they can directly access by a limited view that only shows them stuff relevant to them
This is one of the silliest things I've seen in a long time. Figure out a way to give your customer what they want, or they won't be your customer much longer. They understand that the data is there and that it can be safely accessed (you should be able to figure that how to make that happen-- a few people here have put forth good ideas). If you are the obstacle to what they need, they'll soon figure out a way to go around you. Feed the customer and the customer feeds you. Honestly, this is the kind of stuff that rightfully gives IT folks a bad name.
1. Write views for them and give them access to those.
2. Dump it all to SQLite or something and charge them to send it to them.
3. Keep a replica online and sync their data to it.
4. Tell them to bring a replica online and set up a VPN and charge them to run replication to it (nice high maintenance charge option).
5. Tell them to go away impolitely.
The best way to say "No" to a client is to explore how their idea could be built in a reasonable way, then come back to them with a cost estimate.
In this case there are 2 issues:
* Security: There is the risk that malware on their machine(s) gets write access to your database, or a rogue employee does, or if there are tables they shouldn't see, that they may gain access to them.
* Performance: Direct SQL access could lead to slowing the DB for everyone - one bad query from them and all your clients are impacted, a risk you can't take.
Outline a solution to this problem (maybe a web service that accepts their sql and limits their usage/reach/etc) and come back to them with the cost to build it.
If they say "OK," you've successfully sold additional services. More revenue in the door and you might be able to offer the results as additional services to other clients.
If they say "That's too much," you just said No the proper way.
Replication the database not necessarily in real time to a separate server. Explain to them that you have no responsibility to debug their queries or possible performance problem arising therefrom. Ideally replicate it to a server they own.
Netapp Filers and Flexclones is your answer.
A flexclone is a writeable disk block level copy of a volume/filesystem on a Netapp filer. It can be created in a matter of seconds and destroyed in a matter of seconds.. Google it for more info. Best of all, it wont double the use of storage unless you break it and it becomes a volumes on its own.
That ll give you what you want/need.
Furthermore, denying them access cause you like your DB sounds plain daft to me. I am not sure if you have the right attitude towards customers/being in business here.
Cheers,
Eric
Have you thought that you may end up in law suits if customers access data that for what ever reason is not the data that you want them to see.
Anyway, easiest way to stop all the chatter is have your Attorney's put in their two million cents on the matter.
It is not your database; it belongs to the organization and your clients. You are just an employee who provides a service to your clients, and your clients provide you with a pay check. Your neanderthal attitude is territorial instead of an attitude about providing solutions for your clients. I have been in IT for 25 years and find it surprising that your type still exist. One of the many purposes of a database is to provide decision makers with information. If you are concerned about long running queries impacting transactional processes, then consider implementing a reporting instance of that same database. Consider building a datamart for your customers. If you have valid security concerns, work with your Information Security Manager to solve those concerns. Work to look for solutions with your clients for your clients instead of being a roadblock. Be a problem solver and provide solutions to your clients. I cannot tell you how to best provide your clients with the solution they seek. But, I know that you can design a solution that will provide them with the results they need.
The problems isn't that they want access. The problem is they want supported access; and it doesn't matter if they swear black and blue that they don't need support, you know they want it.
1. They will ask for support. They'll immedately ask questions like 'what does field xyz' do? where do I find this? Where do I find that? It doesn't matter how well they think they know the schema. And it doesn't matter if you said you wouldn't support it.
2. Restricts what schema modifications you can make - if you change the datatype of a field, you know exactly what is affected. With this, you'll get an angry call complaining about you trying to clean up a schema.
3. They'll blame you for their incomplete understanding. They might not realise that for historical reasons, customer record 1 doesn't mean what it should. Or that they need to join against table A and table B, so they'll miss half the orders and get the wrong numbers and blame you.
4. You wish to upgrade the server and move the application to another box. Cue complaint from 'customer' who has hard coded the name of the server into their application.
5. Perfomance drag. Poorly written queries, et.
6. They'll then proceed to use Microsoft Access via a VPN connection from another country and complain at you that performance sucks. They'll have unreasonable expectations on what they can do - and they will blame you for your failure to support it. (The fact that you said you weren't going to support it is, of course, completely irrelevant.)
7. By the look of it, they're actually asking to talk to the database server directly too - something you didn't previously allow. This means a VPN connection to it or putting the database 'naked' on the net. It means you're no longer protected by a firewall; meaning you don't have any time to patch if there is an 0-day attack. There are security implications of all that.
The real reason why sysadmins say no: It's not the access that costs; it's the support after it. All the other hundred knock on things after it that you can't quite identify now but WILL bite you in twelve months time.
The cost of granting them access is a lot higher that in seems, it's just you pay that cost in a hundred ways, not a simple single hit.
I don't think you have any real "right" to keep them from what is, essentially, their own data.
On the other hand, poorly formed reads can kill database performance.
I would suggest that you keep a hot snapshot slash read only hot backup database and give them ad-hoc access to that.
I forget the oracle-isms for it. Do that thing where you forward the journals from the live database to the standby database. Since that database is up, they can use it in read-only mode without you having to worry about table locks (etc) boning your tuning.
If worse comes to waors, you should be able to show them some concrete (if contrived) examples of incomplete transactions costing time and money.
But really, being territorial about the data itself is unbecoming and unprofessional.
That being said, I worked for a company that wanted to keep our customers out of our databses full of their data. The main reason was that our database was _awful_. It was dnormal and could have been used as textbook material for how not to "do a database". Columns named Field7 with the single character minus "-" for null values because the code designer didn't understand what null was or why you would want to "SELECT AVG(duration) WHERE..." wen you could put a read loop in the application.
Seriously...
What are your exact objections to allowing this access? Those objections are your reasons for disallowing the access. If your reasons are good, your argument is good. If not, not...
Innocent people shouldn't be forced to pay for inferior software development.
--"Code Complete" Microsoft Press
"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
Its not YOUR database. Grow up and be professional. If you can't handle the challenge of implementing a sound security model and disaster-recovery then get a real job. Joe did, and he's a happy guy now.
It's surprising to see just how naive slashdotters have become. Never underestimate the idiocy of a client. They want a way to run adhoc queries, they'll do their damndest (although well-intentioned) best to destroy all the data, get bad results, choke themselves with their mouse cord, and burn down their own building on the way out.
Most customers/clients defy logic by having enough brain power to breathe, while being stupider than a sack of rock salt. (Which is the perfect combination to win a lawsuit when they've destroyed their own business and have you to blame)
maybe 10% of the replies I've read show any business experience. Is there some college course requiring their graduates to post in slashdot before they've learned they know nothing about practical applications in a customer/client-targetted environment?
I'm taking any bets that within 15 minutes of giving them adhoc access, they'll call up asking why there are so many duplicate records because they ran a join query on x.[first name]=y.[first name]
Those users know more about their data than you do. Your specialty is running databases, not limiting access to the users' own data.
But users will be running queries that have not been tested and could therefore put a heavy load on the database. Therefore you should maintain a replica, either through nightly dump and load, or some other technique supported by your database software. Then let them play with the replica.
Note that you already do this for developers, and since the users are running untested queries, they are de-facto developers.
If you truly have super users that are "not stupid" and can craft a SQL statement then create a view of the tables specific to their login and the data they need to see and set the permissions to SELECT only for the view (Disallow UPDATE, INSERT, etc.). The problem then will be customers who think that since you opened up the database to SQL queries you should be their support and mentor when their dumb-@$$ queries don't return the values they expect, or have "errors".
I've spent most of my multi-decade career providing end-users with access to their data. Several of the previous suggestions are part of the solution. You do need to engineer a solution that allows valid secured access from individuals trained for that that purpose without affecting overall system performance. Some of the good ideas:
- database replica, hopefully tuned for queries. If the data is not onerous, some user-oriented views can go a long way in preventing Cartesian solutions.
- an end-user oriented query tool that allows previously-defined queries to be rerun as needed. For eleven years, I've used PeopleSoft's Query (which is proprietary to their systems) as the end-user tool, to give you an idea. This type of tool can also be configured to minimize the number of joins allowed by user id. Many of these tools allow power users and DBA's to define queries for others who can only execute the canned query.
- require minimum training requirements for the end users like basic Select statements and a copy of the data structure.
- phase in the end-users. There may only be a few individuals who need the direct access.
Bad idea: - Try to prevent users from getting to their data. From your comments, you are an external service and the customer owns the data. That is how you lose a customer!
Worst idea: - They didn't need it before, why do it now? ANY analyst that thinks today's specs aren't already obsolete doesn't belong in business! Go work for the government!
If I am not for myself, then who will be for me? If I am only for myself, what am I? If not now, when?
Have you ever joined a very large table to itself - several times? There can be legitimate reasons to do this (such as finding duplicate entries) but it can also bring a server to its knees for several days before producing results - if it doesn't run out of space and crash first. Letting people submit their own SQL queries is just asking for trouble if they don't understand the consequences of what they are asking the server to do. Your IT needs to rule on all queries before allowing them to be submitted, at the very least.
I can not believe this post made a slashdot news item. It is absolutely trivial to create a schema with views to the customer tables - and even then not expose all table columns. This question is as old as databases itself. You can implement tde(transparent data encryption) - you can build a read-only database using streams or dataguard for them to use. Seriously - this wasn't a question that really deserves the attention it got.
I've gotten lots of requests for difficult things from customers that turned out to be mere whims of some random person over there. So what I do is suggest we have a meeting with myself, the customer, and our managers to discuss the options.
Half the time, I get a "forget it, it's not important" and go back to surfing Slashdot.
expandfairuse.org
I would suggest you start thinking like a business. By providing the ability to run the "ad-hoc queries" you're essentially providing another service to them. You should be compensated for the value of the service, your time and expertise, as well as the risk that they may screw something up in the database. Figure out what that's worth to you and then counter with an offer to provide the services they want for that price. That's business, pure and simple. In turn, their response will help you gauge how much they really want/need this functionality.
I am a very senior level DBA. I am experienced in both Oracle and SQL Server. As a DBA you must develope a firm voice of authority based upon your knowledge, skills and experience. As a DBA, it is both anticipated and expected for an experienced DBA to provide access to the "precious" database" for the paying customer. You simply need to research the best solution before providing access for that client (to access their data only). Of course, depending upon your level of DBA experience, you could easily create limited (least privilege) access with user permissions, application permissions, group or role permissions, views, triggers, & or stored procedures, ect. How many years of production Oracle (DBA) database administrator experience do you have? It sound like the customer thinks they can do a better job pulling back ad hoc reports. Why not ask exactly what type of data the customer may need and get that data set up into a view and also provide the customer with select permissions to access that view of their data? I ask because you stated: "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."
I would try to work out what they actually need, they probably do not know what it is that they want.
I would then offer to build any queries that they require using your expertise in the database as a service which can be charged for, and create views based on the queries which they can run whenever required.
The good thing with this approach is that you still retain the control over what runs on your server and allows you to maintain your DB.
Your problem is that you are doing currently is perfectly sensible in a technical environment, but you are dealing with people who live in a different world. You need to provide them with an asnwer that makes sense to the way they work. Explain to management that there are implications for your infrastructure - that they can potentially create a load that your existing infrastructure may not be able to manage along with existing business focussed work. Then suggest a cost recovery model - implement quotas on machine resources, and associate charges with those resources. Have your legal people draw up a contract, specify a reasonable figure for implementing this facility, a per month figure for monitoring and billing, and ensure that whatever the contract states they pay for what they consume. I would also ensure that the contract has a minimum period so that they can't just try it for a month and then walk away. Make the numbers real - don't try to make them artifically high - and see what happens. I would be quite confident that once your clients see that you are willing to work with them, but that they will have some additional costs, then their management will have issues they can comprehend. Oh, and you can then pray for that nice big cartesian product that will pay for your next new server ;-)
I've seen too many lazy bums DBA like you. You're a DBA right? where the A in DBA stands for Administrator?
If the customer wants to have direct query, then your job as a DBA is to make it possible without hassle. As many suggested, Oracle has fine access and resources limit, so do so. Or otherwise setup a copy like others also say.
Or are you just afraid of the additional work?
People like you, or like sirgoran (charging customer a ton for a simple work), gives IT a bad-name.
If you cannot or do not want to do your job properly, how about switch your career, then? Sheeesh !
How would you advise me to keep my customer [away from my precious tables]?" there, fixed that for you. Now go show this to president of your company, continue to administer your database (you seem too be doing well) and wait for his phone call.
There are so many posts, so I am not sure if anyone has posed the question of, "Is this your decision to make?" I trust your DB expertise. Depending on the size of your company, if there are "higher ups" beyond you and they are accessible then you may consider presenting the situation to one of them. Here's why: Sometimes customers ask for things they should not be asking for out of stupidity. On some occasions, they ask out of evil genius. The customer may know they shouldn't ask for this and therefore try to run it through channels that are inappropriate in an effort to pressure an answer they shouldn't get. Then they can argue the "high ground" that their request was agreed to. As a developer I have had this done by various clients by accident and by deception. We all like to be the smart person with the answer one way or the other, but I have learned to "run it up the flagpole" when I felt it appropriate. I have experienced 3 different categories of results: 1. I am concerned about reason A. The higher up simply agrees or disagrees. Either way it is a CYA 2. I am concerned about reason A. The higher up knows about reasons B and C that I was not privy to. The higher up actually provides me more ammo for it not to occur and may actually step in to handle the customer. (I know it is rare, but it happens) 3. I am concerned about reason A. The higher up knows I am right but due to reasons E and F that I may not be privy to. The higher up decides to appease the customer regardless of reason A. That is my humble offer of a way to handle this. Good luck, dredmon
This is so stupid it has to be a hook to get free dba advice!!!
Uggh.
With data houses, cubes, replication, log shipping, backups/restores, snap shotting and a score of other things i've probably missed, how do you even ask this question and call yourself a DBA!!!
Disclaimer i'm not a dba.
If you must give a reason, why not simply say that ad-hoc queries tend to be a performance problem as they are not able to be optimized with indexes and such. We used exactly that reason to prevent ad-hoc access to a large customer database at a telco I once worked for. They were required to specify what they wanted and we used a stored procedure to do the actual work - once it was explained that our process was to first ensure the query would run fast (DBA analysis to create the correct indexes) AND not bring down database performance for other uses, they accepted it.
Depending on the industry that you are supporting, you could reference SarbOx (Sarbanes-Oxley Act); HIPPA (Health Insurance Portability and Accountability Act), or FINRA (previously known as NASD) security regulations as a reason to surpress direct access.
Alternatively, permit access only via a 300 Baud modem connection.
..."Have you experienced a similar situation?"...
Yes, It frustrates me frequently that useful, non-contentious (ie. not hippa, ssn, pci, DoD etc.) data seems to make a one-way trip into the corporate oracle system to be held hostage there by the dbas for not much good reason than they want to ensure perpetual employment.
There are plenty of good ways in which this data could be made available. Hell; save it periodically to a flat text file, but don't lock people out of it. You're the guardian of the data, not its gaoler and I'm willing to bet that the people that want access to the data have their reasons.
Nullius in verba
Some other factors to consider:
:-)
Network bandwith might be impacted due to huge data unloads.
The PC client might have a restriction on the number of return rows it can handle making most queries useless. This is specially bad if users use Excel to mine data.
Ignorance of the database tables cause users to spend much more time reverse-engineering table relationships and their queries will fail if database structure changes over time.
On the other hand, on some ocasions I HAVE allowed that to happen and it turns out users are inteligent enough to realize what not to do most of the time. Sure, you'll have a couple of spikes on the charts now and then, but in the end it will work fine for everybody.
I can understand your worry, blame it on IT paradigms
they want their data warehouse
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 -
I'm sorry, but who are you to make that decision for your management and your customers? Did you pay for the creation of the database? Do you pay for the machines?
Your job is to tell people how much it would cost (time, extra load) to support this, nothing more. And you should be honest about it, not make up some silly numbers. The decision on whether to grant the access is between your management and the customer.
You could make views for everything they need to hit then either make a web interface or a standalone pre-complied gui that only hits the view then if need be you can even index the views, that way they cant see whats going on in the background.
Just say NO.
Next question please...
Actually, that is a valid reason if framed in the correct language. You can easily claim that unrestricted ad hoc queries might cause performance issues because it's true. (There are ways to get around this, if the customer is prepared to pay e.g. a replica database). However, that's not your reason for restricting access. Your real reason is you don't want the customer "pawing through the database", so it seems to me that you aren't worried that the customer is too stupid but that they are bright enough to recognise a poor DB design when they see one.
Anyway, if you are based in the UK and your database contains contains personal data, the point is probably moot since what your customer is asking for may be illegal under the DPA (seek proper legal advice at the customer's expense).
All I want is a secure system where it's easy to do anything I want. Is that too much to ask ~~ Randall Munroe
Unless you can make a strong argument that them running a bunch of queries will slow the system to a crawl, there is really no reason not to let them have access. Are the systems even shared with other customers? If not, then you really have no good reason.
I would not recommend a flat out "no" answer as it can definitely piss off the other party. Also, being seen as a road block when someone is paying you for a service is rarely beneficial.
Having said that, the only times I've found a flat out "No" to be effective for specific types of situations: if the person doesn't really care, if the person doesn't understand the details of their request and are wiling to rely on your answer as a professional, or if the person is the type that functions really well being told what their limitations are without questioning them (aka sheep).
If you believe that a couple "adhoc" reports are going to kill your database. It is DBA's like you that shit me to tears. What, are you scared that when they see how badly you've set up the database tables and joins that they are going to laugh at you? Honestly get over yourself and be a professional. If you are really scared that these amateurs are going to put too much demand on your system (although I believe that you are the amateur and don't know how to manage a database) then make a copy of it. Set up an overnight script and create a data warehouse or use the flashback ability that is available in ORACLE 9+.
. But this isn't a sales deal this is a tech issue and that is how the question was phrased.
How is it that we are on a board praising open systems such as Linux and yet we have so many argue the customers should not see their own data? What's the difference between what you are doing and what Microsoft does - "oh, leave the operating system to the professionals". It seems to me that if we expect our software to be open, it should only be in support of allowing our customer's data to be open too.
This is my sig.
Your attitude represents everything that is wrong with typical 'IT' thinking: "Pesky customers/business-stakeholders/clients - life would be much easier if they'd just leave me alone in my office to surf pr0n all day". Give them what they want, or they'll go elsewhere. If you can't figure out how to manage resource usage on whatever your database is, especially when the requested access is R/O, then you should look for another vocation. If you ever wondered why IT orgs get outsourced, look no further than this example.
You're a DBA. You're supposed to figure out how to give your customers access to the data they want, not to keep them away from it. You have forgotten what your calling is. You should be ashamed of yourself. Stop asking yourself how to restrict access and lock your customer out, and start asking how to give them what they want.
I'm in a similar position in my company, we have multiple people from marketing and sales that have requested direct database access to do their jobs. As much as I don't like it management decrees that they must have that access.
We have created a couple of interfaces for them to use to do queries directly on the database, one web based, one XML command line based. After one of our sales people ran a query that he wrote and created a cartesian product on two 3 million row tables (It brought the MySQL server to a crawl, but not until it had run for 6 days), I started verbose logging in each of their interfaces. Every query that is run through an interface that they have SQL access to logs:
query start time
end time
rows retrieved
the query
username
hostname
This gives us a workable balance, the users get what they need and we have a direction in which to point the fingers if someone manages to take our production db again.
We also replicate to a secondary server which we direct most of our long running queries to.
As long as you give access via an application and some database exports you have a pretty narrow interface to your customer.
You are free to change the database scheme behind it whenever you need it - because of your narrow interface you have all applications accessing the scheme under your own control.
If you give complete access ( even read access ) to the customer you expand your interface. Maybe the customer will have one, maybe he will have 200 different applications accessing the data. Then you will be stuck with your database scheme -- it will become difficult or even impossible to do necessary extensions or improvements because doing so would break the customers applications.
1: Avoid saying "No".
2: Say "Yes, but:"
3: Write a report; throw in some costs & disaster scenarios; gold plate it to make your life easier all the way.
4: Offset the time spent writing the report as you mis-spend the hours you would have spent chugging along happily churning out reports manually.
5: Profit (bigger domain, less work)!
Depending upon how crucial it is to your clients (though they are fellow workers, they're still your clients: no them - no job there for you), to access 'current' data; our finance people could run any kind of query they wanted (though if they didn't provide ANY qualifiers, their query wouldn't run); against a copy that was updated weekly (run over-night from HQ to all regions financial data servers every Thursday night). Was faster than going across the WAN and was reasonably current and their read-only access was not affecting 'precioussssssss'.
Remember, you're maintaining it so it won't get pooched; it's their data they need, in order to be (more) effective, right? Or is the data simply gathered because that's the way the system was designed or some screwed-up business resumption plan? If it's useful - let them access it. And for the stupid ones - they give-up when they find-out there's no magic 8-ball due to their assinine query parameters (or they become management).
The rest, may mine some gold or quality 'gems' to help the company and that's a good bottom line. If they get results - why worry over a copy? If they don't; nothing lost but time and the wise ones will move on, or fine-tune. It's all good...
One reason to heavily restrict sqlplus style access to an Oracle database is that unless you're completely on top of your patching there's a significant risk that anyone with that access can get their privileges escalated to DBA level using one of the many SQL Injection vulnerabilities in Oracle packages. If you want the really paranoid option you can point out that there are probably zero-days in there as well, even if you are fully patched (AFAIK NGS SQuirreL (a database security scanner) will mention those in any reporting you do).
If you're looking for examples to show your management, then a quick trip to millw0rm should do the trick http://www.milw0rm.com/exploits/4994 , http://www.milw0rm.com/exploits/4570 etc etc
Really ... you sound like a typical sysadmin. "No" is the first word that comes out of your mouth. Really the dept you work in should be called the No Department. IT departments should be slaves to the business ... they are the ones that keep you employed.
... like a second copy of the DB that they query and it does not matter if they issue long running expensive queries.
How about something more creative
Replicate but use MS Access, its easier and you'll be done with giving them access to their data in 1 hour. That's all they probably need anyway...
Without knowing the specific relationship and obligations you have with the company, I might proffer a suggestion: Offer access to your data as a service for a price. If you are worried about neophytes causing mass chaos on your well-oiled machine, tell them that you can give certain people access, if they pass your certification program. Certification and training (by you) cost them, which may make them back down. Otherwise, it puts more money in your pockets and minimizes the risk of problems. It also may make your job easier in the long run by having them do the heavy lifting instead of you. More money for less work - sounds like a win.
Comment removed based on user account deletion
Think of the situation as being akin to another programmer requiring that you write all of your classes with only public members and public inheritance. Giving customers unfettered access to your raw data could at some stage in the future seriously inhibit your ability to make changes to the ways in which you hold and manipulate that data.
Hey there. The way I do this at work is by giving the client an Access database, where using ODBC, I link it to the Oracle/MSSQL database tables. This way, they can use Microsoft Access to create their own ad-hoc queries / reports etc... MS Access is a very powerful and affordable ad-hoc query tool to interface most databases supported by ODBC. If you are concerned with security, you can implement MDW security and password protection against the MS Access database. Your client will be extremely happy with such a solution... He can't complain about this, as you are giving him full access to the tables... You may have to add some security restrictions to to only allow viewing permissions. Regards from Down Under.
... just not being a petty, territorial dickhead?
If they are asking for read-only access the database then it's likely that that your website/database doesn't fully line up with their business.
Start by making sure you understand their business requirements. Is their business changing ?
If they still press for read-only access to the database then they need to understand that they will have to incur the costs for maintaining these ad-hoc SQL queries and updating them for schema changes. N.B. In my experience these on off queries are anything but one off.
If they understand the costs and still want to proceed then you could try exporting the data to a secondary database but they will probably want the data to be live. Also, I suspect neither you nor your customer wants to look after another database box.
Instead, I'd recommend defining the logical data model and representing this as a set of read-only views. At least this will allow you to make changes to the physical schema without breaking their "one off" queries.
You have this completely the wrong way around. I always welcome interested user-access to my databases, except of course I make sure that they have access to read-only views set up so they can extract the maxiumum amount of business information out with the minimal amount of sql and if performance is an issue I give them access to a replicated copy (it's very rare indeed that a business user won't be happy with a nightly update).
I used to work in blue-chips inhouse as dba, and now I'm an consultant to a mix of large and small businesses. In both positions one thing I always try to do is identify an office worker who is interested in IT and encourage them to act as the local 'expert' for extracting data from their databases. There's always someone in an office who everyone else asks first to fix their computers, if you identify them and take them under your wing not only do they get a lot of status from being the local expert (and possibly a good argument for a pay increment!) but they keep you from having to do mundane sql enquiries and you get a natural ally with your users.
Don't make the mistake of dismissing office clerks as somehow stupid and not capable of handling the mysteries of IT like you do. Generally these people will be massively familiar with spreadsheets so explaining tables as like spreadsheets is an easy first step. I've encouraged and trained clerks to the level where they can throw around multi-level joins and subqueries quite happily (and taking acount of the indexes!) before now and these people often have an advantage over you in that they probably know the data at a more intimate level then you do. Furthermore having a good fan base of such people tends to work well for you as they then appreciate what a dba actually does and they're sure to make nice noises to their bosses.
The simple way to make sure that someone really, really wants to do the job is simple.
You cost up what's likely to be a realistic cost to yourself for allowing someone access to the database.
Some of the things to consider are:
1) Who will be accessing the Database, and what level of qualification do they have with Oracle. The answer "We use Microsoft Access to generate queries" is a bad sign. This effectively means 'little to no Oracle experience". Where I work, I point blank say I don't support Access, as half the Access issues used to end up on my desk as reported Server issues, which investigation (taking time) pointed right back at Access.
If they don't know SQL enough to be comfortable explaining all the joins and how correlated subqueries work, they don't get to write their own queries (the amount of times I've had to turn round and say to someone
"You know, that isn't doing what you think it is." is quite incredible).
2) What is the expected traffic level. If they don't know what kind of queries they're looking at, how many per day they intend to use, and what bandwidth they use, then you let them know that complete ad-hoc usage is very expensive (if you have many customers, you can't afford them ALL to be running round using heavy load queries all day with an arbitrary number of users at the site. That costs in Hardware and support, and someone needs to foot the bill, and you need to make a profit).
Work in the cost of extra hardware necessary to fulfill this (with extra support costs etc.).
3) Renegotiation of the Contract for extra staffing levels at your end (and time taken for you). If you agreed that they could have an interface, and you system was designed around that, then by all means, they can have direct access, but it's a change to the terms of the contract, and may mean you'll need extra staff to keep tabs on this (so much more can go wrong with ad-hoc queries that doesn't with well designed interfacing, which needs closer eyes kept on the server, and more debugging time, again, someone has to pay for this, and it's something they want, not you).
4) Standardised process (Dev -> QA -> Live). If they want to run queries on a live server, then they need to have tested the query (either on a 'test' database local to themselves with anonymised data, or on your test database, again with anonymised data), then have it passed by you for sanity checking, then have it able to be run on Live. Again, QA negotiable if they have someone who actually knows how to use Oracle properly, but the Dev phase should be non-negotiable.
5) Enhanced debugging costs. If you find a query they have run that causes issues with your server that need fixing (runaways that don't get reaped, etc.) causing issues to other clients on the server (or even just to them), you have a very high cost for fixing this.
They need to understand they're entering a different ball game by having direct access. They also need to know the ramifications, and that if they screw up, it's not you that'll be carrying the can financially. They need to take responsibility for what they do. If they're good, and have someone qualified to do the job, then for them, it should be quite cheap to have direct access.
If they're just thinking it'd be good for a PHB to play with access, or tinker with the data import to Excel without really knowing what's going on, it should be extremely expensive.
With the debugging costs, this could be rather expensive anyway. As long as they're aware of that, and are prepared to pay, then sure. Go ahead. But make sure they pay a very realistic figure.
Mostly, the figures you arrive at dissuade all the but most deep pocketed 'tinkerers' from playing around, while allowing the people who really can be a boon to you if you manage the relationship well to get extra value from the product.
Being sensible about it gives you a good reputation (when I've denied access based on realistic evaluation in the past, and explained this, even though client
Write some views, be sure you write them well, as to reference everything the customer would EVER need. Then, give them the public role to the database, but do not grant select on the tables... only the views. Now, the customer doesn't see your tables, and only runs (essentially queries you have written as views) what you would approve of through the views. Additionally, you could have the customer purchase something like Crystal Reports to build these reports.
The problem is here that the internet made everyone experts on everything and some weekend gamer wants to expand his resume, and his boss was dumb enough to sell it up and now here you are. Don't be silly. If I was your client, and you gave me this BS "only on sales the customer is right, on ops the customer is just a nuisance", you would be out of a job. As many people said below, there are a lot of competitors who would think I'm right and you are never irreplaceable. The client supplies what you need ($$$), not the other way around. Did you know that each satisfied customer generates at most three more sales and each unsatisfied customer BLOCKS at least eleven sales?? [citation needed]
Get this: every part of a deal is a sales deal. I (and many consumers) only shop where I know I will be well treated before, during, and, most importantly, after a deal.
One example: I make my car insurance with the same broker for 12 years... because she always gets me a good deal and the insurance company she deals with never failed to deliver in the case of a broken car window (covered by my insurance) or anything else. Once the insurance company made something covered difficult, and one call to the broker, things were solved in the same day: because she knows _I_ make her bottom line, next year I will purchase the insurance for two cars again and she will get the comission... so she can eat.
Other example: I am a jeans-and-t-shirt kind of guy, but sometimes I have to wear a suit. I bought three different suits in the last 12 years, and in the same period, my weigth varied from 80kg to 100kg (175lb to 220lb), up and down, and _every_ single adjustment I had to do in my current suit was done for free, quickly (saving me to have to buy ten instead of three suits).
So, don't give me "I only do what the client wants if it's convenient", because your client always has the power to put you out of business.
It's better to be the foot on the boot than the face on the pavement. ~~ tkx Kadin2048
WITH (NOLOCK) will get around that, with a suitable sacrifice of consistency for long queries.
I think you need to engage them as to why they want live access, the reason is probably for 1 of 2 reasons: Your support is not fast enough or they want to integrate your data with theirs and do some correlation or unified display. The simple solution is to set up webservices so they can get at the data and you can control the SQL.
AMEN! And I say that AS one of those "amateurs" ... I had to work on live, production data on a daily basis. Sometimes it was a simple report, sometimes it was a VERY complex report and somtimes (more often than I'd like) I had to issue updates to the database.
We had a set of rules we were required to follow. It's been about half a year but here's many of them:
I could go on, but you get the idea. My point is that you could say "Alright, but if we do that, EVERYONE who MIGHT query the database needs to send me an email stating that they agree to the following rules and I will reply with their login credentials. From there, create a script that can search through the log file for habitual rule breakers." It might be enough to get them to say, "You know what? forget it."
We also had 3 tools. One was just a standard SQL query tool (WinSQL ... this is not an endorsement) and the other two provided access to otherwise inaccessible tables. The idea was that the tools enforced certain rules and restrictions on what information could be retrieved.
First question to them: are they willing to provide signed legal documents making them financially liable should any damage or data theft occur due to their access to the d/b?
If so, then the next thing you need to do is have your software developers build a website interface for them, that does validation of the queries, and also scan for injection attacks. DO NOT ALLOW THEM UNFILTERED ACCESS.
If your management doesn't like that, request their written signoff on a document/email that includes the above warnings.
mark
There is always a way to implement solution that is safe. Some posters above have mentioned a few with user permissions, virtual tables/views, and complete database replication on a standalone system.
When I was first getting into administration, a good friend of mine who is very successful in the field said something that has stuck with me throughout my career. Not just in administration/IT, but very generally applicable. I'll pass it on here:
What does the customer want? Access to his data. You should not be thinking "Should I or shouldn't I allow this?", rather the question should be "What is an acceptable means of providing this service that will meet my and my companies requirements of security, accessibility, and policy? How much will this solution cost?". If you approach all of your problems this way, it never comes across like you are denying your customer or opposing your company. The decision is always in the hands of the customer/manager/boss. They walk away feeling like they've been given a choice and made the decision that suites them best.
Not to mention that sometimes you find your initial instinct was wrong. Maybe the customer is fully willing to absorb what you thought would be an outlandish cost. Maybe it doesn't end up costing as much as you thought it would.
It's always better to be known as a solution finder then a decision maker unless you are the person paying the cost associated with those decisions.
Most CRM systems (big databases) *NO NOT* allow free query of the production database by the users because random, unoptimized queries will grind the database server to a halt. In fact, even on production servers, usually the template queries are so badly written that the thing runs terribly even without randomization from free queries.
Read only access to the database to someone writing poorly optimised queries is extremely dangerous. As an example, I've optimized poorly written queries before which previously ran in 45 minutes down to running in 45 seconds, on the same system. If someone runs a stupid query against the live database it can kill the performance for hours, and also may cause denial of service, timing bugs, and race conditions to be exposed in the applications dependent on the database due to the poor performance.
What most folks do in this situation is to allow free query of a second copy of the database which is not updated in realtime. This allows random queries for research to run against something that is not in production and will not have side effects for the other live database clients. This should be quite acceptable for random one-off research requests.
Cheers!
We do that all the time, and it is not a problem at all. Our customers get periodic reports, we do ad-hoc reports for them, and they all get read-only access to the database. Because that way, everyone saves time and they get exactly what they want.
They can't bring down the production servers because they only get access to a clone. And each customer query is limited to something like 15 seconds of cpu time, after which it just returns an error code (this is an IBM system but Oracle must have a similar method of enforcing quotas).
OTOH, if your customer's only reason is wanting the ability for ad-hoc queries you probably just need to explain to them how they can load your csv-reports into a database.
Also, if your schema is complex enough, or even if the data itself is complex enough, the people who want the queries might not have the knowledge to actually pull meaningful data out of the system.
Now, that having been said, ''No!' is never a valid response, especially a 'No, MINE!' response, which is exactly what the OP gave.
'If we do that....' is certainly a valid response, or 'The reservations I have about that request are....' is certainly a valid response, 'That's against best practices; this, this and that are considered bad ideas by the industry, for the following reasons...' or even 'that's against standard practices, because...' are always good ones, too.
Vintage computer games and RPG books available. Email me if you're interested.
I am not a regular poster do I didn't bother to register, therefore I guess that makes me a "coward".
Anyway, I am an IT Security professional in the US and not the UK, I am not a "database" expert. But reading this touches upon a problem I see quite regularly and can be resolved with some tactful information being provided to your business leadership.
As the title suggest, leave business decisions to the business, the inverse being true as well. The business should leave IT decisions to IT, (if it is in-line with the business requirements!).
Anyway, your particular instance is a customer requesting something which seems from a technology perspective to not make sense, I am also guessing your business leadership delivered this request to you and not the actual customer. If that is the case then it would be recommended to respond to the business point of contact.
What is recommended is to identify the IT business risk, (e.g. accidental information disclosure,system unavailability, application failure, and/or non-interested customer impact, etc...). You could also run this past your IT Security organization as it very well could impact the CIA of security (confidentiality, integrity, and availability).
Also quantify the additional number of man hours required to not only support the request but to also rectify any negative impact from this request. If the numbers are significant enough the business leaders may return to the client with a statement of "we can fulfill this request with no problems but unfortunately this is beyond the scope of our current service agreement and therefore we must charge an additional premium for this service". (This will usually lead the customer to a conclusion they do not really need the request).
In providing all of this information in your response to the business leadership it is also recommended you offer better alternatives to the initial request which may make far more sense and yet deliver to the customer request.
While I know it is all too often a pain in the ass, don't forget the actual customer is paying the bills including your salary as such it is in your best interest to attempt to deliver to some sibilance of the request. With that said you must also honor the requirements of your other customers and no one customers requirements should deny another customer of their ability to do business. Unless that customer is willing to absorb the costs, which is usually not an option.
Sorry for the long monologue and best of luck.
Too often -- and it sounds as if you're an example of this -- IT thinks it's really the Department of Information Prevention. Your only possible legitimate basis for refusing this effort is cost.
As somebody already pointed out, you can dump their data into a separate data mart, and let them query away to their heart's content.
This data mart could, if you like, reside in the cloud. EnterpriseDB/Elastra is an Oracle-compatible solution that would work well for the size range I'm guessing you're in. If it's multi-terabyte, however, I'd look more at Vertica/Amazon.
Or if the database fits in a single machine's RAM, look at QlikView.
There are many better ways to answer the question than simply saying "No, because you're/I'm stupid."
To err is human. To forgive is good system design.
well Ok first of all I would like to answer some people who wrote before me and said "How read only access can screw the DB up?!!!" well for your info as some of our fellows up there mentioned correctly if they write a stupid command which generates millions of lines out put or they join tables in a very very stupid way (which some amateurs will I am sure of that) then you are going to have a problem. A real story, I had a professor at the university who was working for the Federal Government and his job was to write and maintain DB of salaries of all people working for the Federal government in that state! One day one of his subordinates writes a stupid Query. Guess what? He became famous in a day! And every one referred to him as a stupid who brought the DB down!! He had to wait three year for his Boss to retire to get a promotion!!!
The way I see it you have two may be three options:
1- Come up with set of valid reasons and convince the other side that giving them an access to the live DB can harm them (Not you THEM).
2.Give them the access but Restrict them so they can not Generate out put of more than a certain length (I think that is possible to do)
3- Make an I identical copy of the database on another server which is being fed at the same time with Live DB (or with some delays) and give them access to it.
I believe these are the options you have to choose from.
Hope this helped.
Good luck
It's pretty interesting to read (and pick out) the responses written by techies - response ranging from "No way, absolutely not!" to "Sure, come on in, Oracle will take care of your crap SQL".
The former is the sort of reply that paints IT in a bad light. As technologist, we do not have a particularly good reputation anyway (see The IT Crowd) and by blatantly rejecting a request like this we are not exactly be doing ourselves any favours. The latter response is perhaps a little too cooperative. There are definitely technical issues to consider, first and foremost the priorities of the database and its related systems. Any other services should be evaluated carefully before making a decision.
My approach on this would be to take a step back from the technology and actually *talk* to the customer. Try to understand their requirements - what exactly are they trying to do here? What is their overal goal/objective with the data? Often you'll find that the user has such a narrow-minded view on the world that they make very specific requests (such as this) when their actual aim is something completely different.
It may be that they are simply using SQL because historically that is the only tool they have had at their disposal for querying the data. Perhaps what they are really after is a better reporting/MIS tool. Maybe putting Crystal Reports or Business Objects on top of your database is a better way to go.
If after all this, they really do want direct database access, then the technical discussion would be pertinent. Do it make sense for them to query your database? How about replicating part or all of your database to their site?
But before you move forward on any action points, depending on the management/ownership structure in your area, you may want to escalate to superiors/team leads before proceeding.
Hope that helps.Setup Interactive Reporting for them. You can allow them to generate their ad hoc queries, without bjorking your database. I'll even give you a much discounted price, if you mention slashdot!
If they want Read-only access, then the only issue you have to worry about is that they'll request TOO MUCH data at one time and bring up the load on the server to an unacceptable level.
To that end, build a front end so they can put together their own queries. Restrict the "select all" query, add limits.
If there's a date field, experiment with your engine to determine what the range could be before increasing the load to a crazy level, and set up your gui to return some sort of message to the person along the lines of "You've selected too much data... please try again with a smaller selection or contact the Help Desk for a custom query"...
Also limit the # of queries that can be executed simultaneously - build that into your GUI as well so they know how many are running and if their requests might be delayed...
That will probably cover 99% of the crap they want to run on their own, and you can run the other 1% at night when things are more quiescent.
One thing you can count on is that management is going to make this happen - they never would have asked if they didn't want it to happen. All they want to know is how it's going to impact their profits - tell them the gui will need to be developed - stage 1 will offer X functionality, stage 2 will offer Y functionality...and so on. Each stage is an upgrade - billable hours, and the like... having the GUI frees up the crew to do "whatever" which reduces costs, increases billable hours, etc...
Start planning now... it's gonna happen - if you make it happen correctly, you're a godlike creature. Make it turn to shit, and you're gone.
Write a quick front end that directs there query construction. This could be a simple browser based tool that allows the customer to pick from acceptable tables and fields in a specific way you decide upon. This enbles you to open up access but not allow the customer to construct dangerous queries. In addition you can tune views specifically for the customers needs making the construction process even easier, more efficient and safer.