如何在MySQL中找到当前用户连接的IP地址？ [英] How can I find the IP address of the current user's connection in MySQL?

问题描述

在你回答使用current_user（）之前，它确实适用于很多情况，或者使用user（），这真的不起作用，请阅读以下内容......

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

我试图在表上创建一个视图，限制用户对表中某些行的访问，由用户连接的IP地址控制。

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';

现在理论上我应该可以从主机192.168.3.30以testuser身份登录做一些类似从testview 中选择*并获取适用于我的测试表的适当子集。

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.

以上是不行。它不起作用的原因是 current_user（）默认返回视图的 definer ，导致没有数据，或者（更糟）错误数据，取决于定义者是谁。如果我想 current_user（）返回调用用户，我需要使用 SQL SECURITY INVOKER <创建视图/ code>子句，它还限制了调用用户的安全权限，从而破坏了代码的原始目的。

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.

我很乐意使用 user（），但不幸的是，几乎总是返回主机名/域而不是IP地址。

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

Side请注意，如果不清楚：在这种情况下，获取PHP（或Ruby，或perl或其他）的IP地址是没有用的。我正在建立一些数据库安全性，所以依赖客户端显然是不够的。我需要SQL中的IP地址。

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:

作为参考，我从这里获得了这个漂亮的安全技巧的想法，但他们'重新使用用户名而不是IP地址，这将使这更容易。就我而言，我正在尝试建立一个主机数据库，该数据库从主机本身部分更新。我不想为每个主机设置不同的用户，但我确实希望每个主机能够更新自己的记录（文件系统，风扇速度，温度等）。

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).

推荐答案

我讨厌留下未回答的问题......

I hate to leave a question unanswered...

似乎没有办法要做到这一点，不要全局修改mysqld的行为（通过完全禁用名称解析），这显然会在其他方面产生影响。

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.

幸运的是，另一个选择是创建一个通过SSH访问的存根程序，它接受来自客户端的数据，检查IP地址，并将（可能修改的）数据传递给数据库。当然，这使用SSH验证而不是数据库帐户，并引入了另一层复杂性。它还要求您具有对服务器的shell级访问权限，该服务器可以充当中间人。从好的方面来说，如果正确实现存根，它可以提供更好的安全性（线路加密和高级认证）。

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.

这篇关于如何在MySQL中找到当前用户连接的IP地址？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！