Re: Trigger not firing!!!!!!!!!!(URGENT)

The select grant is only for the demo(the select from sys.log_info from the 
test's session), you don't need it.
With db link it works in the same way: you get the user you have in the 
"connect to" clause.


Cheers
Dimitre 


  ----- Original Message ----- 
  From: Onkar N Tiwary 
  To: Radoulov, Dimitre 
  Cc: rjamya ; Thomas.Mercadante@xxxxxxxxxxxxxxxxx ; rjsearle@xxxxxxxxx ; 
yasbs@xxxxxxxxxxxxxx ; jo_holvoet@xxxxxxxx ; davidsharples@xxxxxxxxx ; 
oracle-l@xxxxxxxxxxxxx 
  Sent: Thursday, June 09, 2005 3:02 PM
  Subject: Re: Trigger not firing!!!!!!!!!!(URGENT)


  Thanx a lot Dimitre and all of u too who guided me. It worked. Now I guess 
the error was that I was not issuing the select priv to the LOG user (may be) 
and while inserting I was just using "INSERT INTO LOG VALUES......". Anyways 
once again thanx a lot. One last thing I would like to know what will happen 
when a user will be logging into the database from the remote location using 
some dblinks? Will this trigger work as expected or refuse the connection????? 
If refuse the connection then how to resolve it... 



   
  On 6/9/05, Radoulov, Dimitre <cichomitiko@xxxxxxxxx> wrote: 
    Check this:


    14:15:12 SQL> CREATE TABLE log_info (login_date DATE, ipadds
    VARCHAR2(20),username VARCHAR2(30)); 

    Table created.

    Elapsed: 00:00:01.10
    14:15:17 SQL> grant select on log_info to test;

    Grant succeeded.

    Elapsed: 00:00:00.07
    14:15:25 SQL> CREATE OR REPLACE TRIGGER logon_audit AFTER LOGON ON DATABASE 
    14:15:30   2  BEGIN
    14:15:30   3  insert into sys.log_info
    values(sysdate,ora_client_ip_address,ora_login_user);
    14:15:30   4  END;
    14:15:30   5  /

    Trigger created.

    Elapsed: 00:00:00.19
    14:15:30 SQL> select * from sys.log_info;

    no rows selected

    Elapsed: 00:00:00.00
    14:15:40 SQL> conn test/test
    Connected.
    14:15:44 SQL> select * from sys.log_info;

    LOGIN_DAT IPADDS               USERNAME
    --------- -------------------- ------------------------------ 
    09-JUN-05                      TEST

    Elapsed: 00:00:00.00
    14:15:47 SQL> conn test/test@dbarepo1
    Connected.
    14:15:53 SQL> select * from sys.log_info;

    LOGIN_DAT IPADDS               USERNAME
    --------- -------------------- ------------------------------
    09-JUN-05                      TEST
    09-JUN-05 10.44.19.31          TEST

    Elapsed: 00:00:00.01
    14:15:55 SQL>

    If get the IP if you use the listener.


    Cheers
    Dimitre Radoulov

    ETNØTEAM


    ----- Original Message -----
    From: "Onkar N Tiwary" < onkarnath.tiwary@xxxxxxxxx>
    To: "rjamya" <rjamya@xxxxxxxxx>; <oracle-l@xxxxxxxxxxxxx>
    Sent: Thursday, June 09, 2005 1:51 PM 
    Subject: Re: Trigger not firing!!!!!!!!!!(URGENT)


    > Raj,
    > The code for the trigger is :
    > CREATE OR REPLACE TRIGGER INFO
    > AFTER LOGON
    > ON DATABASE
    > begin
    > insert into info 
    > 
values(ora_client_ip_address,to_char(sysdate,'dd-mm-yyyy:HH:MI:SS'),ora_login_user);
    > end;
    > And I test it by logging into the DB with another user also say a user
    > called LOG but no record in the table. Moreover I tried the following sql 
    > on
    > the prompt also:
    > SELECT ORA_CLIENT_IP_ADDRESS FROM DUAL; -- no output
    > But when I issued :
    > SELECT ORA_LOGIN_USER FROM DUAL; -- gives me the current username logged
    > in
    > the DB
    > Any idea?????
    >
    > On 6/9/05, rjamya <rjamya@xxxxxxxxx> wrote:
    >>
    >> show us the real code and don't log in as SYS to test.
    >> 
    >> Raj
    >>
    >> On 6/9/05, Onkar N Tiwary <onkarnath.tiwary@xxxxxxxxx> wrote:
    >> >
    >> > Sorry David,
    >> > Its a typo error...its not LOGOFF its LOGON... I checked it once again 
    >> > also.
    >> > On 6/9/05, David Sharples <davidsharples@xxxxxxxxx> wrote:
    >> > >
    >> > > You say its a logon trigger, but it it written as a LOGOFF - try 
    >> > > logging off first
    >> > >
    >> > > On 6/9/05, Onkar N Tiwary <onkarnath.tiwary@xxxxxxxxx> wrote:
    >> > > > hi all, 
    >> > > > I have created one trigger on LOGON event which is storing the ip
    >> > > > of
    >> > the
    >> > > > machine making the connection,current date n time and oracle 
    >> > username in
    >> > > a
    >> > > > table with the following structure:
    >> > > > 16:15:31 SQL log@TEST35> > CREATE OR REPLACE TRIGGER INFO
    >> > > > BEFORE LOGOFF 
    >> > > > ON DATABASE
    >> > > > begin
    >> > > > insert into info
    >> > > >
    >> > >
    >> > 
values(ora_client_ip_address,to_char(sysdate,'dd-mm-yyyy:HH:MI:SS'),ora_login_user);
 
    >> >
    >> > >
    >> >
    >> >
    >> >
    >> > --
    >> > Thanks & Regards,
    >> > T. Onkar Nath
    >> > Ph : +91-9826728111(Cell) 
    >> > to_onkar@xxxxxxxxx
    >> > onkarnath.tiwary@xxxxxxxxx
    >> >
    >> > --
    >> > http://www.freelists.org/webpage/oracle-l
    >> >
    >>
    >>
    >>
    >> --
    >> ------------------------------
    >> select standard_disclaimer from company_requirements where category = 
    >> 'MANDATORY';
    >>
    >
    >
    >
    > --
    > Thanks & Regards,
    > T. Onkar Nath
    > Ph : +91-9826728111(Cell)
    > to_onkar@xxxxxxxxx 
    > onkarnath.tiwary@xxxxxxxxx
    >
    > --
    > http://www.freelists.org/webpage/oracle-l





  -- 
  Thanks & Regards,
  T. Onkar Nath
  Ph : +91-9826728111(Cell)
         to_onkar@xxxxxxxxx
         onkarnath.tiwary@xxxxxxxxx 

--
http://www.freelists.org/webpage/oracle-l

Other related posts: