MySQL学习Day17——用户与权限管理

一、用户管理:

MySQL的用户可以分为普通用户和root用户。root用户是超级管理员，拥有所有的权限，包括创建用户、删除用户和修改用户的密码等。普通用户只拥有被授予的各种权限。MySQL提供了许多语句用来管理用户账号，MySQL数据库的安全性需要通过用户管理来保证。

1.登录MySQL服务器:

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

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

2.创建用户:

在MySQL数据库中，官方推荐使用CREATE USER语句创建新用户。MySQL8版本移除了PASSWORD加密方法，因此不再推荐使用INSERT语句直接操作MySQL中的user表来增加用户。使用CREATE USER语句来创建新用户时，必须拥有CREATE USER权限。每添加一个用户，CREATE USER语句会在MySQL.user表中添加一条新记录，但是新创建的账户没有任何权限。如果添加的账户已经存在，CREATE USER语句就会返回一个错误。

CREATE USER语句的基本语法形式如下:

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

3.修改用户名(使用较少):

UPDATE mysql.user SET USER=新用户名 WHERE USER=原用户名;
FLUSH PRIVILEGES;

4.删除用户:

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

(1)使用DROP方式删除(推荐):使用DROP USER语句来删除用户时，必须用于DROP USER权限。DROP USER语句的基本语法形式如下:

DROP USER user[,user]....;
其中，user参数是需要删除的用户，由用户的用户名(User)和主机名(Host)组成。DROP USER语句可以同时删除多个用户，各用户之间用逗号隔开。

(2)使用DELETE方式删除:可以使用DELETE语句直接将用户的信息从mysql.user表中删除，但必须拥有对mysql.user表的DELETE权限。DELETE语句的基本语法形式如下:

DELETE FROM mysql.user WHERE Host='hostname’ AND User='username;
Host字段和User字段是user表的联合主键,两个字段的值才能唯一确定一条记录,
执行完DELETE命令后要使用FLUSH命令来使用户生效:FLUSH PRIVILEGES;

5.设置当前用户的密码:

适用于root用户修改自己的密码，以及普通用户登录后修改自己的密码。root用户拥有很高的权限，因此必须保证root用户的密码安全。root用户可以通过多种方式来修改密码，使用ALTER USER修改用户密码是MySQL官方推荐的方式。此外也可以通过SET语句修改密码。由于MySQL 8中已移除了PASSWORD()函数，因此不再使用UPDATE语句直接操作用户表修改密码。

方式一:使用ALTER USER命令来修改当前用户密码
ALTER USER USER()IDENTIFIED BY 'new_password';
方式二:使用SET语句来修改当前用户密码
SET PASSWORD='new_password';

 6.修改其他用户密码:

root用户不仅可以修改自己的密码，还可以修改其它普通用户的密码。root用户登录MySQL服务器后，可以通过ALTER语句和SET语句来修改普通用户的密码。由于PASSWORD()函数已移除，因此使用UPDATE直接操作用户表的方式已不再使用。

方式一:使用ALTER语句来修改普通用户的密码
ALTER USER user[IDENTIFIED BY'新密码',user[IDENTIFIED BY'新密码']]....;
其中，user参数表示新用户的账户，由用户名和主机名构成;“IDENTIFIED BY”关键字用来设置密码
方式二:使用SET命令来修改普通用户的密码
SET PASSWORD FOR 'username'@'hostname'='new_password';
其中username参数是普通用户的用户名;hostname参数是普通用户的主机名;new_password是新密码

二、权限管理

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

1.MySQL权限列表(show privileges)

权限	USER表中对应的列	权限的范围
CREATE	Create_priv	数据库、表或索引
DROP	Drop_priv	数据库、表或视图
GRANT_OPTION	Grant_priv	数据库、表或存储过程
REFERENCES	References_priv	数据库或表
EVENT	Event_priv	数据库
ALTER	Alter_priv	数据库
DELETE	Delete_priv	表
INDEX	Index_priv	表
INSERT	Insert_priv	表
SELECT	Select_priv	表或列
UPDATE	Update_priv	表或列
CREATE TEMPORARY TABLES	Create_tmp_table_priv	表
LOCK TABLES	Lock_tables_priv	表
TRIGGER	Trigger_priv	表
CREATE VIEW	Create_view_priv	视图
SHOW VIEW	Show_view_priv	视图
ALTER ROUTINE	Alter_routine_priv	存储过程或函数
CREATE ROUTINE	Create_routine_priv	存储过程或函数
EXECUTE	Execute_priv	存储过程或函数
FILE	File_priv	访问服务器上的文件
CREATE TABLESPACE	Create_tablespace_priv	服务器管理
CREATE USER	Create_user_priv	服务器管理
PROCESS	Process_priv	存储过程和函数
RELOAD	Reload_priv	访问服务器上的文件
REPLICATION CLIENT	Repl_client_priv	服务器管理
REPLICATION SLAVE	Repl_slave_priv	服务器管理
SHOW DATABASES	Show_db_priv	服务器管理
SHUTDOWN	Shutdown_priv	服务器管理
SUPER	Super_priv	服务器管理

