Mysql用户管理

Mysql用户管理

MySQL 用户管理 是数据库安全管理的重要组成部分，涉及用户的创建、权限分配、修改和删除等操作。通过合理的用户管理，可以确保数据库的安全性，防止未授权访问和数据泄露。

基本概念

1. 用户
   • 用户是访问 MySQL 数据库的账户，每个用户都有一个唯一的用户名和主机名组合。
   • 用户名和主机名共同标识一个用户，例如 'user'@'localhost'
   • 用户相关信息存储在mysql.user表中
2. 权限
   • 权限定义了用户可以对数据库执行的操作，如 SELECT、INSERT、UPDATE、DELETE 等。
   • 权限可以全局授予（所有数据库），也可以针对特定数据库、表或列授予
3. 角色
   • 角色是一组权限的集合，可以将角色授予用户，简化权限管理。
   • MySQL 8.0 及以上版本支持角色功能

用户管理操作

1. 创建用户

   使用 CREATE USER 语句创建用户

   CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';
   
   --创建一个用户 'alice'，允许从 localhost 访问，密码为 'password123'
   CREATE USER 'alice'@'localhost' IDENTIFIED BY 'password123';
   

   若需允许用户从任意主机访问，可将 hostname 设为 %

   若需限制其中一部分，使用百分号作为占位符，'alice'@'192.168.1.%'：允许用户 alice 从 192.168.1.0/24 网段连接

2. 修改用户

   • 使用 ALTER USER 语句修改用户密码

     ALTER USER 'username'@'hostname' IDENTIFIED BY 'new_password';
     -- 修改用户 'alice'@'localhost' 的密码
     ALTER USER 'alice'@'localhost' IDENTIFIED BY 'new_password123';
     

   • 使用 RENAME USER 语句重命名用户

     RENAME USER 'old_username'@'hostname' TO 'new_username'@'hostname';
     
     --将用户 'alice'@'localhost' 重命名为 'bob'@'localhost'
     RENAME USER 'alice'@'localhost' TO 'bob'@'localhost';
     

   • 重命名用户后，原用户的权限需手动重新授予（需执行 GRANT 语句）

3. 删除用户

   • 使用 DROP USER 语句删除用户

     DROP USER 'username'@'hostname';
     
     --删除用户 'bob'@'localhost'
     DROP USER 'bob'@'localhost';
     

     如果后面不加主机名则删除所有同名用户，等同于'bob'@'%'

权限管理操作

1. 授予权限

   使用 GRANT 语句授予用户权限。语法如下

   GRANT privilege_type ON database_name.table_name TO 'username'@'hostname';
   
   --授予用户 'alice'@'localhost' 对数据库 testdb 的所有表的 SELECT 和 INSERT 权限
   GRANT SELECT, INSERT ON testdb.* TO 'alice'@'localhost';
   

   可选参数

   • WITH GRANT OPTION：允许用户将其权限授予其他用户。
   • IDENTIFIED BY 'password'：在授予权限的同时设置用户密码

2. 撤销权限

   使用 REVOKE 语句撤销用户权限。语法如下：

   REVOKE privilege_type ON database_name.table_name FROM 'username'@'hostname';
   --撤销用户 'alice'@'localhost' 对数据库 testdb 的所有表的 INSERT 权限
   REVOKE INSERT ON testdb.* FROM 'alice'@'localhost';
   

3. 查看权限

   使用 SHOW GRANTS 语句查看用户的权限

   SHOW GRANTS FOR 'username'@'hostname';
   --查看用户 'alice'@'localhost' 的权限
   SHOW GRANTS FOR 'alice'@'localhost';
   

角色管理操作(MySQL 8.0 及以上)

1. 创建角色

   使用 CREATE ROLE 语句创建角色

   CREATE ROLE 'role_name';
   --创建一个角色 'developer'
   CREATE ROLE 'developer';
   

2. 授予角色权限

   使用 GRANT 语句为角色授予权限

   GRANT privilege_type ON database_name.table_name TO 'role_name';
   --为角色 'developer' 授予对数据库 testdb 的所有表的 SELECT 权限
   GRANT SELECT ON testdb.* TO 'developer';
   

3. 将角色授予用户

   使用 GRANT 语句将角色授予用户

   GRANT 'role_name' TO 'username'@'hostname';
   --将角色 'developer' 授予用户 'alice'@'localhost'
   GRANT 'developer' TO 'alice'@'localhost';
   

4. 激活角色

   默认情况下，角色授予用户后不会自动激活，需要使用 SET ROLE 语句激活

   SET ROLE 'role_name';
   --激活用户 'alice'@'localhost' 的角色 'developer'
   SET ROLE 'developer';
   

5. 查看角色权限

   使用 SHOW GRANTS 语句查看角色的权限

   SHOW GRANTS FOR 'role_name';
   --查看角色 'developer' 的权限
   SHOW GRANTS FOR 'developer';
   

6. 撤销角色权限、撤销用户的角色

   使用 REVOKE 语句撤销角色的权限和撤销用户的角色

   REVOKE privilege_type ON database_name.table_name FROM 'role_name';
   REVOKE 'role_name' FROM 'username'@'hostname';
   --撤销角色 'developer' 对数据库 testdb 的所有表的 INSERT 权限
   REVOKE INSERT ON testdb.* FROM 'developer';
   --撤销用户 'alice'@'localhost' 的角色 'developer'
   REVOKE 'developer' FROM 'alice'@'localhost';
   

7. 删除角色

   使用 DROP ROLE 语句删除角色。语法如下

   DROP ROLE 'role_name';
   --删除角色 'developer'
   DROP ROLE 'developer';
   

原则

• 最小权限原则：只授予用户完成任务所需的最小权限。
• 定期审查权限：定期检查用户的权限，确保没有不必要的权限。
• 使用角色：通过角色管理权限，简化权限分配和撤销。
• 强密码策略：要求用户设置强密码，并定期更换密码。