MySQL权限管理

以你之姓@ 2022-06-18 10:24 155阅读 0赞

MySQL中权限分配是按照user表, db表, tables\_priv表和columns\_priv表的顺序进行分配的. 数据库系统中, 先判断user表中的值为Y.  
如果user表中的值为Y, 就不需要检查后面的表. 如果user表的为N, 则依次检查db表, tables\_priv表和columns\_priv表

### mysql.user表 ###

##### 权限列中有很多权限字段需要特别注意: #####

<table> 
 <thead> 
  <tr> 
   <th>字段名</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Grant_priv</td> 
   <td>表示是否拥有GRANT权限</td> 
  </tr> 
  <tr> 
   <td>Shutdown_priv</td> 
   <td>表示是否拥有停止MySQL的权限</td> 
  </tr> 
  <tr> 
   <td>Super_priv</td> 
   <td>表示是否拥有超级权限</td> 
  </tr> 
  <tr> 
   <td>Execute_priv</td> 
   <td>表示是否有EXECUTE权限, 该权限可以执行存储过程和函数</td> 
  </tr> 
 </tbody> 
</table>

##### user表的资源控制列 #####

<table> 
 <thead> 
  <tr> 
   <th>字段名</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>max_questions</td> 
   <td>每小时可以允许执行多少次查询</td> 
  </tr> 
  <tr> 
   <td>max_updates</td> 
   <td>每小时可以允许执行多少次更新</td> 
  </tr> 
  <tr> 
   <td>max_connections</td> 
   <td>每小时可以建立多少连接</td> 
  </tr> 
  <tr> 
   <td>max_user_connections</td> 
   <td>每个用户可以同时具有的连接数.</td> 
  </tr> 
 </tbody> 
</table>

以上每个字段默认值为0, 表示没有限制

### 用户账号相关 ###

##### 新建普通用户 #####

# CREATE USER user [IDENTIFIED BY [PASSWORD] 'passwd'] [, user [IDENTIFIED BY [PASSWORD] 'passwd']] ...
    # GRANT priv_type ON databse.table TO user [IDENTIFIED BY [PASSWORD] 'passwd'] [, user [IDENTIFIED BY [PASSWORD] 'passwd']] ...
    
    MariaDB [mysql]> create user 'test1'@'localhost' identified by 'test1_password';
    Query OK, 0 rows affected (0.27 sec)
    
    MariaDB [mysql]> GRANT SELECT ON carltest.* TO 'test3'@'localhost' IDENTIFIED BY 'test3';
    Query OK, 0 rows affected (0.05 sec)

##### 删除普通用户 #####

# DROP USER user [, user] ...
    # DELETE FROM mysql.user WHERE Host='localhost' AND User='username';
    
    MariaDB [mysql]> DROP USER 'test1'@'localhost';
    Query OK, 0 rows affected (0.00 sec)
    
    MariaDB [mysql]> DELETE FROM mysql.user WHERE Host='localhost' AND User='test3';
    Query OK, 1 row affected (0.00 sec)
    
    MariaDB [mysql]> FLUSH PRIVILEGES;
    Query OK, 0 rows affected (0.11 sec)

##### root用户修改root用户的密码 #####

# mysqladmin -u $username -p password "new_password";
    # update mysql.user set Password=PASSWORD('new_password') where User='root' and Host='localhost';
    # SET PASSWORD=PASSWORD('new_password');

##### root用户修改普通用户密码 #####

# SET PASSWORD FOR 'username'@'hostname'=PASSWORD('new_password');
    # UPDATE mysql.user SET Password=PASSWORD('mytest2') WHERE User='test3' AND Host='localhost';
    # GRANT priv_type ON database.table TO user [IDENTIFIED BY [PASSWORD] 'password'];
    > GRANT SELECT ON *.* TO 'test3'@'localhost' IDENTIFIED BY 'mytest3';

##### 普通用户修改密码 #####

# SET PASSWORD=PASSWORD('new_password');

### MySQL的各种权限 ###

