Oracle Database - AUTHID

Card Puncher Data Processing

Introduction

The AUTHID property of a stored PL/SQL unit affects the name resolution and privilege checking of SQL statements that the unit issues at run time. The AUTHID property does not affect compilation, and has no meaning for units that have no code, such as collection types.

Data dictionary

AUTHID property values are exposed in the static data dictionary view *_PROCEDURES. For units for which AUTHID has meaning, the view shows the value CURRENT_USER or DEFINER; for other units, the view shows NULL.

Definition / Syntax

For stored PL/SQL units that you create or alter with the following statements, you can use the optional AUTHID clause to specify either DEFINER (the default) or CURRENT_USER:

  • “CREATE FUNCTION Statement”
  • “CREATE PACKAGE Statement”
  • “CREATE PROCEDURE Statement”
  • “CREATE TYPE Statement”
  • “ALTER TYPE Statement”

IR unit / DR unit

  • A unit whose AUTHID value is CURRENT_USER is called an invoker's rights unit, or IR unit.
  • A unit whose AUTHID value is DEFINER is called a definer's rights unit, or DR unit.

An anonymous block always behaves like an IR unit. A trigger or view always behaves like a DR unit.

The AUTHID property of a unit determines whether the unit is IR or DR, and it affects both name resolution and privilege checking at run time:

  • The context for name resolution is CURRENT_SCHEMA.
  • The privileges checked are those of the CURRENT_USER and the enabled roles.

Value of CURRENT_SCHEMA, CURRENT_USER

When a session starts, CURRENT_SCHEMA has the value of the schema owned by SESSION_USER, and CURRENT_USER has the same value as SESSION_USER. (To get the current value of CURRENT_SCHEMA, CURRENT_USER, or SESSION_USER, use the SYS_CONTEXT function, documented in Oracle Database SQL Language Reference.)

CURRENT_SCHEMA can be changed during the session with the SQL statement ALTER SESSION SET CURRENT_SCHEMA. CURRENT_USER cannot be changed programmatically, but it might change when a PL/SQL unit or a view is pushed onto, or popped from, the call stack.

Oracle recommends against issuing ALTER SESSION SET CURRENT_SCHEMA from in a stored PL/SQL unit.

Name resolution and privilege checking

During a server call, when a DR unit is pushed onto the call stack, the database stores the currently enabled roles and the current values of CURRENT_USER and CURRENT_SCHEMA.

It then changes both CURRENT_USER and CURRENT_SCHEMA to the owner of the DR unit, and enables only the role PUBLIC. (The stored and new roles and values are not necessarily different.) When the DR unit is popped from the call stack, the database restores the stored roles and values. In contrast, when an IR unit is pushed onto, or popped from, the call stack, the values of CURRENT_USER and CURRENT_SCHEMA, and the currently enabled roles do not change.

For dynamic SQL statements issued by a PL/SQL unit, name resolution and privilege checking are done once, at run time. For static SQL statements, name resolution and privilege checking are done twice: first, when the PL/SQL unit is compiled, and then again at run time. At compilation time, the AUTHID property has no effect—both DR and IR units are treated like DR units. At run time, however, the AUTHID property determines whether a unit is IR or DR, and the unit is treated accordingly.

Documentation Reference





Discover More
Card Puncher Data Processing
Oracle Database - Java Stored Procedure

Application developers familiar with procedural programming styles developed business application logic using languages like PL/SQL. The business logic they developed was deployed as stored program units...



Share this page:
Follow us:
Task Runner