账号创建好后,可以通过如下命令查看权限:
show grants for user@host; MySQL [mysql]> show grants for p2@'%'; +-------------------------------------------------------------+ | Grants for p2@% | +-------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'p2'@'%' | | GRANT SELECT, INSERT, UPDATE, DELETE ON `db1`.* TO 'p2'@'%' | +-------------------------------------------------------------+ 2 rows in set (0.00 sec) 更改账户权限 创建用户账号p4,权限为对db1所有表具有select权限 MySQL [mysql]> grant select on db1.* to p4@'%'; Query OK, 0 rows affected, 1 warning (0.01 sec) MySQL [mysql]> show grants for p4@'%'; +-------------------------------------+ | Grants for p4@% | +-------------------------------------+ | GRANT USAGE ON *.* TO 'p4'@'%' | | GRANT SELECT ON `db1`.* TO 'p4'@'%' | +-------------------------------------+ 2 rows in set (0.00 sec) 增加delete权限 MySQL [mysql]> grant delete on db1.* to p4@'%'; Query OK, 0 rows affected (0.01 sec) MySQL [mysql]> show grants for p4@'%'; +---------------------------------------------+ | Grants for p4@% | +---------------------------------------------+ | GRANT USAGE ON *.* TO 'p4'@'%' | | GRANT SELECT, DELETE ON `db1`.* TO 'p4'@'%' | +---------------------------------------------+ 2 rows in set (0.00 sec)和已有的 select 权限进行合并
删除delete权限revoke 语句可以回收已经赋予的权限
MySQL [mysql]> show grants for p4@'%'; +---------------------------------------------+ | Grants for p4@% | +---------------------------------------------+ | GRANT USAGE ON *.* TO 'p4'@'%' | | GRANT SELECT, DELETE ON `db1`.* TO 'p4'@'%' | +---------------------------------------------+ 2 rows in set (0.00 sec) MySQL [mysql]> revoke delete on db1.* from p4@'%'; Query OK, 0 rows affected (0.01 sec) MySQL [mysql]> show grants for p4@'%'; +-------------------------------------+ | Grants for p4@% | +-------------------------------------+ | GRANT USAGE ON *.* TO 'p4'@'%' | | GRANT SELECT ON `db1`.* TO 'p4'@'%' | +-------------------------------------+ 2 rows in set (0.00 sec)usage能revoke?
MySQL [mysql]> revoke select on db1.* from p4@'%'; Query OK, 0 rows affected (0.02 sec) MySQL [mysql]> show grants for p4@'%'; +--------------------------------+ | Grants for p4@% | +--------------------------------+ | GRANT USAGE ON *.* TO 'p4'@'%' | +--------------------------------+ 1 row in set (0.00 sec) MySQL [mysql]> revoke usage on db1.* from p4@'%'; ERROR 1141 (42000): There is no such grant defined for user 'p4' on host '%'usage 权限不能被回收,也就是说,revoke 用户并不能删除用户。
要彻底的删除账号,可以使用 drop user
drop user p4@'%'; 账号资源限制创建 MySQL 账号时,还有一类选项称为账号资源限制,这类选项的作用是限制每个账号实际具有的资源限制,这里的“资源”主要包括:
max_queries_per_hour count : 单个账号每小时执行的查询次数 max_upodates_per_hour count : 单个账号每小时执行的更新次数 max_connections_per_hour count : 单个账号每小时连接服务器的次数 max_user_connections count : 单个账号并发连接服务器的次数注意:
添加用户或者权限,使用mysql> flush privileges; 刷新权限
具体权限可以参考官网文档
https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html
Linux公社的RSS地址:https://www.linuxidc.com/rssFeed.aspx