数据库的高级操作之权限管理

曾经终败给现在 2022-04-23 05:12 346阅读 0赞

在MySQL数据库中，为了保证数据的安全性，数据管理员需要为每个用户赋予不同的权限，以满足不同用户的需求，

本节将针对MySQL的权限管理进行详细的讲解。

8.3.1 MySQL的权限

MySQL中的权限信息被存储在MySQL数据库的user、db、host、tables\_priv、column\_priv和procs\_priv表中，当

MySQL启动时会自动加载这些权限信息，并将这些权限信息读取到内存中。接下来通过表8-2列举一下MySQL的相关

权限以及在user表中对应的列和权限范围。

<table style="width:500px;">  表8-2 MySQL的权限信息  
 <tbody> 
  <tr> 
   <td>user表的权限列表</td> 
   <td>权限名称</td> 
   <td>权限范围</td> 
  </tr> 
  <tr> 
   <td>Create_priv</td> 
   <td>CREATE</td> 
   <td>数据库、表、索引</td> 
  </tr> 
  <tr> 
   <td>Drop_priv</td> 
   <td>DROP</td> 
   <td>数据库、表、视图</td> 
  </tr> 
  <tr> 
   <td>Grant_priv</td> 
   <td>GRANT OPTION</td> 
   <td>数据库、表、存储过程</td> 
  </tr> 
  <tr> 
   <td>References_priv</td> 
   <td>REFERENCES</td> 
   <td>数据库、表</td> 
  </tr> 
  <tr> 
   <td>Event_priv</td> 
   <td>EVENT</td> 
   <td>数据库</td> 
  </tr> 
  <tr> 
   <td>Alter_priv</td> 
   <td>ALTER</td> 
   <td>数据库</td> 
  </tr> 
  <tr> 
   <td>Delete_priv</td> 
   <td>DELETE</td> 
   <td>表</td> 
  </tr> 
  <tr> 
   <td>Insert_priv</td> 
   <td>INSERT</td> 
   <td>表</td> 
  </tr> 
  <tr> 
   <td>Index_priv</td> 
   <td>INDEX</td> 
   <td>表</td> 
  </tr> 
  <tr> 
   <td>Select_priv</td> 
   <td>SELECT</td> 
   <td>表、列</td> 
  </tr> 
  <tr> 
   <td>Update_priv</td> 
   <td>UPDATE</td> 
   <td>表、列</td> 
  </tr> 
  <tr> 
   <td>Create_temp_table_priv</td> 
   <td>CREATE TEMPORARY TABLES</td> 
   <td>表</td> 
  </tr> 
  <tr> 
   <td>Lock_tables_priv</td> 
   <td>LOCK TABLES</td> 
   <td>表</td> 
  </tr> 
  <tr> 
   <td>Trigger_priv</td> 
   <td>TRIGGER</td> 
   <td>表</td> 
  </tr> 
  <tr> 
   <td>Create_view_priv</td> 
   <td>CREATE VIEW</td> 
   <td>视图</td> 
  </tr> 
  <tr> 
   <td>Show_view_priv</td> 
   <td>SHOW VIEW</td> 
   <td>视图</td> 
  </tr> 
  <tr> 
   <td>Alter_routine_priv</td> 
   <td>ALTER ROUTINE</td> 
   <td>存储过程、函数</td> 
  </tr> 
  <tr> 
   <td>Create_routine_priv</td> 
   <td>CREATE ROUTINE</td> 
   <td>存储过程、函数</td> 
  </tr> 
  <tr> 
   <td>Execute_priv</td> 
   <td>EXECUTE</td> 
   <td>存储过程、函数</td> 
  </tr> 
  <tr> 
   <td>File_priv</td> 
   <td>FILE</td> 
   <td>范围服务器上的文件</td> 
  </tr> 
  <tr> 
   <td>Create_tablespace_priv</td> 
   <td>CREATE TABLESPACE</td> 
   <td>服务器管理</td> 
  </tr> 
  <tr> 
   <td>Create_user_priv</td> 
   <td>CREATE USER</td> 
   <td>服务器管理</td> 
  </tr> 
  <tr> 
   <td>Process_priv</td> 
   <td>PROCESS</td> 
   <td>存储过程和函数</td> 
  </tr> 
  <tr> 
   <td>Reload_priv</td> 
   <td>RELOAD</td> 
   <td>访问服务器上的文件</td> 
  </tr> 
  <tr> 
   <td>Repl_client_priv</td> 
   <td>REPLICATION CLIENT</td> 
   <td>服务器管理</td> 
  </tr> 
  <tr> 
   <td>Repl_slave_priv</td> 
   <td>REPLICATION SLAVE</td> 
   <td>服务器管理</td> 
  </tr> 
  <tr> 
   <td>Show_db_priv</td> 
   <td>SHOW DATABASES</td> 
   <td>服务器管理</td> 
  </tr> 
  <tr> 
   <td>Shutdown_priv</td> 
   <td>SHUTDOWN</td> 
   <td>服务器管理</td> 
  </tr> 
  <tr> 
   <td>Super_priv</td> 
   <td>SUPER</td> 
   <td>服务器管理</td> 
  </tr> 
 </tbody> 
