Logon Trigger Not Working?
I have a database login trigger which is supposed to turn on auditing for a user with DBA privilege
CREATE OR REPLACE TRIGGER dba_logon_audit after logon on database
declare
v_username varchar2(30);
v_cnt number default 0;
begin
select user into v_username from dual;
select count(*) into v_cnt from dba_role_privs where grantee = v_username and granted_role = 'DBA';
if v_cnt > 0 then
execute immediate 'audit select any table by '||v_username;
execute immediate 'audit delete any table by '||v_username;
execute immediate 'audit insert any table by '||v_username;
execute immediate 'audit update any table by '||v_username;
execute immediate 'audit execute any procedure by '||v_username;
execute immediate 'audit alter any procedure by '||v_username;
execute immediate 'audit create any index by '||v_username;
execute immediate 'audit drop any index by '||v_username;
execute immediate 'audit alter any index by '||v_username;
execute immediate 'audit create any table by '||v_username;
execute immediate 'audit drop any table by '||v_username;
execute immediate 'audit alter any table by '||v_username;
execute immediate 'audit create any sequence by '||v_username;
execute immediate 'audit drop any sequence by '||v_username;
execute immediate 'audit alter any sequence by '||v_username;
execute immediate 'audit create any trigger by '||v_username;
execute immediate 'audit drop any trigger by '||v_username;
execute immediate 'audit alter any trigger by '||v_username;
execute immediate 'audit create any view by '||v_username;
execute immediate 'audit drop any view by '||v_username;
execute immediate 'audit alter any view by '||v_username;
execute immediate 'audit alter database by '||v_username;
execute immediate 'audit alter profile by '||v_username;
end if;
end dba_logon_audit;
Once I am logged in as a DBA it looks like the audit statements did not run??
I have the audit_trail set to DB and this is Oracle 8.1.7.
Thanks
