-2

I'm basically looking for arguments to persuade the internal "consultants" at work as the following are not working:

  • What happens if you do a Cartesian join and crash the server?
  • You don't need to do selects from that server. You have other servers for that.
  • It's not for you it's for the clients.

We're going to be implementing DB wide triggers to stop casual logging on during work hours but with a fail safe so that devs / DBAs can fix issues if they come up but need some more arguments to forstall the screaming that's probably going to happen.

EDIT To clarify; there are no actual problems. The database will be locked down better no matter what.

I'm not looking for technical reasons to do things or advice on how to manage a DB. I'm hoping that some other technical people on here have some advice on how to explain to non-technical people the, possibly only perceived, importance of this particular issue.

7
  • 3
    What actually is your problem with the practice? Integrity? There's read-only access or offline backups for that. Performance? Measure the effects of concurrent queries and relate them to business value. Fear of database engine crashes caused by JOINs? Frankly, I think in that case you've got bigger problems than winning mindshare...
    – Kilian Foth
    Commented Feb 17, 2012 at 15:00
  • 2
    FYI you might get more/better answers from dba.stackexchange.com
    – sdg
    Commented Feb 17, 2012 at 15:13
  • 4
    You answered your own question. In any case, if you lock down the DB appropriately and set the query governor you should be able to mitigate most of those issues.
    – JohnFx
    Commented Feb 17, 2012 at 15:21
  • 3
    @KilianFoth, a cartesian join over multi-million row tables can easily do huge amounts of I/O and bring a server to it's knees. When people are logging on and doing their thing it's more than possible it's going to happen at some point. We're dealing with the issue and I'm looking for ways to explain that.
    – Ben
    Commented Feb 17, 2012 at 15:36
  • 4
    Perhaps the most concerning thing here is that you need/feel the need to explain it to them and gently.
    – Zelda
    Commented Feb 17, 2012 at 15:59

5 Answers 5

Reset to default
20

I've seem this battle play out innumerable times. It almost always starts with a few novice programmers (usually using Access) querying directly against the back-end databases and creating performance/locking problems.

The most common reaction is for the DBA to just implement martial law and lock everyone out of direct access to the database with the exact same explanations you gave above. This is unfortunate, because it ignores the fact that the people running those queries aren't just doing it for fun or to spite the DBA, these professionals need access to the data to get their jobs done. The problem is that you have application users and back-end data analysts fighting over resources and you need to appease both groups.

I'm not saying you shouldn't lock down the DB, but just that the DBA shouldn't always let the users of the app trump the needs of the analysts. Instead, he/she should work with the analysts to come up with alternative ways to meet their data needs without bringing down the system.

