MySQL 常用用户权限管理命令

た入场券 2023-02-15 15:54 15阅读 0赞

## 一、用户 ##

### 1.1 创建用户 ###

-- 创建用户，并允许其在任何IP登陆
    create user 'username'@'%' identified by 'password'; -- 创建用户，并允许其在任何主机登陆，不设置密码 create user 'username'@'%'; -- 创建用户，并仅允许使用jitwxs.cn域名的主机登陆 create user 'username'@'jitwxs.cn' identified by 'password'; -- 创建用户，并仅允许使用192.168.1.1的主机登陆 create user 'username'@'192.168.1.1' identified by 'password'; -- 创建用户，并仅允许使用192.168.1开头的主机登陆 create user 'username'@'192.168.1.%' identified by 'password';

说明：

*  密码可以为空，如为空，则可以免密登陆
 *  如果主机位使用 `%`，表示允许任意地址的主机登陆
 *  主机位可以使用域名或者 IP 地址，但是不允许既有数字又有字母
 *  主机位中可以使用 `%` 进行通配，例如：`%.jitwxs.cn` 或 `192.168.1.%`

### 1.2 修改用户 ###

> 一般在修改完密码后，需要手动执行命令，将配置刷新到内存：`flush privileges;`

-- 重新设置用户名和登陆IP
    rename user 'old_username'@'old_ip_addr' to 'new_username'@'new_ip_addr'; -- 修改密码 set password for 'username'@'ip_addr'=Password('new_password');

### 1.3 删除用户 ###

drop user 'username'@'ip_addr';

## 二、权限 ##

> 一般在调整完权限后，需要手动执行命令，将配置刷新到内存：`flush privileges;`

### 2.1 查看权限 ###

-- 查看用户所有权限
    show grants for 'username'@'ip_addr';

### 2.2 权限授予 ###

-- 授予用户所有库所有表的所有权限
    grant all privileges on *.* to 'username'@'ip_addr'; -- 授予用户database1库所有表的所有权限 grant all privileges on `database1`.* to 'username'@'ip_addr'; -- 授予用户database1库table1表的所有权限 grant all privileges on `database1`.`table1` to 'username'@'ip_addr'; -- 授予用户database1库所有表的只读权限 grant select on `database1`.* to 'username'@'ip_addr'; -- 授予用户database1库table1表的插入、更新权限 grant insert,update on `database1`.`table1` to 'username'@'ip_addr';

### 2.3 权限回收 ###

-- 回收用户所有权限
    revoke all,grant option from 'username'@'ip_addr'; -- 回收用户database1库所有表的只读权限 revoke select ON `database1`.* FROM 'xiangsheng.wu'@'%';

### 2.4 授予什么就收回什么 ###

基于 2.1 节，我们授予权限是可以有不同粒度的，且这些权限保存的位置也是不一样的，比如：

*  授予全局权限: `*.*`, 保存在: `mysql.user`
 *  授予某个库的权限: `database1.*`, 保存在: `mysql.db`
 *  授予某张表的权限: `database1.tabl1`, 保存在: `mysql.tables_priv`

因此，你要确保当初授予时是如何授予的，回收时候就要咋么回收。

例如授予时采用 `.*` 的方式授予了所有表的权限，那么回收的时候也必须使用 `.*` 的方式全部回收，而不能使用 `.table_xx` 的方式回收某一张表的权限，因为它们压根就不是一个粒度的配置。

### 2.5 附：常用权限列表 ###

