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 的权限按其作用范围可分为不同的级别，这些级别从大到小依次为：

• 全局级别（Global Level）
  
  
  
  • 权限控制整个MySQL服务器上的操作，对所有数据库、表和列都有效。
    
  • 使用 *.* 来指定授权范围，比方 GRANT ALL PRIVILEGES ON *.* TO 'user'@'host';。
    
  • 权限信息存储在 mysql.user 表中。
    
  
  
• 数据库级别（Database Level）
  
  
  
  • 权限限定用户对指定命据库的操作，包罗对该数据库中所有表和列的访问。
    
  • 使用 database_name.* 来指定授权范围，比方 GRANT SELECT, INSERT ON database_name.* TO 'user'@'host';。
    
  • 权限信息存储在 mysql.db 表中。
    
  
  
• 表级别（Table Level）
  
  
  
  • 权限控制用户对详细表的操作，如查询、插入、更新和删除表中的记载。
    
  • 使用 database_name.table_name 来指定授权范围，比方GRANT UPDATE ON database_name.table_name TO 'user'@'host';。
    
  • 权限信息存储在 mysql.tables_priv 表中。
    
  
  
• 列级别（Column Level）
  
  
  
  • 权限控制用户对表中指定列的访问，这是MySQL权限系统中最细粒度的控制。
    
  • 使用 database_name.table_name(column_name1, column_name2, ...) 来指定授权范围。
    
  • 权限信息存储在 mysql.columns_priv 表中。
    
  
  
• 例行步调级别（Routine Level）
  
  
  
  • 权限控制用户对存储过程和函数的访问，包罗实行、修改和删除存储过程或函数。
    
  • 使用 PROCEDURE 或 FUNCTION 关键字以及详细的存储过程或函数名来指定授权范围。
    
  • 权限信息存储在 mysql.procs_priv 表中。
    
  
  

通过公道设置不同级别的权限，数据库管理员可以确保数据库的安全性和数据的完备性，同时满意不同用户对数据库资源的访问需求。在实际应用中，发起遵照最小权限原则，即只授予用户完成其工作所需的最小权限集，以淘汰潜伏的安全风险。
权限的管理

授权权限 GRANT

在 MySQL 中，使用 GRANT 语句 授予指定用户对指定操作对象的指定操作权限。可以根据需要为用户分配特定的权限。
实行 GRANT 语句需要具有相应权限的用户才能实行:

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

语法如下：

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

复制代码

• <rivilege>: 指定要授予的权限。可以使用特定的权限关键字，比如 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 语句革新权限，使授权更改生效。

1. FLUSH PRIVILEGES;

复制代码

创建用户的同时授权

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

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

复制代码

示例

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

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

复制代码

• 授予用户 user2 和 user3 对数据库 ecommerce 的所有权限，并答应该用户将本身拥有的权限授予其他用户：
  

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

复制代码

• 授予用户 user4 对表 orders 和 customers 的 DELETE 权限，并限定仅答应从特定的 IP 地点登录：
  

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

复制代码

检察用户权限

检察已经授权给用户权限信息:

1. SHOW GRANTS FOR 'username'@'host'

复制代码

检察创建用户的语句:

1. SHOW CREATE USER 'username'@'host'

复制代码

收回权限 REVOKE

在 MySQL 中，使用 REVOKE 语句收回某用户的权限，可以使已被授权的用户失去其指定权限。
只有拥有 GRANT OPTION 权限的账户才能够实行 REVOKE 下令并从其他用户账户中撤销权限。

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

复制代码

• <rivilege>：要收回的权限，如 SELECT、INSERT、UPDATE、DELETE 等。
  
• obj_type：可选部门，指定对象的范例，如 TABLE、PROCEDURE 等。如果不指定，默认为 TABLE。
  
• obj_name：要收回权限的对象的名称。
  
• <username>：指定要收回权限的用户，可以是单个用户或多个用户。
  
• [@'IP']：可选部门，指定用户的主机名或 IP 地点。如果不指定，默认为所有主机。
  
• [CASCADE|RESTRICT]：可选部门，用于指定是否级联撤销权限或限定撤销权限。
  
  
  
  • CASCADE 表现级联撤销（收回某用户权限的同时也会把该用户所有授权过用户的权限一并收回）
    
  • RESTRICT 表现限定（只收回指定的用户权限）。
    
  
  

示例

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

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

1. REVOKE SELECT,INSERT ON employees FROM user1;

复制代码

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

1. 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 的标识符命名规则。

1. CREATE ROLE 'role_name';

复制代码

向角色授权

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

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

复制代码

• privilege 是要授予的角色权限
  
• obj_type 和 obj_name 表现权限作用的对象范例和名称。
  

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

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

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

复制代码

• 如果指定了 WITH ADMIN OPTION 子句，则得到某种权限的角色或用户还可以把这种权限再授予其他的角色。
  
• 一个角色包含的权限：包罗直接授予这个角色的全部权限加上其他角色授予这个角色的全部权限。
  

收回角色的权限

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

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

复制代码

只有拥有 GRANT OPTION 权限的账户才能够实行 REVOKE 下令并从其他用户账户中撤销权限。
删除角色

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

1. DROP ROLE 'role_name'

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。