DDL/DML commands executed in Dbms_scheduler job Is Not Audited (Doc ID 2577855.1)

Last updated on FEBRUARY 04, 2020

Applies to:

Symptoms

DML/select commands executed inside DBMS_SCHEDULER jobs are not captured in audit records.

But executing the same DML/select command by same DB user in sql*plus or inside pl/sql procedure gets audited.

Test case:

========

SQL> show parameter audit

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_sys_operations boolean TRUE
audit_trail string DB

sqlplus / as sysdba
audit alter table;
audit alter any table;
create user user1 identified by ****;
grant connect, resource to user1;

-- create a dbms_scheduler_job to create an audit record. This job executes every 1 minute.
-- There is no table junk and so the alter table command would fail.
-- an audit record is expected for this command alter table.
DECLARE
v_start_date TIMESTAMP WITH TIME ZONE;
v_timezone VARCHAR2(50);
BEGIN
v_timezone := extract( timezone_region from dbms_scheduler.stime);
IF v_timezone = 'UNKNOWN' THEN
v_start_date := CURRENT_TIMESTAMP AT TIME ZONE 'GMT' + interval '1' minute;
else
v_start_date := dbms_scheduler.stime + interval '1' minute;
end if;

DBMS_SCHEDULER.create_job (
job_name => 'EDBS_AUDIT.GEN_AUDIT_ROW_2',
job_type => 'PLSQL_BLOCK',
job_action => 'begin execute immediate ''alter table junk parallel 1''; exception when others then null; end; ',
start_date => v_start_date,
repeat_interval => 'FREQ=MINUTELY; INTERVAL=1',
enabled => TRUE,
comments => 'Job to generate audit event.');
END;
/

-- wait for at least 1 minute and confirm job is running
set lines 132
col job_name for a25
col start_date for a17
col repeat_interval for a25
col run_duration for a18
col next_run_date for a20
col status for a10
col ACTUAL_START_DATE for a40

ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS TZR TZD';

select a.JOB_NAME, b.status, ACTUAL_START_DATE,
REPEAT_INTERVAL, errors, output,
to_char(extract(HOUR from RUN_DURATION), 'fm00')||':'||
to_char(extract(MINUTE from RUN_DURATION), 'fm00')||':'||
to_char(extract(SECOND from RUN_DURATION), 'fm00.00') run_duration
from dba_scheduler_jobs a, dba_scheduler_job_run_details b
where a.job_name in ('GEN_AUDIT_ROW_2')
and a.job_name = b.job_name
order by ACTUAL_START_DATE
/

-- No audit records were generated from the job executions.
select userid, ACTION#, NTIMESTAMP#, RETURNCODE, obj$name from sys.aud$ where obj$name = 'JUNK';
No rows returned

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.