Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Arif.

Asked: July 24, 2001 - 11:14 am UTC

Answered by: Tom Kyte - Last updated: July 30, 2001 - 2:26 pm UTC

Category: SQL*Plus - Version: 7.3.3.6.0

Viewed 1000+ times

You Asked

What are the drawbacks in using stored procedures ? Is there any overhead ?



I am one of the novice ORACLE DBA. I have been asked by one of the SQL programmer to authorize him with CREATE PROCEDURE system privilege on an ORACLE user on a production instance. The user has CREATE PROCEDURE privilege on development instance but not on production instance.

The user account in question has been there for past many years.
I am wondering as to why did the original DBA authorized this user with the CREATE PROCEDURE on devlopement instance and not on the production instance ?


and we said...

Stored procedures are a great:

o security mechanism
o programming device
o performance enhancing tool

I cannot imagine a database without them.

The developers can do as much damanage using client applications with SQL in them -- in fact, they can do more because you cannot see the code at all. If you have them put their SQL in the database -- you can fix (they can fix) poorly performing queries in the stored procedures and immediately have all clients running the better query -- without re-deploying anything.


and you rated our response

  (8 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

putting their SQL in the database?

July 24, 2001 - 8:57 pm UTC

Reviewer: Tom from Florida

How do you put their SQL in the database? views? or returning CURSOR references from procedures?

Do you recommend putting all SQL code (selects, too) in procedures?

re: putting their SQL in the database?

July 24, 2001 - 10:17 pm UTC

Reviewer: Lionel Mandrake from Burpelson, AFB

Gosh, what would be a good way to put SQL into the database instead of leaving it on the client-side? Oh, wait, I know, maybe something like... a stored procedure? or even better, a package and package body containing the procedures/functions? Then you could use simply use the *_SOURCE views if you needed to see the, um, well, source...

Also, I would never give a developer create privileges (or the CONNECT/RESOURCE roles) on PROD. EVERY object (ie, table, package, index, etc...) that goes into production goes through one of our DBA's desks first. Non-admin users get 'create session' and that is all they need (as far as system privs). Now, maybe if all our developers promise to read Tom's book and follow the guidelines, it would be a different situation... :) I can guarantee from the code that has been sent back to the developers lately, not much reading has been going on :( I mean, is it really that hard to use a bind variable, for instance? Ah, but I digress...

Devloper's Don't need privileges on the Production Database

July 25, 2001 - 1:17 am UTC

Reviewer: MSK from OH USA

It's DBA job to move the code from the Development Database
to production database. Developer's shouldn't have access
for the production database. Any code before moved into
prodcution should be viewed by the DBA

Have to disagree with MSK

July 25, 2001 - 8:03 am UTC

Reviewer: KT from Scotland

I don't believe it is the DBA's job to give code the once over before putting it into a production environment. That's why developers are paid big bucks is because they know what they're doing (sometimes!). That's why a successful development team will always have an experienced developer who a) knows his code and b) knows the business to look over any code before releasing to production. As part of our development standards a developer must produce documented proof that they are using the most effecient method for retrieving data, whether that's via tkprof output or explain plans.

Have to disagree with MSK

July 25, 2001 - 8:17 am UTC

Reviewer: KT from Scotland

I don't believe it is the DBA's job to give code the once over before putting it into a production environment. That's why developers are paid big bucks is because they know what they're doing (sometimes!). That's why a successful development team will always have an experienced developer who a) knows his code and b) knows the business to look over any code before releasing to production. As part of our development standards a developer must produce documented proof that they are using the most effecient method for retrieving data, whether that's via tkprof output or explain plans.

re: putting their SQL in the database?

July 25, 2001 - 8:52 am UTC

Reviewer: Tom from Florida

Lionel misinterpreted my question. We use packages and package bodies extensively in our application; in fact, we don't use UPDATE or DELETE from clients at all. We do, however, use SELECT statements in the client. Is this a bad practice?
Perhaps my question should have been "Are you suggesting that we code SELECT statements into packages and return the cursor as an ORACLETYPES.CURSOR?"
In (the knowledgable) Tom's answer, he states "if you have them put their SQL in the database--you can fix (they can fix) poorly performing queries in the stored procedures...."
I assume he also means SELECT statements. Am I wrong?

to: tom from fl

July 25, 2001 - 9:36 am UTC

Reviewer: Lionel Mandrake from Burpelson, AFB

Tom (from FL):

Didn't mean to go tripping on you, and, yes, I did misinterpret your question - my bad. Let's just say yesterday was one of those days where we had a slew of "consultants" in-house that "had never worked with Oracle before, but we know SQL Server." Like that is a big help. Anyway, what we do here (even though you didn't ask) is encourage developers to put as much in the db as possible. If the client allows it (and the developer can do it), I encourage returning ref cursors. Most client-side development here is done using VB and using Oracle Objects for OLE on the client-side allows ref cursors with no problem. Having said that, though, I am not a developer, so a developer may have better input. Sorry for the confusion again.

-LM

Response to KT

July 30, 2001 - 2:26 pm UTC

Reviewer: Jim from Philly,PA

imho even experienced developers forget what the differences between DEV and PROD. So I require that a DBA install and compile stored procedures. I find that most developers are either inexperienced with Oracle or are not knowledgable about the configuration of the production environment.