Re: Trap SQL statements in network traffic instead of database
Date: Mon, 14 Aug 2017 11:25:30 +0000
Message-ID: <MMXP123MB1037ABFE422B3CF3E28A8084A58C0_at_MMXP123MB1037.GBRP123.PROD.OUTLOOK.COM>
The difficulty with that one is that to meet the stated requirement the system tap code will have to be modified trap on a call to execute, and then find the SQL text of the statement to be executed. How many possible calls are there - I don't know if there's a single point that all execute calls have to go through.
On top of that, the specification needs one point clarified - do the clients also call PL/SQL blocks, and should the log contain only the PL/SQL call, or calls to execute the SQL inside the PL/SQL. Similarly, I suppose, if you have SQL inside pl/sql functions that are called from SQL - which you really shouldn't have, ask Bryn - do you want every such call to be logged; and do you want to avoid logging the sys-recursive calls ?
It might be an idea to look at the features of the Teleran product suggested by Tim to find out what options you need to consider.
Regards
Jonathan Lewis
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Stefan Koehler <contact_at_soocs.de> Sent: 12 August 2017 09:09:51
To: oracle-l; sbecker6925_at_gmail.com
Subject: Re: Trap SQL statements in network traffic instead of database
Hello Sandy,
no need to purchase any fancy product - AFAIK you are running Oracle on Linux and so you can do all of that with Linux.
Getting the SQL statements (and any other metadata) with SystemTap: * http://externaltable.blogspot.de/2016/03/systemtap-guru-mode-and-oracle-sql.html * https://mahmoudhatem.wordpress.com/2016/04/18/systemtap-a-mini-oracle-db-firewall/
Just modify the scripts according to your needs (e.g. logging in a particular format) and that's it :)
P.S.: The overhead is also very little as you only probe on specific C functions (and not all).
Best Regards
Stefan Koehler
Independent Oracle performance consultant and researcher
Website: http://www.soocs.de
Twitter: _at_OracleSK
> Sandra Becker <sbecker6925_at_gmail.com> hat am 11. August 2017 um 22:43 geschrieben:
>
> We need to produce a "log" of sql statements--along with the user, IP (or host) they are coming from, and the sql statement--for another team to analyze. My manager does not want to user auditing because of the uncertainty of the load on this critical database. He suggested doing a SPAM port capture. I opened a ticket with our SAs and they wanted to know what ports. I gave them the listener ports. The SA ran a tcpdump (said it was verbose), but it didn't give any information on users, app servers, or sql statements. I really don't know what I'm doing here, just passing information between my manager and SAs. So, questions:
>
> 1. Will tcpdump give me what my manager is asking for? If yes, what are the options the SA should use?0
> 2. Is there a better way to retrieve this information without using database auditing?
>
> Any assistance you can provide will be greatly appreciated.
>
> Sandy B.
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Mon Aug 14 2017 - 13:25:30 CEST