</table>

表8-2对MySQL的权限以及权限的范围进行 了介绍，对于初学者来说可能无法理解，接下来针对表中部分权限进行分析，

具体如下。

（1）CREATE和DROP权限，可以创建数据库、表、索引，或者删除已有的数据库、表、索引。

（2）INSERT、DELETE、UPDATE、SELECT权限，可以对数据库中的表进行增删改查操作。

（3）INDEX权限，可以创建或删除索引，适用于所有的表。

（4）ALTER权限，可以用于修改表的结构或重命名表。

（5）GRANT权限，允许为其他用户授权，可用于数据库和表。

（6）FILE权限，被赋予该权限的用户能读写MySQL服务器上的任何文件。

上述这些权限只要了解即可，无须特殊记忆。

8.3.2 授予权限

在前面的章节中，之所以可以对数据进行增删改查的操作，是因为数据库中的用户拥有不同的权限，

合理的授权克保证数据库的安全。在MySQL中提供了一个GRANT语句，该语句可以为用户授权。

GRANT语句语法格式如下：

GRANT privileges \[(columns)\] \[,privileges\[(columns)\]\] ON database.table

TO 'username'@'hostname' \[IDENTIFIED BY \[PASSWORD\] ''password\]

\[,'username'@'hostname' \[IDENTIFIED BY \[PASSWORD\] 'password'\]\]...

\[WITH with\_option \[with\_option\]...\]

上述语法格式中，privileges表示权限类型，columns参数表示权限作用于某一列，该参数可以省略不写，

此时权限作用于整个表，username表示用户名，hostname表示主机名，IDENBTIFIED BY参数为用户设置密码，

PASSWORD参数为关键字，password为用户的密码。

WITH关键字后面可以带有多个参数with\_option，这个参数有5个取值，具体如下：

（1）GRANT OPTION：将自己的权限授予其他用户。

（2）MAX\_QUERIES\_PER\_HOUR count：设置每小时最多可以执行多少次(count)查询。

（3）MAX\_UPDATES\_PER\_HOUT count：设置梅小时最多可以执行多少次更新。

（4）MAX\_CONNECTIONS\_PER\_HOUR count：设置每小时最大的连接数量。

（5）MAX\_USER\_CONNECTIONS：设置每个用户最多可以同时建立连接的数量。

使用GRANT语句创建一个新的用户，用户名为user4、密码为e123，user4用户对所有数据库有INSERT、

SELECT权限，并使用WITH GRANT OPTION子句，GRANT语句如下：

GRANT INSERT,SELECT ON \* . \* TO 'user4' @ 'localhost' INDENTIFIED BY '123' WITH GRANT OPTION;

上述语句执行成功后，可以使用SELECT语句来查询user表中的哦用户权限，查询结果如下：

mysql> use musql;
    Database changed
    mysql> SELECT Host,User,Password,Insert_priv,Select_priv,Grant_priv FROM
    mysql.user WHERE user = 'user4'\G
    
    
    *****************************1.row*************************
    
            Host:localhost
            User:user4
    
        Password:* 23AE8099DDACAF96AF0FD78ED04B6A265E05AA257
    Insert_priv:Y
    Select_priv:Y
     Grant_priv:Y
    
    1 row in set(0.05 sec)

从上述结果可以看出，User的值为user4，Insert\_priv、Select\_priv、Grant\_priv的值都为Y，因此可以说明

用户user4对所有数据库具有增加、查询以及对其他用户赋予相应权限的功能。

8.3.3 查看权限

