| How to grep client's IP address when LOGON [message #518507] |
Thu, 04 August 2011 03:54  |
JADYCHEN
Messages: 82
Registered: July 2010
|
Member |
|
|
Hi!! All Experts!!
How to grep client's IP address when LOGON to database??
I write a trigger , but when compile , it show Error ,
Here is code :
CREATE OR REPLACE TRIGGER On_Logon
AFTER LOGON ON The_user.Schema
DECLARE
v_addr VARCHAR2(11);
BEGIN
IF (ora_sysevent = 'LOGON') THEN
v_addr := ora_client_ip_address;
raise_application_error( '-20001', 'user IP: '||v_addr);
END IF;
END;
and the Error :
ORA-30506: "system triggers cannot be based on tables or
views"
*Cause: An attempt was made to base a system trigger on a
table or a view.
*Action: Make sure the type of the trigger is compatible
with the base object.
Please help me , Thanks!!
|
|
|
|
|
|
| Re: How to grep client's IP address when LOGON [message #518515 is a reply to message #518508] |
Thu, 04 August 2011 04:57  |
JADYCHEN
Messages: 82
Registered: July 2010
|
Member |
|
|
Hi!! Dear Michel Cadot ,
I had success compile the trigger , code here :
CREATE OR REPLACE TRIGGER On_Logon
AFTER LOGON
ON scott.Schema
DECLARE
v_addr VARCHAR2(11);
BEGIN
IF (ora_sysevent = 'LOGON') THEN
v_addr := ora_client_ip_address;
raise_application_error( '-20001', '使用者IP: '||v_addr);
END IF;
END;
but , when I re-logon scott/tiger , it seem not fire this trigger ,
and let me connect no any error ,
Please help me , Thanks!!
|
|
|
|
|
|
|
|
|
|
| Re: How to grep client's IP address when LOGON [message #518546 is a reply to message #518507] |
Thu, 04 August 2011 09:33 |
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
You will get client ip address only if tcp ip was used as connection protocol:
SQL> CREATE OR REPLACE
2 TRIGGER On_Logon
3 AFTER LOGON ON U1.SCHEMA
4 BEGIN
5 raise_application_error('-20001','user IP: ' || ora_client_ip_address);
6 END;
7 /
Trigger created.
SQL> connect u1/u1
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: user IP:
ORA-06512: at line 2
Warning: You are no longer connected to ORACLE.
SQL> connect u1@sol10
Enter password: **
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: user IP: XX.XXX.XXX.XXX
ORA-06512: at line 2
SQL>
SY.
|
|
|
|
| Re: How to grep client's IP address when LOGON [message #518581 is a reply to message #518546] |
Thu, 04 August 2011 22:18 |
JADYCHEN
Messages: 82
Registered: July 2010
|
Member |
|
|
Hi!! Dear syakobson ,
I copy and paste your code (just modify schema u1, example : user1.schema) , and compile at schema user1's
connection , then logout from user1 , and then re-logon (connect user1/xxxx@databasname) database , and then
connected no any error !! , I do'nt know what happen ?? , Please Help me , Thanks!!
[Updated on: Thu, 04 August 2011 22:19] Report message to a moderator |
|
|
|
|
|
| Re: How to grep client's IP address when LOGON [message #518599 is a reply to message #518582] |
Fri, 05 August 2011 00:48 |
JADYCHEN
Messages: 82
Registered: July 2010
|
Member |
|
|
Hi!! Dear BlackSwan ,
Sorry , now I copy and paste and show oracle response
CREATE OR REPLACE TRIGGER On_Logon AFTER LOGON ON RubyTST.SCHEMA
BEGIN
raise_application_error('-20001','user IP: ' || ora_client_ip_address);
END;
/
and show : Trigger created.
and then connect by rubytst :
Connect rubytst/****@orcl
and show : connected.
and then when I excute the code :
it show the select result,that's what I happen , Please help me!! , Thanks!!
|
|
|
|
|
|
|
|
| Re: How to grep client's IP address when LOGON [message #518652 is a reply to message #518599] |
Fri, 05 August 2011 04:50 |
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
JADYCHEN wrote on Fri, 05 August 2011 01:48Please help me!!
You are staring at two and a half line code for two days now and can't figure it out??? In fact, there is only one line in your code that can raise ORA-06502:
v_addr := ora_client_ip_address;
Who told you IP address is limited to 11 characters? And what's the purpose of checking ora_sysevent = 'LOGON' in AFTER LOGON trigger? Anyway, change your trigger to:
CREATE OR REPLACE TRIGGER On_Logon
AFTER LOGON ON The_user.Schema
BEGIN
raise_application_error( '-20001', 'user IP: ' || ora_client_ip_address);
END;
SY.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
