Prior to Oracle8i, all compiled stored objects executed with the privileges and namespace of the definer of the object. That is, the set of privileges granted directly to the definer (owner) of the stored object were used at compile time to figure out

ć What objects (tables and so on) to access.
ć Whether you were in fact allowed to access them.

This static compile time binding went as far as to limit the set of privileges to on the privileges granted to the definer directly (eg: no roles were ever enabled during the compilation or execution of a stored procedure, trigger or view). This is predominantly a performance optimization and typically is not a burden (it is truly a feature).

Beginning in Oracle8i, we have a feature called Invokers rights which allows us to create procedures that do not follow these rules. We can now develop a stored procedure that executes with the privilege set of the INVOKER (the currently logged in user) at runtime. This allows us to create a stored procedure that might execute properly and correctly for one user (they had access to all of the relevant objects) but not for another (who didn¡¦t). This is because the access to the underlying objects is not defined at compile time (although the definer must have access to these objects ¡V or at least objects with those names in order to compile the PLSQL code) but rather at runtime ¡V based on the current user.


In your case above however -- it will not solve the issue. When we compile a procedure (definer or invoker) we will check that:

ć All of the objects it statically accesses (anything not accessed via dynamic SQL) are verified for existence. Names are resolved via the standard scoping rules as they apply to the definer of the procedure. ROLES are not enabled.

ƒá All of the objects it accesses are verified to ensure that the required access mode will be available. That is, if an attempt to UPDATE T is made ¡V Oracle will verify the definer or PUBLIC has the ability to UPDATE T without use of any ROLES.

ć A dependency between this procedure and the referenced objects is setup and maintained. If this procedure SELECTS FROM T, then a dependency between T and this procedure is recorded


What this means is that a invokers rights routine, at compile time, is treated exactly the same as a definers rights routine. This is an area of confusion for many. They have heard ¡§roles are enabled in invokers rights routines¡¨ and that statement is in fact accurate HOWEVER (and this is a big however), they are not in effect during the compilation process. That means that the person who compiles the stored procedure, the owner of the stored procedure, still needs to have direct access to all statically referenced tables. The rules spelled out in the Application Developers Guide on ¡§Privileges Required to Create Procedures and Functions¡¨ remain in place. You need direct access to the underlying objects.


What is changes is the runtime behavior. Let's say you compiled your procedure above (after getting direct access). You grant execute on it to BOB. When BOB runs it -- the access to CORPORATE.DIRECT_CAD will be evaluated under BOB's account -- not MDA's account. Even though MDA can access it -- it does not mean that BOB can via this procedure. Therein lies the MAJOR difference for IR (invokers rights) over DR (definers rights) routines.

Now, if you coded the above routine as:


execute immediate 'SELECT max(EFF_DATE)
FROM CORPORATE.DIRECT_CAD'
INTO DtCurr_RepDate;

Then, we would be in business. MDA would not need access to corporate.direct_cad at all (ever) but anyone who RAN the procedure would access to it either directly OR via a role.