If you have been working with Oracle for some time, you have most likely seen this scenario:

• From SQL, an operation works.
• From an anonymous PL/SQL block, it also works.
• From a stored procedure… ORA-01031: insufficient privileges.

The usual way to try to fix it (besides complaining to the DBA) is to review grants, add direct privileges, try a different role, test again… even request the DBA role. And yet, the error still persists.

This article does not aim to provide a quick fix, but rather to explain why this problem still exists today, even in current versions such as Oracle AI Database 23ai / 26ai, and why it is not an isolated issue, but a direct consequence of Oracle’s privilege inheritance model.

The basic privilege inheritance model under Definer’s Rights

In Oracle, the basic model is well known:

• A privilege defines something a user is allowed to do.
• A role is a collection of privileges.
• Privileges are granted with GRANT and revoked with REVOKE.

In interactive execution (SQL), this model works in a fairly intuitive way, but problems arise as soon as we enter the realm of stored PL/SQL.

First of all, it is important to understand that stored PL/SQL units (procedures, functions, packages… even views) execute under the security context of their owner. That is, under Definer’s Rights.

At execution time (this is important), this implies that:

• Oracle switches the execution context to the object owner.
• Roles are disabled (except PUBLIC).
• Only privileges granted directly to the owner are considered.

This behavior is perfectly documented and has been consistent across Oracle versions for decades.

The practical result is that a privilege that resolves correctly from SQL via a role may not exist from the perspective of a stored procedure.

And this is where the first apparent inconsistency shows up.

An anonymous PL/SQL block executes in the context of the current session and uses the user’s enabled roles. In other words, it does not change the security identity, even though it is still PL/SQL. At this point, developers run the code in an anonymous block, see no errors, create the procedure, and when they execute it, it fails. They then request the DBA role, and even with that role granted, it still fails — while the anonymous block keeps working every time.

And most importantly, without any change to either the user or the session. That is, they execute the procedure using the same user that created it, running exactly the same code that works from SQL and from an anonymous PL/SQL block.

As an example, consider the following, executed on an Oracle 26AI Database.

Note: The user DESARROLLO has only CONNECT, RESOURCE, UNLIMITED TABLESPACE and CREATE MATERIALIZED VIEW

  SQL> create materialized view test as select * from dual;
  
  Materialized view created.
  
  SQL> drop materialized view test;
  
  Materialized view dropped.
  
  SQL> begin
    2     execute immediate 'create materialized view test as select * from dual';    
    3  end; 
    4  /
  
  PL/SQL procedure successfully completed.
  
  SQL> drop materialized view test;
  
  Materialized view dropped.
  
  SQL> create procedure crea_mv_test as
    2  begin
    3     execute immediate 'create materialized view test as select * from dual';   
    4  end;
    5  /
  
  Procedure created.
  
  SQL> exec crea_mv_test
  BEGIN crea_mv_test; END;
  
  *
  ERROR at line 1:
  ORA-01031: insufficient privileges
  ORA-06512: at "DESARROLLO.CREA_MV_TEST", line 3
  ORA-06512: at line 1
  Help: https://docs.oracle.com/error-help/db/ora-01031/
  

The reason is not the materialized view itself, but rather the implicit privileges that Oracle requires internally (such as CREATE TABLE) and how it evaluates their origin.

This is one of the most common — and most frustrating — cases. In fact, I have seen it so many times that it motivated me to write an article back in 2012, published on Experts Exchange.

Here it is:

https://www.experts-exchange.com/articles/9749/The-strange-case-of-the-insufficient-privileges-error-when-creating-a-materialized-view-using-execute-immediate-inside-a-PL-SQL-procedure.html

Over the years, Oracle has introduced several mechanisms to work around these limitations:

• AUTHID CURRENT USER, to execute code with the privileges of the invoker (that is, the user executing the procedure).
• BEQUEATH CURRENT_USER, to prevent views from acting as a security boundary, since they always execute under the Definer’s Rights model.
• Privileges such as INHERIT PRIVILEGES, to control privilege inheritance between schemas, especially when accessing remote databases via database links.

It is important to keep in mind that these mechanisms do not replace the original model that has existed since the early days of PL/SQL.

They are workarounds to the expected behavior.

Conclusion

The insufficient privileges error in PL/SQL is neither an accident nor an oversight.

It is the visible symptom of a privilege inheritance model that, from its original conception, was designed under an approach that seemed secure and consistent, but that in practice has required multiple corrections, extensions, and auxiliary mechanisms to cover its limitations.

Evidence of this can be found in the constant emphasis on applying the principle of least privilege — difficult to implement cleanly in this model — and in the need to provide additional tools for auditing and effective privilege capture.

Ultimately, we are dealing with a model that has evolved by accumulation rather than by redesign, which explains many of the inconsistencies and unexpected behaviors that still appear today, even in recent Oracle versions.