同一個賬號，不同客戶端登錄，可以根據user()來記錄IP、判斷是誰在操作。 ...
Mysql在進行登陸時，會去匹配mysql庫中的user表，並賦予相應的許可權，但是怎麼知道我們當時的登陸的用戶名及相應的許可權呢？
在Mysql中，有兩個函數，一個是user(),一個是current_user();
我們來運行一下看一下他們有什麼區別：
mysql> select user();
+----------------------+
| user() |
+----------------------+
| test@192.168.203.132 |
+----------------------+
1 row in set (0.00 sec)
mysql> select current_user();
+------------------+
| current_user() |
+------------------+
| test@192.168.%.% |
+------------------+
1 row in set (0.00 sec)
user()是用來顯示當前登陸的用戶名與它對應的host，幫助文檔是這樣描述的：
Returns the current MySQL user name and host name as a string in the
utf8 character set.
currrent_user()是用來顯示當前登陸用戶對應在user表中的哪一個，幫助文檔是這樣描述的：
Returns the user name and host name combination for the MySQL account
that the server used to authenticate the current client. This account
determines your access privileges. The return value is a string in the
utf8 character set.
所以假如我們想知道當前登陸的用戶具有什麼許可權的話，
第一步是找出當前登陸用戶是用user表中的哪一個，用current_user()
mysql> select current_user();
+------------------+
| current_user() |
+------------------+
| test@192.168.%.% |
+------------------+
1 row in set (0.00 sec)
第二步用show grants命令，如下：
mysql> show grants for 'test'@'192.168.%.%';
+--------------------------------------------------------------------------------------------------------------------------------+
| Grants for test@192.168.%.% |
+--------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE ON *.* TO 'test'@'192.168.%.%' IDENTIFIED BY PASSWORD '*032197AE5731D4664921A6CCAC7CFCE6A0698693' |
+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
好了，那另一個問題是，如果有如下的用戶名，host及許可權，我在登陸時到底會是匹配到哪一個呢？
mysql> grant select on *.* to test@192.168.203.132 identified by '000000';
Query OK, 0 rows affected (0.00 sec)
mysql> grant select,update on *.* to 'test'@'192.168.203.%' identified by '000000';
Query OK, 0 rows affected (0.01 sec)
mysql> grant select,update,insert on *.* to 'test'@'192.168.%.%' identified by '000000';
Query OK, 0 rows affected (0.01 sec)
mysql> grant select,update,insert,delete on *.* to 'test'@'192.%.%.%' identified by '000000';
Query OK, 0 rows affected (0.00 sec)
mysql> grant update,insert,delete on *.* to 'test'@'%' identified by '000000';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from user order by user,host;
+-------------+-----------------+
| user | host |
+-------------+-----------------+
| root | localhost |
| test | % |
| test | 192.%.%.% |
| test | 192.168.%.% |
| test | 192.168.203.% |
| test | 192.168.203.132 |
+-------------+-----------------+
如果我用如下命令進行登陸，會匹配到user表中的哪一個？
| 1 |
[root@host2 ~]# mysql -h192.168.203.132 -utest -p
|
我們可以用上面提到的select current_user()可以清楚地查找出來
mysql> select current_user();
+----------------------+
| user() |
+----------------------+
| test@192.168.203.132 |
+----------------------+
1 row in set (0.00 sec)
我們刪除對應的帳戶：
delete from user where user='test' and host='192.168.203.132';
再次登陸：
[root@host2 ~]# mysql -h192.168.203.132 -utest -p
此時：
mysql> select current_user();
+------------------+
| current_user() |
+------------------+
| test@192.168.203.% |
+------------------+
1 row in set (0.00 sec)
繼續刪除
mysql> delete from user where user='test' and host='192.168.203.%';
再登陸：
mysql> select current_user();
+------------------+
| current_user() |
+------------------+
| test@192.168.%.% |
+------------------+
1 row in set (0.00 sec)
以上每一次執行後用user()都可以得到相同的結果：
mysql> select user();
+----------------------+
| user() |
+----------------------+
| test@192.168.203.132 |
+----------------------+
1 row in set (0.00 sec)
所以結論是：mysql在登陸時會用最精確匹配user表中的帳戶，host來作為當前的用戶。
