RelationalDBDesign RelationalDBDesign 


SQL Tuning Tools   «Prev  Next»
Lesson 5 Using SQL inside stored procedures
Objective Describe the reasons for placing SQL inside stored procedures.

Where is the SQL source?

Determining where the SQL source is located has dramatic impact on three areas:
  1. security,
  2. performance analysis, and
  3. tuning.
Because of benefits realized in all these areas, SQL should be placed inside stored procedures.

Benefits of using SQL inside of stored procedures 1) Security 2) Performance Analysis 3) Tuning
Benefits of using SQL inside of stored procedures 1) Security 2) Performance Analysis 3) Tuning



The only way to enforce the use of stored procedures is to remove ALL table GRANTs in your database and replace them with GRANTs to the stored procedures. This will prevent all unwanted SQL since any unauthorized SQL will not have table access privileges. The only downside to this approach is the extra work for the DBA, placing the SQL into stored procedures and packages, and then granting execute privileges to the end users. Now, let us conclude this module with a review of the important concepts.