Unexplainable rows in AUD$

Hey all,
On 9.2.0.5.0 (HPUX 11.11), we had a new programmer add a column to a table. This caused an avalanche of dependant objects to become invalid. Since we have a job that regularly checks for invalid objects in production, we were notified. In the same DB, I also have auditing on for everything fail or success, by access, save for successful logins. Here's a dump of SYS.DBA_STMT_AUDIT_OPTS (hopefully formatted enough to be readable):

USER_NAME  PROXY_NAME  AUDIT_OPTION          SUCCESS      FAILURE
NULL       NULL        ALTER SYSTEM          BY ACCESS    BY ACCESS
NULL       NULL        SYSTEM AUDIT          BY ACCESS    BY ACCESS
NULL       NULL        CREATE SESSION        NOT SET      BY ACCESS
NULL       NULL        TABLE                 BY ACCESS    BY ACCESS
NULL       NULL        CLUSTER               BY ACCESS    BY ACCESS
NULL       NULL        TABLESPACE            BY ACCESS    BY ACCESS
NULL       NULL        USER                  BY ACCESS    BY ACCESS
NULL       NULL        ROLLBACK SEGMENT      BY ACCESS    BY ACCESS
NULL       NULL        INDEX                 BY ACCESS    BY ACCESS
NULL       NULL        CREATE USER           BY ACCESS    BY ACCESS
NULL       NULL        ALTER USER            BY ACCESS    BY ACCESS
NULL       NULL        DROP USER             BY ACCESS    BY ACCESS
NULL       NULL        SYNONYM               BY ACCESS    BY ACCESS
NULL       NULL        PUBLIC SYNONYM        BY ACCESS    BY ACCESS
NULL       NULL        VIEW                  BY ACCESS    BY ACCESS
NULL       NULL        SEQUENCE              BY ACCESS    BY ACCESS
NULL       NULL        DATABASE LINK         BY ACCESS    BY ACCESS
NULL       NULL        PUBLIC DATABASE LINK  BY ACCESS    BY ACCESS
NULL       NULL        ROLE                  BY ACCESS    BY ACCESS
NULL       NULL        PROCEDURE             BY ACCESS    BY ACCESS
NULL       NULL        TRIGGER               BY ACCESS    BY ACCESS
NULL       NULL        ALTER TABLE           BY ACCESS    BY ACCESS
NULL       NULL        GRANT TABLE           BY ACCESS    BY ACCESS
NULL       NULL        SYSTEM GRANT          BY ACCESS    BY ACCESS

My issue is in the DBA_AUDIT_TRAIL view of AUD$. Some of the rows in here show that triggers were created (audit ACTION = 59 "CREATE TRIGGER") by end-users, based on the OS_USERNAME and TERMINAL columns. End-users do not have the knowledge nor the schema password to create these triggers. I haven't been able to reproduce this in test.

I figure the ACTION "59" rows in DBA_AUDIT_TRAIL from the end-users got there because the program they were running caused the invalid trigger to fire. Since the trigger was invalid, a recompile was attempted. And since the RETURNCODE on the DBA_AUDIT_TRAIL row is 0, the recompile should have been successful.

But when I try this, I get no audit row. Here's my test:

CREATE TABLE MYTABLE
( MYVARCHAR VARCHAR2(10) )
/

CREATE OR REPLACE TRIGGER MYTABLE_TRG
BEFORE INSERT ON MYTABLE FOR EACH ROW
BEGIN
        INSERT INTO MYAUDIT VALUES ('Fired'); END MYTABLE_TRG;
/

Simple enough. And the corresponding audit rows appear in DBA_AUDIT_TRAIL. I then tested this by inserting a single row into MYTABLE:

INSERT INTO MYTABLE (MYVARCHAR) VALUES ('Done'); COMMIT; And the trigger works as expected. I now invalidate the trigger:

ALTER TABLE MYTABLE ADD (MYNEWCOL CHAR(1))
/

A check of the STATUS column in USER_OBJECTS shows that the trigger on MYTABLE is invalid. So I force a revalidate of the trigger via insert:

INSERT INTO MYTABLE (MYVARCHAR) VALUES ('Done again'); COMMIT; But there are no rows in DBA_AUDIT_TRAIL regarding the trigger. I also tried adding a column to the MYAUDIT table and inserting a row in MYTABLE. The INSERT blew up because the trigger could not be validated, so I dropped the column and added the row to MYTABLE to fire the trigger. The only new rows in DBA_AUDIT_TRAIL are the ALTER TABLEs from me adding/dropping columns.

Anyone know how else these seemingly errant rows could get into DBA_AUDIT_TRAIL?

Rich

Rich Jesse                        System/Database Administrator
rich.jesse_at_quadtechworld.com      QuadTech, Sussex, WI USA

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