Oracle : Is there a way to grant the ability to run a job to a role?

Question

We have several dbms_scheduler jobs in an 11.2 database that we would like users with a certain role to be able to run. As such, we don't want to grant the ability to run directly to the users, as it will be more difficult to maintain.

Based on Oracle's documentation, granting ALTER JOB to the user will allow them to run the job. Unfortunately, it doesn't appear this extends to roles: I can run the GRANT statement just fine, and the job shows up in ALL_SCHEDULER_JOBS for the user, but attempting to run the job results in the following error:

ORA-27476: "S1.J1" does not exist
ORA-06512: at "SYS.DBMS_ISCHED", line 185
ORA-06512: at "SYS.DBMS_SCHEDULER", line 486
ORA-06512: at line 1

Given that DBMS_SCHEDULER is AUTHID USER, but DBMS_ISCHED is AUTHID DEFINER, am I just out of luck? Or is there a way I can grant it to the role and not the user?

Answer 1

This sounds like the usual problem with stored procedures: when they are compiled by developer A in his schema, the compiler only considers the privileges DIRECTLY granted to A - as if the roles were all stripped off before compiling. This is often addressed by writing your procedure as AUTHID CURRENT_USER, but if the procedure calls another procedure granted through a role, it can fail.

Generally it seems anything you want to compile into a PL/SQL object has to be directly granted to you. I've written a number of scripts to turn role-granted privileges into direct GRANT statements to the procedure owner so that it would compile.

Note this does not apply to anonymous blocks - they compile and run just fine with the benefit of your current roles' privileges.

So I think the scheduler is another case where you have to code as if the roles weren't there. It's a pain and I hate it, but that's how it is.