Mysql用户与权限操作

1.用户与权限概述

用户是数据库的使用者和管理者。 MySQL通过用户的设置来控制数据库操作人员的访问与操作范围。 服务器中名为mysqI的数据库，用于维护数据库的用户以及权限的控制和管理。 MySQL中的所有用户信息都保存在mysql.user数据表中。

根据mysql.user表字段的功能可将其分为6类

• 客户端访问服务器的账号字段

Host和User字段共同组成的复合主键用于区分MySQL中的账户。

1. User字段用于代表用户的名称。
2. Host字段表示允许访问的客户端IP地址或主机地址。
3. 当Host的值为“*”时，表示所有客户端的用户都可以访问。

mysql> SELEC Thost, user FROM mysql.user;

在这里插入图片描述

root:默认的超级用户。 session: MySQL5.7新增用户，用于用户身份验证。 sys:MySQL5.7新增用户，用于系统模式对象的定义，防止DBA（数据库管理员）重命名或删除root用户时发生错误。

• 身份验证字段

在MySQL5.7中，mysql.user表中已不再包含Password字段，而是使用plugin和authentication_string字段保存用户身份验证的信息。

1. plugin字段用于指定用户的验证插件名称。
2. authentication_string字段是根据plugin指定的插件算法对账户明文密码（如123456）加密后的字符串。

mysql> SELECT plugin,authentication_string FROM mysql.user
->WHERE user='root';

在这里插入图片描述

MySQL中root用户的默认验证插件名为mysql_native_password。 authentication_string字段保存的则是一串不能看出具体含义的值，相对于能够直接看懂的明文密码（如123456），它是经过加密处理的暗码。 其他与身份验证的账号密码相关的字段还有password_expired（密码是否过期)、password_last_changed（密码最后一次修改的时间）以及password_lifetime（密码的有效期）。

• 安全连接的字段

判断当前连接是否符合SSL安全协议。

1. ssl_type:用于保存安全连接的类型，它的可选值有"(空）、ANY（任意类型）、X509(X509证书）、SPECIFIED（规定的）四种。
2. ssl_cipher:用于保存安全加密连接的特定密码。
3. x509_issuer:保存由CA签发的有效的X509证书。
4. x509_subject:保存包含主题的有效X509证书。

mysql>SHOW VARIABLES LIKE 'have_openssl';

在这里插入图片描述

• 资源限制的字段

1. 以“max_”开头的字段，保存对用户可使用的服务器资源的限制。
2. 用来防止用户登录MySQL服务器后的不法或不合规范的操作浪费服务器的资源。
3. 用户资源限制字段默认值均为0，表示对此用户没有任何的资源限制。

• 权限字段

以“priv”结尾的字段一共有29个，这些字段保存了用户的全局权限，如Select_ priv 查询权限、Insert_ priv插入权限，Update_ priv更 新权限等。 user表对应的权限字段的数据类型都是ENUM枚举类型，取值只有N或Y两种。

1. N表示该用户没有对应权限，默认值都为N。
2. Y表示该用户有对应权限。

• 账户是否锁定的字段

account_ locked字 段用于保存当前用户是锁定、还是解锁状态。

1. 该字段是一个枚举类型，当其值为N时表示解锁，此用户可以用于连接服务器。
2. 当其值为Y时表示该用户已被锁定，不能用于连接服务器使用。

2.用户管理

1.创建用户

• 由于MySQL中所有用户的信息都保存在mysql.user表中。创建用户可以直接利用root用户登录MySQL服务器后，向mysql.user表中插入记录，但是在开发中为保证数据的安全，并丕推茬使用此方式创建用户。
• 采用MySQL提供的CREATE USER语句创建用户。
• 使用CREATE USER语句每创建一个 新用户，都会在mysql.user表中添加一条记录，同时服务器会自动修改相应的授权表。
• 该语句创建的新用户默认情况下没有任何权限，需要使用GRANT进行授权。

创建用户语法

