mysql 查看当前登陆用户匹配原则及权限user()与current_user()

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来作为当前的用户。