PL/SQL Documentation privilege

This documentation contains info on how to manipulate scheduler privileges with PL/SQL.

Back to top 

Edit user privileges

All scheduler user privileges are granted or revoked with the procedure SO_USER.SET_USER_PRIVILEGES.

The procedure SO_USER.SET_USER_PRIVILEGES also registers a scheduler user if the user is not registered yet.

Give a procedure parameter the boolean value TRUE if the privilege must be set, FALSE otherwise. If no value is passed to a parameter (or the value NULL the privilege is not changed.
For new scheduler users the default value for each privilege is false.

procedure set_user_privileges(
    p_user_name                  in varchar2,
    p_user_administrator         in boolean default null,
    p_user_developer             in boolean default null,
    p_user_log                   in boolean default null,
    p_user_read                  in boolean default null,
    p_user_disabled              in boolean default null,
    p_user_blocked               in boolean default null,
    p_schedule_flow              in boolean default null,
    p_schedule_days_of_the_week  in boolean default null,
    p_schedule_months            in boolean default null,
    p_schedule_days_of_the_month in boolean default null,
    p_schedule_weeks             in boolean default null,
    p_schedule_repeat            in boolean default null,
    p_schedule_overtime          in boolean default null,
    p_schedule_trace             in boolean default null,
    p_schedule_dbms_job          in boolean default null,
    p_schedule_precreate         in boolean default null);
  
Parameter Description
p_user_name The name of the scheduler user.
The name is not case sensitive.
p_user_administrator If set the scheduler user has the administrator privilege.
p_user_developer If set the scheduler user can develop scheduler scripts.
p_user_log If set the scheduler user can view the scheduler server log.
p_user_read If set the scheduler user only has read access .
p_user_disabled If set the scheduler user is disabled.
p_user_blocked If set the scheduler user is blocked.
p_schedule_flow If set the scheduler user can create a flow.
p_schedule_days_of_the_week If set the scheduler user can specify if a scheduler task must be scheduled on a specific day of the week.
p_schedule_months If set the scheduler user can specify if a scheduler task must be scheduled in a specific month.
p_schedule_days_of_the_month If set the scheduler user can specify if a scheduler task must be scheduled on a specific day of the month.
p_schedule_weeks If set the scheduler user can specify if a scheduler task must be scheduled in a specific week of the year.
p_schedule_repeat If set the scheduler user can specify if a scheduler task must be repeated.
p_schedule_overtime If set the scheduler user can create for a scheduler task an overtime period.
p_schedule_trace If set the scheduler user can enable the scheduler trace for a scheduler task.
p_schedule_dbms_job If set the scheduler user can specify if a scheduler task must be restarted if the oracle server process executing the task fails.
p_schedule_precreate If set the scheduler user can specify for new scheduler tasks who are scheduled more than once that they are all created immediate.
Error Description
OSC-10013
USER "%1" IS NOT REGISTERED
OSC-10020
SCHEDULER USER "%1" HAS READ ACCESS
OSC-10029
ORACLE USER "%1" DOES NOT EXIST
OSC-10030
SCHEDULER USER "%1" HAS NO ADMINISTRATOR PRIVILEGES
OSC-10081
SCHEDULER USER "%1" CANNOT CHANGE HIS OWN PRIVILEGES

If a scheduler user (for example PLSQL1) needs to be blocked the next command should be used.

begin
  so_user.set_user_privileges(
      p_user_name=>'PLSQL1',
      p_user_blocked=>true);
end;
/
  
Back to top