第03章 用户与权限管理

🏠个人主页：shark-Gao

🧑个人简介：大家好，我是 shark-Gao，一个想要与大家共同进步的男人😉😉

🎉目前状况：23 届毕业生，目前在某公司实习👏👏

❤️欢迎大家：这里是 CSDN，我总结知识的地方，欢迎来到我的博客，我亲爱的大佬😘

# 1. 用户管理

# 1.1 登录 MySQL 服务器

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

mysql –h hostname|hostIP –P port –u username –p DatabaseName –e "SQL语句"

• -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.2 创建用户

CREATE USER 用户名 [IDENTIFIED BY '密码'][,用户名 [IDENTIFIED BY '密码']];

举例：

CREATE USER zhang3 IDENTIFIED BY '123123'; # 默认host是 %
CREATE USER 'kangshifu'@'localhost' IDENTIFIED BY '123456';

# 1.3 修改用户

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

# 1.4 删除用户

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

DROP USER user[,user]…;

举例：

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

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

DELETE FROM mysql.user WHERE Host=’hostname’ AND User=’username’;
FLUSH PRIVILEGES;

# 1.5 设置当前用户密码

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';

# 2. 权限管理

# 2.1 权限列表

show privileges;

• CREATE和DROP权限 ，可以创建新的数据库和表，或删除（移掉）已有的数据库和表。如果将 MySQL 数据库中的 DROP 权限授予某用户，用户就可以删除 MySQL 访问权限保存的数据库。
• SELECT、INSERT、UPDATE和DELETE权限 允许在一个数据库现有的表上实施操作。
• SELECT权限 只有在它们真正从一个表中检索行时才被用到。
• INDEX权限 允许创建或删除索引，INDEX 适用于已有的表。如果具有某个表的 CREATE 权限，就可以在 CREATE TABLE 语句中包括索引定义。
• ALTER权限 可以使用 ALTER TABLE 来更改表的结构和重新命名表。
• CREATE ROUTINE权限 用来创建保存的程序（函数和程序）， ALTER ROUTINE权限 用来更改和删除保存的程序， EXECUTE权限 用来执行保存的程序。
• GRANT权限 允许授权给其他用户，可用于数据库、表和保存的程序。
• FILE权限 使用户可以使用 LOAD DATA INFILE 和 SELECT … INTO OUTFILE 语句读或写服务器上的文件，任何被授予 FILE 权限的用户都能读或写 MySQL 服务器上的任何文件（说明用户可以读任何数据库目录下的文件，因为服务器可以访问这些文件）。

# 2.2 授予权限的原则

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

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

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

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

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

# 2.3 授予权限

GRANT 权限1,权限2,…权限n ON 数据库名称.表名称 TO 用户名@用户地址 [IDENTIFIED BY ‘密码口令’];

• 该权限如果发现没有该用户，则会直接新建一个用户。
• 给 li4 用户用本地命令行方式，授予 atguigudb 这个库下的所有表的插删改查的权限。

GRANT SELECT,INSERT,DELETE,UPDATE ON atguigudb.* TO li4@localhost;

• 授予通过网络方式登录的 joe 用户 ，对所有库所有表的全部权限，密码设为 123。注意这里唯独不包括 grant 的权限

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

# 2.4 查看权限

• 查看当前用户权限

SHOW GRANTS; 
# 或 
SHOW GRANTS FOR CURRENT_USER; 
# 或 
SHOW GRANTS FOR CURRENT_USER();

• 查看某用户的全局权限

SHOW GRANTS FOR 'user'@'主机地址';

# 2.5 收回权限

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

• 收回权限命令

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

• 举例

#收回全库全表的所有权限 
REVOKE ALL PRIVILEGES ON *.* FROM joe@'%'; 
#收回mysql库下的所有表的插删改查权限 
REVOKE SELECT,INSERT,UPDATE,DELETE ON mysql.* FROM joe@localhost;

• 注意： 须用户重新登录后才能生效

# 3. 角色管理

# 3.1 创建角色

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

角色名称的命名规则和用户名类似。如果 host_name省略，默认为% ， role_name不可省略 ，不可为空。

# 3.2 给角色赋予权限

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

上述语句中 privileges 代表权限的名称，多个权限以逗号隔开。可使用 SHOW 语句查询权限名称

SHOW PRIVILEGES\G

# 3.3 查看角色的权限

SHOW GRANTS FOR 'role_name';

只要你创建了一个角色，系统就会自动给你一个 “ USAGE ” 权限，意思是 连接登录数据库的权限 。

# 3.4 回收角色的权限

REVOKE privileges ON tablename FROM 'rolename';

# 3.5 删除角色

DROP ROLE role [,role2]...

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

# 3.6 给用户赋予角色

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

GRANT role [,role2,...] TO user [,user2,...];

查询当前已激活的角色

SELECT CURRENT_ROLE();

# 3.7 激活角色

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

SET DEFAULT ROLE ALL TO 'kangshifu'@'localhost';

方式 2：将 activate_all_roles_on_login 设置为 ON

SET GLOBAL activate_all_roles_on_login=ON;

这条 SQL 语句的意思是，对 所有角色永久激活 。

# 3.8 撤销用户的角色

REVOKE role FROM user;

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

方式 1：服务启动前设置

[mysqld] 
mandatory_roles='role1,role2@localhost,r3@%.atguigu.com'

方式 2：运行时设置

SET PERSIST mandatory_roles = 'role1,role2@localhost,r3@%.example.com'; #系统重启后仍然有效
SET GLOBAL mandatory_roles = 'role1,role2@localhost,r3@%.example.com'; #系统重启后失效