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).
|
1 2 3 4 5 6 7 8 9 10 11 |
sqlplus / as sysdba create user admin_user identified by admin_user; grant dba to admin_user; grant create any job to admin_user; grant execute on dbms_lock to admin_user; create user regular_user identified by regular_user; grant connect to regular_user ; grant create job to regular_user ; grant execute on dbms_lock to regular_user ; grant select on sys.user$ to 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Conn admin_user/admin_user create table admin_user.log(mydata varchar2(4000), time timestamp); create or replace procedure admin_user.sp_log(p_data varchar2) as pragma autonomous_Transaction; begin insert into admin_user.log values (p_data, systimestamp); commit; end; / Grant select on admin_user.log to public; Grant execute on admin_user.sp_log to public; |
this code is just for logging and I will log some information in the job.
My job code is:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
declare x_count number; begin Dbms_lock.Sleep(20); --wait 20 seconds admin_user.sp_log('User: ' || User); -- add current user name to log Select count(*) Into x_count From sys.user$; admin_user.sp_log('Number of user$: ' || x_count); -- add Number of user$ end; / |
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:
|
1 2 3 4 5 |
select * from admin_user.log order by time; MYDATA TIME ------------------------------------------- User: REGULAR_USER 17/03/2020 11.50.29,732000000 Number of user$: 133 17/03/2020 11.50.29,734000000 |
Now Let’s create jobs using Admin_User and see the result:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
--First For the Admin_User Begin Dbms_Scheduler.Create_Job(Job_Name => 'ADMIN_USER.JOB_ADMIN', Job_Action => q'!declare x_count number; begin Dbms_lock.Sleep(20); --wait 20 seconds admin_user.sp_log('User: ' || User); -- add current user name to log Select count(*) Into x_count From sys.user$; admin_user.sp_log('Number of user$: ' || x_count); -- add Number of user$ end;!', Job_Type => 'PLSQL_BLOCK', enabled=> true); End; / select owner, Job_Name, Status, Additional_Info from dba_scheduler_job_run_details Where Job_Name = 'JOB_ADMIN' order by log_Date desc; OWNER JOB_NAME STATUS ADDITIONAL_INFO ------------------------------------------------------- ADMIN_USER JOB_ADMIN FAILED "...table or view does not exist" |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
Conn admin_user/admin_user truncate table admin_user.log; Begin Dbms_Scheduler.Create_Job(Job_Name => 'REGULAR_USER.JOB_REGULAR', Job_Action => q'!declare x_count number; begin Dbms_lock.Sleep(20); --wait 20 seconds admin_user.sp_log('User: ' || User); -- add current user name to log Select count(*) Into x_count From sys.user$; admin_user.sp_log('Number of user$: ' || x_count); -- add Number of user$ end;!', Job_Type => 'PLSQL_BLOCK', enabled=> true); End; / |
I added the Dbms_Lock.Sleep to examine the session of the job. after creating job, let’s check the session:
|
1 2 3 4 |
Select Username, SchemaName, program from v$session where action = 'JOB_REGULAR'; USERNAME SCHEMANAME PROGRAM ------------------------------------------------- ADMIN_USER REGULAR_USER ORACLE.EXE (J000) |
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:
|
1 2 3 4 5 6 7 8 |
select OWNER, JOB_NAME, STATUS, ADDITIONAL_INFO from dba_scheduler_job_run_details Where Job_Name = 'JOB_REGULAR' order by log_Date desc; OWNER JOB_NAME STATUS ADDITIONAL_INFO -------------------------------------------------------------------------- REGULAR_USER JOB_REGULAR SUCCEEDED Null |
as you see job completed successfully. when we check our own log:
|
1 2 3 4 5 |
select * from admin_user.log order by time; MYDATA TIME ------------------------------------------- User: ADMIN_USER 17/03/2020 12.11.06,387000000 Number of user$: 133 17/03/2020 12.11.06,387000000 |
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.