程序员scholar
  • MySQL
  • MySQL高级
scholar
2023-08-29
目录

第03章_用户与权限管理

友情提示

转载须知: 以下所有文章整理于B站宋红康老师的《MySQL数据库入门到大牛》。MySQL (opens new window)

# 1. 用户管理

# 1.1 登录MySQL服务器

启动MySQL服务后，可以通过mysql命令来登录MySQL服务器，命令如下：

mysql –h hostname|hostIP –P port –u username –p DatabaseName –e "SQL语句"
1
  • -h参数后面接主机名或者主机IP，hostname为主机，hostIP为主机IP。
  • -P参数后面接MySQL服务的端口，通过该参数连接到指定的端口。MySQL服务的默认端口是3306，不使用该参数时自动连接到3306端口，port为连接的端口号。
  • -u参数后面接用户名，username为用户名。
  • -p参数会提示输入密码。
  • DatabaseName参数指明登录到哪一个数据库中。如果没有该参数，就会直接登录到MySQL数据库中，然后可以使用USE命令来选择数据库。
  • -e参数后面可以直接加SQL语句。登录MySQL服务器以后即可执行这个SQL语句，然后退出MySQL服务器。
mysql -uroot -p -hlocalhost -P3306 mysql -e "select host,user from user"
1

# 1.2 创建用户

CREATE USER 用户名 [IDENTIFIED BY '密码'][,用户名 [IDENTIFIED BY '密码']];
1
  • 用户名参数表示新建用户的账户，由 用户（User） 和 主机名（Host） 构成；
  • “[ ]”表示可选，也就是说，可以指定用户登录时需要密码验证，也可以不指定密码验证，这样用户可以直接登录。不过，不指定密码的方式不安全，不推荐使用。如果指定密码值，这里需要使用IDENTIFIED BY指定明文密码值。
  • CREATE USER语句支持同时创建多个用户。

举例：

create user zhang3 identified by '123123'; # 默认host是 %
create user 'wym'@'localhost' identified by '123456';
1
2

# 1.3 修改用户名

-- 更新mysql.user表，将用户名从'wang5'改为'li4'
UPDATE mysql.user SET USER='li4' WHERE USER='wang5'; 
flush privileges;
1
2
3

# 1.4 删除用户

方式1：使用DROP方式删除（推荐）

使用DROP USER语句来删除用户时，必须用于DROP USER权限。DROP USER语句的基本语法形式如下：

DROP USER user[,user]…;
1

举例：

DROP USER li4 ; # 默认删除host为%的用户
DROP USER 'kangshifu'@'localhost';
1
2

方式2：使用DELETE方式删除（不推荐，有残留信息）

DELETE FROM mysql.user WHERE Host=’hostname’ AND User=’username’;
flush privileges;
1
2

注意

不推荐通过 DELETE FROM USER u WHERE USER='li4' 进行删除，系统会有残留信息保留。而drop user命令会删除用户以及对应的权限，执行命令后你会发现mysql.user表和mysql.db表的相应记录都消失了。

# 1.5 设置当前登录用户密码

适用于root用户修改自己的密码，以及普通用户登录后修改自己的密码。

image-20240224194318741

1. 使用ALTER USER命令来修改当前登录用户密码(推荐)

-- 使用ALTER USER命令修改当前登录用户的密码
-- USER()函数返回当前登录的用户名
-- identified by 后跟新的密码
ALTER USER USER() IDENTIFIED BY 'new_password';
1
2
3
4

2. 使用SET语句来修改当前登录用户密码

-- 使用SET PASSWORD语句直接为当前登录用户设置新密码
-- 'new_password'是新设定的密码值
SET PASSWORD='new_password';
1
2
3

# 1.6 修改其它用户密码

root用户不仅可以修改自己的密码，还可以修改其它普通用户的密码。

1. 使用ALTER语句来修改普通用户的密码

-- 使用ALTER USER命令修改一个或多个普通用户的密码
-- user 替换为要修改密码的用户名
-- identified by '新密码' 指定新的密码
-- 可以一次修改多个用户的密码，每个用户的密码更新用逗号分隔
ALTER USER user IDENTIFIED BY '新密码'
[, user IDENTIFIED BY '新密码']...;
1
2
3
4
5
6

2. 使用SET命令来修改普通用户的密码

-- 使用SET PASSWORD命令为指定的普通用户设置新密码
-- 'username'@'hostname' 替换为目标用户的用户名和主机名
-- 'new_password' 替换为新设定的密码
SET PASSWORD FOR 'username'@'hostname' = 'new_password';
1
2
3
4

使用场景：如果你是数据库管理员需要更新多个用户的密码，ALTER USER可能更适合你的需求。如果你只需要为特定用户快速更改密码，尤其是当用户的连接主机也需要指定时，SET PASSWORD会是一个更好的选择。

