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.
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.
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
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.
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.)
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 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
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.
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
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.
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
"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.
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
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.
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"
No. You simply don't understand the problems here.
A Pirate and a Puritan look the same on a balance sheet.
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'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.
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.
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
"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.
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
I've had customers like that as well. Honestly, since they're paying and it's their data I can see where they're coming from.
On the other hand, I usually have someone sign off on a MOU that they understand writing to their database outside of the application is inherently risky, and that they understand there will be additional charges for recovery work.
WHEN a customer mangles their data we get to test our recovery processes. So far, so good.
Also good to have multiple backups using multiple methods. I've had customers mangle multiple tables, so a full database restore with point in time recovery was best while other customers munged up one table, so a restore from export worked out better.
A Human Right
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.
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."
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
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.
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!
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
"what kind of DBA is unable to create views and restrict access to tables?"
One that works for EDS, who are famous for their incompetence. Next question!
A pizza of radius z and thickness a has a volume of pi z z a
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.
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.
What about your license with Oracle? Will that allow such access? Their licenses tend to be restrictive about this type of thing.
...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.
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.
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.
"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
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
..."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
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.
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+.
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.
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.