Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

Oracle Scheduling Security

Oracle Tips by Burleson Consulting

Security

Chapter 2 of this text outlined roles and privileges associated with the scheduler, which form the basis of the security mechanism.  All users that need to schedule jobs should be granted the CREATE JOB privilege, while users that need to administer the scheduler should be granted the MANAGE SCHEDULER privilege.  Granting excessive privileges can be dangerous, in terms of security risks, as users may be able to run code as different users.  Granting the CREATE ANY JOB privilege enables a user to schedule a job as the SYSTEM user, which effectively gives them unlimited privileges.  In addition to running commands, the user could grant to themself any extra privileges using a method like the one listed below.

-- Check the current roles assigned to JOB_USER.
conn sys/password as sysdba
select
   granted_role
from
   user_role_privs
where
   username = 'JOB_USER';

GRANTED_ROLE
------------------------------
CONNECT
SELECT_CATALOG_ROLE

2 rows selected.

-- Grant excessive privileges to JOB_USER by accident.
grant create any job to job_user;

-- JOB_USER exploits the extra privileges by creating a job
-- as the SYSTEM user to grant the DBA role to JOB_USER.
conn job_user/job_user

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'system.dangerous_job',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN EXECUTE IMMEDIATE ''GRANT DBA TO job_user''; END;',
    start_date      => SYSTIMESTAMP,
    enabled         => TRUE);
END;
/

-- Check the current roles assigned to JOB_USER.
-- Notice that the DBA role is present.
select
   granted_role
from
   user_role_privs
where
   username = 'JOB_USER';

GRANTED_ROLE
------------------------------
CONNECT
DBA
SELECT_CATALOG_ROLE


3 rows selected.

The scheduler allows external jobs to be run using the executable program_type or job_type, which may introduce a whole set of security issues.  If a privileged operating system user runs external jobs, any database user with the CREATE JOB privilege has the ability to run operating system commands and scripts as that privileged user.  Needless to say, this can quickly become a security nightmare.

Depending on the operating system, there may be some extra post installation steps required to configure jobs to run as a low-privileged guest user.

The safest option is not to allow any users except the DBAs to create jobs.  Not only does this limit any potential security holes, but it also prevents users from scheduling resource intensive and inefficient jobs without understanding the consequences.

Over the last few years, the number of security flaws identified in software packages has risen dramatically to the point where the identification of new flaws is almost a daily occurrence.  Unfortunately, Oracle software is not immune to this problem, so every effort should be made to keep on top of the latest security advisories and patches.  This approach should dramatically reduce the amount of security holes in the system.

With security issues addressed, the following section will introduce the methods available for setting scheduler object-specific attributes.

Setting Scheduler Object Attributes

Many scheduler object attributes can be amended after object creation using the set_attribute overloaded procedures.  Enabled objects are typically disabled, amended and then re-enabled.  Problems during this process result in the object being left in a disabled state, which results in the generation of an error.  Objects that are already disabled remain disabled after they are altered.

Using the set_attribute procedure against job classes, windows and window groups requires the MANAGE SCHEDULER privilege.

In most cases, alterations to objects do not affect the currently running instance of that object.  For example, altering a window definition will not affect the currently open window, but its effect will be seen the next time that window opens.

The following tables show which attributes can be altered for each object type.  Many of the attributes can be set during object creation, but some can only be altered subsequently.

Table 6.1 below lists the associated job attributes and their descriptions.

ATTRIBUTE

DESCRIPTION

logging_level

The amount of logging that should be done for this job, specified by the constants logging_off, logging_runs and logging_full.

restartable

Specifies whether a job can be restarted in the event of a failure.  If set to TRUE, a failure will result in the job being retried up to six times.  The run and failure counts are not incremented as part of failure retries.  The scheduler waits for one second after a failure before a retry.  After each successive failure, the delay is increased by a factor of 10.  The retries end when a retry is successful, the sixth retry fails or the run date of the next retry is later than the next scheduled ?proper? run date.

max_failures

Specifies the number of consecutive failures after which the job is disabled.  Valid values range from one to 1,000,000 with the default value being NULL, which implies no limit.

max_runs

Specifies the maximum number of times the job can run, after which it is disabled and its status is set to COMPLETED.

job_weight

Specifies the degree of parallelism with valid values ranging from one to 100, with the default value being one.

instance_stickiness

Specifies if the job should attempt to run on the same instance in a RAC environment.  Typically, the server with the lightest load will run a job.  If this parameter is set to TRUE, all subsequent runs will occur on the same RAC node unless it is unavailable or extremely overloaded.

stop_on_window_close

Specifies whether the job should be forced to stop when the open window which specifies its schedule closes.  If set to FALSE, the job will continue to run after the window closes, but its resource allocation may change.

job_priority

