Home » RDBMS Server » Security » sql_text in dba_audit_trail is not populating
sql_text in dba_audit_trail is not populating [message #164453] Thu, 23 March 2006 09:03 Go to next message
pzlj6x
Messages: 107
Registered: May 2005
Location: Louisville
Senior Member


I have oracle 10g installed. I have turned on the audit_trail to 'DB'. I see all or most values in dba_audit_trail view. However, I don't see anything in sql_text field, which is what I am looking for.

I am trying to find the SQLs run by the user anytime in the past days. I do have SQL written to find the current SQLs run by the user while they are in current session.

My objective is to find the SQLs run by users in the past. How do I do that. I thought, by turing on the audit_trail feature would give me the SQL_TEXT, but nada... Sad

Can anyone help me please.

thanks in Advance.
R.
Re: sql_text in dba_audit_trail is not populating [message #164547 is a reply to message #164453] Thu, 23 March 2006 23:19 Go to previous messageGo to next message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member

Just curious , it's Ok that you started auditing to DB but what is the thing that you want to audit. I mean, what are you auditing??

 Audit by session, Audit by access etc. 


can you post the statements that you ran to start auditing?
Re: sql_text in dba_audit_trail is not populating [message #164708 is a reply to message #164453] Fri, 24 March 2006 19:46 Go to previous messageGo to next message
pzlj6x
Messages: 107
Registered: May 2005
Location: Louisville
Senior Member
Audit by every activity that the user is doing.

Example, My manager wants to know what kind of SQL the user has run in the past days.

I wrote the query to find out the current SQL that the user is running. The below SQL gives the information of the sql_text in the current session.
==========
SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c
WHERE a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
ORDER BY b.tablespace, b.blocks;
========

While reading the manual, I found the values for audit_trail. The allowed values are 'DB', 'DB_EXTENDED'. The DB_EXTENDED setting should give the sql_text field. I set the same and bounced back the database.
Still I am unable to get the result. I think, as you said, I need to set 'audit level' (by session, transaction) or so, which I do not know and don't have an idea.

Please help as this gets critical to our need.

Thanks
R.
Re: sql_text in dba_audit_trail is not populating [message #164825 is a reply to message #164708] Sun, 26 March 2006 22:40 Go to previous messageGo to next message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member

The specific commands are mentioned in the manuals. Please go through it again and you will come to know about it.

can follow this also

http://www.securityfocus.com/infocus/1689

but you need to read docs anyhow.
Re: sql_text in dba_audit_trail is not populating [message #438294 is a reply to message #164453] Thu, 07 January 2010 21:46 Go to previous messageGo to next message
palazzi
Messages: 11
Registered: June 2009
Location: Toluca
Junior Member
Hi.

You have to set audit_trail to db_extended.

ALTER SYSTEM SET audit_trail=db_extended SCOPE=SPFILE;

after that shutdown and start again and then you have to see the sql_text value

Saludos
Re: sql_text in dba_audit_trail is not populating [message #438334 is a reply to message #438294] Fri, 08 January 2010 02:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The syntax is "db,extended" not "db_extended".

Regards
Michel
Re: sql_text in dba_audit_trail is not populating [message #438396 is a reply to message #438334] Fri, 08 January 2010 10:40 Go to previous message
palazzi
Messages: 11
Registered: June 2009
Location: Toluca
Junior Member
In Oracle 10g Release 1, db_extended was used in place of db,extended.

You have to choose the correct syntax accord your version.

Saludos
Previous Topic: DBA_REGISTRY_HISTORY is not in database
Next Topic: sysasm privilege
Goto Forum:
  


Current Time: Thu Mar 28 05:49:51 CDT 2024