ciphersol
asked on
Oracle Failed Login Trigger - Capture Oracle User
How can the Oracle username be captured using a trigger and table for a failed log on attempt. I'm aware Oracle auditing captures this information. See example below:
create table stats$user_log
(
terminal varchar2(255),
os_username varchar2(255),
username varchar2(30),
extended_timestamp date)
tablespace users
;
CREATE OR REPLACE TRIGGER failed_logon_trg
AFTER SERVERERROR ON DATABASE
when (ora_server_error(1)=1017)
BEGIN
insert into stats$user_log
(terminal, os_username, username, extended_timestamp)
values
(SYS_CONTEXT('USERENV','TE RMINAL'),
SYS_CONTEXT('USERENV','OS_ USER'),
USER,
sysdate);
COMMIT;
END failed_logon_trg;
/
I've tried both 1) USER result NULL and 2) SYS_CONTEXT('USERENV','CUR RENT_USER' ) result SYS
create table stats$user_log
(
terminal varchar2(255),
os_username varchar2(255),
username varchar2(30),
extended_timestamp date)
tablespace users
;
CREATE OR REPLACE TRIGGER failed_logon_trg
AFTER SERVERERROR ON DATABASE
when (ora_server_error(1)=1017)
BEGIN
insert into stats$user_log
(terminal, os_username, username, extended_timestamp)
values
(SYS_CONTEXT('USERENV','TE
SYS_CONTEXT('USERENV','OS_
USER,
sysdate);
COMMIT;
END failed_logon_trg;
/
I've tried both 1) USER result NULL and 2) SYS_CONTEXT('USERENV','CUR
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
No problem. Glad to help.
ASKER