权限说明:

(1)CREATE和DROP权限:可以创建新的数据库和表，或删除已有的数据库和表。如果将MySQL数据库中的DROP权限授予某用户，用户就可以删除MySQL访问权限保存的数据库。

(2)SELECT,INSERT,UPDATE和DELETE权限:允许在一个数据库现有的表上实施操作。

(3)SELECT权限:只有在他们真正从一个表中检索行时才被用到。

(4)INDEX权限:允许创建或删除索引，INDEX权限适用于已有的表。

(5)ALTER权限:可以使用ALTER TABLE来更改表的结构和重命名表

(6)CREATE ROUTINE权限:用来创建保存的程序(函数或程序),ALTER ROUTINE权限用来更改和删除保存的程序,EXECUTE用来执行保存的程序

(7)GRANT权限:允许授权给其他用户，可用于数据库、表和保存的程序

(8)FILE权限:使用户可以使用LOAD DATA INFILE和SELECT ... INTO OUTFILE语句读写服务器上的文件，任何被授予FILE权限的用户都能读或写MySQL服务器上的任何文件。

权限分布	可能的设置的权限
表权限	'Select', 'Insert', 'Update', 'Delete', 'Create', 'Drop', 'Grant', 'References', 'Index', 'Alter'
列权限	'Select', 'Insert', 'Update', 'References'
过程权限	'Execute', 'Alter Routine', 'Grant'

2.授予权限的原则:

(1).只授予能满足需要的最小权限

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

(3).为每个用户设置满足密码复杂度的密码

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

3.授予权限:

给用户授权的方式有2种，分别是通过把角色赋予用户给用户授权和直接给用户授权。

授权命令:

方式一:
GRANT 权限1,权限2,权限n ON 数据库名称.表名称 TO 用户名@用户地址[IDENTIFIED BY'密码口令’]
1.该权限如果发现没有该用户，则会直接新建一个用户
2.如果需要赋予包括GRANT的权限，添加参数“ WITH GRANT OPTION”这个选项即可，表示该用户可以将自己拥有的权限授权给别人。经常有人在创建操作用户的时候不指定WITH GRANT OPTION选项导致后来该用户不能使用GRANT命令创建用户或者给其它用户授权。
3.可以使用GRANT重复给用户添加权限，权限叠加，比如你先给用户添加一个SELECT权限，然后又给用户添加个INSERT权限，那么该用户就同时拥有了SELECT和INSERT权限。

4.查看权限:

SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();

5.收回权限:

收回权限就是取消已经赋予用户的某些权限。收回用户不必要的权限可以在一定程度上保证系统的安全性。MySQL中使用REVOKE语句取消用户的某些权限。使用REVOKE收回权限之后，用户账户的记录将从db、host、tables_priv和columns_priv表中删除，但是用户账户记录仍然在user表中保存(删除user表中的账户记录使用DROP USER语句)。在将用户账户从user表删除之前，应该收回相应用户的所有权限。

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

三、权限表:

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

表名	描述
user	用户账号以及权限信息
global_grants	动态全局授权
db	数据库层级的权限
tables_priv	表层级的权限
columns_priv	列层级的权限
procs_priv	存储过程和函数的权限
proxies_priv	代理用户的权限
default_roles	账号连接并认证后默认授予的角色
role_edges	角色子图的边界
password_history	密码更改信息

四、角色管理:

1.角色:

角色是在 MySQL8.0 中引入的新功能。在MySQL中，角色是权限的集合，可以为角色添加或移除权限。用户可以被赋予角色，同时也被授予角色包含的权限。对角色进行操作需要较高的权限。并且像用户账户一样，角色可以拥有授予和撤消的权限。引入角色的目的是方便管理拥有相同权限的用户。恰当的权限设定，可以确保数据的安全性，这是至关重要的。

2.创建角色:

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

CREATE ROLE 'role_name'[@'host_name'] [,'role_name'[@'host_name']]...
角色名称的命名规则和用户名类似。如果host_name省略,默认为%,role_name不可省略，不可为空。

3.给角色赋予权限:

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

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

4.查看角色的权限:

SHOW GRANTS FOR ROLE_NAME;

5.回收角色的权限:

REVOKE privileges ON table_name FROM 'role_name'[@'host_name'];

6.删除角色:

DROP ROLE role1[,role2,....];

7.给用户赋予对应的角色:

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

GRANT role1[,role2,....] TO user1[,user2...];

 8.激活角色:

方式一:SET DEFAULT ROLE ALL TO user;
方式二:将系统变量activate_all_roles_on_login设置为ON
SET GLOBAL activate_all_roles_on_login = ON

9.撤销用户的角色:

REVOKE role1[,role2,....] FROM user1[,user2...];