Blueshire Services
Scheduler FE
Setting Up a User To Run Specific Jobs
Table of Contents
Setting Up The Scheduler FE Config File
Appendix A - Description of the Restriction Codes
The scenario described is a familiar one. A user has a requirement to run a specific batch process. Currently this is achieved by phone calls to Customer Support who in turn contact the Scheduler Manager who is usually a DBA, to make a request for the job to be run. Because there are three contact points, the user, the Customer Support contact and the Scheduler Manager, a level of frustration is felt by the user due to the time lag between the requirement being identified and the specific job being run.
The problem from the Scheduler Manager's viewpoint is not that there is any objection to the specific job being run, it requires limited computer resources and has a minimal impact of other users, the problem is that to allow the user to run the job themselves would involve granting rights to that user that could be used to run other jobs that if run could impact on the organisation and the role of the Scheduling Manager is to prevent this. At the core of the problem is to how to restrict the user to run only the specified jobs.
The aim in the design and build of Scheduler FE was always more then a tool for the Scheduler Manager, it aimed to extend the restricted use of Oracle's scheduling module out to the user community. This article explains how to achieve this.
The first task in setting up this demonstration is to set up the user on the database which the Dba can accomplish by the following:-
SQL> CREATE USER JSMITH IDENTIFIED BY JSMITH
Then we grant permission to connect to the database with the following:-
SQL> GRANT CONNECT TO JSMITH
Now we set up the Jobs the user JSmith will be able to run. These are set up by the Scheduler Manager as the user JSmith has no rights on the Scheduler at this point in time.
Shown on the right is a screen shot of Scheduler FE with a Job being set up. Note that the Job Name is defined as "JSMITH.BATCH_MSA_JOB". So the Job "BATCH_MSA_JOB" is being set up in the user's JSmith schema.
Now we turn to the set up of the Scheduler FE for the user JSmith.
Setting Up The Scheduler FE Config File
It is the intention to load Scheduler FE onto the users PC, but before that is carried out we need to add the additional line entries shown below into the Scheduler FE config file.
07,2,2,No Creation of Jobs
07,2,3,No Dropping of Jobs
07,2,4,No Updating of Jobs
07,2,5,No Enabling of Jobs
07,2,6,No Disabling of Jobs
07,3,1,No Display of Programs
07,3,2,No Creation of Programs
07,4,1,No Display of Schedules
07,4,2,No Creation of Schedules
07,5,1,No Display of Job Class
07,5,2,No Creation of Job Class
07,6,1,No Display of Window
07,6,2,No Creation of Windows
07,7,1,No Display of Window Group
07,7,2,No Creation of Window Group
07,8,2,No Creation of Job Argument
07,8,3,No Dropping of Job Arguments
07,8,4,No Update of Job Arguments
07,9,2,No Creation of Program Argument
07,11,1,No Display of Chains
07,11,2,No Creation of Chains
07,12,2,No Creation of Chain Steps
07,13,2,No Creation of Chain Rules
07,15,1,No Display of Global Attributes
07,17,1,No Display of Credentials
07,17,2,No Creation of Credentials
07,20,10,No Permission to Purge Logs
The aim of these additional lines is to restrict the user JSMITH from all tasks except running and stopping the specific Jobs assigned to the user.
A copy of the new config file is here for download. It has been named SchedFileData_11gR1_No2.csv but will need to be renamed to SchedFileData.csv and saved in the same directory as the java executable, Scheduler.jar, before it can be read by the Scheduler FE application. This configuration file is for use with the Oracle 11g Release1 version of Scheduler FE.
We now are in the position of the user having been setup and the Scheduler FE having been loaded onto the users PC.
When the user JSMITH logs onto Scheduler FE in the standard way the screen shown on the right is the screen that the user can see. The user is now in the position to run the assigned jobs.
The additional entries in the config file have resulted in the user only being able to see the specific jobs that he/she has permission to run. All the additional objects that exist within Oracle's scheduling module, for instance, programs, schedules, windows, etc, are no longer visible. In addition there is no functionality to change the existing job or to create new jobs.
The functionality that the user has is to be able run the assigned jobs and to stop running the assigned jobs. No more. There are the additional screens that will display the jobs that have run. In the user JMITH as currently setup this screen will show instances of the job "BATCH_MSA_JOB" that have run.
This then will fulfill the users requirements to run themselves the assigned jobs and yet still satisfy the Scheduler Manager in that it does not give the user powers beyond the defined jobs.
One assumption has to be made and that is that it is assumed that the user does not have access to SQL-PLUS on his machine that would allow him/her to bypass the application security of Scheduler FE.
As a note of completeness there were two additional statements made by the Scheduler Manager to allow the job shown in the screen dump to run. These were as follows:-
SQL> GRANT EXECUTE ON PKG_SCHED_TEST TO JSMITH;
SQL> CREATE PUBLIC SYNONYM PKG_SCHED_TEST FOR COLIN.PKG_SCHED_TEST;
I hope you have found this article useful.
Appendix A - Description of the Restriction Codes
| Scheduler FE - Security Restriction Setup | ||
| Screen No | Option No | Description |
| 2 | 2 | No Permission to Creation New Job |
| 2 | 3 | No Permission to Drop Existing Jobs |
| 2 | 4 | No Permission to Update Existing Jobs |
| 2 | 5 | No Permission to Enable Existing Jobs |
| 2 | 6 | No Permission to Disable Existing Jobs |
| 3 | 1 | No Display of Existing Programs |
| 3 | 2 | No Permission to Create New Programs |
| 4 | 1 | No Display of Existing Schedules |
| 4 | 2 | No Permission to Create New Schedules |
| 5 | 1 | No Display of Job Classes |
| 5 | 2 | No Permission to Create New Job Classes |
| 6 | 1 | No Display of New Windows |
| 6 | 2 | No Permission to Create New Windows |
| 6 | 1 | No Display of New Windows |
| 6 | 2 | No Permission to Create New Windows |
| 6 | 1 | No Display of New Windows |
| 6 | 2 | No Permission to Create New Windows |
| 6 | 1 | No Display of New Windows |
| 7 | 1 | No Display of Window Groups |
| 7 | 1 | No Permission to Create New Window Groups |
| 8 | 2 | No Permission to Create New Job Arguments |
| 8 | 3 | No Permission to Drop Existing Job Arguments |
| 8 | 4 | No Permission to Update Existing Job Arguments |
| 9 | 2 | No Permission to Create New Program Arguments |
| 11 | 1 | No Display of Chains |
| 11 | 2 | No Permission to Create New Chains |
| 12 | 2 | No Permission to Create New Chain Steps |
| 13 | 2 | No Permission to Create New Chain Rules |
| 15 | 1 | No Display of Global Attributes |
| 17 | 1 | No Display of Credentials |
| 17 | 2 | No Permission to Create New Credentials |
| 20 | 10 | No Permission to Purge Job Logs |