Audit table insert restriction

auditoracleoracle-11g-r2triggerusers

So I'm trying to create my own audit table for some user management analysis and stumbled across a little problem. I use this trigger

CREATE OR REPLACE 
TRIGGER usr_t_audit_user_alnd
    AFTER LOGON ON DATABASE
    BEGIN
        INSERT INTO usr_t_audit_user (username, session_id, logon_day, logon_time)
        VALUES(UPPER(USER), SYS_CONTEXT('USERENV','SID'), SYSDATE, TO_CHAR(SYSDATE, 'HH24:MI:SS'));
    END;

to insert some standard informations into my table. It works fine, except the logoff time inside my other trigger isn't always that correct. But my actual problem is, that I don't want to insert all user logons. For example I only want to audit some chosen users. How can I achieve such a restriction? I can't put a WHERE-clause in my trigger. Anybody got some ideas?

Best Answer

You probably want to use something like,

CREATE OR REPLACE TRIGGER usr_t_audit_user_alnd AFTER LOGON ON DATABASE
BEGIN
  -- insert audit record only if user is one of specified, else ignore
  if ora_login_user in ('USER_A','USER_C','USER_D') then
    INSERT INTO usr_t_audit_user (username, session_id, logon_day, logon_time)
    VALUES(UPPER(USER), SYS_CONTEXT('USERENV','SID'), SYSDATE, TO_CHAR(SYSDATE, 'HH24:MI:SS'));
  end if;
END;
Related Question