Re: DBMS_JOB.SUBMIT bug?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 28 Aug 1999 14:45:16 GMT
Message-ID: <37d1f37e.15195660@newshost.us.oracle.com>

A copy of this was sent to "Andrew Woods" <awoods_at_one.net> (if that email address didn't require changing) On Fri, 27 Aug 1999 20:22:34 -0400, you wrote:

>I wrote a stored procedure called DBMS_JOB_SUBMIT which takes the same
>arguments as DBMS_JOB.SUBMIT, then calls DBMS_JOB.SUBMIT for me. This stored
>procedure is in the same package as a function called USER_JOBS_COUNT which
>returns to me the number of jobs currently in the USER_JOBS table for that
>user (user 'a' just for example). The package itself is owned by user 'a'.
>When I log in to sqlplus as user 'b' (who has been granted the right to
>execute the package) and call DBMS_JOB_SUBMIT, it puts a record into
>USER_JOBS like this:
>
>job: 1
>log_user: 'b'
>priv_user: 'a'
>...
>
>which seems to be fine (as I understand it the owner of the job is 'a'). The
>problem I'm having is when I call the USER_JOBS_COUNT function (still logged
>in as user 'b') to return the number of jobs, which in this example should
>be 1, but the function returns 0. If I log in as user 'a' and make the same
>function call, it returns the correct number, 1. I understand that user 'b'
>would only see the jobs he owns (and in that case 0 is correct), but since I
>am executing the count query via a stored procedure in a package owned by
>'a', shouldn't it return 1, since the query should actually be executing as
>the owner (user 'a') of the package (this is 8.0.5)?
>
>Am I missing something, or does this sound like a bug?
>

user_jobs is defined to return all of the jobs such that the priv_user is the same as the currently logged in user:

tkyte_at_8.0> select text from all_views where view_name = 'USER_JOBS';

TEXT

---

select j.* from dba_jobs j where j.priv_user = USER

This does seem to be contrary to the rest of the data dictionary USER_* views. for example:

tkyte_at_8.0> select text from all_views where view_name = 'USER_OBJECTS';

TEXT

---

select o.name, o.subname, o.obj#, o.dataobj#,

       decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
                      4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
                      7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
                      11, 'PACKAGE BODY', 12, 'TRIGGER',
                      13, 'TYPE', 14, 'TYPE BODY',
                      19, 'TABLE PARTITION', 20, 'INDEX PARTITION',
                      22, 'LIBRARY', 23, 'DIRECTORY', 'UNDEFINED'),
       o.ctime, o.mtime,
       to_char(o.stime, 'YYYY-MM-DD:HH24:MI:SS'),
       decode(o.status, 0, 'N/A', 1, 'VALID', 'INVALID'),
       decode(bitand(o.flags, 2), 0, 'N', 2, 'Y', 'N'),
       decode(bitand(o.flags, 4), 0, 'N', 4, 'Y', 'N')

from sys.obj$ o
where o.owner# = userenv('SCHEMAID')
  and o.linkname is null
  and (o.type# not in (1 /* INDEX - handled below */,

                      10 /* NON-EXISTENT */)
       or
       (o.type# = 1 and 1 = (select 1
                             from sys.ind$ i
                            where i.obj# = o.obj#
                              and i.type# in (1, 2, 3, 4, 6, 7))))

union all
select l.name, NULL, to_number(null), to_number(null),

       'DATABASE LINK',
       l.ctime, to_date(null), NULL, 'VALID', 'N', 'N'

from sys.link$ l
where l.owner# = userenv('SCHEMAID')

that uses the userenv('schemaid') which returns the 'effective userid' running the query. If I am in a definers rights procedure -- a query against user_objects would return the objects owned by the CREATOR of the procedure, not the person running it.

I filed a bug on it.

However, since you've now seen the view definition -- you can fix your issue by querying dba_jobs with the correct predicate instead of user_jobs.

Make sure you have SELECT on dba_jobs granted directly to you to create a procedure that references it (not via a role). to test if you can create a procedure that queries dba_jobs do this:

SQL> set role none;
SQL> select * from dba_jobs;

if that fails, it'll fail in a procedure create. get select on dba_jobs granted to your account directly.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sat Aug 28 1999 - 09:45:16 CDT