Oracle 19c, strange behavior grants in case of parallel execution

Viewed 216

Oracle 19c, Grant execution on package for role is not enough in case of parallel execution, have to grant direct to the user, don't understand why. Details below, please help....

  1. There are 2 users: ETL and DQ.
  2. In DQ package test_pkg was created:

Specification:

 create or replace package test_pkg
    authid current_user
    as
       procedure test_proc2(in_start number, in_end number);
       procedure test_proc;
    end  test_pkg;
  

 

Body:

create or replace package body test_pkg
as
   procedure test_proc2 (in_start number, in_end number)
   as
      v_id number;
   begin
      select 1 
      into v_id
      from dual ;
   end  test_proc2;
   
   procedure test_proc
   as
   begin
     begin
        dbms_parallel_execute.drop_task('test_task');
     exception when others then null;
     end;
     dbms_parallel_execute.create_task (
                      task_name=>'test_task'
                     );

     dbms_parallel_execute.create_chunks_by_sql (
                     task_name => 'test_task',
                     sql_stmt=> 'select 1 as start_id, 1 as end_id from dual',
                     by_rowid  => false
                    );
     
     dbms_parallel_execute.run_task
        (task_name      => 'test_task',
         sql_stmt       => 'BEGIN dq.test_pkg.test_proc2 (in_start=>:start_id, in_end=>:end_id); END;',
         language_flag  => DBMS_SQL.NATIVE,
         parallel_level => 1
                           );
  end;
end;

  1. Create role etl_process, grant role to etl.
create role etl_process;
grant etl_process to etl;
grant execute on dq.test_pkg to etl_process;
  1. user ETL execute:
call dq.test_pkg.test_proc();
  1. after execution check user_parallel_execute_chunks and notice that chunk failed with strange error

select * from user_parallel_execute_chunks where task_name='test_task' order by start_ts desc

ERROR:

ORA-06550: line 1, column 7:
PLS-00201: identifier 'DQ.TEST_PKG' must be declared
  1. when we grant execute to etl directly, everything is fine
  2. If change procedure test_proc in package like below(remove parallel execution), everything is fine too.
procedure test_proc
   as
   begin
      dq.test_pkg.test_proc2 (in_start=>1, in_end=>1);
   end;

1 Answers

This behaviour is neither strange and nor relevant to 19c.

See the link from the documentation of 8.0 that describes:

The owner of the procedure or package must have been explicitly granted the necessary object privileges for all objects referenced within the body of the code; the owner cannot have obtained required privileges through roles.

If you remove the authid current_user from the calling packakge you get the expected behaviour, both cases direct execution and the execution through the package dbms_parallel_execute lead to an exception PLS-00201

You may mitigate the former case with the adding of the authid current_user because in this case the role privileges are considered, but obviously you can't inherit the current user privileges in the dbms_parallel_execute package, so it continues to fail.

Related