Skip to Main Content
  • Questions
  • Auditing of PL/SQL Functions and Procedure Calls

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Ketan.

Asked: July 28, 2003 - 9:46 pm UTC

Last updated: May 06, 2012 - 3:13 pm UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Hi Tom,

Thanks a lot for sharing your in-depth knowledge with us.

I have a requirement to audit PL/SQL procedures and function calls with all parameter values. I want to write some generic framework which could be used by all the packages and functions.

Example is:

Procedure CreateEmp ( Emp ID Number,
EmpName Varchar2,
Department Varchar2) is
BEGIN
--
-- First call should be the audit call
-- with all parameter values
--
audit('CreateEmp',EmpId, EmpName,Department);
--
END CreateEmp;
--
Procedure ChangeEmp ( Emp ID Number,
EmpName Varchar2,
Address varchar2) is
BEGIN
--
-- First call should be the audit call
--
audit('ChangeEmp',EmpName, Address);
--
END ChangeEmp;

I want to write a generic audit procedure which could take any number of arguments, and create audit records with values.

The result of above audit would be:

Audit table will have a master record "CreateEMP".
Whereas AuditValues table will have all child records with name, value pair. Like Emp Name=Scott, Dept=Accounts, etc.

This type of auditing provides many advantages to the application:
1. User can see what PL/SQL they are executing
2. Developers can use it for debugging
3. During fresh install, when schema is changed, import/export or backup/recovery is not an option. User can replay the entire provisioning generated through PL/SQL audit. QA would love it.

What is the right way to design this framework so that I can utilize Oracle's power more and with less code. Any new features in Oracle 8i, 9i? Please guide me with right approach of solving it.

Thanks a lot in advance.

Regards,

Ketan.



and Tom said...

It could be as easy as this:

ops$tkyte@ORA920> create or replace type params as table of varchar2(4000);
2 /

Type created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> create table audit_tbl
2 ( call_stack varchar2(4000),
3 called timestamp default systimestamp,
4 parameters params
5 )
6 nested table parameters store as audit_tbl_parameters;

Table created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> create or replace trigger audit_tbl_trigger
2 before insert on audit_tbl for each row
3 begin
4 :new.call_stack := dbms_utility.format_call_stack;
5 end;
6 /

Trigger created.

ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> create or replace procedure p( p_input1 in int, p_input2 in date, p_input3 in varchar2 )
2 as
3 begin
4 insert into audit_tbl(parameters) values (params(p_input1,p_input2,p_input3));
5 dbms_output.put_line( 'Hello World' );
6 end;
7 /

Procedure created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> exec p( 1, sysdate, 'blah blah blah' );
Hello World

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> select * from audit_tbl;

CALL_STACK
-----------------------------------------------------------------------------------------------------------------------------------
CALLED
---------------------------------------------------------------------------
PARAMETERS
-----------------------------------------------------------------------------------------------------------------------------------
----- PL/SQL Call Stack -----
object line object
handle number name
0x57d6cb10 2 OPS$TKYTE.AUDIT_TBL_TRIGGER
0x57d36220 4 procedure OPS$TKYTE.P
0x57d2748c 1 anonymous block
29-JUL-03 07.42.30.684036 AM
PARAMS('1', '29-JUL-03', 'blah blah blah')






