Re: How to trap the user's login program and disallow if Windows based?

From: Andy <enzoweb_at_hotmail.com>
Date: 5 Jun 2002 21:58:20 -0700
Message-ID: <8d4033cd.0206052058.554268bf@posting.google.com>

Thanks for all the info and advice; they are supposedly going to upgrade to V817 soon, so I have created a trigger using the LOGON ON DATABASE facility in 8i to insert the username and module into a table:

create or replace trigger disallow_toad after logon on database declare

        uname   VARCHAR2(30);
        modname VARCHAR2(48);
        cursor c_progname is
        select * from v$session;
        session_info    c_progname%ROWTYPE;
begin
        select username into uname from v$session
        where username='ANDY';
        select module into modname from v$session
        where username='ANDY';
        open c_progname;
    loop
        fetch c_progname into session_info;
        exit when c_progname%notfound;
        if session_info.username ='ANDY'
        then
        insert into oradba.logit
        values(session_info.username,session_info.module);
      end if;
     end loop;

end;
/

However, this doesn't seem to work properly, because it only inserts the username into the table, unless I rerun it while the user is logged in.

$ sqlplus andy

SQL*Plus: Release 8.0.6.0.0 - Production on Thu Jun 6 14:50:43 2002

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Enter password:

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production With the Partitioning option
JServer Release 8.1.7.0.0 - Production

SQL> select * from oradba.logit;

USERNAME MODULE
-------------------- ------------------------------------------------
ANDY SQL> @logon_trig

Trigger created.

SQL> select * from oradba.logit;

USERNAME MODULE
-------------------- ------------------------------------------------
ANDY

ANDY                 SQL*Plus

It inserts the username at logon time, but for some reason doesn't insert the module.

I suppose I could scheduled a job to run the procedure every minute as someone suggested, but it seems a bit clunky.

As for a user changing the program name - there has to be a level of trust, and while they could possibly do this I wouldn't expect them to. Received on Wed Jun 05 2002 - 23:58:20 CDT