Blueshire Services

Scheduler FE

Setting Up a User To Run Specific Jobs

 

 

Table of Contents

Introduction

Setting Up The User

Setting Up The Scheduler FE Config File

Putting It All Together

Appendix A - Description of the Restriction Codes

 

 

 

Introduction

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.

 

 

Setting Up The User

 

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.

 

 

 

Putting It All Together

 

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