janar
asked on
Schema level trigger
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
DECLARE
/* variable declarations-------start*/
ssn number(8) ;
hst varchar(30) ;
logtime varchar(10);
BEGIN
select sys_context('USERENV','SES SIONID') into ssn from dual;
select sys_context('USERENV','HOS T') into hst from dual;
select to_char(SYSDATE,'HH24:MM:S S') into logtime from dual;
insert into XYZDBA.Audloginlogs(user_i d,session_ id,host,lo gon_day,lo gon_time)
values (user,ssn,hst,to_date(sysd ate),logti me) ;
END ;
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 ?
regards
jana
I have created public synonyms for all these tables.
I have created USER1 who has alter/insert/update/select
I have a schema level trigger like
CREATE OR REPLACE TRIGGER XYZDBA_logintrig
AFTER LOGON ON XYZDBA.SCHEMA
DECLARE
/* variable declarations-------start*/
ssn number(8) ;
hst varchar(30) ;
logtime varchar(10);
BEGIN
select sys_context('USERENV','SES
select sys_context('USERENV','HOS
select to_char(SYSDATE,'HH24:MM:S
insert into XYZDBA.Audloginlogs(user_i
values (user,ssn,hst,to_date(sysd
END ;
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 ?
regards
jana
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.