1

On my oracle database I am trying to figure out which osuser presents in sessions during running dbms_scheduler jobs. I can't check it myself, because in my company there is a trigger using triggering_event = 'LOGON', and simply not allowing me to start jobs in background, but i can add exceptions for osuser, but unfortunetelly i don't know which osuser is starting those jobs.

William Robertson
  • 15,273
  • 4
  • 38
  • 44
Potato
  • 172
  • 1
  • 12
  • Run `SELECT OSUSER FROM V$SESSION WHERE SID = SYS_CONTEXT('USERENV', 'SID')` within your job. I guess it is the `oracle` user. – Wernfried Domscheit Apr 28 '23 at 09:06
  • @WernfriedDomscheit the point is that i can not run any job through `DBMS_SCHEDULER` because my osuser is not on exception list. There are few users like SYS which LOGON trigger event dont include, but still i don't know how to get information about osuser's presence in sessions during `dbms_scheduler` job run. – Potato Apr 28 '23 at 09:12
  • Grant `ADMINISTER DATABASE TRIGGER` to your user, then exceptions in LOGON triggers are ignored. (thus having users like SYS or SYSTEM excluded, is not needed) – Wernfried Domscheit Apr 28 '23 at 09:53
  • 1
    The OS user always appears to be 'oracle'. However I couldn't find this documented so it's possible that it depends on some other setting. – William Robertson May 07 '23 at 09:55

1 Answers1

1

Quick test on my laptop db running 19c and Oracle Cloud 21c (results were the same):

begin
    dbms_scheduler.create_job
    ( job_name     => 'DEMO_JOB'
    , job_type     => 'PLSQL_BLOCK'
    , job_action   => q'[begin dbms_output.put_line('USER = '||user||', os_user = '||sys_context('userenv','os_user')); end;]' 
    , start_date   => systimestamp
    , enabled      => true );
end;

select output from user_scheduler_job_run_details where job_name = 'DEMO_JOB';

OUTPUT
--------------------------------------------------------------------------------
USER = WILLIAM, os_user = oracle

This is the same OS user (oracle) as I see on production databases. Database installation isn't really my area but I recall that you can set one or more OS users as the owners of the Oracle software and background processes, with 'oracle' being the default.

William Robertson
  • 15,273
  • 4
  • 38
  • 44