CREATE USER [IF NOT EXISTS]
账户名[用户身份验证选项][,账户名[用户身份验证选项].
[WITH资源控制选项][密码管理选项|账户锁定选项]

CREATE USER可以一次创建多个用户，多个用户之间使用逗号分隔。 账户名是由“用户名@主机地址”组成。 其余选项在创建用户时，若未设置则使用默认值。 用户名的设置不能超过32个字符，且区分大小写，但是主机地址不区分大小写。

• 用户身份验证选项的设置仅适用于其前面的用户名，可将其理解为某个用户的私有属性。
• 其余的选项对声明中的所有用户都有效，可以将其理解为全局属性。

1.创建最简单的用户

mysql> CREATE USER' test1' ;
Query OK, 0 rows af fected .(0.00 sec)
mysql> SELECT host, user FROM mysql . user;

在这里插入图片描述

2.创建含有密码的用户

mysql> CREATE USER 'test2' @ 'localhost' IDENTIFIED BY ' 123456' ;
Query OK,0 rows affected (0.00 sec)

在这里插入图片描述

3.同时创建多个用户

mysql> CREATE USER
-> 'test3'@' localhost' IDENTIFIED BY '333333' ,
-> 'test4'@' localhost' IDENTIFIED BY '444444' ;
Query OK，0 rows affected (0.01 sec)

多个用户之间使用逗号分隔。 在创建每个用户时可以单独为其设置密码，省略用户身份验证选项时，表明此用户在登录服务器时可以免密登录,但为了保证数据安全，不推荐用户这样做。

在创建用户时，可以添加WITH直接为用户指定可操作的资源范围，如登录的用户在一小时内可以查询数据的次数等。

MAX_USER_CONNECTIONS选 项的值为0时，服务器将根据max_ user_ connections 系统变量的值确定用户的同时连接数，若此变量值也为0,表示对该用户没有限制。 MAX_QUERIES_PER_HOUR选项不会计算从缓存中查询数据的次数。

例：限制其每小时最多可以更新10次

mysql> CREATE USER
-> 'test5'@' localhost' IDENTIFIED BY ' 555555'
-> WITH MAX_ UPDATES_ _PER_ HOUR 10;
Query OK，0 rows affected (0.00 sec)

查看user表的max_ updates字段

mysql> SELECT max_ _updates FROM user WHERE user='test5' ;
+-------------+
|max_ updatesI|
+-------------+
|	  10	  | 
1 row in set (0.00 sec)

2.修改密码

• ALTER USER是更改密码的首选SQL语句，推荐使用。
• 第2种语法可能会被记录到服务器的日志或客户端的历史文件中，会有密码泄露.的风险，因此建议用户尽量少的使用此方式设置密码。

为指定用户设置密码

mysql> ALTER USER 'test1' @ '&' IDENTIFIED BY '123456' ;
Query 0K，0 rows affected (0.00 sec)

为登录户设置密码

mysql> ALTER USER USER() IDENTIFIED BY '000000' ;
Query 0K，0 rows affected (0.00 sec)

3.修改用户

用户创建完成后，管理员可以通过MySQL提供的专门SQL语句修改用户的密码、身份验证的方式、资源限制、密码的属性、以及账户的锁定和解锁的状态。

ALTER USER [IF EXISTS]
账户名[用户身份验证选项][,账户名[用户身份验证选项]].
[WITH资源限制选项][密码管理选项|账户锁定选项]

ALTER USER可同时修改一个或多个用户，多个用户之间使用逗号(,)分隔。 语法中选项的可选值与创建用户时的选项完全相同。 每个修改的用户，都会更新其在mysql.user表中对应的字段值，而未修改的字段仍然保留它原来的值。

例：修改用户验证插件、密码以及密码过期时间

mysql> ALTER USER test1
-> IDENTIFIED WITH sha256_ password BY '111111 '
-> PASSWORD EXPIRE;
Query OK，0 rows affected (0.01 sec)

查看修改后户的密码

mysq1> SELECT authentication_ string FROM mysql. user
-> WHERE user='test1' AND plugin= 'sha256_ password' ；

在这里插入图片描述

解锁用户

mysql> ALTER USER 'test7'@ ' localhost' ACCOUNT UNLOCK;
Query 0K，0 rows affected (0.00 sec)

同时修改多个户资源

mysql> ALTER USER
-> 'test1' IDENTIFIED WITH mysql_native_password,
-> 'test2'@' localhost' IDENTIFIED BY '222222'
-> WITH max_ _user_ connections 2;
Query 0K，0 rows affected (0.00 sec)

4.删除用户

在MySQL中经常会创建多个普通用户管理数据库，但如果发现某些用户是没有必要的，就可以将其删除，通常删除用户的方式采用MySQL提供的专门SQL语句。

DROP USER [IF EXISTS]账户名[,账户名]

例：

mysql> DROP USER IE EXISTS test7;
Query 0K，0 rows affected, 1 warning (0.01 sec) .
#在删除账户时，如果省略主机地址，则默认为%’。

当DROP USER语句删除当前正在打开的用户时，则该用户的会话不会被自动关闭。只有在该用户会话关闭后，删除操作才会生效，再次登录将会失败。另外，利用已删除的用户登录服务器创建的数据库或对象不会因此删除操作而失效。

3.权限管理

1.授予权限

根据权限的操作内容可将权限大致分为数据权限、结构权限以及管理权限。

权限级别指的就是权限可以被应用在哪些数据库的内容中。

例如，SELECT权限可以被授予到全局(任意数据库下的任意内容)、数据库(指定数据库下的任意内容)、表(指定数据库下的指定数据表)、列(指定数据库.下的指定数据表中的指定字段)。

GRANT 权限类型[字段列表][，权限类型[字段列表] ...
ON [目标类型]权限级别
TO 账户名[用户身份验证选项] [,账户名[用户身份验证选项]
...
[REQUIRE 连接方式]
[WITH {GRANT OPTION |资源控制选项}]

• 权限类型:指的就是SELECT、DROP、CREATE等权限。
• 字段列表:用于设置列权限。
• 目标类型:默认为TABLE，表示将全局、数据库、表或列中的某些权限授予给指定的用户。其他值为FUNCTION (函数)或PROCEDURE (存储过程)。
• 权限级别:用于定义全局权限、数据库权限和表权限。
• 添加GRANT OPTION: 表示当前账户可以为其他账户进行授权。
• 其余各参数均与CREATE USER中的用户选项相同，这里不再赘述。

例：查看root用户和test1用户的授权情况

mysql>SHOW GRANTS FOR root'' localhost' ;

在这里插入图片描述

mysql> SHOW GRANTS FOR 'test1' @ '%' ;

在这里插入图片描述

• ALL_PRIVILEGES表示除GRANT OPTION (授权权限)和PROXY (代理权限)外的所有权限。
• USAGE表示没有任何权限。
• ON后的*.*表示全局级别的权限，即MySQL 服务器下的所有数据库下的所有表，“@"表示任何主机中的匿名用户。

例：授予test1户shop.sh_ goods表的SELECT权限，以及对name和price字段的插入权限

mysql> GRANT SELECT, INSERT (name, price)
-> ON shop.sh goods
-> TO 'test1 '@'号';
Query 0K，0 rows affected (0.00 sec)

查看权限的保存情况

mysql> SELECT db, table name, table priv, column_priv
-> FROM mysq1.tables priv WHERE user = 'test1' ;

在这里插入图片描述

mysql> SELECT db, table name , column name , column_priv
-> FROM mysql.columns_ priv WHERE user= ' test1' ;

在这里插入图片描述

2.回收权限

在MySQL中，为了保证数据库的安全性，需要将用户不必要的权限回收。 例如，数据管理员发现某个用户不应该具有DELETE权限，就应该及时将其收回。.

#①回收指定用户的指定权限
REVOKE权限类型[(字段列表)] [,权限类型[(字段列表)]]
ON [目标类型]权限级别FROM账户名[,账户名] ....
#②回收所有权限以及可为其他用户授权的权限
REVOKE ALL [PRIVIL EGES], GRANT OPTION FROM账户名[,账户名]
#③回收用户的代理权限
REVOKE PROXY ON账户名FROM账户名1[,账户名2] ...

回收test1用户的插入权限

mysq1> REVOKE INSERT (name, price)
-> ON shop.sh_ _goods FROM ' test1' @ '%' ;
Query OK, 0 rows affected (0.00 sec)

test1用户登录MySQL服务器，并插入数据

mysql> INSERT INTO shop.sh_ goods (name, price) VALUES('test', 23);
ERROR 1142 (42000):INSERT command denied to user ' test1'@ 'localhost' for table 'sh_goods'

3.刷新权限

刷新权限:指的是从系统数据库mysq|中的权限表中重新加载用户的权限。 原因在于: GRANT、CREATE USER等操作会将服务器的缓存信息保存到内存中,而REVOKE、DROP USER操作并不会同步到内存中，因此可能会造成服务器内存的消耗，所以在REVOKE、DROP USER后推荐读者使用MySQL提供的“FLUSH PRIVILEGES"重新加载用户的权限。

#方式1
FLUSH PRIVIL.EGES;
#方式2
mysqladmin -uroot -p reload
#方式3
mysqladmin -uroot -p flush-privileges