running dbms_job on behalf of an other user

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. To my amazement the jobs all of a sudden disappeared, they obviously failed. 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 Received on Tue Apr 20 1999 - 09:42:49 CDT