Home » SQL & PL/SQL » SQL & PL/SQL » Question about tracing Web Applications SQL statements by using oracle AUDIT command
 Question about tracing Web Applications SQL statements by using oracle AUDIT command [message #627098] |
Fri, 07 November 2014 03:35  |
 |
dariyoosh
Messages: 538
Registered: March 2009
Location: France
|
Senior Member |
|
|
Hi,
Environment:
Oracle version: 11g Release 11.1.0.7.0 - 64bit (Standard Edition)
OS: Linux Redhat 4
My requirement
Finding a set of tables/columns behind a Web application by using the oracle AUDIT command by viewing and tracing SQL statements run by the Web application.
Requirement details
Currently I'm preparing several reports for a Web application by using Jasper iReport. The target application is a Web application based on Java+Apple WebObjects. So far, by looking table structures, constraints on tables (dictionary in general) and creating Entity Relationship Diagrams I've managed to find most of the links among tables and columns required by my report SQL query (to be put in iReport). Yet, there are few fields in the report model, for which, despite searching a lot, I've not managed to find the correspondence in the database, that is, finding the tables which include columns with the values corresponding to the fields indicated in the report model.
Besides, it seems to me that all constraints are not put at database level (for example there should be foreign key on many tables, but, instead the software editor (Open Source) has hard coded this in the application ! ). So in summary, IMHO, the database behind the application is far away to be considered proper and normalized. Yet I don't maintain the code and I have to create reports based on it.
So I started searching in the oracle documentation, in order to see is there any command allowing to trace at real time the SQL statements which are being run by an application (Web in my case) and I found the AUDIT command. As I understand, this is rather an Admin command not to be used by ordinary users. Yet I cannot be DBA even under the development instance. Therefore the DBA created a user for me specifically for this operation and configured the instance in the following way:
alter system set audit_trail=db,extended scope=spfile;
-- shutdown and startup the instance
create user <username> identified by <password>;
alter user <username> quota unlimited on users;
grant connect to <username>;
grant create table, create procedure to <username>;
grant audit system to <username>;
grant audit any to <username>;
So I start audit by running in SQL*Plus
SQL> audit delete table, execute procedure, insert table, select table, update table by access;
And for stop auditing I run the following (in the very same SQL*Plus session used for start auditing):
SQL> noaudit delete table, execute procedure, insert table, select table, update table
Now the problem is, that when I start auditing, I'm able to trace all SQL statements that I run, for example in SQL Developer I run (SELECT count(*) FROM students) and this works pretty well, with the username, timestamp and sql_text I query sys.dba_audit_trail and it gives me the information. But, if I log to the Web application (same instance, same user) by Firefox nothing (in particular no SELECT) is being traced, absolutely nothing.
So I would like to ask:
Am I proceeding correctly? I mean do I run the AUDIT command correctly or there should be additional parameters to be added in order to work also with WEB applications?
Thanks in advance,
|
|
|
|
|
|
|
|
| Re: Question about tracing Web Applications SQL statements by using oracle AUDIT command [message #627197 is a reply to message #627128] |
Sat, 08 November 2014 13:50  |
|
dariyoosh
Messages: 538
Registered: March 2009
Location: France
|
Senior Member |
|
|
Dear John and BlackSwan
First of all, I would like to thank you very much both of you for your kind help and your time. I really appreciate that.
"John Watson Fri, 07 November 2014 19:27"To capture everything, try AUDIT ALL STATEMENTS BY YourUser ;
Interesting I didn't know that this option is available, I thought that the only way was to specify each SQL operation individually.
"John Watson Fri, 07 November 2014 19:27"Is it possible that your user when you come in through web is not you, but some application user?
In fact I'm not sure about that and that was precisely the reason that I didn't include the auditing_by_clause in the AUDIT command (shown in my previous comment above) because as I understand according to oracle this will include operations of all users:
http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_4007.htm#i2077123
Quote:
...
Specify the auditing_by_clause to audit only those SQL statements issued by particular users. If you omit this clause, then Oracle Database audits all users' statements.
...
"BlackSwan Fri, 07 November 2014 19:36"ALTER SESSION SET SQL_TRACE=TRUE;
As I understand, if I want to trace the operations of another session via this method, I have to use dbms_system.set_sql_trace_in_session by the appropriate SID and Serial# from v$session. Now the problem is that I don't know almost anything about the internal mechanism of the application in terms of opening and closing connection objects. I mean I don't know whether once the user logs in, there is a single connection object (connection to oracle) which is created and remains open till the user signs out, or there are several objects (= several sessions) opened and closed depending on the operations and queries run by the user activities. If there are multiple connections, then there could be the possibility of several SID and Serial#, different from those specified in dbms_system.set_sql_trace_in_session. Besides, I don't have access to the server machine to get the generated file. And the DBA is not really available. That's why I thought that the AUDIT method was more appropriate given the current situation.
Yesterday when I arrived home, just to make sure that I had run the command correctly I created a very small Web application (just a JSP and a servlet with NetBeans+Tomcat). In my home I have access to a 12c Enterprise database. I proceeded exactly as shown above in my previous comment, that is, I created a specific user for AUDIT with required previliges, then I logged in to my web application with a different user and everything worked nicely. I was able to trace all SQL statements without any problem. So, I think at least at SQL*Plus level I proceed correctly and I write the command correctly. Maybe there is some admin parameter (I don't know I'm not a DBA) somewhere configured in a way that blocks Web application auditing !
Anyway, I go back there wednesday and I will continue with your remarks in particular what John suggested about dba_audit_session. If I find the solution of course I'll write it here.
Once again thank you very much both of you for your attention to my problem and for your time.
Regards,
|
|
|
|
| Re: Question about tracing Web Applications SQL statements by using oracle AUDIT command [message #627692 is a reply to message #627197] |
Fri, 14 November 2014 09:37 |
|
dariyoosh
Messages: 538
Registered: March 2009
Location: France
|
Senior Member |
|
|
Well ...
I tried and I tried again but with no success. 
Finally, one of developers showed me a way to activate logs on WebObjects Application file to view the generated SQL and this almost fulfilled my requirement. But of course the main question, that is, why the AUDIT command that is supposed to see everything, once the required privileges are there, cannot trace SQL instructions of this application, remains unanswered, if I found the reason of course I would update this thread.
For now, for those who may have encountered the same problem and are looking a way to trace the SQL statements behind a WebObjects application, this is what you need to have in terms of parameters in your <Your Application Name>.Properties file:
log4j.loggerFactory=er.extensions.logging.ERXLogger$Factory
log4j.rootCategory=INFO,A1
log4j.appender.A1=er.extensions.logging.ERXConsoleAppender
log4j.appender.A1.layout=er.extensions.logging.ERXPatternLayout
log4j.appender.A1.layout.ConversionPattern=%d{MMM dd HH:mm:ss} %$[%#] %-5p %c %x - %m%n
log4j.logger.er=INFO
er.extensions.ERXNSLogLog4jBridge=INFO
log4j.logger.er.eo.ERXGenericRecord=DEBUG
log4j.logger.er.validation.ERXEntityClassDescription=DEBUG
log4j.logger.er.default.ERXEntityClassDescription=DEBUG
log4j.logger.er.extensions.ERXDatabaseContextDelegate=WARN
log4j.logger.er.extensions.ERXConfigurationManager=INFO
log4j.logger.er.transaction.adaptor.EOAdaptorDebugEnabled=DEBUG
log4j.logger.org.cocktail.cocowork.server.metier.convention=INFO
log4j.logger.org.apache.commons.jexl2.JexlEngine=ERROR
Thanks again everybody for your attention to my problem and for your time. 
|
|
|
|
Goto Forum:
Current Time: Wed Apr 24 12:49:12 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
