Security Roles   «Prev  Next»

Lesson 8 Roles and stored code
ObjectiveExamine why roles do not affect stored code.

Security Granted by Roles

Roles do not affect Stored Code

Stored code, also known as stored procedures, is a way of extending the power of the standard SQL language with procedural code stored in and executed by Oracle.

Roles versus Stored Procedures

There is a basic mismatch between
  1. the functionality provided by roles and
  2. the way stored procedures are compiled.
Stored procedures are created by a particular user and executed by many other users. Once it is created, a stored procedure is not modified at runtime.
Roles can be changed at runtime and this can create a problem for a stored procedure, because the characteristics of a stored procedure are not allowed to change at runtime. Because of this, Oracle can not allow a stored procedure to use the security granted by roles at runtime.
Oracle does not allow any privileges granted by roles to be associated with a stored procedure. A stored procedure only has the object privileges directly granted to the schema that contains the procedure.

Example of Privileges and Stored Procedures

As an example, suppose that user BID1 was assigned SELECT privileges on the BID table directly, but was also assigned the role of BIDDER, which had INSERT and UPDATE privileges on the table. If you created a stored procedure as BID1, the stored procedure would only have SELECT privileges on the BID table.
This limitation makes sense, but you must be aware of it and its implications when you use stored procedures.


In Oracle 8i, you can modify this behavior by using the invoker on stored procedures.

Role implementation - Quiz

Click the Quiz link below to answer a few questions about implementation of roles.
Role implementation - Quiz
The next lesson demonstrates how to view information about the privileges granted to roles.