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?"

Reporting Database

[WreckDiver (685191)]

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.

Re:Reporting Database

[Builder (103701)]

Actually, it can be a huge deal - badly written read queries can bring a database to its knees, slowing it down for the critical business writes.

Re:Reporting Database

[jedidiah (1196)]

...although this is pretty trivial to avoid in Oracle. You just need to make sure
that the adhoc query account has limited access and limited resource priveleges.
However, the customer is bound to complain when Oracle will refuse to run their
heinous query.

Re:Reporting Database

[Musrum (779646)]

Oracle has a different concurrency model to older versions of MS-SQL. There are no read locks.

Re:Reporting Database

[hackstraw (262471)]

Oracle has a different concurrency model to older versions of MS-SQL. There are no read locks.

You just violated the MS-SQL license.

Re:Reporting Database

[Anonymous Coward]

That kind of locking issues does not happen in Oracle due to its multi-version concurrency control strategy and locking implementation. MS SQL place locks on every row a SQL statement touches for the duration of that particular statement in order to prevent changes to those rows while the statement is running. Oracle avoid this by making "backups" (rollback/undo) of any changed rows and reads from those backups instead if needed. Locks are still used but they don't block writers. Also, MS SQL uses a "lock manager" process which keeps track of every lock in the entire database. Statements and transactions touching many rows will therfore cause the lock manager to allocate a lot of memory in order to keep track of all locks. Then it uses lock escalation techniques, i.e. changing the granularity of the locks so fewer locks are used at the expense of larger locked areas, to keep the memory usage down. Oracle does not have a lock manager (except when dealing with distributed transactions and two phase commits (2PC) across multiple databases. Oracle store the lock information in the header of the rows themselves.

Re:Reporting Database

[samwhite_y (557562)]

I would second this notion. This is the classic way to solve this problem. Modern databases provide many mechanisms to periodically push over changes to another cloned copy of the database. The advantage of doing this is you can do interesting things to the cloned data that you would be unwilling to do to a live database. For example, you can create temporary reports that get stored in another temporary database table and which in turn allow other reports to be created from this derived table. Oracle lets you do this pretty easily by creating "views". There is a whole industry built around this approach called "data cubing" with specialized tools to let you construct more involved data mining types of queries based on massaging the data in interesting ways.

Why?

[iElucidate (67873)]

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.

Re:Why?

[Bill, Shooter of Bul (629286)]

So can any Slashdotters assist me in building my case to restrict access?
I think he was asking us to help him flesh out his argument, ie give him some reasons to back up his intuition. Well intentioned or not, your response is like telling someone who's asking for directions that they are lost.

Re:Why?

[vanyel (28049)]

It may very well be that the original question was a well intentioned response to a gut feel about the resource usage issues, but it comes across as a BOFH "it's mine and you just stay away!", like the "gut feel response" is "NO! now what was the question?". I much prefer "I can, but this is what will happen", or in this case, "A customer wants read only access to a database; I'm nervous about this, but not sure what actual harm they could cause" as opposed to "I don't want to give them access, give me some reasons why I can say no". There is a big difference between the two.

Re:Why?

[ZeroConcept (196261)]

1) Who is responsible to change the customer queries when the schema changes and their reports no longer work?
2) Who is liable if the customer queries affect the performance of other processes/services (lack of index usage, expensive queries, etc)?

Re:Why?

[Unordained (262962)]

3) Who is responsible when they make decisions based on the results of queries that were incorrectly written, because they had an incomplete understanding of the tables?

You currently have a choke point where you can make sure this is well-defined: up-front before they use canned reports, or ad-hoc when they request new ones; when they start writing their own, it needs to be clear that they're on their own, and that they should *probably* at least ask for help along the way to make sure they get what they want (get the right answer), and that what they want makes sense for what they need (ask the right question), and that they understand the limitations of the data (missing data, small sample sizes, small list of codes, known-dirty data...)

