RE: How to best schedule jobs in another schema?

From: Kenneth Naim <kennaim_at_gmail.com>
Date: Fri, 12 Feb 2010 09:48:43 -0500
Message-ID: <01d201caabf2$7a8da920$6fa8fb60$_at_com>



Write a single wrapper procedure that creates a procedure in the other persons schema that submits the job, then execute said procedure and drop it. The user will need create any procedure and execute any procedure which are security holes themselves.

Ken

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Rich Jesse
Sent: Thursday, February 11, 2010 3:21 PM To: oracle-l_at_freelists.org
Subject: How to best schedule jobs in another schema?

Howdy,

Revisiting my post from 2008:

http://www.freelists.org/post/oracle-l/Scheduler-jobs-run-as-creator-instead
-of-owner

...I found a vague inference to a Scheduler job running as *creator* instead of *owner*:

http://download.oracle.com/docs/cd/B14117_01/server.101/b10739/scheduse.htm

Confusing and inconsistent with other Oracle security IMHO, but that's the way it works.

So how should one schedule jobs to run in another schema? The schemas I'm trying to run jobs in cannot be logged into for security purposes (have an invalid password). The only thing I can come up with is to write a DBMS_SCHEDULER wrapper package to be created in each schema, but this is a maintenance nightmare. Either that, or use a generic "execute immediate" proc in each schema, but the security implications of that should be obvious.

Anyone using DBMS_SCHEDULER to run jobs in another schema? How do you use it?

Thanks,
Rich

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Fri Feb 12 2010 - 08:48:43 CST

Original text of this message