mysql 用户管理及权限分配

﹏ヽ暗。殇╰゛Y 2023-02-23 08:49 7阅读 0赞

## 用户添加 ##

命令格式:  
`CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'user_password';`

> ‘%‘代表任何主机  
>   
> CREATE USER ‘newuser’@’%’ IDENTIFIED BY ‘user\_password’; 创建用户，其可以通过任何地址访问进行连接

mysql> create user 'mysql'@'%' identified by 'mxxs123!Fuck';
    Query OK, 0 rows affected (0.00 sec)

## 权限分配 ##

命令格式:  
`GRANT permission1, permission2 ON database_name.table_name TO 'database_user'@'localhost';`

常用的权限列表如下:

<table> 
 <thead> 
  <tr> 
   <th>权限名称</th> 
   <th>权限解释</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>ALL PRIVILEGES</td> 
   <td>Grants all privileges to a user account.</td> 
  </tr> 
  <tr> 
   <td>CREATE</td> 
   <td>The user account is allowed to create databases and tables.</td> 
  </tr> 
  <tr> 
   <td>DROP</td> 
   <td>The user account is allowed to drop databases and tables.</td> 
  </tr> 
  <tr> 
   <td>DELETE</td> 
   <td>The user account is allowed to delete rows from a specific table.</td> 
  </tr> 
  <tr> 
   <td>INSERT</td> 
   <td>The user account is allowed to insert rows into a specific table.</td> 
  </tr> 
  <tr> 
   <td>SELECT</td> 
   <td>The user account is allowed to read a database.</td> 
  </tr> 
  <tr> 
   <td>UPDATE</td> 
   <td>The user account is allowed to update table rows.</td> 
  </tr> 
 </tbody> 
</table>

示例:

mysql> create database demo;
    Query OK, 1 row affected (0.00 sec)
    
    mysql> grant all privileges on demo.* to 'mysql'@'%';
    Query OK, 0 rows affected (0.00 sec)

## 显示权限分配 ##

命令格式：  
`SHOW GRANTS FOR 'database_user'@'localhost';`

mysql> show grants for 'mysql'@'%';
    +-------------------------------------------------+
    | Grants for mysql@%                              |
    +-------------------------------------------------+
    | GRANT USAGE ON *.* TO 'mysql'@'%'               |
    | GRANT ALL PRIVILEGES ON `demo`.* TO 'mysql'@'%' |
    +-------------------------------------------------+
    2 rows in set (0.00 sec)

## 撤销用户权限 ##

命令格式:  
`REVOKE ALL PRIVILEGES ON database_name.* FROM 'database_user'@'localhost';`

示例:

mysql> revoke all privileges on demo.* from 'mysql'@'%';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> show grants for 'mysql'@'%';
    +-----------------------------------+
    | Grants for mysql@%                |
    +-----------------------------------+
    | GRANT USAGE ON *.* TO 'mysql'@'%' |
    +-----------------------------------+
    1 row in set (0.00 sec)

## 删除用户 ##

命令格式:  
`DROP USER 'user'@'localhost'`

示例:

mysql> drop user 'mysql'@'%';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> show grants for 'mysql'@'%';
    ERROR 1141 (42000): There is no such grant defined for user 'mysql' on host '%'
    mysql>