mysql 用户管理-账户管理

学习了《mysql 用户管理-权限表》。接着学习更常用的的账户管理。

2，账户管理

MySQL提供许多语句用来管理用户账号,这些语句可以用来管理包括登录和退出MySQL服务器、创建用户、删除用户、密码管理和权限管理等内容。MySQL 数据库的安全性，需要通过账户管理来保证。

1，登录和退出MySQL服务器

已经知道登录MySQL时，使用MySQL命令并在后面指定登录主机以及用户名和密码。

通过MySQL- -help 命令可以查看MySQL命令帮助信息。MySQL 命令的常用参数如下：

（1）-h 主机名，可以使用该参数指定主机名或ip, 如果不指定，默认是localhost.

（2）-u 用户名，可以使用该参数指定用户名。

（3）-p 密码，可以使用该参数指定登录密码。如果该参数后面有一段字段， 则该段字符串将作为用户的密码直接登录。如果后面没有内容，则登录的时候会提示输入密码。注意：该参数后面的字符串和-p之前不能有空格。

（4）-P 端口号，该参数后面接MySQL服务器的端口号，默认为3306。

（5）数据库名，可以在命令的最后指定数据库名。

（6）-e 执行SQL语句。如果指定了该参数，将在登录后执行-e后面的命令或SQL语句

并退出。

[例1]使用root用户登录到本地MySQL服务器的test库中，命令如下：

mysql -h localhost -u root -p test

命令执行如下：

执行命令时，会提示Enter password；如果没有设置密码，可以直接按Enter键。密码正确就可以直接登录到服务器下面的test数据库中了。

[例2]使用root用户登录到本地MySQL服务器的MySQL数据库中，同时执行一条查询语句。命令如下：

mysql -h localhost -u root -p mysql -e "DESC user;"

命令执行如下：

按照提示输入密码，命令执行完成后查询出person 表的结构，查询返回之后会自动退出MySQL。

PS： 这个得知道有哪些数据库，一般是先登录后，再用show databases; 再进入对应数据库中。

mysql -u root -p
输入密码：
show databases;
use mysql;
show tables;
desc user;

2，新建普通用户

创建新用户，必须有相应的权限来执行创建操作。在MySQL数据库中，有两种方式创建

新用户： 一种是使用CREATE USER或GRANT语句；另一种是直接操作MySQL授权表。

最好的方法是使用GRANT语句，因为这样更精确，错误少。

#

1，使用CREATE USER语句创建新用户

执行CREATE USER或GRANT语句时，服务器会修改相应的用户授权表，添加或者修改用户及其权限。CREATE USER语句的基本语法格式如下：

CREATE USER user_specification
[，user_specification] ...
user_specification：
user@host
  [
    IDENTIFIED BY [PASSWORD] 'password '
    | IDENTIFIED WITH auth_plugin [AS 'auth string' ]
  ]

user表示创建的用户的名称： host 表示允许登录的用户主机名称；

IDENTIFIED BY表示用来设置用户的密码： [PASSWORD]表示使用哈希值设置密码，该参数可选；

‘password’ 表示用户登录时使用的普通明文密码；

IDENTIFIED WITH语句为用户指定-一个身份验证插件；

auth_plugin 是插件的名称，插件的名称可以是一个带单引号的字符串，或者带引号的字符串；

auth_string 是可选的字符串参数该参数将传递给身份验证插件，由该插件解释该参数的意义。

CREATE USER语句会添加一个新的MySQL账户。使用CREATE USER语句的用户，必须有全局的CREATE USER权限或MySQL数据库的INSERT权限。每添加一一个用户, CREATE USER语句会在MySQL.user表中添加一条新记录，但是新创建的账户没有任何权限。如果添加的账户已经存在，CREATE USER语句会返回-一个错误。

[例3]使用CREATE USER创建-一个用户， 用户名是jeffrey,密码是mypass，主机名是localhost,命令如下：

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';

如果只指定用户名部分’jeffrey’ ，主机名部分则默认为“%’( 即对所有的主机开放权限)。

user_specification 告诉MySQL服务器当用户登录时怎么验证用户的登录授权。如果指定用户登录不需要密码，可以省略IDENTIFIED BY部分：

CREATE USER 'jeffrey'@'localhost';

