- \n
- The Job Owner<\/strong>\u00a0\u2013\u00a0“OWNER” column of all_scheduler_jobs.<\/em> view<\/li>\n
- The Job Creator<\/strong>\u00a0\u2013 “JOB_CREATOR” column of all_scheduler_jobs.<\/em> view<\/li>\n
- The Job latest “Modifier”<\/strong>\u00a0\u2013 This information is not present in all_scheduler_jobs<\/em> view and must be obtained through queries on dictionary tables.<\/li>\n<\/ul>\n
The attribution of each role is what we will see next.<\/p>\n
1 \u2013 The Job Owner<\/strong><\/h3>\n
The job owner is the schema informed within the JOB_NAME parameter when creating a Job. If omitted, the owner will be the same user who\u00a0executed the CREATE_JOB procedure. In the next example, the owner is “SCOTT” user.<\/p>\n
BEGIN\r\n DBMS_SCHEDULER.CREATE_JOB(JOB_NAME => 'SCOTT.JOB1', ..........);\r\nEND;\r\n\/<\/pre>\n
The owner will also be the executor<\/u><\/strong>. If the task runs a procedure or PL\/SQL, this user must therefore be allowed<\/u> to execute the procedure or privileges on the objects accessed by PL\/SQL code. Otherwise, you will receive an error such as “ORA-00942: table or view does not exist<\/em>“.<\/p>\n
In case of executing\u00a0a PL\/SQL, the environment variables CURRENT_USER<\/em> (user whose privileges are currently active) and CURRENT_SCHEMA<\/em> (active default schema at the time) will obviously be the job owner because, as already mentioned, he is the executor.<\/p>\n
However, in case of running\u00a0a Procedure, the variables CURRENT_USER<\/em> and CURRENT_SCHEMA<\/em> show the owner of the Procedure since\u00a0when it is called, it executes with the privileges of its owner (except if it has “AUTHID CURRENT_USER” and in this case would behave similar to a PL\/SQL explained above).<\/p>\n
And what happens if we lock the user who owns the job? In this scenario, nothing interferes and the task will continue to run\u00a0on the scheduled time\u00a0without any problems. However, if the owner is removed, the scheduled job will also be deleted.<\/p>\n
2 \u2013 The Job Creator<\/strong><\/h3>\n
The job creator is the schema that runs the procedure DBMS_SCHEDULER.CREATE_JOB. If you create a job to another user, you will be defined\u00a0as the creator<\/u><\/strong>\u00a0of that job.<\/p>\n
NOTE: Remember that to create a job in another schema you must have the privilege “CREATE ANY JOB.”<\/em><\/p>\n
The job creator is the user that will connect to the database before running it, despite the executor be the owner. Therefore, the environment variable SESSION_USER always points to the creator. However, those who create do not need any privileges on the objects involved and executed by the job.<\/p>\n
During the execution of the scheduled job, if we query the column “USERNAME” from v$session, it returns the job creator user (as he is the one who logged in).<\/p>\n
In the latest example, if the SCOTT.JOB1 job was created by INFRAUSR, this would be the output of the v$session<\/strong> while the task was running:<\/p>\n
SQL> select username,schemaname,status,program,type,module,action from v$session where module='DBMS_SCHEDULER';\r\n \r\nUSERNAME SCHEMANAME STATUS PROGRAM TYPE MODULE ACTION\r\n-------------- --------------- -------- ---------------------------- ---------- ------------------- --------\r\nINFRAUSR SCOTT ACTIVE oracle@localhost (J000) USER DBMS_SCHEDULER JOB1\r\n \r\nSQL><\/pre>\n
Despite the user who created be used to log in, if it is locked (or revoked the\u00a0CREATE SESSION privilege), the job will continue to run normally. This is because the\u00a0internal behind the\u00a0sign in for the execution of a job is different. Be aware that, even so, jobs trigger “BEFORE LOGIN” actions.<\/p>\n
And what if we delete the user who created the job? In this situation, the job isn’t<\/strong> deleted, only disabled. When trying to activate it, the job status in all_scheduler_job_logs will be changed to BROKEN. You will need to recreate it to make it work again.<\/p>\n
3 \u2013\u00a0The Job latest “Modifier”<\/strong><\/h3>\n
What a few people know is that Oracle maintains in its records the user who changed a\u00a0job for the last time. This information is important only if the database has “Database Vault” feature enabled\u00a0and when the job is also protected by a Realm.<\/p>\n
This happens because the last one\u00a0who changes a particular task is monitored by Oracle Database Vault and only jobs modified by users who have a special privilege on a separated job layer will be authorized to run.<\/p>\n
What is the reason for this protection?<\/strong> A user with the “CREATE ANY JOB” privilege could easily compromise the DB or explore\u00a0a privilege escalation by creating\/changing a job from another user who is DBA making that process do whatever he wanted.<\/p>\n
Example:<\/strong><\/p>\n
BEGIN\r\n DBMS_SCHEDULER.CREATE_JOB(\r\n JOB_NAME => 'SCOTT.JOB_TEMP',\r\n JOB_TYPE => 'PLSQL_BLOCK',\r\n JOB_ACTION => q'[BEGIN EXECUTE IMMEDIATE 'GRANT SELECT ON SCOTT.EMP TO MYUSER'; END;]',\r\n ENABLED => TRUE);\r\nEND;\r\n\/ \r\n<\/pre>\n
With Oracle Database Vault feature, this would not be possible.<\/p>\n
To get the last user who changed a job, we need to query the “RUN_INVOKER<\/strong>” column on SYS.SCHEDULER$_JOB<\/strong> table:<\/p>\n
SQL> col job_creator format a25\r\nSQL> col job_owner format a25\r\nSQL> col job_name format a25\r\nSQL> col perms_check_user format a25\r\n \r\nSQL> SELECT JU.NAME \"JOB_OWNER\",\r\n 2 JO.NAME \"JOB_NAME\",\r\n 3 J.CREATOR \"JOB_CREATOR\",\r\n 4 JR.NAME \"JOB_MODIFIER\"\r\n 5 FROM SYS.SCHEDULER$_JOB J,\r\n 6 SYS.USER$ JU,\r\n 7 SYS.USER$ JR,\r\n 8 SYS.OBJ$ JO\r\n 9 WHERE J.RUN_INVOKER = JR.USER# (+)\r\n 10 AND J.OBJ# = JO.OBJ#\r\n 11 AND JO.OWNER# = JU.USER#\r\n 12 AND JO.NAME = 'JOB_TEST';\r\n \r\nJOB_OWNER JOB_NAME JOB_CREATOR JOB_MODIFIER\r\n------------------------- ------------------------- ------------------------- -------------------------\r\nSCOTT JOB_TEST INFRAUSR OPERUSR<\/pre>\n
In the example above, the job was last modified by OPERUSR. This means that if the OPERUSR has no privileges on DB Vault to run it, the job will display the error upon execution:<\/p>\n
ORA-12012: error on auto execute of job \"SCOTT\".\"JOB_TEST\"\r\nORA-01031: insufficient privileges\r\nORA-06512: at line 2<\/pre>\n
This error has nothing to do with the permissions on the objects involved, because privileges on those\u00a0objects, as already said, who needs to have is the job owner, which in this case is SCOTT.<\/p>\n
So what privilege it is necessary?<\/strong> Only the simple authorization\u00a0that this job will be allowed\u00a0to run for the user who last modified it.<\/p>\n
In the Database, logged in as a DV Owner account, this authorization may be granted\/consulted by running:<\/p>\n
SQL> EXEC DVSYS.DBMS_MACADM.AUTHORIZE_SCHEDULER_USER(UNAME => 'OPERUSR', SNAME => 'SCOTT');\r\n \r\nPL\/SQL procedure successfully completed.\r\n \r\nSQL> col grantee format a30\r\nSQL> col schema format a30\r\nSQL> select * from dvsys.dba_dv_job_auth;\r\n \r\nGRANTEE SCHEMA\r\n------------------------------ ------------------------------\r\nOPERUSR SCOTT<\/pre>\n
Lastly, as we saw in this article, a job may be more complex than it seems. If you are having problems privilege in any of your scheduled tasks, be sure to check what is the role of each user who is participating and that all have the appropriate privileges.<\/p>\n
<\/p>\nHave you enjoyed? Please leave a comment or give a \ud83d\udc4d!<\/b>\n
- The Job Creator<\/strong>\u00a0\u2013 “JOB_CREATOR” column of all_scheduler_jobs.<\/em> view<\/li>\n
