Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: auditing actions

RE: auditing actions

From: Taft, David <TaftD_at_saic-dc.com>
Date: Tue, 17 Apr 2007 13:40:27 -0400
Message-ID: <DCE76463749C64499892A0DB3AF05AC60EF45F9A@challenger.vta.saic-dc.com>


joe_dba,

We typically only "audit session" in our shop unless there is justification to audit further. The script I run most frequently checks for failed login attempts. If you keep a lot of audit data online, as we do, then you will want to limit your return set. I do this with the v_totsessid and v_maxsessid variables. Just adjust the value of v_totsessid to go back further in time. The right value for v_totsessid varies by database instance depending on how heavily it is accessed, so you just have to experiment.

Also, this logic can be added to any aud$ script, just be sure to include the define statements, the "COLUMN maxsessid NEW_VALUE v_maxsessid" and the "SESSIONID > (&&v_maxsessid-&&v_totsessid)" in your WHERE clause.

Cheers,

David Taft

define v_totsessid=200000;
define v_maxsessid=0;

COLUMN maxsessid NEW_VALUE v_maxsessid
select max(SESSIONID) as maxsessid
from aud$;

SET TERMOUT on

spool audit_failed_logins

select 'Date of first sessionid: '||to_char(timestamp#,'MM/DD/YY') from aud$
where sessionid = (&&v_maxsessid-&&v_totsessid);

set feedback on pagesize 999

select

        userid
        ,terminal
        ,spare1 os_user
        ,to_char(timestamp#,'MM/DD/YY.HH24:MI:SS') login
        --,to_char(logoff$time,'MM/DD/YY.HH24:MI:SS') logoff
        ,returncode code

from aud$
where SESSIONID > (&&v_maxsessid-&&v_totsessid) and returncode != 0
and timestamp# > sysdate-30
order by timestamp#, userid, spare1, logoff$time; spool off

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

From: Joe Smith [mailto:joe_dba_at_hotmail.com] Sent: Monday, April 16, 2007 1:58 PM
To: oracle-l_at_freelists.org
Subject: auditing actions

Can you join aud$ and audit_actions to pull useful information?

select count(*), a.action#, aa.name, a.obj$name from sys.aud$ a, audit_actions aa
where a.action# = aa.action
group by a.action#, aa.name, a.obj$name
/

Is this the correct join for sys.aud$?

Is this what most people do to pull then number of actions (i.e. select , update, ..)
from the aud$ table with name value and object (obj$name)?

thanks.



Can't afford to quit your job? - Earn your AS, BS, or MS degree online in 1 year.
http://www.classesusa.com/clickcount.cfm?id=866145&goto=http%3A%2F%2Fwww.cla ssesusa.com%2Ffeaturedschools%2Fonlinedegreesmp%2Fform-dyn1.html%3Fsplovr%3D 866143

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Tue Apr 17 2007 - 12:40:27 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US