14Jan
By: Steven Feuerstein On: January 14, 2022 In: APEX Developer Solutions, Feuertips Comments: 1

The Oracle database offers two different models for object permissions in your PL/SQL programs. The default is definer rights. With this model, a stored program executes under the authority of its owner or definer. 

The other permission model uses the privileges of the user invoking the program and is referred to as invoker rights.

Before a PL/SQL program can be executed from within a database instance, it must be compiled and stored in the database itself. Thus, a program unit is always stored within a specific schema or database account, even though the program might refer to objects in other schemas.

With the definer rights model….

  • Any external reference in a program unit is resolved at compile time, using the directly granted privileges of the schema in which the program unit is compiled.
  • Database roles are ignored completely when compiling stored programs. All privileges needed for the program must be granted directly to the definer (owner) of the program.
  • Whenever you run a program compiled with the definer rights model (the default), its SQL executes under the authority of the schema that owns the program.
  • Although direct grants are needed to compile a program, you can grant EXECUTE authority to give other schemas and roles the ability to run your program.

With the invoker rights model….

  • Any external reference in a program unit is resolved at compile time, using the directly granted privileges of the schema in which the program unit is compiled.
  • When the program unit is executed, all those external references are resolved again, using the directly-granted and role-based granted privileges  of the schema in which the program unit is running.

When to use invoker rights….

  • Avoiding compiling copies of code across multiple schemas. Suppose you have implemented multi-lingual support by creating a separate schema for each language. In other words, each schema has a help_text table with different contents. With definer rights, you’d have to compile all code referencing that table in each schema. With invoker rights, the code can be compiled into a single schema, invoked from each language-specific schema.
  • (from Oracle doc) When creating a PL/SQL procedure in a high-privileged schema. When lower-privileged users invoke the procedure, it can do no more than those users are allowed to do. In other words, the invoker’s rights procedure runs with the privileges of the invoking user.
  • (from Oracle doc) When the PL/SQL procedure contains no SQL and is available to other users. The DBMS_OUTPUT PL/SQL package is an example of a PL/SQL subprogram that contains no SQL and is available to all users. The reason you should use an invoker’s rights procedure in this situation is because the unit issues no SQL statements at run time, so the run-time system does not need to check their privileges. Specifying AUTHID CURRENT_USER makes invocations of the procedure more efficient because when an invoker’s right procedure is pushed onto, or comes from, the call stack, the values of CURRENT_USER and CURRENT_SCHEMA, and the currently enabled roles do not change.

Resources

Managing Security for Definer’s Rights and Invoker’s Rights

Control Invoker Rights Privileges for PL/SQL Code – ORACLE-BASE

See Feuertips episode #32

Feuertips

Every Wednesday at 11 am ET, I go live on YouTube and Facebook with Michelle Skamene to provide you with a not-to-be-missed PL/SQL tip and fun conversation. Sure, we record it so you can always watch later, but live is so much more rewarding!
Speaking of rewards, Steven finishes each show with a quick quiz to see if you were listening. Participation, of course, is optional!
One lucky quiz answerer will be selected at random to choose from three organizations dedicated to the preservation of our planet and its biodiversity: The Sheldrick Wildlife Trust, The Center for Biological Diversity, and Earthwatch.
Insum will then make a donation of $25 to that group in your name.
What could be better than leveling up your PL/SQL tips and helping one of these worthy organizations? Join us Wednesdays at 11!
Share this:
Share

1 Comment:

    • Rachel Barker
    • March 24, 2022
    • Reply

    I loved this tip! The different use cases and aspects you presented are so interesting!

Leave reply:

Your email address will not be published. Required fields are marked *