Rating

  (8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Auditing of PL/SQL procedures and functions

Ketan. Bengali, July 29, 2003 - 2:10 pm UTC

Hi Tom,

Thank you very much. It is very much useful. Ours is a complex Element Management/Network Management System developed using Oracle Forms,Java Swing/Servlet, CORBA and SNMP. To configure any element in the database, all of these clients invoke PL/SQL APIs. To simulate the exact scenario, I have packaged your procedures.

create or replace package audit_pack is
--
-- Putting it in seperate package will
-- allow us to add/change any future audit
-- requirements without changing other PL/SQL APIs.
--
procedure insert_audit( p_params params);
end audit_pack;
/
create or replace package body audit_pack as
--
procedure insert_audit(p_params params) is
begin
insert into audit_tbl(parameters) values
(p_params);
--
end insert_audit;
end audit_pack;
/
create or replace package emp_pack as
--
procedure CreateEmp(
p_input1 in int,
p_input2 in date,
p_input3 in varchar2 );
--
end emp_pack;
/
create or replace package body emp_pack as
--
procedure CreateEmp(
p_input1 in int,
p_input2 in date,
p_input3 in varchar2 ) is
begin
--
audit_pack.insert_audit(params(p_input1,p_input2,p_input3));
--
dbms_output.put_line( 'Hello World' );
--
end CreateEmp;
--
end emp_pack;
/
EMSDB53.IPVERSE SQL > exec emp_pack.CreateEmp(29,sysdate,'Scott');
Hello World

PL/SQL procedure successfully completed.

EMSDB53.IPVERSE SQL > select * from audit_tbl;

CALL_STACK
--------------------------------------------------------------------------------
CALLED
---------
PARAMETERS
--------------------------------------------------------------------------------
----- PL/SQL Call Stack -----
object line object
handle number name
81fcf92c 2 IPVERSE.AUDIT_TBL_TRIGGER
82e58ea0 6 package body IPVERSE.AUDIT_PACK
80e6eb54 9 package body IPVERSE.EMP_PACK
80fe2374 1 anonymous block
29-JUL-03
PARAMS('29-JUL-03', 'Scott', '29')

If you look at call stack, it doesnot print procedure name "EMP_PACK.CreateEmp". It only prints package name "EMP_PACK". Is this the default behaviour? While passing params, I could pass procedure name as well; is it a right approach? How to get parameter names (p_input1, p_input2, p_input3)? I can use dbms_sql.describe_columns; is there a better way other than using describe_columns?

Once again thanks a lot for helping us understand the power of Oracle.

Regards,

Ketan.

Tom Kyte
July 29, 2003 - 2:26 pm UTC

the object of interest is "EMP_PACK" (you could have 50 CreateEmp procedures in there).

The line number -- 9 -- identifies where in the package you are. We only track top level objects -- things you could "grant on". You can grant on EMP_PACK, you cannot grant on EMP_PACK.CREATEEMP -- createemp doesn't "exist" really.

I would not bother with the procedure name unless you really want it, the line number tells me what I want.


same with the parameter names, no need to record that -- you don't want to make the audit routine dog slow.



Auditing of PL/SQL Procedures and Functions

Ketan, July 29, 2003 - 5:21 pm UTC

I have a requirement to store procedure name and parameter names in the audit.

Use Case I:

We are developing an application which would be deployed to many telecom customers. Customers donot know the internal table structures. Whenever user makes a change, GUI invokes the API. Now API might be creating records in 10 tables but user is not aware of it. When user wants to see the audit, we can't show them records from 10 tables. We have to show them the original API call which they understands very well.

Use Case II:

Since it is a real time network management system, certain test cases can be executed only when the network is in certain state. It takes a lot of time for QA to bring network to that state. It would be easier for QA team to generate PL/SQL calls from these audit data and reprovision any new system any time.

If we donot capture API calls then QA would have to:
a. Test GUI anyway as it is part of the product
b. Maintain PL/SQL test automation files to configure different systems to test different scenario.

Now if we add more parameters to the procedure then QA has to change all test automation scripts manually. It also adds chances of human errors.

By providing procedure name and parameter names in the audit, they can only test the GUI and generate test automation scripts from the audit tables. This is less error prone and will give more productivity.

Use Case III:

GUI is written in Java Swing which internally calls servlet and servlet calls PL/SQL API. Different developers are responsible for maintaining different part of the code. Now when a problem arise, entire team is needed to debug it as different team members have specialization in different technologies. By providing this audit, it is easy to narrow down problem between Swing, Servlet or PL/SQL level and only one person can solve it either in QA or at customer site.

We are planning to provide this feature in such a way that it could be turned on or off any time by specifying application parameter.

Thanks,

Ketan

Tom Kyte
July 29, 2003 - 8:09 pm UTC

Ok, then the programmers will have to pass a name, and two arrays -- an array of names and an array of values.

Thanks a lot

Ketan, July 29, 2003 - 8:57 pm UTC

Thanks a lot Tom. Your input is very much valuable to us. I will design it the way you have specified.

Tom Kyte
July 29, 2003 - 9:24 pm UTC

doh -- do it the way that works best for you -- I just gave you ideas, run with it.

Auditing procedures and functions when they compile

Arun Mathur, November 12, 2003 - 11:02 am UTC

Hi Tom,

I can't say enough good things about your site, and how much you're contributing to those using Oracle. I'm looking at auditing for the first time, and see that I can audit a procedure when it's executed. However, is there a way I can audit a procedure/function/package when it's been modified?


Take care,
Arun


Tom Kyte
November 12, 2003 - 11:28 am UTC

you would be auditing creates and drops on them...

Top

Orca, November 13, 2003 - 2:15 am UTC

Short and efficient
Orca


Giovanni, May 10, 2006 - 8:14 am UTC

Unvaluable to solve a problem I had!

audit procedure / package

June, August 08, 2011 - 5:22 pm UTC

Hi Tom,

Thanks for all your diligent work to help us in better place.

I have similar request to audit the modification of store procedure and package, as our security office see there is difference in column timestamp, last_ddl_time and created @dba_objects, however, there is no change order indicating that we have new code release, and none of my developers claim that they touch the code in production (we have small team):

object_name EMPLOYEE_EXTRACT
object_type PROCEDURE
last_ddl_time 7/20/2011 9:36:54PM
timestamp 2011-07-20:21:36:54
created 6/9/2011 4:47:26 PM

So, only thing I can think of is to turn on the audit for those identified stored procedure and package (body).

However, I read oracle doc and your answer to others, it seems 'audit all on test.<procedure_name>' will only audit create and drop activity. How can I audit alter / grant activity as well, as they will change the last_ddl_time ?

Your assistance is always appreciated.

Tom Kyte
August 13, 2011 - 3:39 pm UTC

you can audit EXECUTE and GRANT on procedures.

Altering a procedure would just compile it - it won't change it really. You could audit that with a DDL event trigger if necessary.

function vs procedure.

A Reader, May 05, 2012 - 2:20 am UTC

Hi Tom,

I have studied a book where it was written that use procedure when you want to return more than one value.
But my doubt is even we can do the same with the in out parameter in function.

Though function is used as condition so it must return a single value by return statement but we can make use of the in out parameters (which is nothing but multiple return values).

In that book it has also been written that for computation use function but nothing details have been mentioned(only mentioned that if you want to return more than one value and if you are using function then it is a bad programming style).

But My question is why it is bad programming ,does it affect performance.

could you please gives some complete idea behind this(where to use what and if not why).

Thanks.



Tom Kyte
May 06, 2012 - 3:13 pm UTC

I have studied a book where it was written that use procedure when you want to
return more than one value.
But my doubt is even we can do the same with the in out parameter in function.


That is a book hooked on semantics. In short - a function should not have "side effects", it shouldn't modify anything - it does something and returns a value (the so called functional programming languages - http://en.wikipedia.org/wiki/Functional_programming - take that to the extreme).

However, in most every procedural language I've used - functions are just procedures that can also return a value.

So, in a procedural language - feel free to use either - which ever makes the most sense in the context. But you will find that many of your functions only take IN parameters - whereas it will generally be your procedures with OUT parameters.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library