Specifies the priority in which the jobs should be executed by the scheduler if multiple jobs share the same run date. The default value is three, with valid values ranging from one to five.

schedule_limit

Specifies the maximum delay allowable between the proposed run date and the actual run date, after which the scheduled run will be skipped.  On a heavily loaded system there may be some delay before a job is executed.  This parameter allows an upper limit to be set, between one minute and 99 days, after which the job run is deemed not desirable and can skipped until the next scheduled job run.  A value of NULL implies the job cab be run regardless of any delay.  When a job run is skipped, it has no effect on the run or failure counts, but the event is logged.

program_name

The name of the program which defines the action of the job.  This parameter and the inline program definition attributes (job_action, job_type and number_of_arguments) are mutually exclusive.

job_action

The actual work that is done by the job.

job_type

The type of action associated with this job (plsql_block, stored_procedure or executable).

number_of_arguments

The number of arguments required by this job.  Programs that use arguments, must have their arguments defines before they can be enabled.

schedule_name

The name of the schedule, window or window group used to define the job schedule.  This parameter and the inline schedule attributes (start_date, end_date and repeat_interval) are mutually exclusive.

repeat_interval

The definition of how often the job should execute.  A value of NULL indicates that the job should only run once. The repeat interval is defined using a PL/SQL expression or the calendaring syntax, which is new to Oracle10g.

start_date

The date when this schedule will take effect.  This may be in the future scheduled jobs are being set up in advance.

end_date

The date when this schedule will stop.  This combined with the start_date parameter enables a job to be scheduled for a finite period of time.

job_class

The job class associated with this job.  If no job_class is defined, the default_job_class is assigned.

auto_drop

Indicates if the job should be dropped once it has run for the last time.

comments

Free text, allowing additional information to be recorded.

Table 6.1 ? Job attributes and descriptions

Table 6.2 below lists the associated program attributes and their descriptions.

ATTRIBUTE

DESCRIPTION

program_action

The actual work that is done by the program.

program_type

The type of action associated with this program (plsql_block, stored_procedure or executable).

number_of_arguments

The number of arguments required by this program.  Programs using arguments must have their arguments defined before they can be enabled.

comments

Free text, allowing additional information to be recorded.

Table 6.2 ? Program attributes and descriptions

Table 6.3 below lists the associated schedule attributes and their descriptions.

ATTRIBUTE

DESCRIPTION

repeat_interval

The definition of how often the job should execute.  A value of NULL indicates that the job should only run once. The repeat interval is defined using a calendaring syntax, which is new to Oracle10g. 

start_date

The date when this schedule will take effect.  This may be in the future if scheduled jobs are being set up in advance.

end_date

The date when this schedule will stop.  This combined with the start_date parameter enables a job to be scheduled for a finite period of time.

comments

Free text, allowing additional information to be recorded.

Table 6.3 ? Schedule attributes and descriptions

Table 6.4 below lists the associated job class attributes and their descriptions.

ATTRIBUTE

DESCRIPTION

resource_consumer_group

The resource consumer group associated with the job class.

service

The service database object the job belongs to, not the tnsnames.ora service.

logging_level

The amount of logging that should be done for this job, specified by the constants logging_off, logging_runs and logging_full.

log_history

The number of days the logging information is kept before purging.

comments

Free text, allowing additional information to be recorded.

Table 6.4 ? Job class attributes and descriptions

Table 6.5 below lists the associated window attributes and their descriptions.

ATTRIBUTE

DESCRIPTION

resource_plan

The resource plan associated with the window.  When the window opens, the system switches to use the associated resource plan.  When the window closes, the system switches back to the previous resource plan.

window_priority

The priority ('LOW' or 'HIGH') of the window.  In the even of multiple windows opening at the same time windows with a high priority take precedence over windows with a low priority, which is the default.

duration

The length of time in minutes the window should remain open.

schedule_name

The date after which the scheduler starts to return valid run dates.

repeat_interval

The date after which the scheduler starts to return valid run dates.

start_date

The date when this window will take effect.  This may be in the future, if you are setting up window in advance.

end_date

The date when this window will stop.  This combined with the start_date parameter enables a window to be scheduled for a finite period of time.

comments

Free text, allowing additional information to be recorded.

Table 6.5 ? Window attributes and descriptions

Table 6.6 below lists the associated window group attributes and their descriptions.

ATTRIBUTE

DESCRIPTION

Comments

Free text, allowing additional information to be recorded.

Table 6.6 ? Window group attributes and descriptions

Familiarity with these attributes and their descriptions will assist the properly authorized user in amending object attributes using the set_attribute overloaded procedures. 

 

This is an excerpt from the book "Oracle Job Scheduling" by Dr. Tim Hall.

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle job scheduling scripts.


 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational