Re: Trigger not firing!!!!!!!!!!(URGENT)
- From: "Radoulov, Dimitre" <cichomitiko@xxxxxxxxx>
- To: "Onkar N Tiwary" <onkarnath.tiwary@xxxxxxxxx>
- Date: Thu, 9 Jun 2005 15:28:12 +0200
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
>> >
>> > --
>> > //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
>
> --
> //www.freelists.org/webpage/oracle-l
--
Thanks & Regards,
T. Onkar Nath
Ph : +91-9826728111(Cell)
to_onkar@xxxxxxxxx
onkarnath.tiwary@xxxxxxxxx
--
//www.freelists.org/webpage/oracle-l
Other related posts:
