mysql用户名解锁_MySQL用户与权限

1.用户与权限概述

MySQL中的所有用户信息都保存在mysql.user数据表中。

使用desc即可查看user表含有的45个字段。(5.7版本中只有42个字段)

(1)账号字段

Host和User字段共同组成的复合主键用于区分MySQL中的账户，User字段用于代表用户的名称，Host字段表示允许访问的客户端IP地址或主机地址，当Host的值为“*”时，表示所有客户端的用户都可以访问。

在user表中，除了默认的root超级用户外，MySQL 5.7中还额外地新增了两个用户mysql.session和mysql.sys。前者用于用户身份验证，后者用于系统模式对象的定义，防止DBA(数据库管理员)重命名或者删除root用户时发生错误。

默认情况下，用户mysql.session和mysql.sys已被锁定，使得数据库操作人员无法使用这两个用户通过客户端连接MySQL服务器。

(2)身份验证字段

在MySQL 5.7中，mysql.user表中已不再包含Password字段，而是使用plugin和authentication_string字段保存用户身份验证的信息。其中，plugin字段用于指定用户的验证插件名称，authentication_string字段是根据plugin和authentication_string值。

下面通过select语句查询user表中root用户默认的plugin和authentication_string值。

select plugin,authentication_string from mysql.user where user=‘root’;

(3)安全连接字段

在客户端与MySQL服务器连接时，除了可以基于账户名以及密码的常规验证外，还可以判断当前连接是否符合SSL安全协议，与其相关的字段有以下几种：

ssl_type：用于保存安全连接的类型，它的可选值有’’(空)、ANY(任意类型)、X509(X509证书)、SPECIFIED(规定的)4种。

ssl_cipher：用于保存安全加密连接的特定密码。

x509_issuer：保存由CA签发的有效的X509证书。

x509_subject：保存包含主题的有效的X509证书。

需要注意的是。通常标准的发行版MySQL默认未启用SSL加密连接，可以通过以下SQL语句查看：

show variables like ‘have_oppenssl’;

(4)资源限制字段

max_questions:保存每小时允许用户执行查询操作的最多次数。

max_updates:保存每小时允许用户执行更新操作的最多次数。

max_connections:保存每小时允许用户建立连接的最多次数。

max_user_connections:保存允许单个用户同时建立连接的最多数量。

以上列举的用户资源限制字段默认值均为0，表示对此用户没有任何的资源限制。

(5)权限字段

在mysql.user表中提供的以“_priv”结尾的字段保存了用户的全局权限，如Select_priv查询权限、Insert_priv插入权限、Update_priv个更新权限。

其中，user表对应的权限字段的数据类型都是ENUM枚举类型，取值只有N或Y两种。N表示该用户没有对应权限，Y表示该用户有对应权限。为了保证数据库的安全，这些字段的默认值都为N。

6)账户锁定字段

在mysql.user表中提供的account_locked字段用于保存当前用户是锁定还是解锁状态。该字段是一个枚举类型，当其值为N时表示解锁，此用户可以用于连接服务器；当其值为Y时表示该用户已被锁定，不能用于连接服务器使用。

2.用户管理

(1)创建用户

采用MySQL提供的create user和grant语句创建用户。其中，grant语句在创建用户的同时还可以完成权限的设置。

使用create user 语句创建每一个新用户，都会在mysql.user表中添加一条记录，同时服务器会自动修改相应的授权表。但需要注意，该语句创建的新用户默认情况下没有任何权限，需要使用grant语句进行授权。

a.创建最简单的用户

create user ‘test1’;

使用select查看mysql数据库下的user表，查看创建的用户是否已添加到user表中保存

select host,user from mysql.user;

(host的值为“%”时，表示当前的用户可以在任何主机中连接MySQL服务器；当其值为“localhost”时，表示当前的用户只能从本地主机连接MySQL服务器。)

