MySQL——安全性机制之账户管理

一.   登录和退出  MySQL 服务器

    mysql 命令的常用参数：

参数	描述
-h，- -host=name	指定主机名或ip，默认是localhost
-u，- -user=name	指定用户名
-p（小写），- -password[=pwd]	指定登录密码。如果该字段后面有一段字符，则该段字符串将作为密码直接登录。如果后面没有内容，则登录时会提示输入密码。注意该参数后面的字符串和-p之间不能有空格
-P（大写），- -port=#	指定 mysql 服务器的端口号，默认为3306
-V（大写），- -version	输出版本信息并且退出
无从参数数据库名	可以在命令的最后指定数据库名
-e	指定了该参数，将在登录后执行-e后面的命令或sql语句并退出

1.  登录MySQL 服务器。

【例】 使用  root 用户登录本地  MySQL  服务器  test_db  库。

    命令执行如下：

C:\> mysql  -h  localhost  -u  root  -p  test_db
Enter password: *************
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.21-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

    【例】 使用  root用户登录到本地  MySQL  服务器的  test_db  数据库，同时执行一条查询语句。

    命令如下：

C:\>mysql -h  localhost  -u  root  -p  test_db  -e "DESC fruits;"
Enter password: *************
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| f_id    | char(10)     | NO   | PRI | NULL    |       |
| s_id    | int(11)      | NO   |     | NULL    |       |
| f_name  | char(255)    | NO   |     | NULL    |       |
| f_price | decimal(8,2) | NO   |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+

C:\>

    查询返回之后自动退出  MySQL 服务器。

2.  退出MySQL 服务器。

    输入  quit  或  exit  即可。

【例】退出MySQL 服务器

mysql> exit;
Bye

C:\>

 二.   新建普通用户

    创建新用户，必须要有相应的权限来执行创建操作。在  MySQL  数据库中，有两种方式创建新用户，一种是使用 CREATE USER 或  GRANT 语句；另一种是直接操作  MySOL 授权表。最好的方法是使用  GRANT  语句，因为这样更精确、错误少。

    1.   使用  CREAT  EUSER  语句创建新用户

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

    【例】 使用  CREATE USER  创建一个用户， 用户名是  zth  , 密码是  zthfbb  ,主机名是  localhost。语句如下:

mysql> CREATE  USER   'zth'   @'localhost'  IDENTIFIED  BY  'zthfbb';
Query OK, 0 rows affected (0.15 sec)

•     如果只指定用户名部分，主机名部分则默认为％（即对所有的主机开发权限）。 
• 　如果指定用户登录不需要密码，可以省略IDENTIFIED BY部分。

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

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

mysql> SELECT password('zth');
+-------------------------------------------+
| password('zth')                           |
+-------------------------------------------+
| *5B0FFF997FE144F3E17C50F8EF91AC634DEFBF86 |
+-------------------------------------------+

    *5B0FFF997FE144F3E17C50F8EF91AC634DEFBF86  就是  zth  的哈希值。

【例】 使用  CREATE USER  创建一个用户， 用户名是  zth  , 密码是  zth  的哈希值  ,主机名是  localhost。语句如下:

mysql> CREATE  USER   'zth'   @'localhost'  IDENTIFIED  BY  PASSWORD  '*5B0FFF997FE144F3E17C50F8EF91AC634DEFBF86';
Query OK, 0 rows affected, 1 warning (0.00 sec)

    用户  zth  的密码将被设定为  zth 。

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

    IDENTIFIED BY  和  IDENTIFIED WITH  是互斥的，所以对于一个账户来说只能使用一个验证方法。

    CREATE USER  语句的操作会被记录到服务器日志文件或者操作历史文件中。这意味着对这些文件有读取权限的人，都可以读取新添加用户的明文密码。

2.   使用  GRANT  语向创建新用户

    用  CREATE USER  语句可以在  user  表中添加一条新的记录， 但是用  CREATE USER  语句创建的新用户没有任何权限，

还需要用   GRANT  语句赋予用户权限。而  GRANT  语句不仅可以创建新用户，还可以在创建的同时对用户授权。GRANT 还可以指定账户的其他特点，如使用安全连接、限制使用服务器资源等。使用  GRANT 语句创建新用户时必须有  GRANT  权限。GRANT  语句是添加新用户并授权他们访问  MySOL 对象的首选方法。

    GRANT 语句的基本语法格式如下：

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

• privileges：表示赋予用户的权限类型
• db.table：用户权限所在的数据库中的表
• WITH GRANT OPTION：可选，表示对新建立的用户赋予  GRANT  权限，即该用户可以对其他用户赋予权限。

【例】用  GRANT  语句创建个新的用户 fbb ,密码为  fbb, 并授予用户对  所有数据表的  SELECT  和  UPDATE  权限。GRANT语句及其执行结果如下:

mysql> GRANT  SELECT,UPDATE  ON  *.*  TO  'fbb' @'localhost'
    ->      IDENTIFIED  BY  'fbb';
Query OK, 0 rows affected, 1 warning (0.00 sec)

使用  SELECT  语句查询用户  fbb 的权限:

