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
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
- 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.
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
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!
Burleson is the American Team
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
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
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
and include the URL for the page.
Copyright © 1996 - 2017
All rights reserved by
is the registered trademark of Oracle Corporation.
Remote Emergency Support provided by