<table> 
 <thead> 
  <tr> 
   <th align="left">权 限</th> 
   <th align="left">作用范围</th> 
   <th align="left">作 用</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">all</td> 
   <td align="left">服务器</td> 
   <td align="left">所有权限</td> 
  </tr> 
  <tr> 
   <td align="left">select</td> 
   <td align="left">表、列</td> 
   <td align="left">选择行</td> 
  </tr> 
  <tr> 
   <td align="left">insert</td> 
   <td align="left">表、列</td> 
   <td align="left">插入行</td> 
  </tr> 
  <tr> 
   <td align="left">update</td> 
   <td align="left">表、列</td> 
   <td align="left">更新行</td> 
  </tr> 
  <tr> 
   <td align="left">delete</td> 
   <td align="left">表</td> 
   <td align="left">删除行</td> 
  </tr> 
  <tr> 
   <td align="left">create</td> 
   <td align="left">数据库、表、索引</td> 
   <td align="left">创建</td> 
  </tr> 
  <tr> 
   <td align="left">drop</td> 
   <td align="left">数据库、表、视图</td> 
   <td align="left">删除</td> 
  </tr> 
  <tr> 
   <td align="left">reload</td> 
   <td align="left">服务器</td> 
   <td align="left">允许使用flush语句</td> 
  </tr> 
  <tr> 
   <td align="left">shutdown</td> 
   <td align="left">服务器</td> 
   <td align="left">关闭服务</td> 
  </tr> 
  <tr> 
   <td align="left">process</td> 
   <td align="left">服务器</td> 
   <td align="left">查看线程信息</td> 
  </tr> 
  <tr> 
   <td align="left">file</td> 
   <td align="left">服务器</td> 
   <td align="left">文件操作</td> 
  </tr> 
  <tr> 
   <td align="left">grant option</td> 
   <td align="left">数据库、表、存储过程</td> 
   <td align="left">授权</td> 
  </tr> 
  <tr> 
   <td align="left">references</td> 
   <td align="left">数据库、表</td> 
   <td align="left">外键约束的父表</td> 
  </tr> 
  <tr> 
   <td align="left">index</td> 
   <td align="left">表</td> 
   <td align="left">创建/删除索引</td> 
  </tr> 
  <tr> 
   <td align="left">alter</td> 
   <td align="left">表</td> 
   <td align="left">修改表结构</td> 
  </tr> 
  <tr> 
   <td align="left">show databases</td> 
   <td align="left">服务器</td> 
   <td align="left">查看数据库名称</td> 
  </tr> 
  <tr> 
   <td align="left">super</td> 
   <td align="left">服务器</td> 
   <td align="left">超级权限</td> 
  </tr> 
  <tr> 
   <td align="left">create temporary tables</td> 
   <td align="left">表</td> 
   <td align="left">创建临时表</td> 
  </tr> 
  <tr> 
   <td align="left">lock tables</td> 
   <td align="left">数据库</td> 
   <td align="left">锁表</td> 
  </tr> 
  <tr> 
   <td align="left">execute</td> 
   <td align="left">存储过程</td> 
   <td align="left">执行</td> 
  </tr> 
  <tr> 
   <td align="left">replication client</td> 
   <td align="left">服务器</td> 
   <td align="left">允许查看主/从/二进制日志状态</td> 
  </tr> 
  <tr> 
   <td align="left">replication slave</td> 
   <td align="left">服务器</td> 
   <td align="left">主从复制</td> 
  </tr> 
  <tr> 
   <td align="left">create view</td> 
   <td align="left">视图</td> 
   <td align="left">创建视图</td> 
  </tr> 
  <tr> 
   <td align="left">show view</td> 
   <td align="left">视图</td> 
   <td align="left">查看视图</td> 
  </tr> 
  <tr> 
   <td align="left">create routine</td> 
   <td align="left">存储过程</td> 
   <td align="left">创建存储过程</td> 
  </tr> 
  <tr> 
   <td align="left">alter routine</td> 
   <td align="left">存储过程</td> 
   <td align="left">修改/删除存储过程</td> 
  </tr> 
  <tr> 
   <td align="left">create user</td> 
   <td align="left">服务器</td> 
   <td align="left">创建用户</td> 
  </tr> 
  <tr> 
   <td align="left">event</td> 
   <td align="left">数据库</td> 
   <td align="left">创建/更改/删除/查看事件</td> 
  </tr> 
  <tr> 
   <td align="left">trigger</td> 
   <td align="left">表</td> 
   <td align="left">触发器</td> 
  </tr> 
  <tr> 
   <td align="left">create tablespace</td> 
   <td align="left">服务器</td> 
   <td align="left">创建/更改/删除表空间/日志文件</td> 
  </tr> 
  <tr> 
   <td align="left">proxy</td> 
   <td align="left">服务器</td> 
   <td align="left">代理成为其它用户</td> 
  </tr> 
  <tr> 
   <td align="left">usage</td> 
   <td align="left">服务器</td> 
   <td align="left">没有权限</td> 
  </tr> 
 </tbody> 
</table>

## 三、备份与恢复 ##

### 3.1 备份 ###

-- 备份database1库所有表结构+数据
    mysqdump -u username database1 > database1.sql -p
    
    -- 备份database1库所有表结构
    mysqdump -u username -d database1 > database1.sql -p

### 3.2 恢复 ###

-- 进入数据库
    use database1;
    -- 将备份文件恢复到数据库中
    mysqdump -u username -d database1 < database1.sql -p