How to give an answer

In a recent blog we discussed how to ask a question, this time I would like to look at how to answer a question.

About 3 weeks ago, I talked about how to ask a question.  This time, I’d like to turn it around — how to answer a question.

Question: How can I let a developer see a package body for any package they are allowed to execute.  ALL_SOURCE won’t let them see it.

Potential answer: Grant them EXECUTE ANY PROCEDURE or CREATE ANY PROCEDURE.  (period)

Question asked, answer given. Perfect right?  That is the way it is supposed to work.  Someone asks and you just answer.  Problem is, the answer is totally dangerous.

Question: How do I create a table in a stored procedure?

Potential answer: Use EXECUTE IMMEDIATE ‘create table….’.  Remember you need to have CREATE TABLE granted directly to you in order to be able to create a table in a definers rights stored procedure

Sounds great, the person answering has thought of everything.  They even thought of the most commonly asked question with regards to execute immediate.

Or have they.  EXECUTE/CREATE ANY PROCEDURE certainly does allow you to see the package body in ALL_SOURCE.  Absolutely.  It answers the question.  It is 100% accurate.  It is true. 

It is really bad advice.

Really really bad.  Not just a little bad – big time bad.

Any privilege that has ANY in it is dangerous.  (really, think 500 times before granting it.  Here is a promise – if you are a DBA asked to grant an ANY privilege, you can pick any page on asktom and ask me “why might I think twice before granting this.  I don’t care what page it is, I’ll answer it). 

Give me EXECUTE ANY PROCEDURE (please!).  I’ll wreak havoc on your database.  Give me CREATE ANY PROCEDURE and I’ll be really dangerous (can you spell Trojan Horse – or worse?).

Creating a table in a stored procedure?  One needs to ask why first.  If the answer is “we did it like this in sqlserver”, then the answer is — Ok, tell me what you need to do and I’ll then tell you how to do that.  And you know what, the answer will be “we did it like this in sqlserver”. 

Point is – blindly answer a question with a technically correct, valid, true answer and you might just have really done more harm than good.  You have to think about the person on the other end of the communication link.

How much do they know?  If you say “but consider the implications”.  For example “grant them EXECUTE ANY PROCEDURE, but consider the implications” – are you off the hook?  I think not.  In order for someone to consider the implications they must know what the implications are.  You cannot assume that.  You need more information before answering OR you need to spell out the implications (I prefer the latter in all cases).

“Use execute immediate and get yourself CREATE TABLE to create tables in stored procedures.  But – consider the implications”.  If you are a sqlserver programmer, the implications are (as far as YOU know) “I’ll be able to create a table, cool”.  But to an Oracle programmer the implications are “DDL is hugely expensive”, “I have to drop the table and what happens when my procedure fails”, “DDL commits”, “I cannot use static SQL”, “This will hard parse like mad”, and so on.  The implications are profound and without exception bad, negative, disastrous. 

This was prompted by a recent Q&A on my site.  I don’t want to point to the exact example, it wasn’t that serious, the person was truly trying to be helpful.  I jumped on it, but that particular question is not the issue, just the catalyst.  It made me think.  It reminded me of this blog entry.  But with a twist.  It is not just about asking “why do you want to do that”, it is about answering with “here is an approach, BUT”.  Always “BUT”. 

About never assuming.  We cannot assume anything about the people reading our answers.  Ever. 

I guess it comes back to “I’ll speak pages where others would use paragraphs”.  Caveat Emptor?  I might end an answer with that, but it’ll be after explaining what the caveats are. 

I guess my point is, when we answer a question, we must assume the person on the other end doesn’t know the implications.  We should point them out.  Terseness is not good, details are.  If they skip the details – fine, but we should provide them.

Heck, the person that says “understand the implications” might not themselves understand them all.  At least by stating them, we can build on them, add to them.

Maybe this will help break down the DBA / Developer wall that exists.  Think about it – the DBA instead of saying “no” (arms folded of course) says “no, for the following reason”.  Developer instead of saying “I need this”, says “I need this because…”.  What we need is well thought out, thoughtful, complete, ideas.