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 accountdetermines your access privileges. The return value is a string in theutf8 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表中的哪一个?
[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来作为当前的用户。