For example:
- Could a reporting tool be used to minimize the need for ad-hoc queries?
- Can a time window where ad-hoc queries are allowed be established to mitigate the damage of an errant query?
- Can the analysts get training from the DBA on best practices to avoid DB killing queries?
- Most DB platforms have a query governor that limits the damage that you can do with long running queries (though it won't help much with locking issues)
-Can a DB mirror or data warehouse be set up as an alternative to querying the live system?

TL:DR - Don't look at the people killing your DB with their ad-hoc queries as adversaries that must be stopped, but as another class of users that you must find a way to accomodate their business needs and mitigate the risk of performance impact on your system(s).

4
  • @WyattBarnett, please see comment under Ozz's answer. There are no problems with the business using it. It's just when that is in contention as per the question.
    – Ben
    Commented Feb 17, 2012 at 16:12
  • 1
    When it is icontention,then kill the long-running SPID after grabbing the SQL code it was running and the machine of who was running it and then go tell them what they did wrong and not to doit inthe future..
    – HLGEM
    Commented Feb 17, 2012 at 18:16
  • @JohnFx thank you for the advice, it's definitely along the lines we've been thinking, i.e. explicitly refusing access say 9-5 but allowing it outside. Do you have any pearls relating to communicating the issues to non-Devs that I'm missing or is it in your experience better to just lay down the law?
    – Ben
    Commented Feb 17, 2012 at 21:35
  • 1
    I[m advising just the opposite. Take a softer approach and go with "I want to make sure you have the tools you need to do your job, but I need to balance the performance of the system. Here is the compromise that I think will get everyone what they want, but it might involve a few reasonable limitations on your use of the DB."
    – JohnFx
    Commented Feb 18, 2012 at 1:33
4

Do they have somewhere else to test their queries, such as a duplicate of prod, with the same data, but in an isolated test region where they can do whatever they want? They might want to test their code/queries on prod because there's no suitable substitute. And if there's a suitable substitute, can you change the passwords to lock them out? NO one should have prod access except for prod support staff.

I guess the simplest reason is: "The production server is a PRODUCTION server, not a test server, so stop testing your development work on PROD!"

I think your point about testing queries that could have negative impacts on performance of production is a good one. If they don't respect that (and if you can't lock them out), then this more a workplace authority issue, and it might be worth keeping that in mind when it's time for contract renewal.

10
  • 1
    There should be no need to test code / queries on any prod server - DBF related environments can be created in seconds and Oracle ones would only take 30-40 minutes. I'm yet to come across a valid reason for actually needing access to the data at all but saying "your reason is logically invalid" has yet to work for me as an argument :-).
    – Ben
    Commented Feb 17, 2012 at 14:56
  • I agree that you should not have writable access to a production DB as a developer (or the ability to directly change code), but I have always disagreed about read only access. If you expect me to investigate and resolve issues in the system I have to be able to see what's wrong. Yes, running a full table scan on an enormous table is bad, but you can monitor usage and correct the behavior of people who abuse this ability. If they're not smart enough to adjust their behavior after this, then revoke their access.
    – jmq
    Commented Feb 17, 2012 at 15:14
  • @jmquigley: Notice in my answer I said "..NO one should have prod access except for prod support staff." Of course there are people who need access to troubleshoot production problems, and they should be trained to know what not to do to slow the server down. But not everyone is production support.
    – FrustratedWithFormsDesigner
    Commented Feb 17, 2012 at 15:18
  • @Ben: ""your reason is logically invalid" has yet to work for me as an argument" can't work to win an argument. Is the production server broken? You say "the following are not working". Is this true? Is something actually broken? If so, you don't need logic. You have something which is broken. That's all the evidence that's required.
    – S.Lott
    Commented Feb 17, 2012 at 15:19
  • @FrustratedWithFormsDesigner, where I work those are two entirely separate groups. We have a production support staff and a development staff. The production support staff is triage in my organization, but they are not experts in the system. That's what I thought of when I read your post.
    – jmq
    Commented Feb 17, 2012 at 15:20
3

If these are external consultants, you shouldn't have to persuade them.

Lock them out.

If, as another poster asked, there is no other "test" database they can use, then arrange to have one setup for them, with data anonymised if necessary.

If you really need a reason for a non-technical person, tell them you do not want a non-technical person potentially writing inefficient queries that may compromise the performance of your DB and surrounding application(s).

4
  • They're not external unfortunately otherwise it would be that easy I agree. Though your second point is well-taken. Just bald truth and deal with it.
    – Ben
    Commented Feb 17, 2012 at 15:51
  • If you have a formal "production support" team, employees would be "external" to that as well, so you would still be able to simply put your foot down. Can you explain why you are even bothering to entertain the question of prod access? A firm NO would be what they would get from me unless there is some other factor you have not mentioned.
    – ozz
    Commented Feb 17, 2012 at 15:55
  • We're more than willing to entertain the question of prod access.. outside of working hours. I recognise that people may need access to the data, just would rather it didn't happen when applications are constantly using the DB.
    – Ben
    Commented Feb 17, 2012 at 16:11
  • @Ben my answer stil applies, but you do have a timeframe for them
    – ozz
    Commented Feb 17, 2012 at 21:10
0

If you are a publicly traded company (or plan to be), it is a violation of Sarbanes-Oxley requirements (in particular, section 404) for developers to have direct access to production data. If the users have sufficient access to be running select queries, they almost certainly have permissions to be tampering with data.

4
  • This is untrue, select access can easily be granted without the power to modify data
    – Ryathal
    Commented Feb 17, 2012 at 18:09
  • Permissions for select do not grant the right to change data. Those permissions are granted separately. In general, yes, developers should not have insert/delete/update rights or the rights to exec strored procs or jobs on prod.
    – HLGEM
    Commented Feb 17, 2012 at 18:13
  • Any place smart enough to set up separate permissions for users in production databases is smart enough to prevent users from accessing production data at all.
    – Tangurena
    Commented Feb 17, 2012 at 18:50
  • I'm in the UK as per my profile. Sarbanes-Oxles doesn't apply. Even if it did, I'm not sure it would apply to people in the same company working on the clients using the data I'm talking about.
    – Ben
    Commented Feb 17, 2012 at 21:19
-1

If you have incompetent employees or consultants who can't be trusted not to run a multimillion record cartesion join on prod, then fire them.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.