<table> 
 <thead> 
  <tr> 
   <th>权限名称</th> 
   <th>对应user表中的列</th> 
   <th>权限的范围</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>CREATE</td> 
   <td>Create_priv</td> 
   <td>数据库, 表或索引</td> 
  </tr> 
  <tr> 
   <td>DROP</td> 
   <td>Drop_priv</td> 
   <td>数据库, 表</td> 
  </tr> 
  <tr> 
   <td>GRANT OPTION</td> 
   <td>Grant_priv</td> 
   <td>数据库, 表, 存储过程, 函数</td> 
  </tr> 
  <tr> 
   <td>REFERENCES</td> 
   <td>References_priv</td> 
   <td>数据库, 表</td> 
  </tr> 
  <tr> 
   <td>ALTER</td> 
   <td>Alter_priv</td> 
   <td>修改表</td> 
  </tr> 
  <tr> 
   <td>DELETE</td> 
   <td>Delete_priv</td> 
   <td>删除表</td> 
  </tr> 
  <tr> 
   <td>INDEX</td> 
   <td>Index_priv</td> 
   <td>用索引查询表</td> 
  </tr> 
  <tr> 
   <td>INSERT</td> 
   <td>Insert_priv</td> 
   <td>插入表</td> 
  </tr> 
  <tr> 
   <td>SELECT</td> 
   <td>Select_priv</td> 
   <td>查询表</td> 
  </tr> 
  <tr> 
   <td>UPDATE</td> 
   <td>Update_priv</td> 
   <td>更新表</td> 
  </tr> 
  <tr> 
   <td>CREATE VIEW</td> 
   <td>Create_view_priv</td> 
   <td>创建视图</td> 
  </tr> 
  <tr> 
   <td>SHOW VIEW</td> 
   <td>Show_view_priv</td> 
   <td>查看视图</td> 
  </tr> 
  <tr> 
   <td>ALTER ROUTINE</td> 
   <td>Alter_routine_priv</td> 
   <td>修改存储过程或存储函数</td> 
  </tr> 
  <tr> 
   <td>CREATE ROUTINE</td> 
   <td>Create_routine_priv</td> 
   <td>创建存储过程或存储函数</td> 
  </tr> 
  <tr> 
   <td>EXECUTE</td> 
   <td>Execute_priv</td> 
   <td>执行存储过程或存储函数</td> 
  </tr> 
  <tr> 
   <td>FILE</td> 
   <td>File_priv</td> 
   <td>加载服务器主机上的文件</td> 
  </tr> 
  <tr> 
   <td>CREATE TEMPORARY TABLES</td> 
   <td>Create_tmp_table_priv</td> 
   <td>创建临时表</td> 
  </tr> 
  <tr> 
   <td>LOCK TABLES</td> 
   <td>Lock_tables_priv</td> 
   <td>锁定表</td> 
  </tr> 
  <tr> 
   <td>CREATE USER</td> 
   <td>Create_user_priv</td> 
   <td>创建用户</td> 
  </tr> 
  <tr> 
   <td>PROCESS</td> 
   <td>Process_priv</td> 
   <td>服务器管理</td> 
  </tr> 
  <tr> 
   <td>RELOAD</td> 
   <td>Reload_priv</td> 
   <td>重新加载权限表</td> 
  </tr> 
  <tr> 
   <td>REPLICATION CLIENT</td> 
   <td>Repl_client_priv</td> 
   <td>服务器管理</td> 
  </tr> 
  <tr> 
   <td>REPLICATION SLAVE</td> 
   <td>Repl_slave_priv</td> 
   <td>服务器管理</td> 
  </tr> 
  <tr> 
   <td>SHOW DATABASES</td> 
   <td>Show_db_priv</td> 
   <td>查看数据库</td> 
  </tr> 
  <tr> 
   <td>SHUTDOWN</td> 
   <td>Shutdown_priv</td> 
   <td>关闭服务器</td> 
  </tr> 
  <tr> 
   <td>SUPER</td> 
   <td>Super_priv</td> 
   <td>超级权限</td> 
  </tr> 
 </tbody> 
</table>

### 授权与收回权限 ###

##### 授权 #####

# GRANT priv_type [(column_list)] ON database.table TO user [IDENTIFIED BY [PASSWORD] 'password']
    # [, user [IDENTIFIED BY [PASSWORD] 'password']] ... [WITH with_option [with_option] ...]
    
    # with_option有几个权限值可选
    #
    # - GRANT OPTION: 被授权的用户可以将这些权限赋予给别的用户
    # - MAX_QUERIES_PER_HOUR count: 设置每小时可以允许执行count次查询
    # - MAX_UPDATES_PER_HOUR count: 设置每小时可以允许执行count次更新
    # - MAX_CONNECTIONS_PER_HOUR count: 设置每小时可以建立count连接
    # - MAX_USER_CONNECTIONS count: 设置单个用户可以同时具有的count个连接数
    
    > GRANT SELECT, UPDATE ON *.* TO 'test5'@'localhost' IDENTIFIED BY 'test5' WITH GRANT OPTION;

##### 收回权限 #####

REVOKE priv_type [(column_list)] ... ON database.table FROM user [, user] ...
    
    # 收回全部权限
    REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

##### 查看权限 #####

> SELECT * FROM mysql.user where User='xxxx' and Host='yyyy';
    > SHOW GRANTS FOR 'username'@'hostname';