通过前面的讲解可以知道，使用SELECT语句可以查询user表中的权限信息，但是该语句不仅需要指定用户，还

需要指定查询的权限，比较麻烦，为了方便查询用户的权限信息，MySQL还提供了一个SHOW GRANTS语句。

SHOW GRANTS的语法格式如下：

SHOW GRANTS FOR 'username'@'hostname';

从上述语法格式可以看出，SHOW GRANTS语法格式比较简单，只需要指定查询的用户名和主机名即可。

使用SHOW GRANTS语句查询root用户的权限，具体如下：

SHOW GRANTS FOR 'root'@'localhost';

上述语句执行成功后，可以看到如下结果：

msyql> SHOW GRANTS FRO 'root'@'localhsot' \G
    
    ************************1.row**************************
    Grants for root@localhost：GRANT ALL PRIVILEGES ON * . * TO 'root'
    @'localhost' IDENTIFIED BY PASSWOT=RD '*27FE6B5A028489ECF5079808FC148190EB26F4D7'
    WITH GRANT OPTION

从上述结果可以看出，root用户拥有所有权限，并且可以为其他用户赋予权限。

为了让初学者更好第掌握SHOW GRANTS语句，接下里通过查看普通用户权限的案例来演示SHOW GRANTS的用法。

使用SHOW GRANTS语句，查询user4用户的权限信息，具体如下：

SHOW GRANTS FOR 'user4'@'localhost';

上述语句执行结果如下：

mysql> SHOW GRANTS FOR 'user'@'localhost'\G
    
    ************************1.row************************
    Grants for user4@localhost:GRANT SELECT, INSERT ON * . * TO 'user4'@'localhost'
    IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' WITH GRANT OPTION
    
    1 row in set(0.00 sec)

从上述结果可以看出，user4用户有SELECT权限和INSERT权限，并且具有给其他用户赋予SELECT、INSERT

权限的功能。

8.3.4 收回权限

在MySQL中，为了保证数据库的安全性，需要将用户不必要的权限收回，例如，数据管理员发现某个用户不应该具有

DELETE权限，就应该及将其收回。为了实现这种功能，MySQL提供了一个REVOKE语句，该语句可以收回用户的权限。

REVOKE的语法格式如下：

REVOKE privileges [columns] [,privileges[(columns)]] ON database.table
    
    FROM 'user'@'localhost' [,'username'@'hostname']...

REVOKE语法格式中的参数与GRANT语句中的参数意思相同，privileges参数表示收回的权限，

columns表示权限作用于哪列上，如果不指定该参数表示作用于整个表。

使用REVOKE语句收回user4用户的INSERT权限，REVOKE语句如下：

REVOKE INSERT ON * . * FROM 'user4'@'localhost';

上述语句执行成功后，可以使用SELECT语句来查询user表中的用户信息，查询结果如下：

mysql> SELECT Host,User,Password,Insert_priv FROM mysql.user WHERE user = 'user4' \G
    **************************1.row**************************
            Host:localhost
            User:user4
        Password:*23AE809DDACAAF96AF0FD78ED04B6A265E05AA257
    Inser_priv:N
    1 row in set(0.02 sec)

从上述结果可以看出，Insert\_priv的权限值已经被修改为N，因此可以说明REVOKE语句将user4的INSERT权限收回了。

如果用户的权限比较多，想一次性将其收回，使用上述语句就会比较麻烦，为此，REVOKE语句还提供了收回所有权限的功能。

REVOKE语句收回全部权限的语法格式如下：

REVOKE ALL PRIVILEGES,GRANT OPTION
    FROM 'username'@'hostname' [,'username'@'hostname']...

使用REVOKE语句收回user4的所有权限，REVOKE语句如下：

REVOKE ALL [PRIVILEGES,GRANT OPTION FROM 'user4'@'localhost';

上述语句执行成功后，可以使用SELECT语句来查询user表中的用户信息，查询结果如下：

mysql> SELECT Host,User,Password,Insert_priv,Select_priv,Grant_priv
    from mysql.user where user = 'user4' \G
    
    
    **************************1.row************************
    
            Host:localhost
            User:user4
        Password:*23AE809DDACAF96AF0FD78ED04B6A265E05AA257
    Insert_priv:N
    Select_priv:N
     Grant_priv:N
    
    1 row in set(0.13 sec)

从上述结果可以看出，user4用户的ISNERT、SELECT、GRANT权限都被收回了。