OraFAQ Forum: Security » sql_text in dba_audit

Home » RDBMS Server » Security » sql_text in dba_audit_trail is not populating

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sql_text in dba_audit_trail is not populating [message #164453]

Thu, 23 March 2006 09:03

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.

Report message to a moderator

Re: sql_text in dba_audit_trail is not populating [message #164547 is a reply to message #164453]

Thu, 23 March 2006 23:19

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?

Report message to a moderator

Re: sql_text in dba_audit_trail is not populating [message #164708 is a reply to message #164453]

Fri, 24 March 2006 19:46

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.

Report message to a moderator

Re: sql_text in dba_audit_trail is not populating [message #164825 is a reply to message #164708]

Sun, 26 March 2006 22:40

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.

Report message to a moderator

Re: sql_text in dba_audit_trail is not populating [message #438294 is a reply to message #164453]

Thu, 07 January 2010 21:46

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

Report message to a moderator