RE: auditing actions

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@xxxxxxxxxxx] 
Sent: Monday, April 16, 2007 1:58 PM
To: oracle-l@xxxxxxxxxxxxx
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

Other related posts: