I got this question from one of my clients:
How can I Audit Individual Functions/Procedures inside a PL/SQL Package?.
Let us build a quick demo:
Let us try to enable this type of auditing using the following command:
audit execute on scott.emp_pack.CreateEmp by access;
I got the following error:
So, as per oracle, the standard oracle audit doesn’t provide this type of audit. You can still enable auditing at the package level:
audit execute on scott.emp_pack by access;
and you can query the audit trail for monitoring:
This is the case until Oracle Database version 21c.
But the question is:
is there any other way to fulfill my customer requirements?
The answer is yes, we can build something customized.
First , let us create a table that will act as Audit Log:
Then, let us create a procedure that will be called to populate the above audit log table, whenever a procedure or functions was executed:
The last step is to modify the function or the procedure that you want to audit, and add an AUTONOMOUS call for the AUDIT_LOG:
If you are aware of any other better solution, please share it with us.