Hi,
I am helping developers to track their failed call to oracle procedures in java app, thought unified auditing can do that. so I create a unified audit policy on execute on the procedure, however, the audit only record the successful execution, but when the call fails with error ORA-06550 and PLS-00306, it's not recorded in the audit trail. here is example code to reproduce problem:
CREATE OR REPLACE PROCEDURE output_parameter(p_input VARCHAR2) AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Input Parameter: ' || p_input);
END;
/
CREATE AUDIT POLICY Jiulu_dropit3 ACTIONS EXECUTE ON jiulusun.output_parameter;
AUDIT POLICY Jiulu_dropit3;logout
login
-- successful execution
SQL> exec output_parameter('a');
Input Parameter: aPL/SQL procedure successfully completed.
-- failed execution
SQL> exec output_parameter('a','b');
BEGIN output_parameter('a','b'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'OUTPUT_PARAMETER'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
SELECT event_timestamp, os_username, userhost,dbusername, action_name, return_code,object_schema, object_name, sql_text
FROM unified_audit_trail
WHERE object_name='OUTPUT_PARAMETER'
@pr
==============================
EVENT_TIMESTAMP : 12-AUG-24 10.56.17.340044 AM
OS_USERNAME : jiulusun
USERHOST : my machine
DBUSERNAME : JIULUSUN
ACTION_NAME : EXECUTE
RETURN_CODE : 0
OBJECT_SCHEMA : JIULUSUN
OBJECT_NAME : OUTPUT_PARAMETER
SQL_TEXT : BEGIN output_parameter('a'); END;
PL/SQL procedure successfully completed.so the failed execution is not in audit trail, Is this a bug or I mis-understand how unified auditing works?
Change the policy to audit all ACTIONS EXECUTE rather than just that procedure and on 19c you'll see:
CREATE OR REPLACE PROCEDURE display_param(p_input int) AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Input Parameter: ' || p_input);
END;
/
CREATE AUDIT POLICY Jiulu_dropit3 ACTIONS EXECUTE;
AUDIT POLICY Jiulu_dropit3;
exec display_param(1);
--PL/SQL procedure successfully completed.
exec display_param(1, 2);
--PLS-00306: wrong number or types of arguments in call to 'DISPLAY_PARAM'
exec display_param('a');
--ORA-06502: PL/SQL: value or conversion error: character to number conversion error
SELECT event_timestamp, action_name, return_code, object_schema, object_name, sql_text
FROM unified_audit_trail
WHERE action_name = 'EXECUTE'
AND sql_text like '%display_param%'
AND event_timestamp > systimestamp - interval '1' hour
AND nvl ( object_schema , 'X' ) <> 'SYS'
ORDER BY event_timestamp DESC;
EVENT_TIMESTAMP ACTION_NAM RETURN_CODE OBJECT_SCH OBJECT_NAME SQL_TEXT
------------------------- ---------- ----------- ---------- -------------------- --------------------------------------------------------------------------------
03-SEP-24 14.22.11.945165 EXECUTE 6502 BEGIN display_param('a'); END;
03-SEP-24 14.22.11.883757 EXECUTE 0 CHRIS DISPLAY_PARAM BEGIN display_param(1); END;This has changed in 23ai. Same demo, but the query at the end returns the call with the extra parameter:
EVENT_TIMESTAMP ACTION_NAM RETURN_CODE OBJECT_SCH OBJECT_NAME SQL_TEXT
------------------------- ---------- ----------- ---------- ------------------------------ --------------------------------------------------------------------------------
03-SEP-24 14.23.19.647359 EXECUTE 6502 CHRIS DISPLAY_PARAM BEGIN display_param('a'); END;
03-SEP-24 14.23.19.643761 EXECUTE 904 CHRIS DISPLAY_PARAM BEGIN display_param(1, 2); END;
03-SEP-24 14.23.19.639624 EXECUTE 0 CHRIS DISPLAY_PARAM BEGIN display_param(1); END;I'm not sure if this counts as a bug or a feature. Either way your options are to upgrade to 23ai or ask support to see if a backport is possible.