Security Roles   «Prev  Next»

Security Granted by Roles

Lesson 8 Roles and stored code
ObjectiveExamine why 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 and stored code

There is a basic mismatch between the functionality provided by roles and 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. 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.
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.
The next lesson demonstrates how to view information about the privileges granted to roles.

Role implementation - Quiz

Click the Quiz link below to answer a few questions about implementation of roles.
Role implementation - Quiz