注：用户名和主机名在设置时，若不包含空格、“-”等特殊字符，则可以省略引号。另外，当创建的用户名称为空字符串时，表示创建的是一个匿名用户(登录时，不需要输入用户名和密码。不推荐使用)。

b.创建含有密码的用户

create user [email protected] identified by ‘123456’;

identified by后指定的是字符串形式的明文密码，接着通过select查询存储在user表中test2用户的密码。

select plugin,authentication_string from mysql.user

where user=‘test2’;

由运行结果可知，创建用户时设置的明文密码，在user表中将其转换为暗码。(在MySQL 5.5中，使用select Password from mysql.user where user=‘test2’;进行查询)

除此之外，在设置用户密码时还可以指定对密码加密的插件，只需将“identified by ‘明文密码’”修改为指定的选项即可。

create user [email protected] identified with ‘mysql_native_password’ by ‘123456’;

(MySQL 8.0.18中应写成create user [email protected] identified with ‘caching_sha2_password’ by ‘123456’;)

c.同时创建多个用户

create user

‘test3’@'localhost’identified by ‘333333’,

‘test4’@'localhost’identified by ‘444444’;

多个用户之间使用逗号隔开，省略用户身份验证选项时，表明此用户在登陆服务器时可以免密登录，但为了保证数据安全，不推荐这样做。

d.设置用户可操作资源范围

例如，创建一个名为test5的本地用户，限制其每小时最多可以更新10次。

create user

[email protected] by’555555’

with max_updates_per_hour 10;

查看user表中max_updates字段(使用mysql数据库)

select max_updates from user where user=‘test5’;

d.设置有密码期限的用户

创建一个名为test6的用户，并设置其密码每180天更新一次。

create user [email protected] identified by '666666’password expire interval 180 day;

e.设置用户是否锁定

被锁定的用户不能在客户端登录MySQL服务器。下面创建一个锁定的用户test7.

create user [email protected] identified by ‘777777’ password expire account lock;

利用select查看表mysql.usesr表中名为test7的account_locked字段，若其值为Y。则表示当前创建的用户已被锁定。

select account_locked from mysql.user where user=‘test7’;

此时若利用test7在客户端登录MySQL服务器，会报“账户被锁定，[email protected]

2)设置密码

a.为指定用户设置密码

mysql.user表中的test1用户是一个可以在任何主机上连接MySQL服务器的用户，但此用户在创建时未设置密码，下面将密码设置为123456.

alter user [email protected]%’ identified by ‘123456’;

b.为登录用户设置密码

若当前通过客户端连接MySQL服务器的用户是非匿名用户，则可以使用user()函数更改自己的密码，而无须直接为自己的账户命名。

例如，修改当前正在连接MySQL服务器的root用户密码，将其设置为000000.

alter user user() identified by ‘000000’;

user()函数获取的是客户端提供的用户和主机地址，它可能与当前通过MySQL服务器验证的用户与主机名不同，此时可以利用current_user()函数获取。

select current_user();

c.mysqladmin修改用户密码

在MySQL安装目录bin下还有一个mysqladmin.exe应用程序，它通常用于执行一些管理性的工作，以及显示服务器状态等。同时，在MySQL中也可以使用该命令修改用户的密码。

例如，在命令窗口中，使用mysqladmin命令，将root用户的密码修改为123456

mysqladmin -u root -p password 123456

回车后需要输入原密码

d.root密码丢失找回

若忘记MySQL服务的root用户密码，就不能通过以上设置密码的方式找回或重置。此时可以在MySQL的配置文件my.ini中添加skip-grant-tables选项，然后重启MySQL服务后再次利用root用户登录，就可以跳过密码的输入直接登录MySQL服务器，为root用户设置密码。

(但此种方法存在非常大的安全风险。建议慎用！)

(3)修改用户

a.修改验证插件

