Using “User” function in a scheduled Job

Hi there,

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.

Leave a Reply

Your email address will not be published. Required fields are marked *