capturing user IP address information for audit

We have a requirement to log IP address information of all users who use a certain web application based on Java EE 5.

What would be an appropriate sql data type for storing IPv4 or IPv6 addresses in the following supported databases (h2, mysql, oracle)?

There is also a need to filter activity from certain IP addresses. Should I just t开发者_如何学运维reat the representation as a string field (say varchar(32) to hold ipv4, ipv6 addresses)?

I'd store the IP addresses in a varchar(15). This is easily readable, and you can filter for specific IP's like where ip = '1.2.3.4'.

If you have to filter on networks, like 1.2.3.4/24, it becomes a different story. In that case your better off storing the IP address as a 4 byte binary.

If you have huge amounts of data and have to search through, for performance it would be better to convert string (dotted) representation of IPs to their proper integer values.

Either of these is valid

• 4 bytes, perhaps a 5th byte for CIDR
• varchar(15) or (18) to store full representation in one go

Saying that, varchar(48) for SQL Server's sys.dm_exec_connections...