Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2002
    Posts
    162

    Unanswered: DBMS _JOB questions

    ORACLE 9i

    I have a dba user that owns a Package and a Proc. The proc schedules the package to be run as an ORACLE job. The proc is executed by regular users (they've been grante the appropriate execution rights) and not dba user.

    We now have to write another proc to remove or delete the job. We want only the regular users to be able to do this..........

    Now the Problem:

    when the regular users execute the proc and schedule the Package on the ORACLE job, only the dba user can view the status of the jobs in the all_jobs, dba_jobs or user_jobs views because the proc is owned by the dba user. The regular users cannot view the jobs that were scheduled under the dba user schema, remember the dba user owns both the package and the proc that schedules the package.

    How do I circumvent this problem so that other users can see the jobs scheduled.......without of course having to create these procs under the schema of the regular users???

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Write a third package, which is also owned by DBA user, which displays the jobs and GRANT EXECUTE on it to regular users.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2002
    Posts
    162

    Smile

    BUt to display it, the dba user has to pull the data from one of the jobs views (user, all or dba). this is fine and dandy but even when you grant execute on it to the other users, they can't see what is displayed because they dont have rights to the jobs tables in the dba user schema.

    I solved it by creating the proc that schedules the job in the first place under of the regular users schema, and also created the proc to remove the job under that users schema too. That way when the Job is scheduled it belongs to the regular user.

    unless of course you mean something else entirely

  4. #4
    Join Date
    Jan 2004
    Posts
    370
    Quote Originally Posted by Wale
    BUt to display it, the dba user has to pull the data from one of the jobs views (user, all or dba). this is fine and dandy but even when you grant execute on it to the other users, they can't see what is displayed because they dont have rights to the jobs tables in the dba user schema.
    When you grant execute on it to other users, they can't see what is displayed because the dba doesn't have EXPLICIT select on the views. Explicitly grant select on these views to the dba and the users can see them through the procedure. (Unless you are using Invoker Rights).

    Check out definer rights and invoker rights:

    http://download-west.oracle.com/docs...4privs.htm#943

  5. #5
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482
    Is that something, which you can do ???

    Code:
    SQL> connect sys/change_on_install as sysdba
    Connected.
    
    SQL> create user test identified by test;
    
    User created.
    
    SQL> grant connect to test;
    
    Grant succeeded.
    
    SQL> grant select on dba_jobs to test;
    
    Grant succeeded.
    
    
    
    SQL> connect test/test
    Connected.
    SQL> select * from dba_jobs;
    
    no rows selected
    
    SQL>

    HTH,

    clio_usa - 8/8i/9i OCP DBA

  6. #6
    Join Date
    Mar 2002
    Posts
    162
    It worked. I just Logged in as SYS and granted explicit rights on dba_jobs to the dba user. I then granted execute rights on the proc to the regular users (definers rights) and thruogh that they are able to see the dba_jobs view.

    Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •