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
AFTER LOGON ON DATABASE

DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
loginDate date;
userAccount varchar2(200);
programma varchar2(200);
hostName varchar2(200);

BEGIN

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);
COMMIT;

if ( userAccount not in ('SYS','SYSTEM','DBSNMP') ) and (instr(upper(programma),'SOCKET') = 0)
then
begin
  EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''mail.server.com''';
  UTL_MAIL.send(sender => 'oracle@'||sys_context('USERENV','DB_NAME')||'.server.com',
  recipients => 'admin@server.com',
  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;
end if;
END;

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..

 

Advertisements

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s