I am using oracle database 19c. I need to grant scheduler job privilege for a specific schema in database.

Below privileges can be given to an user but i need to grant these privileges for the user to create job only for specific schema not whole database.

GRANT MANAGE SCHEDULER TO <user_or_role>;
GRANT CREATE JOB TO <user_or_role>;
GRANT CREATE PROGRAM TO <user_or_role>;
GRANT CREATE SCHEDULE TO <user_or_role>;

Thanks in advance

I am using oracle database 19c. I need to grant scheduler job privilege for a specific schema in database.

Below privileges can be given to an user but i need to grant these privileges for the user to create job only for specific schema not whole database.

GRANT MANAGE SCHEDULER TO <user_or_role>;
GRANT CREATE JOB TO <user_or_role>;
GRANT CREATE PROGRAM TO <user_or_role>;
GRANT CREATE SCHEDULE TO <user_or_role>;

Thanks in advance

• oracle-database

1 Answer 1

The grants you have already are sufficient to permit a user to create and manage their own jobs. They don't provide privs to other schemas. I would, however, suggest dropping "MANAGE SCHEDULER", as that's typically for DBAs only.

If, however, what you want is for user A to create jobs under user B, well, there is no privilege that permits that (as of 19c) without giving privs across all schemas. You can, however, create a procedure owned by the job owner (user B) and give exec privs to user A to call that procedure. That procedure can submit jobs.

Example:

As User A:

create table user_a.testtable (col1 integer);

CREATE OR REPLACE PROCEDURE user_a.p_create_job(job_name IN varchar2, job_type IN varchar2, job_action IN varchar2)
AS
BEGIN
   dbms_scheduler.create_job(job_name => job_name,
                             job_type => job_type,
                             job_action => job_action,
                             enabled => true);
END;

grant execute on user_a.p_create_job to user_b;

As User B:

 BEGIN
   user_a.p_create_job(job_name => 'TEST_JOB',
                       job_type => 'PLSQL_BLOCK',
                       job_action => 'BEGIN EXECUTE IMMEDIATE ''DROP TABLE testtable''; END;');
 END;  
 

User B created a job owned by User A which was able to drop User A's table. This is rudimentary; obviously depending on how flexible you need it you'd need to add more parameters, but this demonstrates the concept.

However, the fact that this requires a workaround suggests that it is using the scheduler in a way that Oracle didn't intend. That should at least raise a flag as to whether our approach is the correct one. Jobs really should be managed by their owners, not by others.