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.
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
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.
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
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.
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.
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.