Granting the EXECUTE on SYS.DBMS_JOB right to the database administrator

The Database Administrator user does not have the right to allow the Database User to execute jobs in the Oracle 11g and 12c database software.

To grant the Database User the right to execute jobs, run the following SQL commands as a Database Super User:

GRANT EXECUTE on SYS.DBMS_JOB to < Database Administrator user name > WITH GRANT OPTION;

COMMIT;

EXIT;

In the previous commands, Database Administrator user name can be System, or the user name you assigned to the Database Administrator user.

Due to a known issue in the 11.2.0.3 database software version, after you grant the right to execute jobs, you must revoke the right, and grant the right again. Run the following SQL commands as a Database Super User:

1. REVOKE EXECUTE on SYS.DBMS_JOB from < Database Administrator user name >

   COMMIT;

   EXIT;

2. GRANT EXECUTE on SYS.DBMS_JOB to < Database Administrator user name > WITH GRANT OPTION;

   COMMIT;

   EXIT;

   In the previous commands, Database Administrator user name can be System, or the user name you assigned to the Database Administrator user.

You can then run the following SQL commands as the Database Administrator user you referenced in the previous step to assign a user the right to execute jobs:

GRANT EXECUTE on SYS.DBMS_JOB to &1;

COMMIT;

EXIT;

Copyright © 2007, 2016 Oracle and/or its affiliates. All rights reserved.