mysql>  SELECT   Host, User, Select_priv , Update_priv FROM mysql.user where  user='fbb';
+-----------+------+-------------+-------------+
| Host      | User | Select_priv | Update_priv |
+-----------+------+-------------+-------------+
| localhost | fbb  | Y           | Y           |
+-----------+------+-------------+-------------+

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

   3.  直接操作MySQL用户表

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

    INSERT   INTO   mysql.user(  Host,  User,  authentication_string  ,  [  privilegelist  ]  )
    VALUES  (  'host',  'username',  PASSWORD('password'),  privilegevaluelist  );

•     privilegelist  表示用户的权限，可以有多个权限；
•     PASSWORD() 函数为密码加密函数；
•     privilegevaluelist  为对应的权限的值，只能取值Y或N。

【例】 使用  INSERT  创建一个新账户，用户名为  customer1 ，主机名为 localhost，密码为  customer1 。

mysql> INSERT  INTO  user(  Host,  User,  authentication_string)
    -> VALUES(  'localhost','customer1',PASSWORD('customer1'));
ERROR 1364 (HY000): Field 'ssl_cipher' doesn't have a default value

语句执行失败，查看错误信息：

mysql> show WARNINGS;
+---------+------+-------------------------------------------------------------------+
| Level   | Code | Message                                                           |
+---------+------+-------------------------------------------------------------------+
| Warning | 1681 | 'PASSWORD' is deprecated and will be removed in a future release. |
| Error   | 1364 | Field 'ssl_cipher' doesn't have a default value                   |
| Error   | 1364 | Field 'x509_issuer' doesn't have a default value                  |
| Error   | 1364 | Field 'x509_subject' doesn't have a default value                 |
+---------+------+-------------------------------------------------------------------+

    重新加载授权表。  

     FLUSH   PRIVILEGES;

  因为上面三个字段在  user  表定义中没有默认值。使用  SELECT  语句查看  user 表中的记录：

mysql> SELECT  host ,user, authentication_string  FROM  user;
+-----------+---------------+-------------------------------------------+
| host      | user          | authentication_string                     |
+-----------+---------------+-------------------------------------------+
| localhost | root          | *87F94C1C734EA39689DE0B7178987B6B4675D5E6 |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | zth           | *5B0FFF997FE144F3E17C50F8EF91AC634DEFBF86 |
| loacahost | fbb           | *8E9930B2EE58996B9FEDC5B07765F1730E17426D |
+-----------+---------------+-------------------------------------------+

可看出添加没有成功。

三.   删除普通用户

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

    语法格式如下：

    DROP   USER   user  [ ,  user  ] ;

    要使用  DROP USER  语句，必须拥有  MySQL  数据库的全局  CREATE USER  权限或  DELETE  权限。

    使用  DROP USER  删除一个账户及其权限的操作如下:

    DROP   USER   'user'@'localhost' ;

    DROP USER;

    第 1 条语句可以删除  user 在本地登录的权限；第2条语句可以删除所有授权表中的该账户权限记录。

    【例】使用  DROP USER  在  localhost  主机上删除  fbb  账户。SQL语句如下:

mysql> DROP  USER  'fbb'@'localhost';
Query OK, 0 rows affected (0.00 sec)

    查看执行结果：

mysql> SELECT  host ,user, authentication_string  FROM  user;
+-----------+---------------+-------------------------------------------+
| host      | user          | authentication_string                     |
+-----------+---------------+-------------------------------------------+
| localhost | root          | *87F94C1C734EA39689DE0B7178987B6B4675D5E6 |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | zth           | *5B0FFF997FE144F3E17C50F8EF91AC634DEFBF86 |
+-----------+---------------+-------------------------------------------+

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

    2.   使用  DELETE  语句删除用户

    语法格式如下:

    DELETE   FROM   MYSQL.user    WHERE   host='hostname'   and   user='username';

    【例】使用  DELETE  在  localhost  主机上删除  zth  账户。SQL  语句如下：

mysql> DELETE  FROM  MySQL.user  WHERE  host='localhost'  AND user='zth';
Query OK, 1 row affected (0.09 sec)

    查看执行结果：

mysql> SELECT  host ,user, authentication_string  FROM  user;
+-----------+---------------+-------------------------------------------+
| host      | user          | authentication_string                     |
+-----------+---------------+-------------------------------------------+
| localhost | root          | *87F94C1C734EA39689DE0B7178987B6B4675D5E6 |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+-----------+---------------+-------------------------------------------+

四.   root  用户修改自己的密码

    1.  使用  mysqladmin  命今在命令行指定新密码

    语法格式如下：

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

•     usemame  为要修改密码的用户名称，在这里指定为  root  用户;
•     参数  -h  指需要修改的服务器地址，该参数可以不写，默认是  localhost  ;
•     -P  表示输入当前密码;
•     pssword 为关键字，后面双引号内的内容  newpwd 为新设置的密码。

    【例】使用  mysqladmin  将  root  用户的密码修改为  1530738736。在  Windows 的命令行窗口中执行如下命令：

