How can I find the IP address of the current user&

Question

Before you answer "use current_user()", which does work for many cases, or "use user()", which really doesn't work, please read the following...

I am attempting to create a view on a table which limits user access to certain rows within the table, controlled by the IP address from which the user connects.

My first attempt looked like this:

create table testtable (
  `RowID` bigint not null auto_increment,
  `owner` varchar(64),
  `key` varchar(64),
  `val` varchar(64),
  primary key (`RowID`)
);
create view testview (
  `RowID`,
  `owner`,
  `key`,
  `val`
) as select
  `testtable`.`RowID` as `RowID`,
  `testtable`.`owner` as `owner`,
  `testtable`.`key` as `key`,
  `testtable`.`val` as `val`
from testtable
where (testtable.owner = substring_index(current_user(), '@', -1));

create user 'testuser'@'192.168.3.30' identified by 'testpass';
grant select, insert, update, delete on testview to 'testuser'@'192.168.3.30';

Now the theory is that I should be able to log in as testuser from the host 192.168.3.30 and do something like select * from testview and get the proper subset of testtable that applies to me.

The above does not work. The reason it doesn't work is that current_user() returns the view's definer by default, resulting in no data, or (worse) the wrong data, depending on who the definer was. If I want current_user() to return the invoking user, I need to create the view with a SQL SECURITY INVOKER clause, which also limits the security privileges to those of the invoking user, thus defeating the original purpose of the code.

I would love to use user(), but unfortunately, that almost always returns the hostname/domain instead of the IP address.

Side note, in case it's not clear: Getting the IP address in PHP (or Ruby, or perl, or whatever) is not useful in this case. I'm setting up a bit of database security, so relying on the client is obviously inadequate. I need the IP address in the SQL.

For the curious looking for ideas/reference/context:

For reference, I got the idea for this nifty security trick from here, but they're using the username instead of the IP address, which would make this much easier. In my case, I'm trying to set up a database of hosts which is partially updated from the hosts themselves. I don't want to set up a different user for each host, but I do want each host to be able to update its own records (of filesystems, fan speeds, temperatures, and so on).

Answer 1

If you have control over the mysqld process, you could launch it with the --skip-name-resolve option; then SUBSTRING_INDEX(USER(), '@', -1) would give you the current user's IP address.

Answer 2

I hate to leave a question unanswered...

It appears that there is no way to do this without globally modifying the behavior of mysqld (by disabling name resolution entirely), which obviously has consequences in other areas.

Fortunately, a different option is to create a "stub" program, accessed via SSH, which accepts the data from the client, checks the IP address, and passes the (possibly-modified) data on to the database. Of course, this uses SSH validation instead of a database account and introduces another layer of complexity. It also requires that you have shell-level access to a server which can act as the go-between. On the plus side, it does arguably provide better security (encryption over the line and superior authentication) if the stub is implemented properly.