How Procedure Privileges Affect Definer’s Rights
The owner of a procedure, called the definer, must have the necessary object privileges for objects that the procedure references.
If the procedure owner grants to another user the right to use the procedure, then the privileges of the procedure owner (on the objects the procedure references) apply to the grantee’s exercise of the procedure. The privileges of the procedure’s definer must be granted directly to the procedure owner, not granted through roles. These are called definer’s rights.
The user of a procedure who is not its owner is called the invoker. Additional privileges on referenced objects are required for an invoker’s rights procedure, but not for a definer’s rights procedure.
A user of a definer’s rights procedure requires only the privilege to execute the procedure and no privileges on the underlying objects that the procedure accesses. This is because a definer’s rights procedure operates under the security domain of the user who owns the procedure, regardless of who is executing it. The owner of the procedure must have all the necessary object privileges for referenced objects. Fewer privileges need to be granted to users of a definer’s rights procedure. This results in stronger control of database access.
You can use definer’s rights procedures to control access to private database objects and add a level of database security. By writing a definer’s rights procedure and granting only the EXECUTE privilege to a user, this user can be forced to access the referenced objects only through the procedure.
At run time, Oracle Database checks whether the privileges of the owner of a definer’s rights procedure allow access to that procedure’s referenced objects, before the procedure is executed. If a necessary privilege on a referenced object was revoked from the owner of a definer’s rights procedure, then no user, including the owner, can run the procedure.
An example of when you may want to use a definer’s rights procedure is as follows: Suppose that you must create an API whose procedures have unrestricted access to its tables, but you want to prevent ordinary users from selecting table data directly, and from changing it with INSERT, UPDATE, and DELETE statements. To accomplish this, in a separate, low-privileged schema, create the tables and the procedures that comprise the API. By default, each procedure is a definer’s rights unit, so you do not need to specify AUTHID DEFINER when you create it. Then grant the EXECUTE privilege to the users who must use this API, but do not grant any privileges that allow data access. This solution gives you complete control over your API behavior and how users have access to its underlying objects.
Oracle recommends that you create your definer’s rights procedures, and views that access these procedures, in their own schema. Grant this schema very low privileges, or no privileges at all. This way, when other users run these procedures or views, they will not have access to any unnecessarily high privileges from this schema.
Note: Trigger processing follows the same patterns as definer’s rights procedures. The user runs a SQL statement, which that user is privileged to run. As a result of the SQL statement, a trigger is fired. The statements within the triggered action temporarily execute under the security domain of the user that owns the trigger. For overview information about triggers, Oracle Database Concepts.