MySQL中用户与权限管理那些事（附带代码解析）

用户与权限管理

⭐️写在前面

这里是允谦的学习之路
👍如果对你有帮助，给博主一个免费的点赞以示鼓励把QAQ
👋博客主页🎉 允谦的学习小屋
⭐️更多文章👨‍🎓请关注允谦主页📝
🍅文章发布日期：2022.02.22
👋java学习之路！
欢迎各位🔎点赞👍评论收藏⭐️
🎄冲冲冲🎄

1、用户管理

MySQL可以分为普通用户和root用户。root用户是超级管理员，包括创建用户、删除用户和修改用户的密码等管理权限；普通用户只拥有被授予的各种权限。

MySQL提供了许多语句用来管理用户账号，这些语句可以用来管理包括登录和退出MySQL服务器、创建用户、删除用户、密码管理和权限管理等内容。

MySQL数据库的安全性需要通过账户管理来保证。

1.1登录MySQL服务器

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

mysql -h hostname|hostIP -P port -u username -p DatabaseName -e 'SQL语句'
mysql -h localhost -P 3306 -p dbtest1 -e "select * from emp1";

下面详细介绍命令中的参数：

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

1.2 创建用户

在MySQL数据库中，官方推荐使用CREATE USER语句创建新用户。MySQL8版本移除了PASSWORD加密方法，因此不再推荐使用INSERT语句直接操作MySQL中的user表来增加用户。

使用CREATE USER语句来创建新用户时，必须拥有CREATE USER权限。每添加一个用户，CREATE USER语句会在MySQL.user表中添加一条记录，但是新创建的用户没有任何权限，如果添加的账户已经存在，CREATE USER语句会返回一个错误。

CREATE USER 用户名 [IDENTIFIED BY '密码'][,用户名 [IDENTIFIED BY '密码']];
create user 'zhang3' identified by '123456';

1.3 修改用户

修改用户名：

UPDATE mysql.user SET USER = 'li4' WHERE USER = 'zhang3';
FLUSH PRIVILEGES;

1.4 删除用户

在MySQL数据库中，可以使用DROP USER语句来删除普通用户，也可以直接在mysql.user表中删除用户

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

DROP USER user[,user]...
# 举例
DROP user wang5;
DROP USER 'zhang3'@'localhost';

方式2：使用DELETE

delete from user where user = 'zhang3' and host = '%';

1.5 设置当前用户密码

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

1、使用ALTER USER命令来修改当前用户密码：

ALTER USER USER() IDENTIFIED BY 'new_password';

2、使用SET语句来修改当前用户密码

SET PASSWORD = 'new_password';

1.6 修改其他用户密码

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

ALTER USER user [IDENTIFIED BY '新密码'][,user [IDENTIFIED BY '新密码']]....;

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

SET PASSWORD FOR 'username'@'hostname' = 'new_password';

3、使用UPDATE语句修改普通用户的密码(不推荐)

UPDATE MySQL.user SET authentication_string = PASSWORD('123456') WHERE user = 'username' and host = 'hostname';

2、权限管理

2.1权限列表

show privileges;

MySQL的权限分布：

权限分布	可能设置的权限
表权限	select,insert,update,delete,create,drop,grant,references,index,alter
列权限	select,insert,update,references
过程权限	execute,alter routine,grant

2.2 授予权限的原则

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

1. 只授予满足需要的最小权限，防止用户干坏事。
2. 创建用户的时候，限制用户的登录主机，一般是限制成指定的IP或内网IP段
3. 为每个用户设置满足密码复杂度的密码

grant select,update on dbtest1.* to 'zhang3'@'%';

2.3 查看权限

# 查看当前用户权限
show grants;
或
show grants for current_user;
或
show grants for current_user();
# 查看某个用户的权限
SHOW GRANTS FOR 'user'@'主机地址';

2.4 收回权限

REVOKE 权限1，权限2,.... ON 数据库名称.表名称 FROM 用户名@用户地址;

3、权限表

MySQL服务器通过权限表来控制用户数据库的访问，权限表放在mysql数据库中。MySQL数据库系统会根据这些权限表的内容为每个用户赋予相应的权限。这些权限表中最重要的是user表和db表，除此之外，还有table_priv表，column_priv表和proc_priv表等。在MySQL启动时，服务器将这些数据库表中权限信息的内容读入内存。

4、角色管理

4.1角色的理解

角色是权限的集合，可以为角色添加或移除权限。用户可以被赋予角色，同时也被赋予角色包含的权限。

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

4.2创建角色

在实际应用中，为了安全起见，需要给用户授予权限，当用户数量较多时，为了避免单独给每一个用户授予多个权限，可以先将全向集合放入到角色中，再赋予用于相应的角色。

创建角色使用CREATE ROLE语句，语法如下：

CREATE ROLE 'role_name'[@'host_name'][,'role_name'[@'host_name']];

# 举例
create role 'manager'@'%';

4.3 给角色赋予权限

创建角色之后，默认这个角色是没有任何权限的，我们需要给角色授权，给角色授权的语法结构是：

GRANT privileges ON table_name TO 'role_name'[@'localhost'];

# 举例：
grant select,update on dbtest1.* TO 'manager'@'%';
grant all privileges on *.* to 'boss'@'%';

4.4 查看角色的权限

赋予角色权限之后，我们可以通过SHOW GRANTS语句，来看权限是否创建成功了。

SHOW GRANTS FOR 'role_name';
# 举例
SHOW GRANTS FOR 'manager'[@'localhost'];

4.5 回收角色的权限

角色授权后，可以对角色的权限进行维护，对权限进行添加或撤销。添加权限使用GRANT语句，与角色授权相同。撤销角色或角色权限使用REVOKE语句。

修改了角色的权限，会影响拥有该角色的账户的权限。

撤销角色权限的SQL语法如下：

REVOKE privileges ON tablename FROM 'rolename';
# 举例：
REVOKE update ON tablename FROM 'manager'@'%';

4.6 删除角色

DROP ROLE 'role_name';
# 举例：
DROP ROLE 'manager';

4.7 给用户赋予角色

角色创建并授权后，要赋给用户并处于激活状态才能发挥作用。给用户添加角色可以使用GRANT语句，语法形式如下：

GRANT role [,role2,...] TO user [, user2...];
# 举例
GRANT manager TO 'zhang3'@'%';

查看当前用户的拥有的角色：

 select current_role();

4.8 激活角色

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

# 举例
SET DEFAULT ROLE  'manager'@'%' TO 'zhang3'@'%';
# 设置完之后需要重新登录，否则无法起作用

4.9 撤销用户的角色

REVOKE 'role_name' FROM 'username'@'localhost';
# 举例
REVOKE 'manager' FROM 'zhang3'@'%';

4.10 设置强制角色

方式1：服务启动前设置

[mysqld]
mandatory_roles = 'role1,role2@localhost'

方式2：运行时设置

SET PERSIST mandatory_roles = 'role1,role2@localhost';#系统重启后仍然生效
SET G mandatory_roles = 'role1,role2@localhost';#系统重启后仍然生效