CODEWITHSUNDEEP
oracle-databasejobs
Revious
Revious

Reputation: 8156

Oracle: allow some JOBS to be controlled by every schema

I need to take offline and offline JOBS of schema B while I'm logged from schema A.

Can I reach this using some grants?

Upvotes: 2

Views: 4301

Answers (1)

APC
APC

Reputation: 146329

If you are using DBMS_JOB then your options are limited. According to the documentation:

"There are no database privileges associated with jobs. DBMS_JOB does not allow a user to touch any jobs except their own."

There is a workaround: DBMS_IJOB. This is undocumented, because it is owned by SYS and isn't supposed to be used by real people. You want to be careful with this. As it is undocumented we're reliant on unofficial sources. Adam Donahue wrote a useful blog on this package. Read it here.

Alternatively, you can build your own infrastructure: schema B owns a hand-rolled package to manage their jobs and grants EXECUTE on that package to schema A.

DBMS_SCHEDULER (introduced in 10g) offers a bit more flexibility. Well , actually a lot more flexibility, for which read "complexity". There are various ways to slice and dice responsibilties with scheduled jobs, and a matrix of privileges and roles. Find out more.

Out of the box there isn't a way to allow Schema A to manage all of Schema B's jobs. There are various ANY level privileges associated with DBMS_SCHEDULER but those are pretty powerful and cannot be restricted to rights on a single schema. At the crudest level B can simply do this:

 grant alter on bjob1 to A;
 grant alter on bjob2 to A;

and so on. This will allow A to do most actions on B's jobs except stopping a running job.

Upvotes: 3

Related Questions