Re: Auditing: Trigger or Audit_Trail?

On Mon, 1 Mar 2004 19:08:28 +0100, in comp.databases.oracle.server, "Michel Cadot" <micadot{at}altern{dot}org> scribbled:

>In your case, you can use:
>audit delete, update on <your objects>
>and then do something like the following to report:
>
>Column username FORMAT A11 HEADING User
>Column terminal FORMAT A11 HEADING Terminal
>Column owner FORMAT A10 HEADING Owner
>Column obj_name FORMAT A15 HEADING Object
>Column action_name FORMAT A14 HEADING Action TRUNC
>Column returncode FORMAT 99990 HEADING RC
>Column time_ FORMAT A17 HEADING Time
>Select username, terminal, owner, obj_name, action_name, returncode,
> to_char(timestamp,'DD/MM/YY HH24:MI:SS') time_
>from dba_audit_object
>order by timestamp asc
>/

Michel,

I finally figured out what the problem was. I was logged on to Oracle as SYS when I ran this before, and any editing or deleting was not audited when I did it as SYS. Logging on as the schema.owner registered the activities. Logging in as another user also registered. Nothing when on as SYS.

Sorry for being such a newbie at this, I never thought of logging in as another user!

One final thing: I would like to capture the SQL used, but I can't find the column that attaches the username/terminal to the SQL text. I can put together the user and a hash code in one table, but I can't find anything to put the user together with the SQL. How might I do that?

Thanks so much, all of you. :-)

-- 
Teresa Redmond
Programmer/Analyst III
Anteon Corporation
tredmond at anteon dot com