Auditing with an Oracle back end?

[BiffMaGriff]

Hello,

I have a .net web app with an Oracle back end and I need to audit my database. I created this template trigger that I was using on inserts, edit & deletes however...

"CREATE OR REPLACE TRIGGER {0}.BIUD_{3} 
  BEFORE INSERT OR UPDATE OR DELETE ON {0}.{2} 
  REFERENCING OLD AS OLD NEW AS NEW 
  FOR EACH ROW 
BEGIN 
  IF inserting THEN 
    SELECT {0}.{1}.nextval 
    INTO   :NEW.{4} 
    FROM   dual; 
 
    INSERT INTO {0}.{5} 
               ({4}{6}, 
                auditaction, 
                audituser, 
                auditdate) 
    VALUES     (:NEW.{4}{7}, 
                'INSERT', 
                substr(sys_context('userenv','os_user'), INSTR(sys_context('userenv','os_user'),'\')+1), 
                SYSDATE); 
  END IF; 
 
  IF updating THEN 
    INSERT INTO {0}.{5} 
               ({4}{6}, 
                auditaction, 
                audituser, 
                auditdate)
    VALUES     (:OLD.{4}{7}, 
                'UPDATE', 
                substr(sys_context('userenv','os_user'), INSTR(sys_context('userenv','os_user'),'\')+1), 
                SYSDATE); 
  END IF; 
 
  IF deleting THEN 
    INSERT INTO {0}.{5} 
               ({4}{6}, 
                auditaction, 
                audituser, 
                auditdate) 
    VALUES     (:OLD.{4}{8},
                'DELETE', 
                substr(sys_context('userenv','os_user'), INSTR(sys_context('userenv','os_user'),'\')+1), 
                SYSDATE); 
  END IF; 
END;
/"
 

I only get the username if I use impersonation and I restrict the web app to a user group (no anonymous login).
I have 1 oracle question and 1 .Net question.
Is it possible to get the correct username in Oracle without using impersonation?
If not, is there anyway I can configure .net so I don't have to use impersonation to send the user name?
If not I'm going to have to do my auditing at the application level, boooo.

[BiffMaGriff]

OK, to solve this I added ModifiedBy and IsDeleted Columns to all my tables.
In my DAL I set it up so that ModifiedBy is automatically set to the user I want.
I also changed my DAL to implement soft deletes and then my DAL automatically performs a hard delete after every soft delete. A soft delete is merely an update where the IsDeleted field is set to 1.
In my trigger I changed the audit username value to the modifiedby field that my dal passes. For deletes, I do the auditing on the soft delete that my DAL performs as that is the only opportunity to capture the user name.

This is a screwy fix to this problem, but it is a fix none the less.