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

• audit_failed_logins.sql: Failed Logins
  --
  
• returncode greater than zero represents the ORA error number as to why it failed.
• i.e. 1017 is invalid username/password.
  --
  set pagesize 0 trimspool on linesize 200 verify off termout off feedback off column userid format a16 column terminal format a18 column os_user format a18 column login format a19 column logoff format a19 column code format 99999

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