Oracle trigger delete ip

2
votes

Every time someone delete a user, I have to log who deleted the user. So a created a trigger that get ip address with SYS_CONTEXT('USERENV', 'IP_ADDRESS'). However, the trigger insert 0.0.0.0 instead of the ip address of who actually deleted. How can i solve this?

2
oracletriggers
How are you connected to your RDBMS server when testing ? Remotely or locally ? - Sylvain Leroux
Have you considered using UID instead of the IP address, or perhaps in addition to the IP address? An IP address by itself isn't a particularly strong way to identify someone. - Bob Jarvis - Reinstate Monica
The UID is a good ideia, but i still need the ip address. - Leonardo Guerra
At this point, you really should show us the code of your trigger to see if there is something wrong. - Sylvain Leroux

2 Answers

1
votes

I found the solution:

select UTL_INADDR.get_host_address(SYS_CONTEXT('USERENV', 'HOST')) into ip_address from dual;

It uses a inner function of Oracle.

Tanks.

0
votes

You are very probably connected locally to your Oracle instance without using the Oracle Net TCP/IP service. In that case, SYS_CONTEXT('USERENV', 'IP_ADDRESS') is NULL.

For example, when I connect to my Oracle instance through the LAN:

# using remote TCP connection:
sh$ sqlplus [email protected]
SQL> select coalesce(SYS_CONTEXT('USERENV', 'IP_ADDRESS'), 'NULL') from dual;

-> 10.1.2.3

When I do the same thing locally on the Oracle host:

# using IPC connection:
sh$ sqlplus sylvain  
SQL> select coalesce(SYS_CONTEXT('USERENV', 'IP_ADDRESS'), 'NULL') from dual;

-> NULL

But doing this will work as expected:

# using local TCP connection:
sh$ sqlplus sylvain@localhost
SQL> select coalesce(SYS_CONTEXT('USERENV', 'IP_ADDRESS'), 'NULL') from dual;

-> 127.0.0.1