# 1.7 MySQL密码管理(了解)

# 1.7.1 密码过期策略

MySQL允许数据库管理员为账户设置密码过期策略。这可以是全局策略，也可以为每个账户单独设置。

# 全局密码过期策略设置

  • 通过SQL语句设置并持久化

    使用ALTER USER命令或SET语句来更改全局的default_password_lifetime变量的值，并通过SET PERSIST命令持久化这个设置。

    -- 设置全局策略，使密码每隔180天过期
SET PERSIST default_password_lifetime = 180;
    1
    2

  • 通过配置文件my.cnf维护

    在MySQL配置文件my.cnf中添加以下设置，以建立全局密码过期策略。

    [mysqld]
default_password_lifetime=180 # 设置密码每隔180天过期
    1
    2
# 单独设置账户密码过期策略

  • 使用CREATE USER和ALTER USER语句

    在创建用户或修改用户时，可以通过PASSWORD EXPIRE选项为单独的账户设置密码过期策略。

    -- 创建用户时设置密码90天后过期
CREATE USER 'kangshifu'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

-- 修改用户，使密码90天后过期
ALTER USER 'kangshifu'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
    1
    2
    3
    4
    5

# 1.7.2 密码重用策略

密码重用策略限制用户在更改密码时不能重用最近使用过的密码。

# 全局密码重用策略设置

  • 通过SQL语句设置

    使用SET PERSIST命令来设置全局的密码重用策略，如password_history和password_reuse_interval。

    -- 设置不能选择最近使用过的6个密码
SET PERSIST password_history = 6;

-- 设置不能选择最近一年内的密码
SET PERSIST password_reuse_interval = 365;
    1
    2
    3
    4
    5

  • 通过配置文件my.cnf维护

    在my.cnf配置文件中添加以下设置。

    [mysqld]
password_history=6
password_reuse_interval=365
    1
    2
    3
# 单独设置账户密码重用策略

  • 使用CREATE USER和ALTER USER语句

    可以在创建或修改用户时，为单独的账户设置密码重用策略。

    -- 创建用户时，设置不能使用最近5个密码
CREATE USER 'kangshifu'@'localhost' PASSWORD HISTORY 5;

-- 修改用户时，设置不能使用最近365天内的密码
ALTER USER 'kangshifu'@'localhost' PASSWORD REUSE INTERVAL 365 DAY;
    1
    2
    3
    4
    5

# 2. 权限管理

