Problem Scenario #

I've run into a permission issue with Oracle's DBMS_PARALLEL_EXECUTE when using role-based grants. Here's the setup:

• Two existing users: ETL and DQ
• Created a package test_pkg in the DQ schema with AUTHID CURRENT_USER; the package's test_proc uses DBMS_PARALLEL_EXECUTE to run parallel tasks that call test_proc2
• Created a role etl_process, granted it to ETL, and granted EXECUTE on DQ.test_pkg to etl_process
• When ETL calls test_proc, the parallel task throws:

  ORA-06550: PLS-00201: identifier 'DQ.TEST_PKG' must be declared
  

• However, the operation works fine if I grant EXECUTE on DQ.test_pkg directly to ETL, or if I remove the parallel execution logic entirely.

Relevant Code #

Package Specification (DQ schema):

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;
/

Package Body (DQ schema):

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;
/

Authorization Statements:

create role etl_process;
grant etl_process to etl;
grant execute on dq.test_pkg to etl_process;

Root Cause #

The key issue here is how Oracle handles permissions in parallel execution sessions:

• When DBMS_PARALLEL_EXECUTE runs tasks in parallel, it spawns independent background sessions to execute the dynamic SQL.
• By default, Oracle does not enable roles in these non-interactive background sessions. Only permissions that are directly granted to the user (not via roles) are available in these sessions.
• Even though your package uses AUTHID CURRENT_USER (which makes the package run with the caller's permissions), the dynamic SQL in run_task is executed in a separate session where the etl_process role isn't active. This means the session can't resolve DQ.TEST_PKG because it doesn't have the necessary execute permission via the role.

Solutions #

You have a few options to fix this:

• Directly grant permissions to the user: As you've already verified, granting EXECUTE on DQ.test_pkg directly to ETL will work, because direct user grants are available in all sessions, including parallel background sessions.

  grant execute on dq.test_pkg to etl;
  

• Use AUTHID DEFINER for the package: If security requirements allow, changing the package to use AUTHID DEFINER will make it run with the DQ user's permissions. The parallel sessions will inherit DQ's rights, so they can access DQ.test_pkg without additional grants. Note that this shifts the permission context to the package owner, so you need to ensure this aligns with your security model.
• Explicitly enable the role in the dynamic SQL: While not ideal, you could add a statement to enable the etl_process role in the dynamic SQL block. However, this requires the user to have the SET ROLE privilege, and it's generally less secure than the first two options.

  sql_stmt => 'BEGIN SET ROLE etl_process; dq.test_pkg.test_proc2 (in_start=>:start_id, in_end=>:end_id); END;'
  

内容的提问来源于stack exchange，提问作者HelOs