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

From: Radoulov, Dimitre <cichomitiko_at_gmail.com>
Date: Thu, 9 Jun 2005 15:28:12 +0200
Message-ID: <031b01c56cf7$182e0c00$1a03310a@IBME1D11967173>

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_at_labor.state.ny.us ; rjsearle_at_gmail.com ; yasbs_at_kocbank.com.tr ; jo_holvoet_at_amis.com ; davidsharples_at_gmail.com ; oracle-l_at_freelists.org 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_at_gmail.com> 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_at_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_at_gmail.com> To: "rjamya" <rjamya_at_gmail.com>; <oracle-l_at_freelists.org> 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_at_gmail.com> 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_at_gmail.com> 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_at_gmail.com> 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_at_gmail.com> 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_at_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_at_yahoo.com
>> > onkarnath.tiwary_at_gmail.com
>> >
>> > --
>> > 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_at_yahoo.com
> onkarnath.tiwary_at_gmail.com
>
> --
> http://www.freelists.org/webpage/oracle-l

  --
  Thanks & Regards,
  T. Onkar Nath
  Ph : +91-9826728111(Cell)

         to_onkar_at_yahoo.com
         onkarnath.tiwary_at_gmail.com

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

Received on Thu Jun 09 2005 - 09:34:28 CDT