## 通过grant [aɪ'dɛntɪfaɪd] 命令创建用户并授权 * grant命令简单语法 ``` grant all privileges on dbname.* to username@localhost identified by ‘password’; # 创建用户并授权 ``` 列表说明参数: | grant | all privileges | on dbname.* | to username@localhost | identified by ‘password’ | | -- | -- | -- | -- | -- | | 授权命令 | 对应权限 | 目标:库和表 | 用户名和客户端主机 | 用户密码 | > 说明:上述命令是授权localhost主机上通过username管理dbname数据库的所有权限,密码为password。其中username，dbname，password根据业务情况自行修改。 * create和grant配合方法创建用户并授权 ``` CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password'; # 创建用户 grant all privileges on test.* to 'jeffrey'@'localhost'; # 给用户授权 show grants for 'jeffrey'@'localhost'; # 查看用户权限明细 ``` * 授权局域网内主机远程连接数据库: 根据grant命令语法，我们知道'jeffrey'@'localhost'位置为授权访问数据库的主机,localhost可以使用域名,IP地址或者IP段来替代,因此，要授权局域网内主机可以通过如下方法实现: ``` grant all on *.* to username@'192.168.1.%' identified by 'password'; # 百分号匹配 grant all on *.* to username@'192.168.1.0/255.255.255.0' identified by 'password'; # 子网掩码配置 ``` * 删除MySQL用户 ``` DROP USER 'jeffrey'@'localhost'; ``` ## MySQL用户可以授权的权限有哪些? ``` insert, select, update, delete, create, drop, references, index, alter, create temporary tables, lock tables, execute, create view, show view, create routine, alter routine, event, trigger ``` ## 创建一个数据库备份用户权限 ``` grant SELECT, RELOAD, SHOW DATABASES, LOCK TABLES, REPLICATION CLIENT, EVENT,TRIGGER ON *.* TO 'back_user'@'Host' IDENTIFIED BY 'Password'; flush privileges; ``` 权限列表 1. SELECT 读取 2. SHOW DATABASES 允许访问完整的数据库列表 4. LOCK TABLES 允许锁定表 5. RELOAD 允许载入和刷新服务器缓存 6. REPLICATION CLIENT 允许用户询问从属服务器或主服务器的地址 7. EVENT 允许执行事务 8. TRIGGER 触发器相关的权限