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. |
and include the URL for the page.
