Forum Stats

  • 3,784,147 Users
  • 2,254,897 Discussions
  • 7,880,711 Comments

Discussions

Creating a Scheduler job for another user

Laury
Laury Member Posts: 1,657 Silver Badge

Hi,

I am working with Oracle RDBMS 19c.

I can create an Oracle Scheduler job for another session when at session level I set current_schema=<username>.

If I well understood, this alter session command places a user in a different schema making it easier to access the objects in this schema without having to qualify the object with the actual schema name. 

This method does not change the current user and also does not give any privileges that this new schema has.

This is fine if as user SYSTEM I want to create a job for <username>.

Does someone know if there is a way to achieve this result without gving a password?

Kind Regards

Tagged:

Best Answer

Answers

  • EdStevens
    EdStevens Member Posts: 28,622 Gold Crown

    What do you mean by "without giving a password"?

  • GregV
    GregV Member Posts: 3,077 Gold Crown

    Hi,

    To create a job in a different schema you need the CREATE ANY JOB privilege. I understand you don't want people to use SYSTEM to do so (thus giving away SYSTEM's password).

    Well, as with other powerful privileges usually you create a procedure in a schema that has the "xxx ANY xxx" privilege and grant execute on that procedure to the user for whom you want to allow the action.

    Something like, in SYSTEM like schema's:

    procedure create_job_in_different_schema(p_schema_name in varchar2, p_job_name in varchar2, .....)
    is
    begin
    dbms_scheduler.create_job(job_name => schema_name || '.' || p_job_name,
                              job_type => .....
                                    ....
                             );
    end;
    


    Then

    grant execute on create_job_in_different_schema to your_user;

  • Laury
    Laury Member Posts: 1,657 Silver Badge

    @EdStevens:

    With "without giving a password"? I mean not given explicitly the password in any PL/SQl block.

    It is possible to define credential for the particular OS not for the Database:

    BEGIN

      dbms_scheduler.create_credential(

        username => 'OS Username',

        password => 'OS password',

        credential_name => 'MY_CREDENTIAL'

      );

    END; 

    then:

     dbms_scheduler.create_job(

       job_name => 'JobName',

       job_type => 'stored_procedure',

       job_action => 'StoredPrcedure',

       credential_name => 'MY_CREDENTIAL',

       destination_name => NULL);


    @GregV:

    This is probably a better approach as no passowrd is needed.

    But when created in a given schema I guess that the one who can conect to that user has full privileges on that job. Am I right?

    Kind Regards.

  • GregV
    GregV Member Posts: 3,077 Gold Crown

    Hi,

    Credentials are only needed for external jobs (jobs that run something on the OS server).


    But when created in a given schema I guess that the one who can conect to that user has full privileges on that job. Am I right?

    Say the procedure is in DBA_USER, and you grant execute on that procedure to DEV_USER. So DEV_USER will be able to create a job in a different schema, but won't have any specific privilege on that job. If you need DEV_USER to have privileges on that job, add the necessary grants in the same procedure. For example if you want DEV_USER to be able to run the job, you need to grant the ALTER JOB privilege on the job to DEV_USER. If DEV_USER creates a job in their own schema, then by default they will have all privileges on that job.

  • Laury
    Laury Member Posts: 1,657 Silver Badge

    @GregV:

    Yes, although I didn't mention it explicitly, when creates a job in a given schema, that user has full privileges on that job. He needs to be able to create that job, to modify it, and to drop it.

    So, I guess if I want to create a job for a given user (assuming that this user can run the procedure or packaged procedure), I would need the CREATE ANY JOB, and that's all. Am I right?

     

  • GregV
    GregV Member Posts: 3,077 Gold Crown
    Accepted Answer

    Yes if you have the CREATE ANY JOB privilege you'll be able to create/modify/drop a job in another schema.