MySQL：数据库权限与角色

权限

MySQL 的权限管理系统是保障数据库安全性的关键组件之一。它允许数据库管理员精确控制哪些用户可以对哪些数据库对象执行哪些操作。

自主存取控制 DAC（DiscretionaryAccess Control)：用户对于不同的数据库对象有不同的存取权限，不同的用户对同一对象也有不同的权限，用户可以“自主”地决定将数据的存取权限授予何人、决定是否也将“授权数据库安全性控制的权限授予别人。

SQL 中使用 GRANT 语句向用户授予对数据操作的权限，REVOKE 语句收回已经授予用户的权限。

权限的类型

MySQL的权限类型涵盖了用户对数据库和数据库对象可以执行的各种操作，包括但不限于以下几种：

• SELECT：允许用户查询表中的数据。

• INSERT：允许用户向表中插入新的数据行。

• UPDATE：允许用户更新表中的数据。

• DELETE：允许用户从表中删除数据。

• CREATE：允许用户创建新的数据库、表、索引等对象。

• DROP：允许用户删除数据库、表、索引等对象。

• ALTER：允许用户修改表结构，如添加或删除列、修改列的数据类型等。

• INDEX：允许用户创建或删除索引。

• CREATE ROUTINE：允许用户创建存储过程或函数。

• ALTER ROUTINE：允许用户修改或删除存储过程或函数。

• EXECUTE：允许用户执行存储过程或函数。

• GRANT OPTION：允许用户将自己拥有的权限授予其他用户。

• SUPER：超级权限，允许用户执行一些高级管理任务，如关闭MySQL服务、更改全局变量等。

此外，还有如USAGE（连接权限，无实际操作权限）、FILE（对服务器主机上文件的访问权限）、PROCESS（查看服务器中所有会话的权限）、SHUTDOWN（关闭服务器的权限）等权限。

权限的级别

MySQL 的权限按其作用范围可分为不同的级别，这些级别从大到小依次为：

1. 全局级别（Global Level）

   • 权限控制整个MySQL服务器上的操作，对所有数据库、表和列都有效。

   • 使用 *.* 来指定授权范围，例如 GRANT ALL PRIVILEGES ON *.* TO 'user'@'host';。

   • 权限信息存储在 mysql.user 表中。

2. 数据库级别（Database Level）

   • 权限限制用户对指定数据库的操作，包括对该数据库中所有表和列的访问。

   • 使用 database_name.* 来指定授权范围，例如 GRANT SELECT, INSERT ON database_name.* TO 'user'@'host';。

   • 权限信息存储在 mysql.db 表中。

3. 表级别（Table Level）

   • 权限控制用户对具体表的操作，如查询、插入、更新和删除表中的记录。

   • 使用 database_name.table_name 来指定授权范围，例如GRANT UPDATE ON database_name.table_name TO 'user'@'host';。

   • 权限信息存储在 mysql.tables_priv 表中。

4. 列级别（Column Level）

   • 权限控制用户对表中指定列的访问，这是MySQL权限系统中最细粒度的控制。

   • 使用 database_name.table_name(column_name1, column_name2, ...) 来指定授权范围。

   • 权限信息存储在 mysql.columns_priv 表中。

5. 例行程序级别（Routine Level）

   • 权限控制用户对存储过程和函数的访问，包括执行、修改和删除存储过程或函数。

   • 使用 PROCEDURE 或 FUNCTION 关键字以及具体的存储过程或函数名来指定授权范围。

   • 权限信息存储在 mysql.procs_priv 表中。

通过合理设置不同级别的权限，数据库管理员可以确保数据库的安全性和数据的完整性，同时满足不同用户对数据库资源的访问需求。在实际应用中，建议遵循最小权限原则，即只授予用户完成其工作所需的最小权限集，以减少潜在的安全风险。

权限的管理

授权权限 GRANT

在 MySQL 中，使用 GRANT 语句 授予指定用户对指定操作对象的指定操作权限。可以根据需要为用户分配特定的权限。

