This is turning out to be a bit harder to write than I orginially thought. I generally land in the laissez-faire camp, and I know what you’re thinking: “Jeff, we didn’t come here to read French!” Sorry about that, bad habits picked up from 4 years of public education instruction. In terms of American politics, think of me as the Ron Paul of database security folks.

Noooooooooooooooooooooooo!!!!

Set a user up to be able to do what they need to do, then leave them alone.

Don’t throw roadblocks in their path. Don’t make things harder than they need to be. It’s a pretty simple concept. I ranted earlier this year on a similar and kind-of-related topic, Database Security Should be Handled by the Database.

BUT

And that’s a big ‘but’ (hehehehehehe), but do we need to help push our users towards disaster? Not getting in there way is a bit different than encouraging them to step on themselves. And so my maturity as a person has led me to become less conservative and more liberal in my philosophy. Because we know better, maybe we SHOULD use that power and influence to keep the users out of trouble. At what point does this become babysitting?

And this is the crux of why this post is giving me pause. I’m being forced to take a stand and get off the fence. If I’m going to be a Product Manager, I can’t sit back and just play both sides of every argument.

Let’s look at a sample case:

User: Let me drop multiple tables in a single click.


Our tool makes it relatively easy to drop an individual table now. It’s a click-click kind of thing. You get a ‘Are you sure’ confirmation dialog, and the ability to PURGE it from the database as well. So someone isn’t likely to fat finger themselves into the unemployment line.

Let’s say you are in development and you’ve built 30 or so staging tables, and you need to drop all 30 of them. Yes, you could build a script to generate the series of DROP commands, or you could ask your IDE to do that for you. My libertarian tendencies tell me to make that available to the user. But the pragmatist side of me says, ‘Hey these are the same users that can not remember if they are in DEV or PROD half the time.’

So who wins out?

Hopefully the user does. I think we can find a middle ground. Discussion is on now to let the user do this, but have it generate a DDL script that the user must manually run on their own. I think this is a fine compromise.

There are limits to this live-and-let-live philosophy

Don’t ask me to give you a TRUNCATE button. No way and no how. Some things shouldn’t be easy, like getting married. But you can undo a marriage. TRUNCATE is unrecoverable outside of a database recovery. So I’m babysitting the user here, because I know better, and I want to see them prosper. And I take solace in knowing that I’m not a hypocrite regarding my database security rant as TRUNCATE is an ALTER command and there is no way to revoke a ‘TRUNCATE’ privilege.

So which camp to you subscribe to here? Do you say ‘give me privs or give me death!’, or do you take things on a case-by-case basis? More importantly, do you find this aspect of software development interesting enough to read about it here?

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

6 Comments

  1. So the debate has largely been settled, and Jeff (Kemp, not me, although I do talk to myself from time to time) I think you’ll like the outcome.

  2. I want to select a whole lot of tables and drop or truncate them. If the IDE makes this so unwieldy that I may as well just script it or switch to another IDE, it’s not good enough. It’s my job to know what I’m doing. I’ve run things by mistake with scripts as well as a IDE, but I didn’t blame the scripts or the IDE for my mistake.

  3. For the really paranoid, add a BEFORE DROP DDL trigger that just does a RAISE_APPLICATION_ERROR. That way you have to disable the trigger to successfully drop anything. Or you could have the trigger check some package variable or SYS_CONTEXT value, such as MODULE or ACTION, has been set.

    That effectively gives you the ‘two-step’ process

    • JeffS

      I’ve seen that approach before. I think it sounds excellent in theory, but wondering if anyone has implemented it in the ‘real world’?

      I reckon that if you’re savvy enough to implement such a trigger, you’d probably already have a system of checks in to prevent such problems already.

  4. What DBA would give developers the DROP TABLE privilege in production?

    I have on occasion dropped the wrong table in development. But that is where FLASHBACK technology or Oracle Total Recall on 11g comes in handy.

    • JeffS

      I know many DBAs that login as SYS, and I know in many customer sites, the developers are given the APPLICATION owner login (APPS/APPS.) Flashback is nice, until you drop a table that exceeds the flashback storage allotment. There are many activities that aren’t so simple to recover from.

      So Jimmy, I take it that you put the onus of irresponsibility on the user and not the IDE?

      I was chatting with my boss and I brought up the following: many of us have been TRAINED to expect the ‘are you sure’ dialogs, to the point that we become reliant on them. Is this a good thing? I would say ‘no, but I would also say that it’s pretty severe not to have them in certain scenarios.

Reply To thatjeffsmith Cancel Reply