Thursday, June 23, 2005

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.

POST A COMMENT

17 Comments:

Blogger Bill S. said....

Not too long ago, you helped me out with a problem that one of our developers was having. We had a dog of a query in an application and it was taking about 15 minutes to run beginning to end. I (the junior dba and a former programmer) got with the developer and said, "We can make this faster - and I know just the guy to tell us where we need to go with this." It took 3 posts on AskTom and a couple of weeks of the two of us banging our heads together - end result was the query ran in 15 seconds when we finished with it. Developers and DBAs together are an awesome team. And all because we question each other!

Thu Jun 23, 09:00:00 PM EDT  

Blogger Thomas Kyte said....

Bill S. said... And all because we question each other!

You just reminded me of something. I forget the entire context (it was 1992). Martin Stanell -- Ada programmer was the subject.

We had to trace something. Forget the details. I came up with a very very complex way to do it (it would have worked, but was "hard").

Now, I was the "guy in charge". The manager. The "lead". I said "make it so".

Martin came back and said "the obvious". It was so simple, so small, so easy.

It was so right. Love it.

"Question Authority". If they cannot take it, if they get mad, guess the advice would be akin to:

"back off whilst you start that job search"

Thu Jun 23, 09:06:00 PM EDT  

Blogger David Aldridge said....

It's slightly off topic, but the supplementary question to How do I create a table in a stored procedure is usually Why won't my code compile when I reference that table in my procedure, the problem being that the table does not of course exist at the time that the code is compiled, so SQL can't be parsed etc.

So, you're saying that it's not rude and impertinent to question the questioner, eh Tom? :D

Thu Jun 23, 10:53:00 PM EDT  

Blogger Peter K said....

Isn't it a matter of showing how to fish instead of giving them the fish?

And Dave, it's not rude to question the original poster to get more pertinent information regardless of an "expert's" opinion. :D

Fri Jun 24, 01:54:00 AM EDT  

Blogger Kalita said....

if you are a DBA asked to grant an ANY privilege, you can pick any page on asktom and ask me

Tom,
You have to modify your warning now:-) Also, would you be ok with 'IM' speak if the question is about ANY privilige? :-)

Fri Jun 24, 04:38:00 AM EDT  

Blogger Tim... said....

Good subject.

I try to answer questions by pointing people to articles or manuals that explain the topic in more depth. The problem is people want quick answers. Time after time I point to a quote from a manual (URL and everything), only to receive a reply saying, "Yeah, I read that, but how do I do it?"

I guess you can lead a DBA/Developer to the manual, but you can't make them read it ;-)

The other thing is time. Sometimes the answer seems so simple and you think you're helping by knocking out a quick reply. I know what you're saying, and I think you are correct, but I'm sure we've all answered questions badly like this and I'm sure we all will again, but hopefully with less frequency.

Cheers

Tim...

Fri Jun 24, 04:52:00 AM EDT  

Anonymous Anonymous said....

Just curious -- why are sqlserver developers inclined to create tables in procedural code?

Fri Jun 24, 05:19:00 AM EDT  

Blogger Arthi! said....

Tom,

Glad I acted as a catalyst for one of your blogs!

Fri Jun 24, 05:24:00 AM EDT  

Blogger Thomas Kyte said....

Kalita said...

Also, would you be ok with 'IM' speak if the question is about ANY privilige? :-)


that would of course void the warranty.

Fri Jun 24, 06:21:00 AM EDT  

Blogger Thomas Kyte said....

Tim said but ... I'm sure we've all answered questions badly like this and I'm sure we all will again, but hopefully with less frequency.

Absolutely, self included. This is just a talk about why we need to think twice sometimes.

Fri Jun 24, 06:23:00 AM EDT  

Blogger Thomas Kyte said....

Just curious -- why are sqlserver developers inclined to create tables in procedural code?

that is how temporary tables works in SQLServer. They do not have global temporary tables. They create them on the fly and they go away by themselves.

It is the way sqlserver works.

Fri Jun 24, 06:24:00 AM EDT  

Blogger Thomas Kyte said....

Rajesh said Glad I acted as a catalyst for one of your blogs!

Just part of it actually. The other part was someone following up with "sort of not quite done thoughts that would send someone down a black hole if they tried to follow it" as well.

Fri Jun 24, 06:25:00 AM EDT  

Blogger Jeff Hunter said....

Personally I prefer "Don't do that" accompanied by a blatent eye roll... ;)

Fri Jun 24, 09:24:00 AM EDT  

Blogger Bill S. said....

Tom Kyte wrote:
Absolutely, self included. This is just a talk about why we need to think twice sometimes.

I had a gym teacher in junior high school who had a saying (I use it on my kids all the time):
"We have two ears and one mouth, so we should listen twice as much as we speak." *sigh* now I just have to practice that more. ;-D

Fri Jun 24, 10:06:00 AM EDT  

Blogger Tim... said....

I've set up a standard response on my forum that says,

"I know the answer to your question, but I can't tell you for fear that you may not understand the implications of your question or my answer.

Glads to be of help!"

;-)

Sat Jun 25, 06:33:00 AM EDT  

Blogger Thomas Kyte said....

tim said...
I've set up a standard response on my forum that says,


I'm hoping that was tongue in cheek. The point was, if there are implications - you need to sort of mention them as well. For the person on the other end might well not have the necessary experience to recognize what they are.

I have at times said "I can answer this question, however, before I do, I want to know 'why' for you see - this sounds like a really bad idea and I want to make sure my fears are groundless before giving you advice". Those are rare.

But verbosity is a good thing. Full disclosure.

Sat Jun 25, 08:59:00 AM EDT  

Blogger Tim... said....

Don't worry Tom. I'm never serious ;-)

Sat Jun 25, 03:49:00 PM EDT  

POST A COMMENT

<< Home