查看数据库管理员root@localhost的权限:
mysql> show grants for root@localhost;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*398D8BD83BF246533D81FF0E614402C6505D0EED' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql>
笺注:如果站点的数据库配置文件中直接使用了数据库管理员户root@localhost,并且站点文件和数据库在同一台服务器上,就无需另外创建数据库用户、授权了。
######
######
创建一个数据库用户并授权:
授权数据库用户zhuohua@'%'(密码886),可以从任意IP进行访问,对所有的库有完全控制的权限:
mysql> grant all on *.* to zhuohua@'%' identified by '886';
Query OK, 0 rows affected (0.01 sec)
查看数据库用户zhuohua@'%'的权限:
mysql> show grants for zhuohua@'%';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for zhuohua@% |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'zhuohua'@'%' IDENTIFIED BY PASSWORD '*F961C54AFEB4D281CE53D7CB8E7822890D86FFFC' |
+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
再次查看所有数据库用户及其主机信息:
######
######
在MySQL服务器本地修改数据库用户zhuohua@'%'的密码为1234:
[root@localhost ~]# mysql -u"root" -p"1688" -e "update mysql.user set password=password('1234') where user='zhuohua' and host='%';flush privileges;"
[root@localhost ~]#
[root@localhost ~]# mysql -u"root" -p"1688" -e "show grants for zhuohua@'%';"
+-----------------------------------------------------------------------------------------------------------------+
| Grants for zhuohua@% |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'zhuohua'@'%' IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' |
+-----------------------------------------------------------------------------------------------------------------+
[root@localhost ~]#
注释:密码是加密了的。
######
######
在MySQL服务器本地撤销数据库用户zhuohua@'%'对所有的库有完全控制的权限:
[root@localhost ~]# mysql -u"root" -p"1688" -e "revoke all on *.* from zhuohua@'%';flush privileges;"
[root@localhost ~]#
再次查看数据库用户zhuohua@'%' 的权限:
[root@localhost ~]# mysql -u"root" -p"1688" -e "show grants for zhuohua@'%';"
+--------------------------------------------------------------------------------------------------------+
| Grants for zhuohua@% |
+--------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhuohua'@'%' IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' |
+--------------------------------------------------------------------------------------------------------+
[root@localhost ~]#
此时数据库用户 zhuohua@'%' 可以登录SQLyog,但实际上已经没有任何权限了:
######
重新授权数据库用户zhuohua@'%'(密码1234),可以从任意IP进行访问,只对库data1有完全控制的权限:
[root@localhost ~]# mysql -u"root" -p"1688" -e "grant all on data1.* to zhuohua@'%' identified by '1234';"
[root@localhost ~]#
重新查看数据库用户zhuohua@'%'的权限:
[root@localhost ~]# mysql -u"root" -p"1688" -e "show grants for zhuohua@'%';"
+--------------------------------------------------------------------------------------------------------+
| Grants for zhuohua@% |
+--------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhuohua'@'%' IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' |
| GRANT ALL PRIVILEGES ON `data1`.* TO 'zhuohua'@'%' |
+--------------------------------------------------------------------------------------------------------+
[root@localhost ~]#
此时数据库用户 zhuohua@'%' 可以登录SQLyog,只对库data1有完全控制的权限:
######
撤销数据库用户zhuohua@'%'对库data1的完全控制权限:
[root@localhost ~]# mysql -u"root" -p"1688" -e "revoke all on data1.* from zhuohua@'%';flush privileges;"
[root@localhost ~]#
重新查看数据库用户zhuohua@'%'的权限:
[root@localhost ~]# mysql -u"root" -p"1688" -e "show grants for zhuohua@'%';"
+--------------------------------------------------------------------------------------------------------+
| Grants for zhuohua@% |
+--------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhuohua'@'%' IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' |
+--------------------------------------------------------------------------------------------------------+
[root@localhost ~]#
笺注:撤销数据库用户权限的方式,要匹配授权时的方式。
######
删除数据库用户zhuohua@'%':
[root@localhost ~]# mysql -u"root" -p"1688" -e "drop user zhuohua@'%';flush privileges;"
[root@localhost ~]#
数据库用户被删除后,再查询其权限,会有如下显示:
[root@localhost ~]# mysql -u"root" -p"1688" -e "show grants for zhuohua@'%';"
ERROR 1141 (42000) at line 1: There is no such grant defined for user 'zhuohua' on host '%'
[root@localhost ~]#
重新查看所有数据库用户及其主机信息:
[root@localhost ~]# mysql -u"root" -p"1688" -e "select user,host from mysql.user;"