Insufficient privileges as a definer of package

Question

I cannot run a procedure within a package with a user that also defined it unless I specify "authid current_user" for some reason. Here is my original code that produces insufficient privileges error

create or replace package hr.table_pkg 
is 
    procedure make ( p_table_name varchar2, p_col_specs varchar2);
end  table_pkg;

create or replace package body hr.table_pkg is

  procedure  make  ( p_table_name varchar2, p_col_specs varchar2) is 
    sql_stmt varchar2(32767);
    begin
      sql_stmt := 'Create table '|| p_table_name||' ('||p_col_specs||')';
      dbms_output.put_line(sql_stmt);
      execute immediate sql_stmt ;
  end make ;
end table_pkg;
/

So the user that defines the package is HR. when I query view USER_OBJECTS I can see the package and the body aswell

but when I, as the HR user try to run

execute table_pkg.make('my_contacts','id number(4), name varchar2(40)');

I'm gonna get an error that I cannot create the table. It also does not work when I change the procedure in the body to create the table in a specific schema HR:

sql_stmt := 'Create table hr.'|| p_table_name||' ('||p_col_specs||')';

the call to the procedure only works when I recompile the header of the package to

create or replace package hr.table_pkg 
authid current_user 
is

Can please anyone explain to me what am I missing, I do not get why the definer ( HR user) cannot run the procedure with definer rights

Thanks a lot!

score 2 · Answer 1 · answered Jul 18 '19 at 11:28

When you compile a procedure that executes with the current_user's authorisation, then the current user must be able to do everything that the Procedure wants to do.

When you compile a procedure that executes with the definer's authorisation, then the owner of that procedure must be able to do everything that the Procedure wants to do.

Any and all privileges needed by such a procedure must be directly granted to the owning account; they cannot be conferred through a Role, even if that Role is granted "by default".

You probably need to grant "create table" to the account that owns the procedure.

That said, having a procedure to spin up tables at random strikes me as a slightly odd thing to want to do.

Insufficient privileges as a definer of package

1 Answers1