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).

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;

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.

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;

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:

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;
/

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:

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

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:

--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"

--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:

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;
/

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:

Select Username, SchemaName, program from v$session where action = 'JOB_REGULAR';
USERNAME        SCHEMANAME      PROGRAM
-------------------------------------------------
ADMIN_USER	REGULAR_USER	ORACLE.EXE (J000)

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:

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

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:

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

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.

Leave a Reply Cancel reply