MikeHunt
asked on
Creating a Logon trigger
Hi,
I'm trying to implement a login trigger (see below). The code works except for the last part of the create trigger statement. I need to insert the profile for the user into the user_log table and I can only get this from the sys.dba_users table .. not sys_context. Unfotunately, when I add in the update sql cmd at the bottom the trigger has compilation problems. Any advice?
btw. If I use the commented out update statement it works .. so it's just the way I've written the statement that's the problem.
create table
audit_log
(
user_name varchar2(30),
session_id number(8),
host varchar2(30),
logon_day date,
logon_time varchar2(10),
elapsed_minutes number(8),
profile varchar2(20)
)
;
create or replace trigger
logon_audit_trigger
AFTER LOGON ON DATABASE
BEGIN
insert into audit_log values(
user,
sys_context('USERENV','SES
sys_context('USERENV','HOS
sysdate,
to_char(sysdate, 'hh24:mi:ss'),
null,
null
);
--update audit_log set profile='DEFAULT';
update stats$user_log set profile=(select profile FROM DBA_USERS WHERE USERNAME = USER) where USERNAME = USER;
END;
/
I'm trying to implement a login trigger (see below). The code works except for the last part of the create trigger statement. I need to insert the profile for the user into the user_log table and I can only get this from the sys.dba_users table .. not sys_context. Unfotunately, when I add in the update sql cmd at the bottom the trigger has compilation problems. Any advice?
btw. If I use the commented out update statement it works .. so it's just the way I've written the statement that's the problem.
create table
audit_log
(
user_name varchar2(30),
session_id number(8),
host varchar2(30),
logon_day date,
logon_time varchar2(10),
elapsed_minutes number(8),
profile varchar2(20)
)
;
create or replace trigger
logon_audit_trigger
AFTER LOGON ON DATABASE
BEGIN
insert into audit_log values(
user,
sys_context('USERENV','SES
sys_context('USERENV','HOS
sysdate,
to_char(sysdate, 'hh24:mi:ss'),
null,
null
);
--update audit_log set profile='DEFAULT';
update stats$user_log set profile=(select profile FROM DBA_USERS WHERE USERNAME = USER) where USERNAME = USER;
END;
/
Milleniumaire
What compilation errors do you get? Use SHOW ERRORS in sqlplus to display them.
ASKER
Actually ...
changing the last line to
update stats$user_log set profile=(select profile FROM DBA_USERS WHERE USERNAME = USER);
seems to eliminate the compilation errors. Let me check the results to make sure that it's working.
changing the last line to
update stats$user_log set profile=(select profile FROM DBA_USERS WHERE USERNAME = USER);
seems to eliminate the compilation errors. Let me check the results to make sure that it's working.
what is the username column in stats$user_log.
perhaps it should be USER_NAME instead of USERNAME. which would be why the where clause would fail, assuming that's the problem.
If you post the error it will help a lot
also, why aren't you populating the profile column of your audit_log?
perhaps it should be USER_NAME instead of USERNAME. which would be why the where clause would fail, assuming that's the problem.
If you post the error it will help a lot
also, why aren't you populating the profile column of your audit_log?
ASKER
Actually it should have been ...
update stats$user_log set profile=(select profile FROM DBA_USERS WHERE USERNAME = USER) where user_name=USER;
.. now it works.
update stats$user_log set profile=(select profile FROM DBA_USERS WHERE USERNAME = USER) where user_name=USER;
.. now it works.
ASKER
HI sdstuber: .. I was using the update statement to update that col. If you can see another way of updating it as part of the first statement let me know .. that would be better.
Thanks.
Thanks.
ok, I thought it was probably a column name misspelling.
you have two different tables. audit_log and stats$user_log
Do you need to update the profile in both of them?
if so, you can put the same subquery in the insert too.
you have two different tables. audit_log and stats$user_log
Do you need to update the profile in both of them?
if so, you can put the same subquery in the insert too.
insert into audit_log values(
user,
sys_context('USERENV','SESSIONID'),
sys_context('USERENV','HOST'),
sysdate,
to_char(sysdate, 'hh24:mi:ss'),
null,
(select profile FROM DBA_USERS WHERE USERNAME = USER)
);
or, select the profile into a variable and use it in both the insert to audit_log and the update of stats$user_log.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
that looks good. Let me test it. thanks.
ASKER
Thanks. That works and it's a cleaner solution.
Cheers,
Mike.
Cheers,
Mike.
ASKER
I just saw that sdstuber submitted what I think is the same code for that solution. Sorry mate .. I only saw the last post when I checked back. Don't think I can do anything about that now.
sdstuber gets enough points so I'm sure he won't mind losing a few to me ;-)
Out of interest, which solution did you go for; the straight insert statement or the insert select statement.
Is Mike Hunt your real name or are you a Porkies fan?
Out of interest, which solution did you go for; the straight insert statement or the insert select statement.
Is Mike Hunt your real name or are you a Porkies fan?
ASKER
I used the straight insert:
insert into audit_log values(
user,
sys_context('USERENV','SES
sys_context('USERENV','HOS
sysdate,
to_char(sysdate, 'hh24:mi:ss'),
null,
(select profile FROM DBA_USERS WHERE USERNAME = USER)
);
as part of my create trigger statement and it worked. And yes I did like Porkys when I first saw it. Luckily for me my real name isn't Mike Hunt or I would have had a very difficult childhood :-)
insert into audit_log values(
user,
sys_context('USERENV','SES
sys_context('USERENV','HOS
sysdate,
to_char(sysdate, 'hh24:mi:ss'),
null,
(select profile FROM DBA_USERS WHERE USERNAME = USER)
);
as part of my create trigger statement and it worked. And yes I did like Porkys when I first saw it. Luckily for me my real name isn't Mike Hunt or I would have had a very difficult childhood :-)