mysql8.0.30用户与权限管理

用户管理

登录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
  服务器。

创建用户

create user zhang3 identified by '123_qwerQWER'; # 默认hosts是 % 
flush privileges; #立即生效
---------------------------------------------------------
create user 'kangshifu'@'localhost' identified by '123_qwerQWER'; # 创建指定host的用户

修改用户

update mysql.user set user='li4' where user='zhang3';
flush privileges;

删除用户

drop user li4; # 默认删除host为%的用户
drop user 'kangshifu'@'localhost'; #指定删除host的用户
flush privileges;
---------------------------------------------------------
delete from mysql.user where host='%' and user='zhang3'; # delete也可以删除用户
flush privileges;

查看用户

select host, user from user;
或
select host, user from mysql.user;

修改用户密码

set password for 'zhang3'@'%'='122_qwerQWER';

权限管理

查看权限

show privileges;

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

授予权限的原则

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

• 1、只授予能 满足需要的最小权限 ，防止用户干坏事。比如用户只是需要查询，那就只给select权限就可以了，不要给用户赋予update、insert或者delete权限。
• 2、创建用户的时候 限制用户的登录主机 ，一般是限制成指定IP或者内网IP段。
• 3、为每个用户 设置满足密码复杂度的密码 。
• 4、 定期清理不需要的用户 ，回收权限或者删除用户。

授予权限

注意： 命令行字母可小写

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

实例如下

 grant select on atguigu.* to zhang3@%;
 grant select on custom.users to 'zhang3'@'%';

查看权限

show grants for 'root'@'%'\G
show grants for 'zhang3'@'%'\G

收回权限

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

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

查看user 信息表结构和信息

desc mysql.user;
select * from mysql.user\G

db表

DESCRIBE mysql.db;

• 1. 用户列 db表用户列有3个字段，分别是Host、User、Db。3个字段分别表示主机名、用户名和数据库名。表示从某个主机连接某个用户对某个数据库的操作权限，这3个字段的组合构成了db表的主键。
• 2. 权限列
     Create_routine_priv和Alter_routine_priv这两个字段决定用户是否具有创建和修改存储过程的权限

其他表信息、

procs_priv表 tables_priv表和 columns_priv表 需要时自行了解

角色管理

• 角色（Role），可以认为是一些权限的集合，一直是存在各个数据库中，比如Oracle、SQL Server、OceanBase等，MySQL 自从 8.0 release 才引入角色这个概念。
• 为用户赋予统一的角色，即把一个带有某些权限集合的角色分配给一个用户，那该用户就拥有了该角色所包含的所有权限，权限的修改直接通过角色来进行，无需为每个用户单独授权，大大的方便了权限管理。

创建角色

CREATE ROLE 'role_name'[@'host_name'] [,'role_name'[@'host_name']]...
CREATE ROLE 'manager'@'localhost';

给角色授予权限

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

查看所有权限

show privileges\G

查看角色的权限

SHOW GRANTS FOR 'manager';

回收角色的权限

REVOKE privileges ON tablename FROM 'rolename';

删除角色

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

DROP ROLE role [,role2]...
DROP ROLE 'school_read';

给用户赋予角色

GRANT role [,role2,...] TO user [,user2,...];
GRANT 'school_read' TO 'kangshifu'@'localhost';

激活角色

show variables like 'activate_all_roles_on_login';
SET GLOBAL activate_all_roles_on_login=ON;

这条 SQL 语句的意思是，对 所有角色永久激活 。运行这条语句之后，用户才真正拥有了赋予角色的所有
权限。

撤销用户的角色

REVOKE role FROM user;
REVOKE 'school_read' FROM 'kangshifu'@'localhost';
SHOW GRANTS FOR 'kangshifu'@'localhost';

设置强制角色(mandatory role)