Auditing Individual Functions/Procedures inside a PL/SQL Package

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:

This is just a sample package

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:

As per Oracle 19c documentation:

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.

Custom Solution:

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:

Now, whenever a user calls the “CreateEmp” procedure, a record will be added to the audit_log table:

If you are aware of any other better solution, please share it with us.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s