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....
- There are 2 users: ETL and DQ.
- 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;
- 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;
- user ETL execute:
call dq.test_pkg.test_proc();
- 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
- when we grant execute to etl directly, everything is fine
- 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;