0

In my DB I set up 2 schemas, schemaA and schemaB. In schemaA I create a job (jobA) and a procedure (procA) to set arguments and enable the job. I test the flow in schemaA and it's working correctly. In schemaB I need to call schemaA.procA in order to run the job. I grant to schemaB the privileges needed to execute schemaA.procA but the error ORA-27486: insufficient privileges is raised. Debugging procA I locate the exception at the beginning of dbms_scheduler instructions (DBMS_SCHEDULER.PURGE_LOG(job_name=>jobName);).

EDIT: Add informations as requested in the comment

VERSION : Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

GRANT : system @ mydatabase

grant CREATE ANY JOB, CREATE EXTERNAL JOB , CREATE JOB to schemaA;

PROCEDURE : schemaA @ mydatabase 

procedure procA ...
...    
DBMS_SCHEDULER.PURGE_LOG(...);
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(...);
DBMS_SCHEDULER.SET_ATTRIBUTE(...);
DBMS_SCHEDULER.ENABLE(...);
...
end procA;

GRANT : schemaA @ mydatabase

grant execute on procA to schemaB

In procA there are others operations that are correctly executed until PURGE_LOG that raise the exception

AlexMI
  • 824
  • 1
  • 15
  • 36
  • Please post a sample of the procedure code you are using, a script of the exact privileges you granted, and the exact version of Oracle you are using. There are a number of things that could be an issue here. Also, DBMS_JOB is not part of Scheduler. – pmdba Apr 19 '20 at 11:37

1 Answers1

0

Inspetting others users I found that I miss one system privilege :

MANAGE SCHEDULER

Granting it from sys the procA run successfully both from schemaA and SchemaB

AlexMI
  • 824
  • 1
  • 15
  • 36