RE: Auditing, how to capture sql statement issued

Take a peek at Oracle Security, O'Reilly Press.

I haven't bought the book but I think there is a chapter in there that will show you how to track SQL going through your database.

It uses triggers, so expect a performace hit.

Regards,
Patrice Boivin
Systems Analyst (Oracle Certified DBA)

-----Original Message-----

	From:	Mark Leith [SMTP:mark_at_cool-tools.co.uk]
	Sent:	Tuesday, January 16, 2001 1:39 PM
	To:	Multiple recipients of list ORACLE-L
	Subject:	RE: Auditing, how to capture sql statement issued

	You could infact look at writing a query to select from v$SQLAREA
and loop
	infinitely (via whatever:), to collect SQL statements with certain
	thresholds etc, then insert these statements in full in to a
"Repository"
	table.

	SQL optimistation tools do this all the time..





	-----Original Message-----

	Brian
	Sent: Tuesday, January 16, 2001 04:26
	To: Multiple recipients of list ORACLE-L


	Based on his subject, I think Mr. Cale was wanting to monitor other
users'
	queries. For instance, looking for queries without where clauses
that pull
	credit card numbers to find out if someone is doing something they
	shouldn't.

	There is no effective way to monitor queries from inside the DB.
There are
	no "on select" triggers, system auditing only tracks that a query
was issued
	against the table without any info   on the rows returned or query
text, and
	no log is generated on queries, so logminer is no help.

	Brian Norrell
	Senior Software Engineer
	QuadraMed
	972-831-6600




	-----Original Message-----

	Sent: Tuesday, January 16, 2001 8:31 AM
	To: Multiple recipients of list ORACLE-L




	 Cale
	    I think you mean to reuse the sql statement sometime again.
	    if this is the case then u can  save the query in the sql*plus
editor in
	some file location
	     or u can just use SAVE <A> after your query in the sql*plus .
	     The next time u want to use the query u can just use ' @ A ' .

	    e.g select * from tab;


	TNAME                          TABTYPE CLUSTERID


	------------------------------ ------- ---------

	AB                             TABLE
	CD                             TABLE
	DISTINCT_TEST                  TABLE
	EMP                            TABLE
	LXAPPLICATION                  TABLE
	LXAUCTION                      TABLE
	LXAUCTIONHISTORY               TABLE
	LXAUCTIONSTATUS                TABLE
	LXBID                          TABLE


	SQL> save a replace
	Wrote file a

	SQL> @ a

	TNAME                          TABTYPE CLUSTERID


	------------------------------ ------- ---------

	AB                             TABLE
	CD                             TABLE
	DISTINCT_TEST                  TABLE
	EMP                            TABLE
	LXAPPLICATION                  TABLE
	LXAUCTION                      TABLE
	LXAUCTIONHISTORY               TABLE
	LXAUCTIONSTATUS                TABLE
	LXBID                          TABLE


	you can also use the sql*plus editor  by just typing 'ed' as it will
load
	the last executed sql query in
	the editor where you save your query.

	 Rgds,
	 ======
	 Sachin Puri
	 Oracle Development and Administration
	 Solutions NY
	 Tel     : +91-11-6220102 x 22
	 Direct : +919810373243





	-----Original Message-----

	Sent: Tuesday, January 16, 2001 6:31 PM
	To: Multiple recipients of list ORACLE-L


	Hi DBAs,

	Is there a way to capture the actual sql statement issued while in
	sql*plus,worksheet,etc?

	Thanks,
	Rick Cale, Science Applications International Corp.
	Phone:865-481-2198, fax:865-481-8555
	e-mail: caler_at_saic.com



	--

	Please see the official ORACLE-L FAQ: http://www.orafaq.com


	--

	Author: Cale, Rick T (Richard)
	  INET: RICHARD.T.CALE_at_saic.com

	Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
	San Diego, California        -- Public Internet access / Mailing
Lists


	--------------------------------------------------------------------

	To REMOVE yourself from this mailing list, send an E-Mail message
	to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
	the message BODY, include a line containing: UNSUB ORACLE-L
	(or the name of mailing list you want to be removed from).  You may
	also send the HELP command for other information (like subscribing).



	--

	Please see the official ORACLE-L FAQ: http://www.orafaq.com


	--

	Author: Sachin Puri
	  INET: sachin_at_siworldwide.com

	Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
	San Diego, California        -- Public Internet access / Mailing
Lists


	--------------------------------------------------------------------

	To REMOVE yourself from this mailing list, send an E-Mail message
	to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
	the message BODY, include a line containing: UNSUB ORACLE-L
	(or the name of mailing list you want to be removed from).  You may
	also send the HELP command for other information (like subscribing).


	--

	Please see the official ORACLE-L FAQ: http://www.orafaq.com


	--

	Author: Norrell, Brian
	  INET: BNorrell_at_QuadraMed.com

	Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
	San Diego, California        -- Public Internet access / Mailing
Lists


	--------------------------------------------------------------------

	To REMOVE yourself from this mailing list, send an E-Mail message
	to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
	the message BODY, include a line containing: UNSUB ORACLE-L
	(or the name of mailing list you want to be removed from).  You may
	also send the HELP command for other information (like subscribing).



	-- 

	Please see the official ORACLE-L FAQ: http://www.orafaq.com


	-- 

	Author: Mark Leith
	  INET: mark_at_cool-tools.co.uk

	Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
	San Diego, California        -- Public Internet access / Mailing
Lists


	--------------------------------------------------------------------

	To REMOVE yourself from this mailing list, send an E-Mail message
	to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
	the message BODY, include a line containing: UNSUB ORACLE-L