Oracle and PL/SQL Recipes: A Problem-Solution Approach

4-17. Executing Stored Code Under Your Own Privilege Set

Problem

You have loaded all of an application's objects into a single application schema. However, you do not want packages, procedures, and functions to execute as the schema owner. Instead, you want stored code to execute with the privileges and access of the user who is invoking that code.

Solution

Use invoker's rights by providing the AUTHID property within the declaration of your program. If the AUTHID property is specified when defining a package, procedure, or function, then you have the ability to specify whether the program should be invoked using the CURRENT_USER privileges or the DEFINER privileges. In the case of this solution, you would rather use the CURRENT_USER privileges ...