Schema level trigger
Posted on 2003-12-03
XYZDBA is the owner of all the tables in a schema called XYZ.
I have created public synonyms for all these tables.
I have created USER1 who has alter/insert/update/select/reference priviliges on the public synonyms created above.
I have a schema level trigger like
CREATE OR REPLACE TRIGGER XYZDBA_logintrig
AFTER LOGON ON XYZDBA.SCHEMA
/* variable declarations-------start*/
ssn number(8) ;
hst varchar(30) ;
select sys_context('USERENV','SESSIONID') into ssn from dual;
select sys_context('USERENV','HOST') into hst from dual;
select to_char(SYSDATE,'HH24:MM:SS') into logtime from dual;
insert into XYZDBA.Audloginlogs(user_id,session_id,host,logon_day,logon_time)
values (user,ssn,hst,to_date(sysdate),logtime) ;
XYZDBA has administer database trigger system privilige.
Whenever USER1 logins in I want the trigger to create a row in audloginlogs table ?
Whenever I login as XYZDBA a row is getting created . But, when I login as USER1 the row is not getting created .
What am I doing wrong ?