执行 GRANT 语句需要具有相应权限的用户才能执行:

• DBA
• 数据库对象的创建者
• 已经拥有该权限的用户

语法如下：

GRANT <Privilege> [,Privilege2, ...]
ON [obj_type]<obj_name>[,<obj2_type><obj2_name>]...
TO <username>[,usernames,...]
[@'IP']
[WITH GRANT OPTION];

• <Privilege>: 指定要授予的权限。可以使用特定的权限关键字，比如 SELECT、INSERT、UPDATE、DELETE、CREATE、DROP 等，也可以使用 ALL PRIVILEGES 来表示所有权限。

• [obj_type]: 指定对象的类型，例如 TABLE、DATABASE、PROCEDURE 等。如果不指定，默认为 TABLE。

• <obj_name>: 指定对象的名称，可以是表名、数据库名、过程名等。如果要授予多个对象的权限，可以使用逗号分隔它们。

• <username>: 指定要授予权限的用户名。可以是一个具体的用户名，也可以使用 PUBLIC 来表示所有用户。

可选项：

• 'IP': 如果指定了 IP 地址，则表示要授予该 IP 地址的用户相应的权限。可以使用具体的 IP 地址，也可以使用通配符 ‘%’ 来表示任意 IP 地址。

• WITH GRANT OPTION：SQL标准允许具有 WITH GRANT OPTION 的用户把自己拥有的权限或其子集传递授予其他用户，但不允许循环授权，即被授权者不能把权限再授回给授权者或其祖先。建议只将 WITH GRANT OPTION 授予具有适当权限的管理员或特定需要此功能的用户。

• 发出该 GRANT 语句的可以是数据库管理员，也可以是该数据库对象创建者(即属主owner)，还可以是已经拥有该权限的用户。

• 接受权限的用户可以是一个或多个具体用户，也可以是 PUBLIC，即全体用户。

刷新权限

授予用户权限后，可以使用 FLUSH PRIVILEGES 语句刷新权限，使授权更改生效。

FLUSH PRIVILEGES;

创建用户的同时授权

使用 GRANT 语句，可以在创建用户的同时，为用户授权：

GRANT privilege ON database.table_name
TO 'username'[@'ip']
[WITH GRANT OPTION];

示例

1. 授予用户 user1 对表 employees 的 SELECT,INSERT 和 UPDATE 特定列 Sname 的权限：

GRANT SELECT, INSERT, UPDATE(Sname) 
ON TABLE employees TO user1;

2. 授予用户 user2 和 user3 对数据库 ecommerce 的所有权限，并允许该用户将自己拥有的权限授予其他用户：

GRANT ALL PRIVILEGES ON ecommerce.*
TO user2,user3
WITH GRANT OPTION;

3. 授予用户 user4 对表 orders 和 customers 的 DELETE 权限，并限制仅允许从特定的 IP 地址登录：

GRANT DELETE ON orders,customers TO user4 @'192.168.0.100';

查看用户权限

查看已经授权给用户权限信息:

SHOW GRANTS FOR 'username'@'host'

查看创建用户的语句:

SHOW CREATE USER 'username'@'host'

收回权限 REVOKE

在 MySQL 中，使用 REVOKE 语句收回某用户的权限，可以使已被授权的用户失去其指定权限。

只有拥有 GRANT OPTION 权限的账户才能够执行 REVOKE 命令并从其他用户账户中撤销权限。

REVOKE <Privilege> [,Privilege2, ...]
ON [obj_type]<obj_name>[,<obj2_type><obj2_name>]...
FROM <username>[,usernames,...]
[@'IP']
[CASCADE|RESTRICT];

• <Privilege>：要收回的权限，如 SELECT、INSERT、UPDATE、DELETE 等。

• obj_type：可选部分，指定对象的类型，如 TABLE、PROCEDURE 等。如果不指定，默认为 TABLE。

• obj_name：要收回权限的对象的名称。

• <username>：指定要收回权限的用户，可以是单个用户或多个用户。

• [@'IP']：可选部分，指定用户的主机名或 IP 地址。如果不指定，默认为所有主机。