此种情况，MySQL服务端使用内建的身份验证机制，用户登录时不能指定密码。如果要创建指定密码的用户，需要 IDENTIFIED BY 指定明文密码值：

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';

此种情况，MySQL服务端使用内建的身份验证机制，用户登录时必须指定密码。

为了避免指定明文密码，如果知道密码的散列值，可以通过PASSWORD关键字使用密码的哈希值设置密码。

密码的哈希值可以使用password(函数获取，如：

SELECT password('mypass');

*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4就是 mypass的哈希值。接下来执行下面语句：

CREATE USER 'jeffrey'@'Localhost'
IDENTIFIED BY PASSWORD '*6C8989366EAF75BB670AD8EA7A7EC1176A95CEF4';

用户 jeffrey 的密码将被设定为mypass。

对于使用插件认证连接的用户，服务器调用指定名称的插件，客户端需要提供验证方法所需要的凭据。如果创建用户时或者连接服务器时，服务器找不到对应的插件，将返回一个错误，IDENTIFIED WITH语法格式如下：

CREATE USER 'jeffrey'@'localhost' IDENTIFIED WITH my_auth_lugin;

提示：IDENTIFIED WITH 只能在MySQL 5.5.7及以上版本中使用。IDENTIFIED BY 和IDENTIFIED WITH 是互斥的，所以对于一个账户来说只能使用一个验证方法。CREATEUSER语句的操作会被记录到服务器日志文件或者操作历史文件中,如~/.MySQL_history 。这意味着对这些文件有读取权限的人，都可以读取新添加用户的明文密码。

MySQL的某些版本中会引入授权表的结构变化，添加新的特权或功能。每当更新MySQL到一个新的版本时，应该更新授权表，以确保它们有最新的结构，确认可以使用任何新功能。

2，使用GRANT语句创建新用户

CREATE USER语句可以用来创建账户，通过该语句可以在user表中添加一条新的记录，但是CREATE USER语句创建的新用户没有任何权限，还需要使用GRANT 语句赋予用户权限。而GRANT语句不仅可以创建新用户，还可以在创建的同时对用户授权。GRANT还可以指定账户的其他特点，如使用安全连接、限制使用服务器资源等。使用GRANT语句创建新用户时必须有GRANT权限。GRANT 语句是添加新用户并授权他们访问 MySQL对象的首选方法,GRANT语句的基本语法格式如下：

GRANT privileges ON db.table
TO user@host [IDENTIFIED BY 'password'] [,user [IDENTIFIED BY 'password ']
[WITH GRANT OPTION]

其中，privileges 表示赋予用户的权限类型;db.table表示用户的权限所作用的数据库中的表；IDENTIFIED BY关键字用来设置密码；’password’表示用户密码；WITH GRANT OPTION为可选参数，表示对新建立的用户赋予GRANT权限，即该用户可以对其他用户赋予权限。

【例4】使用GRANT语句创建一个新的用户testUser，密码为testpwd，并授于用户对所有数据表的SELECT 和 UPDATE权限。GRANT语句及其执行结果如下：

GRANT SELECT, UPDATE ON *.* TO 'testUser'@'localhost'
IDENTIFIED BY 'testpwd';

执行结果显示执行成功，使用SELECT语句查询用户testUser的权限：

SELECT Host, User, Select_priv, Update_priv FROM MySQL.user
where user = 'testUser';

查询结果显示用户 testUser 被创建成功，其SELECT 和 UPDATE权限字段值均为‘Y’。

User表中的user 和 host字段区分大小写，在查询的时候要指定正确的用户名称或者主机名。

3，直接操作MySQL用户表

通过前面的介绍，不管是CREATE USER或者GRANT，在创建新用户时，实际上都是在user表中添加一条新的记录。因此，可以使用INSERT 语句向user 表中直接插入一条记录来创建一个新的用户。使用INSERT 语句，必须拥有对MySQL.user 表的INSERT权限。使用INSERT语句创建新用户的基本语法格式如下：

INSERT INTO MySQL.user(Host,User,Password, [privilegelist1])
VALUES('host', 'username', PASSWORD('password'), privilegevaluelist);

Host、User、Password分别为user表中的主机、用户名称和密码字段；privilegelist 表示用户的权限，可以有多个权限；PASSWORD()函数为密码加密函数；privilegevaluelist为对应的权限的值，只能取‘Y’或者‘N’。

【例5】使用INSERT创建一个新账户，其用户名称为customerl，主机名称为localhost，密码为mypass，INSERT语句如下：

INSERT INTO user (Host, User, Password)
VALUES('localhost', 'customer1', PASSWORD('mypass'));

语句执行结果如下：

ERROR 1054 (42S22): Unknown column ‘Password’ in ‘field list’。并没有password列

查看user的表结构：

DESC user;

再查看user的数据：

select * from user \G;

看到 authentication_string 这个字段是存密码的：

修改上面的插入语句：

INSERT INTO user (Host, User, authentication_string)
VALUES('localhost', 'customer1', PASSWORD('mypass'));

ERROR 1364 (HY000): Field ‘ssl_cipher’ doesn’t have a default value 语句执行失败，查看警告信息如下：

SHOW WARNINGS;

因为ssl_cipher、x509_issuer和x509_subject 3个字段在user表定义中没有设置默认值，所以在这里提示错误信息。影响INSERT语句的执行，使用SELECT 语句查看user表中的记录：

SELECT host, user, authentication_string FROM user;

可以看到新用户 customerl并没有添加到user表中，表示添加新用户失败。

3，删除普通用户

在MySQL 数据库中，可以使用DROP USER 语句删除用户，也可以直接通过DELETE语句从MySQL.user表中删除对应的记录来删除用户。

#

1，使用DROP USER语句删除用户

DROP USER语句语法如下：

DROP USER user [,user];

DROP USER语句用于删除一个或多个MySQL 账户。要使用DROP USER，必须拥有MySQL数据库的全局CREATE USER权限或DELETE 权限。使用与GRANT或REVOKE相同的格式为每个账户命名;例如，”jeffrey’@’localhost’账户名称的用户和主机部分与用户表记录的User和 Host列值相对应。

使用DROP USER，可以删除一个账户和其权限，操作如下：

DROP USER 'user'@'localhost';
DROP USER;

第1条语句可以删除user 在本地登录权限;

第⒉条语句可以删除来自所有授权表的账户权限记录。

【例6】使用DROP USER删除账户”jeffrey’@’localhost’，DROP USER语句如下：

DROP USER 'jeffrey'@'localhost';

执行过程如下：t

可以看到语句执行成功,查看执行结果：

SELECT host, user, authentication_string FROM user;

user表中已经没有名称为jeffrey，主机名为localhost 的账户，即”jeffrey’@”localhost的用户版号已经被删除。

DROP USER不能自动关闭任何打开的用户对话。而且，如果用户有打开的对话，此时取消用户，命令则不会生效，直到用户对话被关闭后才能生效。一旦对话被关闭，用户也被取消，此用户再次试图登录时将会失败。

#

2，使用DELETE语句删除用户

DELETE语句基本语法格式如下：

DELETE PROM MySQL.user WHERE  host = 'hostname' and user = 'username';

host和 user 为 user表中的两个字段，两个字段的组合确定所要删除的账户记录。

【例7】使用DELETE删除用户’customer1’@’localhost’，DELETE 语句如下：

-- 删除前，先创建
CREATE USER 'customer1'@'localhost' IDENTIFIED BY 'mypass';
DELETE FROM MysQL.user 
WHERE  host = 'localhost' and  user = 'customer1';

执行结果如下：

可以看到语句执行成功，’customer1’@”localhost’的用户账号已经被删除。可以使用SELECT语句查询user表中的记录，确认删除操作是否成功。

4，root 用户修改自己的密码

root用户的安全对于保证MySQL的安全非常重要，因为root用户拥有很高的权限。修改root用户密码的方式有多种，本小节将介绍几种常用的修改root用户密码的方法。

1，使用MySQLadmin命令在命令行指定新密码

MySQLadmin命令的基本语法格式如下：

mysqladmin -u username -h localhost -p password "newpwd"

username为要修改密码的用户名称，在这里指定为root 用户；参数-h指需要修改的、对应哪个主机用户的密码，该参数可以不写，默认是localhost；-p表示输入当前密码; password为关键字，后面双引号内的内容“newpwd”为新设置的密码。执行完上面的语句，root用户的密码将被修改为newpwd。

【例8】使用MySQLadmin将root 用户的密码修改为“rootpwd”，在 Windows的命令行窗口中执行命令如下：

mysqladmin -u root -p password "rootpwd"

按照要求输入root用户原来的密码，执行完毕后，新的密码将被设定。root 用户登录时将使用新的密码。

#

2，修改MySQL数据库的user表

因为所有账户信息都保存在user表中，因此可以通过直接修改user表来改变root用户的密码。root用户登录到 MySQL服务器后，使用UPDATE语句修改MySQL数据库的user 表的password字段，从而修改用户的密码。使用UPDATA语句修改root用户密码的语句如下：

UPDATE mysql.user set authentication_string = PASSWORD("rootpwd1")
WHERE User="root" and  Host="localhost";

PASSWORD()函数用来加密用户密码。执行UPDATE语句后，需要执行FLUSHPRIVILEGES语句重新加载用户权限。

【例9】使用UPDATE语句将root用户的密码修改为“rootpwd2”。使用root用户登录到MySQL服务器后，执行如下语句;

UPDATE mysql.user set authentication_string = PASSWORD("rootpwd2")
WHERE User="root" and  Host="localhost";
FLUSH PRIVILEGES;

执行完UPDATE语句后，root 的密码被修改成了rootpwd2。使用FLUSH PRIVILEGES语句重新加载权限,就可以使用新的密码登录root用户了。 行完UPDATE 语句后，root 的密码被修改成了rootpwd2。使用FLUSH PRIVILEGES语句重新加载权限，就可以使用新的密码登录root 用户了。

#

3，使用SET语句修改root 用户的密码

SET PASSWORD语句可以用来重新设置其他用户的登录密码或者自己使用的账户的密码。使用SET语句修改自身密码的语法结构如下：

SET PASSWORD = PASSWORD("rootpwd");

新密码必须使用PASSWORDO函数加密。

【例10】使用SET语句将root用户的密码修改为“rootpwd3””。使用root用户登录到MySQL服务器后，执行如下语句;

SET PASSWORD = PASSWORD("rootpwd3");

SET语句执行成功，root用户的密码被成功设置为rootpwd3。为了使更改生效，需要重新启动MySQL或者使用FLUSH PRIVILEGES;语句刷新权限，重新加载权限表。

5，root 用户修改普通用户的密码

root 用户拥有很高的权限，不仅可以修改自己的密码，还可以修改其他用户的密码。root用户登录MySQL 服务器后，可以通过SET语句修改MySQL.user表，以及GRANT 语句修改用户的密码。

1，使用SET语句修改普通用户的密码

使用SET语句修改普通用户密码的语法格式如下：

SET PASSWORD FOR 'user'@'host' = PASSWORD('somepassword');

只有root可以通过更新MySQL数据库的用户来更改其他用户的密码。如果使用普通用户修改，可省略FOR子句更改自己的密码：

SET PASSWORD = PASSWORD('somepassword');

【例11】使用SET语句将testUser用户的密码修改为“newpwd”。

使用root用户登录到MySQL服务器后，执行如下语句：

SET PASSWORD FOR 'testUser'@'localhost'=PASSWORD("newpwd");

SET语句执行成功，testUser 用户的密码被成功设置为newpwd。

2，使用UPDATE语句修改普通用户的密码

使用root用户登录到MySQL服务器后，可以使用UPDATE语句修改MySQL数据库的user表的password字段，从而修改普通用户的密码。使用UPDATA语句修改用户密码的语法如下：

UPDATE MySQL.user SET authentication_string = PASSWORD("pwd")
WHERE Uuser="username" AND Host="hostname";

PASSWORD(函数用来加密用户密码。执行UPDATE语句后，需要执行FLUSH PRIVILEGES语句重新加载用户权限。

[例12]使用UPDATE语句将testUser用户的密码修改为“newpwd2”。

使用root用户登录到MySQL服务器后，执行如下语句：

UPDATE MySQL.user SET  authentication_string = PASSWORD("newpwd2")
WHERE User = "testUser" AND  Host = "localhost";
FLUSH PRIVILEGES;

执行完UPDATE语句后 testUser的密码被修改成了newpwd2。使用FLUSH PRIVILEGES重新加载权限，就可以使用新的密码登录testUser用户了。

PS：如果是直接修改表格的，没有password字段，使用 authentication_string

3，使用GRANT语句修改普通用户密码

除了前面介绍的方法，还可以在全局级别使用GRANT USAGE 语句(*.*)指定某个账户的密码而不影响账户当前的权限，使用GRANT语句修改密码，必须拥有GRANT权限。一般情况下最好使用该方法来指定或修改密码：

GRANT USAGE ON *.* TO 'someuser'@'%' IDENTIEIED BY 'somepassword' ;

【例13】使用GRANT语句将testUser用户的密码修改为“newpwd3”。

使用root用户登录到MySQL服务器后,执行如下语句：

GRANT USAGE ON *.* TO 'testUser'@'%' IDENTIEIED BY 'newpwd3';

执行完GRANT语句后，testUser的密码被修改成了newpwd3。可以使用新密码登录MySQL服务器。

如果使用GRANT …IDENTIFIED BY语句或MySQLadmin password命令设置密码，它们均会加密密码。在这种情况下，不需要使用PASSWORD()函数。

6，普通用户修改密码

普通用户登录MySQL服务器后，通过SET语句设置自己的密码。SET语句修改自己密码的基本语法如下：

SET PASSWORD = PASSWORD("newpassword");

其中，PASSWORD()函数对密码进行加密，“newpassword”是设置的新密码。

【例14】testUser用户使用SET语句将自身的密码修改为“newpwd4”：

使用testUser 用户登录到MySQL服务器后，执行如下语句：

SET PASSWORD = PASSWORD ("newpwd4");

SET语句执行成功，testUser 用户的密码被成功设置为newpwd4。可以使用新密码登录MySQL服务器。

7，root用户密码丢失的解决办法

对于root用户密码丢失这种特殊情况，MySQL实现了对应的处理机制。可以通过特殊方法登录到MySQL服务器，然后在root用户下重新设置密码。执行步骤如下：

#

1，使用—skip-grant-tables选项启动MySQL服务

以 skip-grant-tables选项启动时，MySQL服务器将不加载权限判断，任何用户都能访问数据库。在 Windows操作系统中，可以使用MySQLd或 MySQLd-nt 来启动MySQL服务进程。如果 MySQL 的目录已经添加到环境变量中，可以直接使用MySQLd、MySQLd-nt命令启动MySQL服务。否则需要先在命令行下切换到MySQL的 bin目录。

MySQLd命令如下：

mysqld --skip-grant-tables

MySQLd-nt命令如下：

mysqld-nt --skip-grant-tables

在 Linux操作系统中，使用MySQLd_safe来启动MySQL服务。也可以使用/etc/init.d/MySQL命令来启动MySQL服务。

MySQLd-safe命令如下：

mysqld_safe --skip-grant-tables user-mysql

/etc/init.d/MySQL命令如下：

/etc/init.d/mysql start-mysqld --skip-grant-tables

启动MySQL服务后，就可以使用root用户登录了。

2，使用root用户登录，重新设置密码

在这里使用的平台为Windows 7，操作步骤如下：

步骤1：使用net stop MySQL命令停止MySQL服务进程。

C： \ >net stop

MySQLMySQL服务正在停止。

MySQL服务已成功停止。

步骤2：在命令行输入MySQLd —skip-grant-tables 选项启动MySQL服务。

C：\> mysqld --skip-grant-tables

命令运行之后，用户无法输入指令，此时如果在任务管理器中可以看到名称为MySQLd的进程，则表示可以使用root 用户登录MySQL了。

步骤3：打开另外一个命令行窗口，输入不加密码的登录命令。

C：\> mysql -u root

登录成功以后，可以使用UPDATE语句或者使用MySQLadmin命令重新设置root密码，设置密码语句如下：

UPDATE mysql.user SET authentication_string =PASSWORD('newpwd') 
WHERE User='root' and Host = 'localhost';

设置root密码的方法参考“root用户修改自己的密码”。

3，加载权限表

修改密码完成后，必须使用FLUSH PRIVILEGES语句加载权限表。加载权限表后，新的密码才会生效，同时MySQL服务器开始权限验证。输入语句如下：

FLUSH PRIVILEGES;

修改密码完成后，将输入MySQLd —skip-grant-tables 命令的命令行窗口关闭，接下来就可以使用新设置的密码登录MySQL了。

8，总结

用户管理相对常用些，尤其是创建用户后，进行修改密码，root修改密码或是忘记密码后处理。工作中这块基本都是数据库dba去操作。

上一篇：《mysql 用户管理-权限表》

下一篇：《mysql 用户管理-权限管理》