MySQL Grant 帐号权限后,想要移除或修改权限该怎么做?
关于 MySQL 帐号权限开放的命令,可以参考下述几篇:
MySQL 帐号权限(Grant)的移除、修改
MySQL Grant 要修改权限,基本上只能移除再添加,不过移除、修改权限时,不用担心会干扰到在线运作,全部修改都会在 flush privileges 或 MySQL restart 时才会生效。
查看 MySQL 帐号的权限
mysql -u root -p mysql
SELECT user,host FROM mysql.user;
SHOW grants for 'user'@'host';
ex: SHOW grants for 'root'@'localhost';
MySQL 帐号权限移除、修改
以下使用 backup@localhost 来做范例:
先开一个帐号 backup@localhost,拿来做备份用,然后开给 SELECT 权限,但是 mysqldump 需要 LOCK TABLES 的权限,如何做呢?
开 MySQL 帐号权限
开权限语法:grant SELECT,INSERT,UPDATE,DELETE ON `db`.* TO 'backup'@'localhost' IDENTIFIED BY 'password';
grant SELECT ON `dbname`.* TO 'backup'@'localhost' IDENTIFIED BY 'password'; # 开 SELECT 权限
grant SELECT ON *.* TO 'backup'@'localhost' IDENTIFIED BY 'password'; # 开 SELECT 权限
flush privileges; # 更新授权权限
移除 MySQL 帐号权限
revoke all privileges on *.* from 'backup'@'localhost';
flush privileges; # 更新授权权限
修改 MySQL 帐号权限(移除再添加)
SHOW grants for 'backup'@'localhost'; # 先把密码 "*xxxxxxxx" 一大串复制下来
revoke all privileges on *.* from 'backup'@'localhost'; # 移除所有权限
GRANT SELECT,LOCK TABLES ON *.* TO 'backup'@'localhost' IDENTIFIED BY PASSWORD '*xxxxxxxxxx'; # 添加 SELECT, LOCK TABLS 的权限
flush privileges; # 更新授权权限
相关网页