You seem to be the problem

[etymxris (121288)]

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.

Re:You seem to be the problem

[recoiledsnake (879048)]

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.

I think the only problem would be that changes to improve the schema design would be more difficult to make because there would be pressure from the client not to break their existing adhoc queries that they already wrote and now run for new data.

Partial data replication

[netsavior (627338)]

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.

Use a read only replica

[Giant Electronic Bra (1229876)]

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.

Re:Use a read only replica

[mcrbids (148650)]

Granted, it costs you a bit in hardware and setup time, etc. But if you're really nervous about it, then it should do the trick. Given the limited load on the replica and its read only nature it should be able to live on limited hardware, like maybe an older server that you have hanging around. Plus you don't have to worry about reliability either. If the thing blows up no data is lost.

Cost? What cost? Oh, you mean the profit that you'll make from charging the end user for time and "overhead" in setting up the replication?

That's only a cost to the requesting end user! It's all profit for you!

Suggestion

[ggvaidya (747058)]

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.)

ummm....

[Anonymous Coward]

Wow, you're right. Next week "Ask Slashdot: How to find a DBA job after being fired from EDS"

Becasue you are stupid is a valid reason

[geekoid (135745)]

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.

Re:Becasue you are stupid is a valid reason

[sirket (60694)]

Did you even bother to read the original post? The person said their client wanted read-only access. The problem isn't the client messing up data- it's the client bringing the server to its knees with an abysmally inefficient query. Oracle has some features to limit the damage a user could do- but the only truly safe option is a read-only replica.

