MySQL新建和删除普通用户

要创建新用户，必须有相应的权限来执行创建操作。在MySQL数据库中，有两种方式创建新用户：一种是使用CREATE USER语句；另一种是直接操作MySQL授权表。下面分别介绍这两种创建新用户的方法。

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

【例13.3】使用CREATE USER语句创建一个用户，用户名是jeffrey，密码是mypass，主机名是localhost，SQL语句如下：

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服务器使用内建的身份验证机制，用户登录时必须指定密码。

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

2. 直接操作MySQL用户表

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

INSERT INTO MySQL.user(Host, User, authentication_string)

VALUES('host', 'username', MD5('password'));

Host、User、authentication_string分别为user表中的主机、用户名称和密码字段；MD5()函数为密码加密函数。

【例13.4】使用INSERT创建一个新用户，用户名称为customer1，主机名称为localhost，密码为aa123456，SQL语句如下：

INSERT INTO user (Host,User, authentication_string)
VALUES('localhost','customer1', MD5('aa123456'));

语句执行结果如下：

MySQL> use mysql;

MySQL> INSERT INTO user (Host,User, authentication_string)

    -> VALUES('localhost','customer1', MD5('aa123456'));

Query OK, 1 row affected, 3 warnings (0.00 sec)

语句执行成功，但是有3个警告信息，查看警告信息，结果如下：

MySQL> SHOW WARNINGS;
+-------+------+---------------------------------------------------+
| Level | Code | Message                                                    |
+-------+------+---------------------------------------------------+
| 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 |
+-------+------+---------------------------------------------------+

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

MySQL> SELECT host,user, authentication_string FROM user ;
+---------+-----------------+---------------------------------------------+
| host    | user            |authentication_string                        |
+---------+-----------------+---------------------------------------------+
|localhost| customer1       | 8a6f2805b4515ac12058e79e66539be9            |
|localhost|mysql.infoschema |$A$005$THISISACOMBINATIONOFIVALIDSALTANDP    | 
|localhost|mysql.session    |$A$005$THISISACOMBINATIONOFINVALIDSALTANDPAS |
|localhost|mysql.sys        |$A$005$THISISACOMBINATIONOFINVAUSTNEVERREUSED|
|localhost|root             |*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9    |
+---------+-----------------+---------------------------------------------+

可以看到新用户customer1已经添加到user表中，表示添加新用户成功。

在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;

第一条语句可以删除user在本地的登录权限；第二条语句可以删除所有来自授权表的账户权限记录。

【例13.5】使用DROP USER删除账户“'jeffrey'@'localhost'”，SQL语句如下：

DROP USER 'jeffrey'@'localhost';

执行过程如下：

MySQL> DROP USER 'jeffrey'@'localhost';

Query OK, 0 rows affected (0.00 sec)

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

MySQL> SELECT host,user, authentication_string FROM user ;
+---------+----------------+---------------------------------------------+
| host    | user           |authentication_string                        |
+---------+----------------+---------------------------------------------+
|localhost|mysql.infoschema|$A$005$THISISACOMBINATIONOFIVALIDSALTANDP    | 
|localhost|mysql.session   |$A$005$THISISACOMBINATIONOFINVALIDSALTANDPAS |
|localhost|mysql.sys       |$A$005$THISISACOMBINATIONOFINVAUSTNEVERREUSED|
|localhost|root            |*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9    |
+---------+----------------+---------------------------------------------+

user表中已经没有名称为jeffrey、主机名为localhost的账户，即“'jeffrey' @ 'localhost'”的账户已经被删除。

2. 使用DELETE语句删除用户

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

DELETE FROM MySQL.user WHERE Host='hostname' and User='username'

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

【例13.6】使用DELETE删除用户'customer1'@'localhost'。

首先创建用户customer1，SQK语句如下：

MySQL>CREATE USER 'customer1'@'localhost' IDENTIFIED BY 'my123';

Query OK, 0 rows affected (0.12 sec)

然后使用DELETE删除用户'customer1'@'localhost'，SQK语句如下：

mysql> DELETE FROM MySQL.user WHERE Host= 'localhost' and User='customer1';

Query OK, 1 row affected (0.01 sec)

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