C:\>mysqladmin  -u  root  -h  localhost  -p  password  "1530738736"
Enter password: *************
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

2.   修改  mysql  数据库的  user  表

    因为所有账户信息都保存在  user  表中，因此可以通过直接修改  user  表来改变  root  用户的密码。

    语法格式如下：

    UPDATE   myslq.user   SET  authentication_string= PASSWORD("rootpwd")   WHERE User="root"   AND   Host="localhost";

    PASSWORD()  函数用来加密用户需码。

    执行  UPDATE  语句后，需要执行   FLUSH   PRIVILEGES   语句重新加载用户权限。  

    【例】  使用  UPDATE  语句将 root 用户的密码修改为  zth1530738736。SQL语句如下：

mysql> UPDATE mysql.user  SET  authentication_string = PASSWORD("zth1530738736")
    -> WHERE  User="root"  AND  Host="localhost";
Query OK, 1 row affected, 1 warning (0.10 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> FLUSH  PRIVILEGES;
Query OK, 0 rows affected (0.18 sec)

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

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

    SET   PASSWORD  =  PASSWORD("rootpwd");

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

    【例】  使用  UPDATE  语句将 root 用户的密码修改为  zth1530738736。SQL语句如下：

mysql> SET  PASSWORD=password("zth1530738736");
Query OK, 0 rows affected, 1 warning (0.02 sec)

    为了使更改生效，需要重新启动  MySQL 或者使用  FLUSH PRIVILEGES  语句刷新权限，重新加载权限表。

mysql> FLUSH  PRIVILEGES;
Query OK, 0 rows affected (0.02 sec)

五.   root  用户修改普通用户密码

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

 1.   使用  SET  语向修改普通用户的密码

    语法格式如下：

    SET   PASSWORD   FOR 'user'@'host'=PASSWORD('新的密码');

    【例】使用  SET 语句将  zth  用户的密码修改为   zth，使用  root  用户登录  MySQL 服务器后，执行如下语句:

mysql> SET  PASSWORD  FOR  'zth'@'localhost'  =PASSWORD('zth');
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

    语法格式如下：

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

【例】使用  SET 语句将  zth  用户的密码修改为   zthfbb，使用  root  用户登录  MySQL 服务器后，执行如下语句:

mysql> UPDATE  MySQL.user  SET  authentication_string=PASSWORD("zthfbb")
    -> WHERE  user="zth"  AND  Host="localhost";
Query OK, 1 row affected, 1 warning (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 1

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

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

    语法格式如下：

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

【例】使用  GRANT  语句将  zth  用户的密码修改为   zth，使用  root  用户登录  MySQL 服务器后，执行如下语句:

mysql> GRANT  USAGE  ON  *.*  TO  'zth'@'localhost'  IDENTIFIED  BY  'zth';
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

六.   普通用户修改密码

    语法格式：

    SET   PASSWORD =  PASSWORD("新的密码");

    【例】  zth  用户使用  SET  语句将自身的密码修改为  zthfbb。使用   zth  用户登录  MySQL 服务器后，执行如下语句:

mysql> SET  PASSWORD = PASSWORD("zthfbb");
Query OK, 0 rows affected, 1 warning (0.11 sec)

七.   root 用户密码丢失的解决办法

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

　　以  skip-grant-tables  选项启动时，MySQL 服务器将不加载权限判断，任何用户都能访问数据库。 

　　（1）windows系统下

     在  Windows  操作系统中，可以使用  mysqld  或  mysqld-nt   来启动  MySQL  服务进程。如果  MySQL  的目录已经添加到环境变量中，可以直接使用  mysqld、 mysqld-nt 命令启动MySQL服务，否则需要先在命令行切换到  MySOL 的bin目录。

    mysqld命令如下：

    mysqld   --skip-grant-tables

    mysqld-nt命令如下:

    mysqld-nt    --skip-grant-tables

　　（2）Linux系统下

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

    mysqld-safe  命令如下:

    mysqld_safe   --skip-grant-tables user=mysqld

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

    /ect/init.d/mysqld    start-mysqld   --skip-grant-tables

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

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

    【注】以  windows  为例 

（1）使用  net stop mysql  命令停止  mysql  服务进程。（需要管理员身份打开命令行窗口）

C:\ > net stop mysql
MySQL 服务正在停止.
MySQL 服务已成功停止。

（2）使用  mysqld   - -skip-grant-tables  启动服务器

C:\>mysqld --skip-grant-tables
2016-02-28 13:09:40 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. 
Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-02-28 13:09:40 0 [Note] mysqld (mysqld 5.6.24) starting as process 8864 ...

• 
  

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

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

C:\>mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.24 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

• 
  

（4）登录成功后，可以使用  UPDATE  语句或  mysqladmin  命令重新设置root密码

    输入如下语句：

     UPDATE   myslq.user   SET  authentication_string= PASSWORD("rootpwd")   WHERE User="root"   AND   Host="localhost";

3.加载权限表

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

     FLUSH   PRIVILEGBS;

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

    【注】参考于清华大学出版社《MySQL数据库应用案例课堂》2016年1月第1版