In searching through the Database and Client events, the closest thing I could find initially was AFTER LOGON. That didn't make sense because of that little word AFTER. I then realized today that I could use SERVERERROR.
The SERVERERROR event gives you access to the following Attribute Functions:
I created the trigger using ORA_LOGIN_USER but when I received the email, it would be blank.
That makes sense, they're not logged in.
What could I do?
Well, nothing really there. Some people discussing the same problem, the inability to retrieve the username attempting to login.
Then I went to Metalink and found Doc ID: 352389.1 (not sure how to link up now with the "new" site). The note talks about enabling auditing (done) and an init.ora parameter, but the username was still illusive.
I don't know the internals, but Oracle needs to know the username or how else would it know is logging in?
Anyway, I settled on the following for the time being.
CREATE OR REPLACENow I didn't really want this to fire on every single event, but I had problems with the WHEN clause.
AFTER SERVERERROR ON DATABASE
b VARCHAR2(3) := UTL_TCP.CRLF;
l_subject VARCHAR2(40) := 'Alert - Failed Login';
IF ora_is_servererror( 01017 ) THEN
l_message := 'Date/Time: ' || TO_CHAR( SYSDATE, 'mm/dd/yyyy hh24:mi:ssss' ) || b;
l_message := l_message || 'OS User: ' || SYS_CONTEXT( 'USERENV', 'OS_USER' ) || b;
l_message := l_message || 'Host: ' || SYS_CONTEXT( 'USERENV', 'HOST' ) || b;
l_message := l_message || 'Terminal: ' || SYS_CONTEXT( 'USERENV', 'TERMINAL' ) || b;
l_message := l_message || 'IP Address: ' || SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) || b;
l_message := l_message || 'Protocol: ' || SYS_CONTEXT( 'USERENV', 'NETWORK_PROTOCOL' ) || b;
l_message := l_message || 'Database Instance: ' || ora_instance_num || b;
l_message := l_message || 'Database Name: ' || ora_database_name || b;
( sender => ora_database_name || '@revolutionmoney.com',
recipients => 'firstname.lastname@example.org',
subject => l_subject,
message => l_message );
WHEN others THEN
So if you know how to 1, capture the username or 2, apply the WHEN clause to only fire on ORA-01017, please comment.