Why Oracle Query Fails with Invoker Rights

oracle

I connect to oracle as sysdba and run this

but why is it failing when called with invoker rights? That is the p_user call.

Here is the code:

alter session set current_schema=x;
CREATE or replace package p_definer as
    procedure d(name_ varchar2);
end;
/
CREATE or replace package p_user authid current_user as
    procedure d(name_ varchar2);
end;
/
CREATE or replace package body p_definer as
    procedure d(name_ varchar2) as
    begin
        execute immediate 'drop procedure '||name_;
    end;
end;
/
CREATE or replace package body p_user as
    procedure d(name_ varchar2) as
    begin
        execute immediate 'drop procedure '||name_;
    end;
end;
/
create or replace procedure f_foo as
BEGIN
    dbms_output.put_line('hello world');
END;
/

And here are the calls

SQL> call p_user.d('f_foo');
call p_user.d('f_foo')
     *
ERROR at line 1:
ORA-06598: insufficient INHERIT PRIVILEGES privilege
ORA-06512: at "G_GS.P_USER", line 2

Why doesn't that work?

SQL>
SQL> call p_definer.d('f_foo');

Call completed.

Best Answer

Because this is a new 12c security feature.

User x creates a harmless package and everyone starts using it, even users with higher privileges, like SYS.

Now user x knows that SYS uses this package regularly, so user x could replace the contents of this package with some malicious code any time and do anything in the database, knowing the code will be ran by SYS sooner or later.

12c prevents this with this new feature:

INHERIT PRIVILEGES and INHERIT ANY PRIVILEGES Privileges

But if SYS trusts x, the following can be done:

grant inherit privileges on user sys to x;

This will allow x to inherit the privileges of SYS when running code like the above.

Related Question