The person also wrote that the customer did not want 24 hour old data (you really didn't bother to read the post did you?) so your cube is a useless idea.

If you think it's a good idea to give clients direct access to your production database then please send me over your resume so I can make sure it goes on our "Never, ever hire this person" board.

You have some other problems...

[teknopurge (199509)]

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,

Just say no

[sirgoran (221190)]

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

Sheesh, have a reason, at least

[Reality Master 101 (179095)]

"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.

Say Yes.

[Angostura (703910)]

"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.

How To Do It

[porkface (562081)]

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.

Re:A simple suggestion

[ShieldW0lf (601553)]

You could always put together a demonstration, in which you illustrate how easily an unskilled user can issue the wrong query and bring the server to its knees.

Re:A simple suggestion

[jackharrer (972403)]

Better - show that they would be able to access other customers data and shout "Data Protection Act" as often as possible during demonstration. They'll understand...

Re:A simple suggestion

[cayenne8 (626475)]

"Better - show that they would be able to access other customers data and shout "Data Protection Act" as often as possible during demonstration. They'll understand..."

I don't see what the problem is....just set up a role with select privs. only on that customer's table(s). If you have all the customers' data mixed in the same tables, then create a view on their data and grant select only on that. Or...maybe look into Oracle's granular level permissions you can set up?

Re:A simple suggestion

[Ash Vince (602485)]

Better - show that they would be able to access other customers data and shout "Data Protection Act" as often as possible during demonstration.

The problem with this approach is that you come across as a rubbish DBA. Any DBA worth his salt can set permissions that only allow specific user accounts access to specific tables or views. I have never used Oracle but I do admin several MySQL databases (v4) and even they allow me to limit a particular user in this manner. If you are going to tell me Oracle does not support table level permissions I would be very surprised.

Further down this thread people start mentioning the silly query overloading the server issue. Now this is a real issue but it can be made to work either way you want. We had a similar request from a customer several years ago but we were not so opposed to giving them read only access if it could be done safely. We choose to set up a separate replicated server that they could query directly. If they wipe out the server with a silly query, who cares since it only effects them. The work involved in setting this up, its maintenance and hosting were all chargeable thereby making us more profit. This keeps them happy, the management team happy and me happy since my company operates a profit sharing scheme.

If you still are unable to see the benefit of giving them access then the best bet might be an intellectual property argument. Depending on whether you or they own the IP of the system you provide you may be able to argue that the database structure is a proprietary work and that exposing it would be against company policy in that regard.

Somewhere I used to work had a less than optimal database structure we all inherited from the previous developers who build the system. We knew how bad the design was but changing it was a huge job that we could not make the time to do as we were busy on paid work for other clients. We successfully avoided letting the customer see how awful the design was until the contract ended (it was a fixed term job that could not be extended) by making the IP argument.

Re:A simple suggestion

[lakeland (218447)]

Of course Oracle has permissions. That area of Oracle is substantially more sophisticated than MySQL's, not that surprisingly really - large enterprise access is bread and butter for oracle. Oracle's permissions are so fine grained that most people haven't heard of half of them... has a very nice permission set called 'roles' which allows you to carefully work out a set of common permissions and easily grant them all to a bunch of users. One area Oracle is missing for some reason is grant permissions at a schema level (which MySQL would call a database) rather than the object level - it is something that comes up a lot in practice.

However, when you start talking about load issues, that's where things that are feasible in MySQL just aren't in Oracle. Presuming this DBA is running Oracle EE, he'll be paying $40k/CPU (or technically, $40k/2 cores). That means for him to replicate onto another box for load issues will cost him an extra $40k just for a simple dual-core machine. Or $45k say, hardware isn't completely free.

If he wants that load balancing to happen automatically rather than telling clients which machine to log into, then Oracle has a much better product than MySQL's cluster. Unfortunately, MySQL's cluster is virtually free, while Oracle RAC is over $500k. At the same price, I would have chosen RAC over Cluster, but with that kind of price difference...

So, I think it basically comes down to load issues. Scaling up an Oracle install is unaffordable without a great business case and expecting random clients to not bring the server to its needs (granting them unlimited CPU) won't work - especially on a server which no doubt has limited cores - while not granting unlimited CPU will lead to all sorts of confused issues logged about queries failing.

There are plenty of solutions. Replicate onto Postgres (it supports Oracle's syntax so would be a better choice then MySQL). Create some nice star schemas and export via Discover or similar, replicate onto a machine that the client supplies and pays for licencing of, etc. Ditching Oracle EE and going SE might be enough too, the EE features are nice but not when they prevent business growth. Writing a custom SQL Server integration and syncing daily is probably only a few hours work and good enough for a DB up to about a TB if daily sync is fresh enough. That's just off the top of my head, I'm sure there are more options.

Re:A simple suggestion

[BronsCon (927697)]

Slashdot-reading paying clients, at that. I'll be discussing the option of switching providers with management after reading this thread.

You hit the nail on the head.

Re:A simple suggestion

[sumdumass (711423)]

There's too much of an instinct in IT to think of the systems as "yours", and protect your little kingdom. It's a computer, not your children.

The problem is that a lot of times, the buck stops with you and your reputation is on the line when someone else borks something.

I had a system that was breaking about once a week and I was getting some real heat from the managment to keep it running. It was usually on a Saturday when it failed and sometimes on a Thursday night. I eventually started suspecting that someone was messing with it because there were no logs of anything and none of the usual "something isn't acting right" when it would bork. I even started replacing things that I knew couldn't be the cause of the problems but it was about the only thing I haven't done yet. I wiped and reloaded the system 3 times, each time holding off on all the updates in case one of them was causing the problems. After about a month, I changed the passwords and took an IP camera in and set it by the terminal. Turns out that one of the members of the cleaning crew was on site alone during those nights. He would get the password from the sticky note on the wall of the management's office (why it was there, I will never know) and run a counterstrike/half life server from the server. He would then turn all the services off that he thought nobody needed, half ass his work then pull a laptop out and spend the next 4-6 paid hours playing games over it.

After this came to light, I found out that another client I had been attempting to get a contract with talked to the managment of this place and got a bad review specifically because of this server having repeated issues that I couldn't fix. After the real problem was known, the client called me up and gave me the contract I was looking for and specifically mentioned that he was worried because of all the trouble I was having with the servers at the other place.

It isn't just that one time either. I have sites that we totally lock down and reimage the profiles each night so any unapproved changes to the systems is removed at the end of each shift. I find that I am having to look for reasons to show up to those sites and make sure everything it working right. I also have sites where power users are present without any restrictions and I am constantly being called in to fix something. In fact, If I can keep people away from IE and outlook (express), convince them to not install anything not directly related to their work, make sure an up to date anti virus scanner is present, I don't have too many problems outside of hardware failures and stuff outside our control.

We protect the system like they are our children because our reputations are on the line. In many situations, our reputation determines out pay or potential pay. It stops us from doing productive things when we have to fix over people's mess up's that they attempt to hide so they don't look bad. Even if you can always blame it on someone else, you still end up looking bad because your always blaming someone else.

Re:A simple suggestion

[BitZtream (692029)]

With Oracle, all you would demo is that you aren't a very good oracle DBA.

Oracle has plenty of security and control mechanisms to ensure that a user can't starve the system of resources if you know how to use them.

These 4 word incantation to dispell project...

[mikelieman (35628)]

"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?

Re:A simple suggestion

[Z00L00K (682162)]

What you can do is to create a replicated database where they can execute their queries and do their mistakes. So if they bring down that secondary server it won't affect the production system.

Re:A simple suggestion

[tinkerghost (944862)]

too simple,
select bt.* from big_table bt, bigger_table bbt, biggest_table bbbt where bt.id=bbt.bt_id order by non_indexed_column;
Perhaps a good left outer join tossed in there to really thrash the drives.

Re:A simple suggestion

[tinkerghost (944862)]

why join tables anyway? if you really want to fsck your server just SELECT * FROM HUGE_TABLE t1, HUGE_TABLE t2, HUGE_TABLE t3;

Because the unbounded join on bbbt is the most likely type of problem you'll see in letting other people write their own sql statements, so it stands as a perfect example as to why they shouldn't be allowed to do it.

Re:A simple suggestion

[tinkerghost (944862)]

no, the cross-join (or unbounded join) was deliberate, I think adding a nice left outer join on top of the cross-join would be just the icing on the cake, or sand in the bearings.

Re:A simple suggestion

[TheRealMindChild (743925)]

I will agree with this, but add one more note. You are selling them INFORMATION that you compile from YOUR data... you are not selling the data itself. I have had this conversations with clients many times.

Re:A simple suggestion

[Em Adespoton (792954)]

This is definitely something that should be stressed. What I provide my customers is a front end with standard queries, PLUS the ability to trigger a backup (either daily snapshot or cumulative snapshot... which takes some time to transfer; our databases are generally around 2-8GB of data) of the database that they can then access and manipulate to their heart's content. At no point do they gain access to live data, but they can take snapshots whenever they want.

I have been toying with the idea of a shadow database that they can have live access to but which is only updated, never queried, by the main system. This is another possibility for your customer, and provides fresh income for you and your team as you develop this "new product".

Re:A simple suggestion

[x00101010x (631764)]

You could scare management by explaining to them that allowing direct access will disclose your database schema to the customer which will allow them to reverse engineer some of your service's design and possibly allow them to make their own (eliminating their need to continue working with your company).

Re:A simple suggestion

[phallstrom (69697)]

Additionally, your customer will now begin to rely on your schema and when you decide to change it they will be upset. In OO terms you just gave them access to all your private methods :(

This happened to me. No choice in the matter. And when it came time to build version 2 and make some internal changes we couldn't because the customers had grown accustomed to the schema being a certain way.

Re:A simple suggestion

[PRMan (959735)]

And when it came time to build version 2 and make some internal changes we couldn't because the customers had grown accustomed to the schema being a certain way.

That's what Views are for. They keep using the "old table", which is now a view. You put the changes in the "new table". You don't need to change your code either, except in the instances that required you to change it in the first place.

What's the customer's name?

[tjstork (137384)]

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.

Re:What's the customer's name?

[2short (466733)]

Seriously. If our sales guys had the clients name, we'd eat his lunch.

He seems to think 2-8 GB is a big database. If the customer wants some custom report, he thinks emailing someone who writes custom SQL and sends them an excel spreadsheet the next day is a process that "ticks along happily". If your customer is asking for direct SQL access so they can bypass you and do stuff themselves, your process is not ticking along happily.

Re:A simple suggestion

[CastrTroy (595695)]

Well, you could always ship the information to another machine, and have them access it from there. You could easily do it so that it's only a couple minutes behind real time. If they really think they need the information that badly, they will pay for the cost of the extra machine. If the really can't wait for the CSV files to come in at night, then it must be pretty important for them to have the data right now, or it must be really difficult for them to manager their own copy based on the CSV files. Shipping it to another machine would allow them to do stupid queries without compromising the performance of the main system.

Re:A simple suggestion

[an.echte.trilingue (1063180)]

How you phrase it is everything. "No" will never stick, especially if the customer can easily migrate elsewhere. As a computer guy/dept, management/the customer sees you as somebody who just makes the mysterious boxes do what they want, no matter how asinine you know that request to be. Once you start throwing barriers between the manager/customer and what he thinks he wants, you will soon be replaced by somebody who who doesn't.

The key is to try to steer the customer to another direction. Often they want silly things like this because they don't know the alternatives. Engage the customer and find out what they are doing, and toss out a better solution. In the end, you will both be happier.

If you do end up having to give them RO access, I would be sure to write some method into their user interface that restricts wildcards. You don't want somebody doing the oracle equivalent of
echo "select * from huge_table" | cat > querry.sql; mysql -u user -p huge_db < querry.sql | grep value

Sounds silly but I saw a colleague write a script that did something about like that.

Re:A simple suggestion

[Prof.Phreak (584152)]

Saying ``no'' for business reasons is great; saying ``no'' because there's no good way to handle it technically is a -bad- reason.

From my experience, sysadmins who overuse the `no' response are a buncha pricks who can't do anything (you know something is wrong when seemingly simple requests meet a ``no'' response or take days to complete [something that would take you a minute with command line access to the server]---big corps are full of such folks).

As for one possible solution: with read only access, they can't mess things up; most seasoned db admins can ensure that Oracle handles things gracefully---even from stupid read-only users.

Another solution may be to setup a mirror box, and let'em have at it. Mirror the data every day or so. If they screw it up -somehow-, everything will be reset in 24 hours anyway.

Re:A simple suggestion

[dannannan (470647)]

This is a classic example of what someone wants vs. what they are asking for.

What they don't want is this:

Someday, probably a Monday, you will get a page. The production website is timing out and they've traced it to the database. A look at the Oracle dashboard indicates that several "ad hoc" queries have been running for the past 4 hours, have blown the cache, and are churning up 99% of the read I/O on the database. You kill the queries. Some customer's ad hoc reports fail, they want to know why and they're especially irritated because they've been waiting 4 hours for the result. The other customers are upset that the main website is unavailable.

What they do want is a sandbox to play in.

The production database server is not a sandbox. It's a system of schema and queries there are all designed and QA'ed to meet an SLA. A certain amount of cache is required; a certain amount of logical I/O is possible; queries do their work within those boundaries. The tp99 is under 100ms because it was designed that way. And the schema is not even convenient for reporting. Maybe it happens to be convenient today, but that is a coincidence and things will soon change.

Everyone would be much happier if the sandbox operation could be managed separately. OTOH, improvements to the reporting schema could be made without requiring a dev+QA iteration on the production website.

Whatever you do, don't agree to support this feature without getting sign off on the additional hardware and software you need to run that sandbox. If you can't get that, get sign off on a new, weakened SLA for the main production application that will be impacted.