• [CASCADE|RESTRICT]：可选部分，用于指定是否级联撤销权限或限制撤销权限。

  • CASCADE 表示级联撤销（收回某用户权限的同时也会把该用户所有授权过用户的权限一并收回）
  • RESTRICT 表示限制（只收回指定的用户权限）。

示例

通常情况下，REVOKE 应该与 GRANT 保持一致，即撤销时应该指定与 GRANT 相同的权限、数据库和表，并取消所有选项，包括 WITH GRANT OPTION。这样可以确保权限被正确、彻底地收回。

1. 收回用户 user1 对表 employees 的 SELECT 和 INSERT 权限：

REVOKE SELECT,INSERT ON employees FROM user1;

2. 收回用户 user2 对数据库 ecommerce 中所有表的 ALL PRIVILEGES 权限：

REVOKE ALL PRIVILEGES ON ecommerce.* FROM user2@'host';

REVOKE 只会撤销已经授予的权限，未授权的权限无法被收回。除非用户重新授权，否则收回权限后，用户将无法再执行与该权限相关的操作。

数据库角色

数据库角色是被命名的一组与数据库操作相关的权限，角色是权限的集合。因此，可以为一组具有相同权限的用户创建一个角色，使用角色来管理数据库权限可以简化授权的过程。

在创建数据库时，管理员通常会分配给每个用户一个特定的角色，并指定一组权限和访问权限，以便他们仅能够执行与其角色相关联的操作。这有助于提高数据的安全性和管理。

MySQL 8.0 引入了角色（Role）的概念，可以方便地为用户分配和管理权限。角色使管理员能够更好地管理用户权限并简化权限管理过程。

要使用角色功能，需要在 MySQL 中启用角色授权。在 my.cnf 或 my.ini 文件中，确保 --default-authentication-plugin=mysql_native_password 和 --enable-named-roles 参数被启用。

角色与用户的关系

• 用户可以被赋予一个或多个角色。

• 用户登录后，可以使用 SET ROLE 命令激活或取消激活特定的角色。

• 用户的权限由其自身权限和激活的角色权限共同决定。

角色的继承

• 当一个角色被授予另一个角色时，被授予的角色会继承所有直接授予它的权限，以及间接通过其他角色授予的权限。

• 角色的权限继承是递归的，这意味着如果一个角色被授予了另一个角色，那么它也会继承所有被间接授予的权限。

操作与管理角色

在 MySQL 中首先用 CREATE ROLE 语句创建角色，然后用 GRANT 语句给角色授权，用 REVOKE 语句收回授予角色的权限。

创建角色

使用 CREATE ROLE 命令创建一个新的角色。角色名称应符合 MySQL 的标识符命名规则。

CREATE ROLE 'role_name';

向角色授权

使用 GRANT 命令向角色授予特定的权限。

GRANT privilege
ON [obj_type]'obj_name'
TO 'role_name'[,'role2',...];

• privilege 是要授予的角色权限

• obj_type 和 obj_name 表示权限作用的对象类型和名称。

将角色授予用户或其他角色

使用 GRANT 命令将一个或多个角色授予用户或其他角色。

GRANT 'role1'[,'role2',...]
TO ['role3'|'username'][,...]
[WITH ADMIN OPTION]

• 如果指定了 WITH ADMIN OPTION 子句，则获得某种权限的角色或用户还可以把这种权限再授予其他的角色。

• 一个角色包含的权限：包括直接授予这个角色的全部权限加上其他角色授予这个角色的全部权限。

收回角色的权限

使用 REVOKE 命令从角色中收回特定的权限。

REVOKE privilege
ON ['obj_type']'obj_name'
FROM 'role1'[,'role2',...];

只有拥有 GRANT OPTION 权限的账户才能够执行 REVOKE 命令并从其他用户账户中撤销权限。

删除角色

使用 DROP ROLE 命令删除一个角色。在删除角色之前，请确保没有用户正使用该角色，否则可能会导致权限混乱。

DROP ROLE 'role_name'