Security Granted by Roles

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.

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.

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.

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.