How can I audit the individual functions/procedures inside package

Question

Im trying to set up some audit in our database to figure out which functions/procedures we use but have come to a stop when trying to audit the executions of my individual functions inside my packages.

I can set up audit for various functions,procedures and packages with

    audit execute on [procedure] by access; 
    audit execute on [function] by access; 
    audit execute on [package] by access;

BUT what I also need is the individual objects inside the package something like

    audit execute on [package.function] by access;
    audit execute on [package.procedure] by access;

When executing something in a package the dba_audil_trail just show me that the package has been executed not the actual function inside the package.

score 0 · Answer 1 · answered Oct 04 '19 at 09:05

I gave up getting the audit method to give me what I wanted so I ended up inserting the following codelines in every function/procedure, both inside or outside packages, I wanted to track the use of.. ( some was used so often I disabled the tracking again and put them on an exception list)

PRAGMA AUTONOMOUS_TRANSACTION;--for auditlogning (needed in functions)
BEGIN
CREATE_AUDIT_LOG([Stored_Procedure_name]);--for auditlogning

CREATE OR REPLACE PROCEDURE CREATE_AUDIT_LOG(p_objectname in varchar2) IS 
BEGIN
INSERT INTO AUDIT_EXECUTE_LOG values(sysdate,
p_objectname,
sys_context('USERENV','MODULE'),
SYS_CONTEXT ('USERENV','OS_USER'),
SYS_CONTEXT ('USERENV','HOST'),
SYS_CONTEXT ('USERENV','CURRENT_SCHEMA'),
SYS_CONTEXT ('USERENV','SERVICE_NAME'),
SYS_CONTEXT ('USERENV','SESSION_USER') 
  );
commit;
END CREATE_AUDIT_LOG;

How can I audit the individual functions/procedures inside package

1 Answers1