关于MySQL的权限简单的理解就是MySQL允许你做你权力以内的事情[不可以越界。比如只允许你执行SELECT操作,那么你就不能执行UPDATE操作。只允许你从某台机器上连接MySQL,那么你就不能从除那台机器以外的其他机器连接MySQL。

# 2.1 权限列表

# 查看权限列表
show privileges;
1
2
  • CREATE和DROP权限
    • CREATE权限允许用户创建新的数据库和表。
    • DROP权限使用户能够删除现有的数据库和表。特别注意，授予用户DROP权限时需要谨慎，因为这允许用户移除重要的数据库资源。
  • 数据操作权限
    • SELECT权限允许用户查询数据库中的表，获取表内的数据。
    • INSERT权限使用户能够向表中添加新记录。
    • UPDATE权限允许用户修改表中现有的记录。
    • DELETE权限使用户能够从表中删除记录。
  • 索引管理权限
    • INDEX权限允许用户为表创建或删除索引，这对于提高查询性能非常关键。
  • 表结构修改权限
    • ALTER权限允许用户更改表的结构，例如添加或删除列，更改索引等。
  • 存储过程和函数权限
    • CREATE ROUTINE权限允许用户创建存储过程和函数。
    • ALTER ROUTINE权限用于修改或删除已有的存储过程和函数。
    • EXECUTE权限允许用户执行存储过程和函数。
  • 权限控制权限
    • GRANT OPTION权限允许用户将他们拥有的权限授予其他用户。这是一种强大的权限，可以使用户在权限管理方面具有很大的灵活性。
  • 文件访问权限
    • FILE权限使用户可以读取或写入服务器上的文件。这包括使用LOAD DATA INFILE和SELECT ... INTO OUTFILE语句导入和导出数据。因为这允许访问服务器文件系统，所以应当非常谨慎地授予此权限。
  • 无操作权限
    • USAGE 是MySQL中的一个特殊权限，它不允许用户进行任何数据库、表、记录的操作（如查询、更新、删除等）。

# 2.2 授予权限的原则

权限控制主要是出于安全因素，因此需要遵循以下几个经验原则：

1、只授予能满足需要的最小权限，防止用户干坏事。比如用户只是需要查询，那就只给select权限就可以了，不要给用户赋予update、insert或者delete权限。

2、创建用户的时候限制用户的登录主机，一般是限制成指定IP或者内网IP段。

3、为每个用户设置满足密码复杂度的密码。

4、定期清理不需要的用户，回收权限或者删除用户。

# 2.3 授予权限

使用GRANT语句直接给用户授权是最直接的方式。这种方法允许管理员指定用户可以执行的具体操作（如查询、插入、删除、更新等），以及这些操作可以在哪些数据库或表上执行。

GRANT 权限列表 ON 数据库对象 TO 用户名@用户地址 [IDENTIFIED BY ‘密码口令’];
1
  • 权限列表：你想要授予用户的权限，如SELECT、INSERT、DELETE、UPDATE等，可以是单个权限或多个权限的组合。
  • 数据库对象：指定权限适用的数据库或表。格式为数据库名称.表名称。使用*.*表示所有数据库和所有表。
  • 用户名@用户地址：指定权限被授予的用户及其登录地址。用户名是用户的名字，用户地址定义了用户从哪里连接到数据库。使用localhost表示本地连接，使用'%'表示从任何远程地址连接。
  • [IDENTIFIED BY ‘密码口令’]：可选项，如果授予权限时发现用户不存在，此语句会创建用户，并设置密码。
  1. 给本地用户wym授予对某数据库所有表的基本权限
-- 给用户wym授予对数据库dbtest2中所有表的SELECT（查询）、INSERT（插入）、DELETE（删除）、UPDATE（更新）权限
-- 用户wym通过本地命令行连接数据库
GRANT SELECT, INSERT, DELETE, UPDATE ON dbtest2.* TO wym@localhost;
1
2
3

这个命令授予用户li4对数据库atguigudb中所有表的查询、插入、删除和更新权限，仅限从本地机器连接。

  1. 给远程用户joe授予对所有数据库和表的全部权限，除了GRANT（授予）权限：
-- 授予用户joe通过任意网络地址连接时对所有数据库和表的所有权限（除了GRANT权限）
-- 同时为用户joe设置密码为123
GRANT ALL PRIVILEGES ON *.* TO joe@'%' IDENTIFIED BY '123';
1
2
3

这个命令授予用户joe对所有数据库和表的全部操作权限（all privileges），但不包括授予其他用户权限的GRANT权限。用户joe可以从任何远程地址连接，其密码被设置为123。

如果要给用户 joe 赋予对所有数据库和表的全部权限，包括授予其他用户权限的 GRANT OPTION 权限，命令如下：

GRANT ALL PRIVILEGES ON *.* TO joe@'%' IDENTIFIED BY '123' WITH GRANT OPTION;
1

这个命令中的 WITH GRANT OPTION 部分是关键，它允许用户 joe 不仅拥有对所有数据库和表的所有操作权限，还能将自己拥有的权限(包括授予权限)赋予给其他用户。这样，joe 就能创建新的用户，并给这些用户分配权限。

笔记

使用GRANT重复给用户添加权限，是权限叠加而不是覆盖，比如你先给用户添加一个SELECT权限,然后又给用户添加一个INSERT权限，那么该用户就同时拥有了SELECT和INSERT权限。

# 2.4 查看权限

  • 查看当前用户权限
SHOW GRANTS; 
# 或 
SHOW GRANTS FOR CURRENT_USER; 
# 或 
SHOW GRANTS FOR CURRENT_USER();
1
2
3
4
5
  • 查看某用户的全局权限
SHOW GRANTS FOR 'user'@'主机地址';
1

# 2.5 收回权限

注意：在将用户账户从user表删除之前，应该收回相应用户的所有权限。

  • 收回权限命令
REVOKE 权限1,权限2,…权限n ON 数据库名称.表名称 FROM 用户名@用户地址;
1
  • 举例
#收回全库全表的所有权限 (*.*表示所有数据库和表)
REVOKE ALL PRIVILEGES ON *.* FROM joe@'%'; 
#收回mysql库下的所有表的插删改查权限 
REVOKE SELECT,INSERT,UPDATE,DELETE ON mysql.* FROM joe@localhost;
1
2
3
4
  • 注意：须用户重新登录后才能生效

# 3. 角色管理

角色是在MySQL 8.0 中引入的新功能。在MySQL中，角色是权限的集合，可以为角色添加或移除权限。用户可以被赋予角色,同时也被授予角色包含的权限。对角色进行操作需要较高的权限。并且像用户账户一样,角色可以拥有授予和撤消的权限。

引入角色的目的是方便管理拥有相同权限的用户。恰当的权限设定,可以确保数据的安全性,这是至关重要的。

image-20240224220652712

# 3.1 创建角色

CREATE ROLE 'role_name'[@'host_name'], ['role_name2'[@'host_name2']]...;
1
  • role_name：你要创建的角色的名称。
  • host_name：指定角色的适用主机。如果省略，则默认为%，表示从任何主机连接都有效。

角色名必须唯一，不能与现有的用户或角色名冲突。角色一经创建，即可被授予权限。

# 3.2 给角色赋予权限

GRANT 权限列表 ON 数据库对象 TO 'role_name'[@'host_name'];
1
  • 权限列表：你想要授予用户的权限，如SELECT、INSERT、DELETE、UPDATE等，可以是单个权限或多个权限的组合。
  • 数据库对象：指定权限适用的数据库或表。格式为数据库名称.表名称。使用*.*表示所有数据库和所有表。
  • role_name：你要赋予的角色的名称。
  • host_name：指定角色的适用主机。如果省略，则默认为%，表示从任何主机连接都有效。

可以使用SHOW PRIVILEGES命令查看所有可用的权限名称。

# 3.3 查看角色的权限

-- 列出角色具有的所有权限
SHOW GRANTS FOR 'role_name';
1
2

默认情况下，新创建的角色只拥有USAGE权限，即允许连接到数据库但不包括任何操作数据的权限。

# 3.4 回收角色的权限

REVOKE 权限列表 ON 数据库对象 FROM 'rolename';
1
  • 权限列表：你想要授予用户的权限，如SELECT、INSERT、DELETE、UPDATE等，可以是单个权限或多个权限的组合。
  • 数据库对象：指定权限适用的数据库或表。格式为数据库名称.表名称。使用*.*表示所有数据库和所有表。
  • role_name：你要回收的角色的名称。

# 3.5 删除角色

DROP ROLE role_name [, role_name2]...;
1

注意，如果你删除了角色，那么用户也就失去了通过这个角色所获得的所有权限。

# 3.6 给用户赋予角色

角色创建并授权后，要赋给用户并处于激活状态才能发挥作用。

GRANT role_name [, role_name2, ...] TO user_name [, user_name2, ...];
1
  • role_name：要赋予的角色名称。
  • user_name：目标用户的名称。

用户可以被赋予多个角色，而每个角色可以包含多个权限。

查询当前用户已激活的角色：

-- 这个命令显示当前会话中激活的角色列表。
SELECT CURRENT_ROLE();
1
2

image-20240225012245939

注意

当你用赋予了角色的用户去登录、操作,你会发现,这个账号没有任何权限。这是因为, MySQL中创建了角色之后,默认都是没有被激活，也就是不能用,必须要手动激活,激活以后用户才能拥有角色对应的权限。

# 3.7 激活角色

赋予角色后，需要激活角色，用户才能使用角色的权限。

方式1：使用set default role 命令激活角色

-- 这个命令将指定的角色设置为用户的默认角色，当用户登录时，这些角色会自动激活。
SET DEFAULT ROLE role_name TO 'user_name'@'host_name';
1
2

方式2：将activate_all_roles_on_login设置为ON

-- 这个命令会在全局范围内激活所有用户的所有角色，意味着用户登录时，其所有角色将自动激活。
SET GLOBAL activate_all_roles_on_login=ON;
1
2

# 3.8 撤销用户的角色

-- 这个命令会从指定用户中移除指定的角色。
REVOKE role_name FROM 'user_name';
1
2
  • role_name：要撤销的角色名称。
  • user_name：目标用户的名称。

# 3.9 设置强制角色(mandatory role)

强制角色是指在用户登录时自动激活的角色，无论用户的默认角色设置如何。

方式1：服务启动前设置

在MySQL的配置文件（通常是my.cnf或my.ini）中设置：这种方式设置的强制角色在服务器启动时生效，并对所有用户有效。

[mysqld] 
mandatory_roles='role_name, role_name2@host_name';
1
2

方式2：运行时设置

使用SET PERSIST或SET GLOBAL命令设置：

SET PERSIST mandatory_roles = 'role_name, role_name2@host_name'; #系统重启后仍然有效
SET GLOBAL mandatory_roles = 'role_name, role_name2@host_name'; #系统重启后失效
1
2

SET PERSIST命令设置的强制角色在系统重启后仍然有效，因为它会被写入到磁盘上的配置文件中。而SET GLOBAL命令设置的强制角色只在当前MySQL服务器运行期间有效，重启后失效。

公众号封面

编辑此页 (opens new window)
上次更新: 2024/12/28, 18:32:08
第02章_MySQL的数据目录
第04章_逻辑架构

← 第04章_逻辑架构→

Theme by Vdoing | Copyright © 2019-2025 程序员scholar