Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Grant Execute Security tips

 Oracle Tips by Burleson Consulting

June 9, 2014

To address these issues and simplify Oracle security, Oracle introduced the grant execute method. Using the grant execute method, all data access code is encapsulated into Oracle stored procedures, and the end users are granted the ability to execute the code.

Miss parts of this series?  Catch up on the two previous articles in this series:

Design for grant execute security
Unlike the specific grant model where specific database privileges are granted to specific users, the grant execute model allows users to be granted execution access without having any database privileges. In Oracle, this is called definer rights, and the end user takes on the database privileges of the definer of the stored procedure when the code is executed. Definer rights have several important advantages over standard grant security, primarily the simplification of the access rules.

In Oracle, you may also use invoker rights, by which the end user may only execute the stored procedure using privileges that are assigned using standard grant security. Invoker rights are more complicated (and less desirable to many Oracle designers) because Oracle grant security must also be implemented.

It should be apparent that the grant execute model requires careful up-front design. Developers must be forced to follow the design standards, creating Oracle stored procedures and functions that are then encapsulated into packages.

Procedurally, this requires the Oracle designer to predetermine the process code for the system and define a set of packages that perform all database access and processing.

Benefits of grant execute security
The grant execute model is especially compelling to the Oracle designer because it has several other side benefits:

  • All process code & SQL is stored inside the data dictionary?The grant execute model allows all SQL and programs to reside in the data dictionary where they can be easily located, modified, and tuned.
  • Process code can be pinned for fast execution?In Oracle9i and beyond all stored procedures can be compiled, plus pinned into the Oracle library cache with the dbms_shared_pool.keep procedure. This provides super-fast execution of application code.
  • Coupling of data & behavior?Encapsulation of code into Oracle stored procedures allows the Oracle designer the ability to tightly couple the database entities with the code that acts upon the entities. For example, Oracle member methods can be created for a customer table so that Oracle knows what code is associated with the database. This is identical to the object-oriented model where methods are associated with C++ or Java object classes.
  • Isolation of database code?Because the end user screens have no process code or SQL, all database access is done via standard stored procedure calls. This makes the application database-independent, which means the application could easily be ported to another database without any code changes to the front end.
  • Tight control of database access?Using definer rights, end users can access the database only when using the stored procedures and functions. This allows the procedural code to tightly control access rules and eliminates any backdoor access to the database. The grant execute model also does more than control access to Oracle tables. Since the stored procedure controls the database access, the stored procedures can define the procedural rules by which the end user may see the Oracle data. Row-level, column-level, and data-dependent access rules are all coded inside the PL/SQL or Java of the Oracle stored procedure.
  • No back doors?The end users will only have database privileges when they are executing the stored procedure and will have no ability to access Oracle outside of their procedures.

Like all Oracle security, the grant execute model is not without limitations. Let?s take a look at some of them.

Limitation of grant execute security
The grant execute model is best for formal system design projects where a project analyst carefully maps out the access code and rules prior to the initiation of programming. The major limitations of the grant execute model include:

  • Requires careful up-front design?Ad-hoc systems development is not easily accomplished because the design requires predefinition of the major packages, stored procedures, and functions.
  • Limited choices of procedural languages?Oracle functions and stored procedures require coding in PL/SQL or Java. However, some Oracle designers require only that the SQL be stored inside stored procedures. This allows the developers to use any procedural language they desire. Instead of having the front end totally code-independent, the front end contains the process code, but all database SQL is replaced with stored procedure and function calls.
  • Hard to audit?Because end users have only database-access rights while executing the stored procedure, it is hard to create lists of database entities and those end users who have access to those database entities. Auditing of definer rights and grant execute security requires that you write sophisticated audit programs to parse and interpret the stored procedures.

Grant execute

As you can see, the grant execute approach has many benefits over traditional grant security, but there are also some drawbacks. Because Oracle is the world?s most powerful and flexible database, you have other methods for controlling data access. Oracle provides a wealth of choices for data access control, and your job is to choose and implement the access control that best meets your design requirements.

Oracle Training from Don Burleson 

The best on site "Oracle training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!

Oracle training



Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster