RE: audits

From: Ruiz, Mary A (CAP, CDI) <Mary.Ruiz_at_gecapital.com>
Date: Thu, 20 Jul 2000 12:47:02 -0400
Message-Id: <10564.112550@fatcity.com>

Charlie:

   I recently set up auditing on a schema on a small datamart to find out who selects from the tables / views in it (less than 10 users, rather infrequently). Auditing records are written to the table sys.aud$ which resides in the system tablespace by default. It is authorized by Oracle to move this table to a different tablespace - I would recommend it if you plan to do some auditing. Check Metalink / Tales from the Script ,Problem ID 1019377.6

AUDIT SELECT ON EMP.empid, emp.name, emp.dept by access ; audit select on emp ;

The above will show any attempt to select data from emp, as per your spec.

Here is a script to show the auditing results: ( you should adjust the criteria to suit
your purpose)
select statement, userid, terminal, a.action, b.name, obj$name, timestamp from sys.aud$ a, sys.audit_actions b
where userid not in ('SYS','SYSTEM')

and a.action = b.action
and a.action not in (100, 101,102)
and a.userid <> 'MRUIZ'

order by obj$name, timestamp
/

Here is another script:
col terminal format a10
col obj$name format a25
col userid format a8
col name format a12
accept From_date char prompt "Enter beginning date as MM/DD/RR : " select obj$name, name, terminal, userid, timestamp from sys.aud$ a, audit_actions b
where a.action = b.action
and a.timestamp >= TO_DATE('&From_date','MM/DD/RR') and userid not in ('SYS','SYSTEM')
and name not in ('LOGON','LOGOFF', 'LOGOFF BY CLEANUP') and terminal is not null
/
These are not beautiful but they worked for me.

hth

Mary Ruiz
Atlanta GA

> -----Original Message-----
> From: Charlie Mengler [SMTP:charliem_at_mwh.com]
> Sent: Thursday, July 20, 2000 10:16 AM
> To: Multiple recipients of list ORACLE-L
> Subject: audits
>
> 1) What is the SQL that will report all objects and actions that are
> being audited within any specific instance?
>
> 2) I have an instance where hundreds of users all login with the same
> Oracle username and can store Oracle Browser queries within the DB.
> This has resulted in thousands & thousands of views being stored
> within the DB. Is there a way to enable auditing against just
> views (owned by a specific schema)? If so, how? The idea being
> that after a few months of auditing I'll be able to generate a
> report of those views that have not been used & be able to delete
> them.
>
> Comments, ideas & suggestions are welcomed.
>
> TIA & HAND!
>
> --
> Charlie Mengler Maintenance Warehouse
> charliem_at_mwh.com 5505 Morehouse Drive
> 858-552-6229 San Diego, CA 92121
> There is a fine line between vision & hallucination. Don't cross it!
> --
> Author: Charlie Mengler
> INET: charliem_at_mwh.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
Received on Thu Jul 20 2000 - 11:47:02 CDT