mysql授权用户,撤销用户,撤销权限基本操作
进入mysql库
use mysql; mysql> select host,user from user; +-----------+---------------+ | host | user | +-----------+---------------+ | % | root | | localhost | mysql.session | | localhost | mysql.sys | | localhost | root | +-----------+---------------+ 4 rows in set (0.00 sec) select host,user from user where user='root';
将相应用户数据表中的host字段改成'%';
update user set host='%' where user='root'; update user set host='localhost' where user='root' and host='192.168.%.%'; //ERROR 1062 (23000): Duplicate entry '%-root' for key 'PRIMARY' 不予理会 flush privileges
授权用户
grant all privileges on *.* to 'root'@'172.18.%.%' identified by '123456' with grant option; grant all privileges on *.* to 'root'@'192.168.1.%' identified by '123456' with grant option;
撤销权限
revoke all privileges on *.* from root@'%'; revoke all privileges on db01.* from root@'%';
删除用户
drop user 'test'@'localhost';
查看用户的授权
mysql> SHOW GRANTS FOR 'user01'@'%'; +--------------------------------------------------------+ | Grants for user01@% | +--------------------------------------------------------+ | GRANT USAGE ON *.* TO 'root'@'%' | | GRANT ALL PRIVILEGES ON `db01`.* TO 'user01'@'%' | +--------------------------------------------------------+ 2 rows in set (0.00 sec) //USAGE:create user时的默认的空权限,只能连库,所以user01现在的权限是在mysql01数据库上有所有的权限。