Re: DBMS_JOB and ALL_* data dictionary views (Oracle 9i)

Funkung_at_yahoo.com wrote:
> Hi,
>
> I have been testing the procedure on a schema I know has broken
> triggers. I initially start the procedure by running it through
> SQLplus logged in as System. Everything runs okay.. I recieve the
> email and another run of the procedure is scheduled in the DBMS_JOB
> queue with LOG_USER, PRIV_USER and SCHEMA_USER set to SYSTEM. After x
> mins this runs, but I recieve no email and the procedure is not
> rescheduled into the queue!
>

[snip]
>
> Is this a bug in Oracle, or am I missing something? Has anyone got
> any alternative solutions? Are DBA_TRIGGERS and DBA_OBJECTS more
> reliable? Unfortunately, I cannot login as the SCHEMA and use
> USER_TRIGGERS and USER_OBJECTS.

This is not a bug, but a behaviour as described in the documentation.

If you run a procedure from a job the procedure has no privileges through roles but only privileges granted directly to the user.

In interactive mode the procedure gets the grants also through roles.

You have to assign the grants to access tables from a procedure directly to the user, not through a role.

Uwe Received on Fri Feb 23 2007 - 04:37:59 CST