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, 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.