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
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.