Alerting on Illegal Login..Or..”Catch Me If You Can”..

We had the requirement to generate an alert whenever a user was logging into the database while bypassing the application server. Although a lot of discussion is out there of using triggers or not, I opted for a trigger due to the advantages it gives me.

This trigger does not kick the user out, but does logs him/her and raises an alert.

Prereqs are to create a table to hold the ‘audit’ info, located anywhere but in the SYSTEM tablespace, and setting the permissions to have a user insert it’s information.

SQL> create table audit.login_program_info (LOGON_TIME date, USERNAME VARCHAR2(200),PROGRAM VARCHAR2(500),MACHINE VARCHAR2(500));
SQL> grant insert on audit.login_program_info to public;

Now we only want to have the illegal login, but not the monitoring and maintenance logins (there is another audit method for these, provided by Oracle).

The trigger code:

create or replace TRIGGER login_trigger

loginDate date;
userAccount varchar2(200);
programma varchar2(200);
hostName varchar2(200);


select sysdate, username,program,machine into loginDate, userAccount,programma,hostName from v$session WHERE AUDSID = Sys_Context('USERENV', 'SESSIONID');

INSERT INTO audit.login_program_info values ( loginDate, userAccount,programma,hostName);

if ( userAccount not in ('SYS','SYSTEM','DBSNMP') ) and (instr(upper(programma),'SOCKET') = 0)
  EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''''';
  UTL_MAIL.send(sender => 'oracle@'||sys_context('USERENV','DB_NAME')||'',
  recipients => '',
  subject => 'Illegal Login Alert',
  message => 'The user '||userAccount|| ' is logged into the db ' ||sys_context('USERENV','DB_NAME')|| ' at ' ||to_Char(loginDate,'DD-MON-YYYY HH24:MI:SS')|| ' from '|| hostName ||' with program:'||programma,
  priority => 1,
  mime_type => 'text; charset=us-ascii');
end if;

Note: don’t forget to clean out the table, over time it will fill up.. 😉

This is just a basic mechanism to handle this, but easy to build out upon..


About GemsOfProgramming

Beeing a previously enthusiastic Java programmer, I rolled into the Oracle Database Administration world. It turned out I got a knack for this, and since approx. 2000 I'm a full time DBA. My experiences touches lot of Oracle products like Forms and Reports 9/10, JDAPI, Application Server, Weblogic Fusion and of course: Oracle Enterprise Databases, JavaFX, Swing and other Java components.
This entry was posted in Databases and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.