In our system, we have the stored procedures owned by a central schema. Users have their own database accounts (schemas) where we stored temporary tables. The stored procs frequently create these tables. One developer reported that a rewrite of some stored procedures broke this pattern. The stored procedure, owned by the central schema, was creating temp tables in the central schema. That is no good, as each user needs their own copy of the temp table.When I was asked for help, I said that we have the technology to make this work. We have it set up in the old stored procedures. However I referred the developer to our DBA Team. The lead DBA told us to set up invoker’s rights for the procedure. Normally we seemed to use whatever the default was. And it seemed to work before. We tried all kinds of debugging, but could not get this to work without using invoker’s rights.
Definer’s rights s the default in Oracle. Whoever owns the stored procedure (the definer) has the credentials that determine what other code the stored proc can call, and what tables and objects are used by default. This is not what we want on our project.
You have the ability to override the default behavior and specify invoker’s rights. This is done by specifying the AUTHID CURRENT_USER keywords when creating the stored procedure. This causes the context of the current user to be used when a stored procedure is called. Sure enough when we made this change, the temporary tables started getting created in the calling users’ schemas. Now we got some new stored proc code to update.