例如，将mysql.user表中名为test1的用户验证插件修改为sha256_password，密码修改为111111，并将密码设置为立刻过期。

alter user test1

identified with sha256_password by ‘111111’

password expire;

下面查看名为test1且插件算法为sha256_password的authentication_string字段的值。

select authentication_string from mysql.user

where user='test1’and plugin=‘sha256_password’;

(用户的加密算法不同，则此记录对应的authentication_string字段保存的值也不同。)

b.解锁用户

解锁mysql.user表中被锁定的用户test7.

alter user ‘test7’@'localhost’account unlock;

c.同时修改多个用户的资源限定

例如，修改mysql.user表中的test1和test2用户，限定单个用户最多可同时建立两个连接，同时将test1的验证插件修改为MySQL的默认值，test2用户的密码修改为222222.

alter user

‘test1’ identified with mysql_native_password,

[email protected] identified by ‘222222’

with max_user_connections 2;

d.删除用户

MySQL 5.x版本之后drop user语句可以同时删除一个或多个MySQL中的指定用户，并会同时从授权表中删除账户对应的权限行。在MySQL 5.x之前的版本中，在删除用户前必须先回收用户的权限。

drop user if exists test7;

在不添加if exists关键字时，若删除了一个不存在的用户，则该语句的执行会发生错误；在添加后，会在删除不存在的用户时生成一个警告作为提示。其中，在删除账户时，如果省略主机地址，则默认为“’%’”。

3.权限管理

(1)授予权限

查看一下root和test1用户被授权的情况

show grants for [email protected];

show grants for [email protected]%’;

查询结果中all privileges表示除grant option(授权权限)和proxy(代理权限)外的所有权限，usage表示没有任何权限。on后的“ * . * ”表示全局级别的权限，即MySQL服务器下的所有数据库下的所有表，“"@"”表示任何主机中的匿名用户。

grant select,insert (name,price) on sh_goods to [email protected]%’;

上述语句中，select权限是表级权限(sh_goods表)，insert是列级权限(sh_goods表中的name和price字段)。因此，在SQL语句执行成功后，若要查test1的权限，可以到mysql.tables_priv中查看表权限，到mysql.columns_priv中查看列权限。

select db,table_name,table_priv,column_priv from mysql.tables_priv where user=‘test1’;

select db,table_name,column_name,column_priv from mysql.columns_priv where user=‘test1’;

(2)创建用户

grant select on * .* to [email protected];

在MySQL 5.7中，当grant语句中指定的账户不存在时，系统不支持自动创建用户，它会报一个在user中找不到用户的提示信息。例如上述语句(test8不存在)。

为了解决上述问题，只需确保MySQL中no_auto_create_user模式未开启，就可以利用grant自动创建一个不存在的用户。

set sql_mode=’’;

grant select on * .* to [email protected]; #MySQL5.7z中

grant创建新用户的方式已经被遗弃，并在未来会被移除。高版本中已经不能执行，因此，MySQL官方推荐使用create user语句创建用户，使用alter user语句修改用户的非特权选项(如验证插件、资源控制选项等)，使用grant为新用户授予权限。

(3)回收权限

下面以删除test1用户对sh_goods表的name和price字段的插入权限为例进行演示。

revoke insert(name,price) on sh_goods from [email protected]%’;

(执行上述操作后，用test1用户登录MySQL服务器，向sh_goods表中插入数据，就会报错。)

(4)刷新权限

从系统数据库mysql中的权限表中重新加载用户的权限。原因在于grant、create user等操作会将服务器的缓存信息保存到内存中，而revoke、drop user操作并不会同步到内存中，因此可能会造成服务器的内存消耗，所以在revoke、drop user后推荐读者使用MySQL提供的flush privileges重新加载用户的权限。

flush privileges;

另外刷新权限也可以利用mysqladmin命令完成，如下：

mysqladmin -uroot -p reload

或mysqladmin -uroot -p flush-privileges