| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: running dbms_job on behalf of an other user
A copy of this was sent to sybrandb_at_my-dejanews.com
(if that email address didn't require changing)
On Tue, 20 Apr 1999 14:42:49 GMT, you wrote:
>Hi folks,
>
>Guess this will be about my first post here. Situation is this: Oracle 7.3.4
>on Solaris. Our system has it's own repository. No direct access is possible,
>everything has to be done through views (select) and
>packages(insert,update,delete). Currently, we have 10 'almost' identical
>copies of this system as 10 instances on one server (don't laugh, I didn't
>invent it). We want to get rid of those instances, but as we don't have the
>resources to sort out differences, there will be 10 identical schema's in one
>instance and many end-users. Those end-users have to use a pl/sql authorize
>procedure in order to make them self known to the system or the additional
>security will refuse them to select anything. Of course, this inserts a row
>in a session table. A job will remove, every hour, the dangling sessions
>(kind of own PMON, yes!). The authorize procedure checks for the existence of
>the job, and if it's not there, it submits it. Currently, I run into two
>related problems: 1 with multiple schema's there should be multiple jobs 2
>apparently it's not possible to submit a job on behalf of an other user. I
>tried to do that, I deliberately removed the job, and ran the authorize
>procedure as a non-schema owner. I verified dba_jobs afterwards, and it was
>there. log_user, priv_user, schema_user where all equal to the user running
>the authoize procedure, and this user doesn't own this procedure.
yes, that is correct. dbms_job runs with the entire environment of the user submitting the job (NLS settings, user, etc are all preserved). Since you are submitting a stored procedure to be executed tho, the procedure itself runs with the priveleges of the CREATOR not the current user (until Oracle8i that is when you get to pick how it runs -- as invoker or as definer).
> To my
>amazement the jobs all of a sudden disappeared, they obviously failed.
they didn't fail -- they succeeded. If you submit a job with a time to run and a 'what' and that job you submit runs without raising an exception, it is removed from the queue.
If the job failed due to an unhandled exception:
o a message to that effect would have been recorded in your alert log o the job would stay in the queue with the FAILURES column incremented. It would be retried later.
I think you need to add some debug to your jobs you are running in the background (eg: put an insert and commit at the end if it -- in the block that runs the job, if you want to run a procedure "foo", instead of submitting "foo;" as the thing to do submit a block like:
begin
insert into logtable ( 'hey, i am starting' );
commit;
foo;
insert into logtable ( 'hey, i am done' );
commit;
end;
so you can see it start, run and finish. something else is happening, the jobs wont just 'disappear' if they fail with an error (otoh -- they will just disappear if they run sucessfully and have no 'next' date).
> I
>couldn't see them as broken jobs, I couldn't see them at all! I can see two
>possible solutions, the first one completely undesirable and the other one
>less undesirable: 1 provided you have create any procedure privilege, create
>a procedure on the fly in the schema owner, and run that. This will contain
>the submit command 2 make this an administrative task: submit the procedures
>at boot time. I'm a conceptual dba in this situation, and I'm not responsible
>for the real techies. This will probably face some resistance.
>
>What should I do?
>Any comments are appreciated
>
>Sybrand Bakker, Oracle DBA
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |