MySQL学习笔记之用户管理与权限控制(DCL)

MySQL 用户管理与权限控制

用户管理（DCL - Data Control Language）

用户管理主要涉及创建、修改和删除数据库用户。以下是常用的用户管理操作及其语法说明：

1. 查询用户

-- 切换到mysql数据库
USE mysql;

-- 查询所有用户
SELECT * FROM user;

说明：SELECT * FROM user; 查询mysql系统数据库中的user表，以查看所有用户的信息。

2. 创建用户

-- 创建一个用户 'itcast'，只允许从当前主机 'localhost' 访问，密码为 '666666'
CREATE USER 'itcast'@'localhost' IDENTIFIED BY '666666';

-- 创建一个用户 'enhuan'，允许从任意主机访问，密码为 '666666'
CREATE USER 'enhuan'@'%' IDENTIFIED BY '666666';

说明：

• CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码'; 语法用于创建新用户。
• localhost 表示用户只能从本地访问。
• % 是通配符，表示用户可以从任何主机访问。

注意：

• 如果需要创建多个用户，可以重复使用 CREATE USER 语句。
• 用户名和密码应该符合安全规范，例如使用强密码。

3. 修改用户密码

-- 修改用户 'enhuan' 的密码为 '888'
ALTER USER 'enhuan'@'%' IDENTIFIED WITH mysql_native_password BY '888';

说明：

• ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码'; 语法用于修改用户的密码。
• mysql_native_password 是认证插件，用于设置新的密码。

4. 删除用户

-- 删除用户 'itcast'@'localhost'
DROP USER 'itcast'@'localhost';

说明：

• DROP USER '用户名'@'主机名'; 语法用于删除现有用户。
• 删除用户后，用户将无法再访问MySQL服务器，因此应谨慎操作。

注意：

• 主机名可以使用 % 通配符来表示任意主机。
• 这类操作通常由数据库管理员（DBA）进行，开发人员相对较少操作。
• 删除用户不会自动删除与该用户关联的数据或权限，这些可能需要额外的清理操作。

权限控制（DCL - Data Control Language）

权限控制用于授予或撤销用户对数据库对象的访问权限。以下是常用的权限控制操作及其语法说明：

1. 查询权限

-- 查询用户 'enhuan'@'%' 的权限
SHOW GRANTS FOR 'enhuan'@'%';

说明：

• SHOW GRANTS FOR '用户名'@'主机名'; 语法用于查看指定用户的权限。
• 该命令返回一个或多个 GRANT 语句，显示用户的权限设置。

2. 授予权限

-- 授予用户 'enhuan'@'%' 对 'itenhuan' 数据库下所有表的所有权限
GRANT ALL ON itenhuan.* TO 'enhuan'@'%';

说明：

• GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名'; 语法用于授予用户对指定数据库和表的权限。
• ALL 表示授予所有权限。
• * 通配符表示所有表。

注意：

• 权限授予应遵循最小权限原则，只授予用户其需要的权限。
• 如果需要授予特定权限，例如只读权限，可以指定具体权限列表，如 SELECT 或 INSERT。

3. 撤销权限

-- 撤销用户 'enhuan'@'%' 对 'itenhuan' 数据库下所有表的所有权限
REVOKE ALL ON itenhuan.* FROM 'enhuan'@'%';

说明：

• REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名'; 语法用于撤销用户对指定数据库和表的权限。

注意：

• 多个权限之间使用逗号分隔。
• 授权和撤销时，数据库名和表名可以使用 * 通配符，表示所有。

总结

• 用户管理 涉及用户的创建、修改密码和删除操作，主要用于数据库的维护和管理。
• 权限控制 涉及授予和撤销用户对数据库的访问权限，以确保用户只能执行其被授权的操作。
• 在进行这些操作时，应注意权限的最小化原则，确保每个用户仅能访问和操作其必要的数据。

完整代码

-- DCL-管理用户
-- 1.查询用户
--   use mysql;
--   select * from user;
-- 2.创建用户
--   create user '用户名'@'主机名' identified by '密码';
-- 3.修改用户密码
--   alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';
-- 4.删除用户
--   drop user '用户名'@'主机名';

-- 注意：
--   主机名可以使用%通配。
--   这类SQL开发人员操作的比较少，主要是DBA（Database Administrator 市局库管理员）使用。

-- 创建用户itcast，只能够在当前主机localhost访问，密码666666。
create user 'itcast'@'localhost' identified by '666666';

-- 创建用户enhuan，可以在任意主机访问该数据库，密码666666。
create user 'enhuan'@'%' identified by '666666';

-- 修改用户enhuan的访问密码为888。
alter user 'enhuan'@'%' identified with mysql_native_password by '888';

-- 删除itcast@localhost用户。
drop user 'itcast'@'localhost';


-- DCL-权限控制
-- 1.查询权限
--   show grants for '用户名'@'主机名';
-- 2.授予权限
--   grant 权限列表 on数据库名.表名 to '用户名'@'主机名';
-- 3.撤销权限
--   revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

-- 注意：
--   多个权限之间，使用逗号分隔。
--   授权时，数据库名和表名可以使用*通配，代表所有。

-- 查询权限
show grants for 'enhuan'@'%';

-- 授予权限
grant all on itenhuan.* to 'enhuan'@'%';

-- 撤销权限
revoke all on itenhuan.* from 'enhuan'@'%';