I wanted to talk about a problem that I faced recently. Also I thought about the subject because this post is also related with “Session user vs Schema”. Whenever I use the term of “Job” I will be talking about Dbms_Scheduler Jobs not Dbms_Job!
When you created a job for a different user (if you have create any job privilege) session user and schema become important and might cause some headache. Let’s create 2 users and one of them privileged user (Admin_User) and the other is regular user (Regular_User).
if you realize I grant select on SYS.USER$ to regular_user and not to admin_user. even if admin_user is a dba user, it has no privilege to select on sys tables. so regular_user now has the privilege to select sys.user$ but not admin_user. I will use that table in my demo.
So admin_user will create a job for regular_user which means job will be created by admin_user but will run under the regular_user(job will belong to regular_user).
Let’s create some sample code to demonstrate.
this code is just for logging and I will log some information in the job.
My job code is:
if you run this code on Admin_User session then you will got an error: “ORA-00942: table or view does not exist” because Admin_User has no select privilege on Sys.User$ and if you run this code using Regular_User session, it will complete successfully and here is the log table content:
Now Let’s create jobs using Admin_User and see the result:
as expected job failed with table or view does not exists. Now admin_user will create a job for regular_user with same code (of course OWNER of job will be changed:
I added the Dbms_Lock.Sleep to examine the session of the job. after creating job, let’s check the session:
As you see, Job’s session user is ADMIN_USER not regular_user but schema name is REGULAR_USER and since it is running this means job didn’t get “table or view does not exist” error.
After completing job, I check the job log:
as you see job completed successfully. when we check our own log:
job has run under the ADMIN_USER user but run with REGULAR_USER privileges. This tells us two important things.
1- Privileges are inherited from SchemaName not UserName
2- Using USER function (or similarly Sys_Context(‘USERENV’, ‘SESSION_USER’) context variable) can cause problems. because since job is belong to REGULAR_USER you might think that job is running as REGULAR_USER but it is ADMIN_USER. so if you want to get job’s owner you must use Sys_Context(‘USERENV’, ‘CURRENT_SCHEMA‘) instead of USER function.