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
  1. The DBA has complete control over all SQL in the database.
  2. Rather than making Oracle roles to govern database access, the DBA only needs to issue the GRANT EXECUTE statement to the stored procedures that are required. At runtime, Oracle will give the proper table grants to the person who is authorized to execute the store procedure.

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.