Re: LOGIN TRIGGER

From: David Fitzjarrell <oratune_at_aol.com>
Date: Thu, 30 Nov 2000 22:09:01 GMT
Message-ID: <906j5o$qsf$1@nnrp1.deja.com>

In our last gripping episode ketan1_at_my-deja.com wrote:
> Can a trigger be used on a login event (When a user initially logs
> into an Oracle database) ?
>

If you are running 8i, yes.

> And, can a trigger that references v$session be used on a login event?
>

I don't see why you couldn't.

> If so, can someone please provide SAMPLE CODE on allowing or denying
> access to users based on the application that they are trying to
 access.
>

I cannot provide specific examples but you will need to access the PROGRAM field in V$SESSION to determine which application is attempting to access the database. You will also need a reference table of valid applications to compare with the current PROGRAM value. A possible (but untested) sample might be:

create or replace trigger logontrig after logon to database declare

     cursor get_apps(uname in varchar2) is
     select application
     from valid_apps
     where username = uname;
     app   v$session.program%type;
     found  number:=0;
begin
     select program into app
     from v$session
     where username = USER;      -- Presumes user has only one session
     for valapp in get_apps(USER) loop
         if valapp.application = app then
              found := 1;
         end if;
     end loop;
     if found = 0 then
          exit;
     end if;

end;
/

I state again that the above code has not been tested and may not function as written. However, it may provide a starting point for you to build upon. Execptions may need to be defined and raised to get this to function as you desire.

> Thanks.
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Thu Nov 30 2000 - 16:09:01 CST