MySQL创建用户的3种方法（非常详细，附带实例）

MySQL/MariaDB 数据库中提供了 3 种创建用户的方法：

使用 CREATE USER 语句创建用户；
使用 GRANT 语句创建用户；
在 mysql.user 表中添加用户（不推荐）。

先看第一种方式，通过 CREATE USER 语句直接创建数据库用户，其语法格式为：

CREATE USER ‘username’@‘hostname’ IDENTIFIED BY‘密码’；

各字段的含义如下：

CREATE USER：关键字，创建用户。
‘username’：用户名。
@：分隔符，固定不变。
‘host’：表示主机名称，用来做主机限制，即允许用户在什么地方登录。这里可以写localhost（本地）、ip地址或%（任何地方）。
IDENTIFIED BY：用于指定用户密码，可省略但不建议。
‘密码’：用户密码。

【实例】创建单个用户和一次性创建多个用户。

MariaDB [mysql]>
create user "xiaosun"@"localhost" identified by "qwer1234";
Query OK, 0 rows affected (0.126 sec)

## 查看数据库中有哪些用户
MariaDB [mysql]>
select user,host,password from user;
+-------------+-----------+--------------------------------------------+
| User        | Host      | Password                                   |
+-------------+-----------+--------------------------------------------+
| mariadb.sys | localhost |                                            |
| root        | localhost | *2491CA5000A9614AA28C39036702D965584486EC  |
| mysql       | localhost | invalid                                    |
| xiaosun     | localhost | *D75CC763C5551AA420D28A227AC294FADE26A2FF2 |
+-------------+-----------+--------------------------------------------+
6 rows in set (0.001 sec)

## 查看已经授权给用户的权限信息（USAGE 表示该用户对数据库没有任何权限）
MariaDB [mysql]> show grants for 'xiaosun'@'localhost';
+--------------------------------------------------------------------------+
| Grants for xiaosun@localhost                                             |
+--------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'xiaosun'@'localhost' IDENTIFIED BY PASSWORD 'xxx' |
+--------------------------------------------------------------------------+
1 row in set (0.000 sec)

## 一次性创建多个用户，用户与用户之间用英文逗号进行分隔
MariaDB [mysql]>
create user "xiaosun1"@"localhost" identified by "qwer1234",
    -> "xiaosun2"@"localhost" identified by "qwer1234",
    -> "xiaosun3"@"localhost" identified by "qwer1234",
    -> "xiaosun4"@"localhost" identified by "qwer1234";
Query OK, 0 rows affected (0.002 sec)

MariaDB [mysql]>
select user,host,password from user;
+-------------+-----------+--------------------------------------------+
| User        | Host      | Password                                   |
+-------------+-----------+--------------------------------------------+
| mariadb.sys | localhost | *2491CA5000A9614AA28C39036702D965584486EC  |
| root        | localhost | invalid                                    |
| mysql       | localhost | invalid                                    |
| xiaosun     | localhost | *D75CC763C5551AA420D28A227AC294FADE26A2FF2 |
| xiaosun1    | localhost | *D75CC763C5551AA420D28A227AC294FADE26A2FF2 |
| xiaosun2    | localhost | *D75CC763C5551AA420D28A227AC294FADE26A2FF2 |
| xiaosun3    | localhost | *D75CC763C5551AA420D28A227AC294FADE26A2FF2 |
| xiaosun4    | localhost | *D75CC763C5551AA420D28A227AC294FADE26A2FF2 |
+-------------+-----------+--------------------------------------------+
11 rows in set (0.001 sec)

可以看到存储在数据库中的用户密码是经过哈希值加密的。

通过 CREATE USER 语句虽然可以一次性创建多个用户，但是没办法同时配置用户权限，每次新创建的用户拥有的权限都很少，它们只能执行一些不需要权限的操作。这样就导致创建用户之后还得手动配置用户权限。

鉴于 CREATE USER 语句不能在创建用户的同时配置权限，MySQL/MariaDB 数据库又提供了 GRANT 语句。

MySQL GRANT语句

使用 GRANT 语句创建用户的语法格式为：

GRANT 权限 ON 数据库.表 TO ‘username’@‘hostname’ IDENTIFIED BY‘密码’；

各字段的含义如下：

GRANT：创建用户/赋予权限的关键字。
权限：新用户所具备的权限。可以写多个权限，权限与权限之间用英文逗号进行分隔；若想创建管理员用户，可以在这里写“ALL”，表示具备所有权限。
ON：关键字。
数据库.表：新用户的权限范围，即只能在指定的数据库和表上使用赋予的权限。其中“*.*”表示所有数据库下的所有表。
TO：关键字。
username’@‘hostname’ IDENTIFIED BY‘密码’：与CREATE USER语句中的含义一样。

创建用户之后最好通过 FLUSH PRIVILEGES 命令刷新一下权限表，重新读取用户信息。

【实例】通过 GRANT 语句创建用户并赋予权限。

MariaDB [mysql]>
show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test01             |
+--------------------+
5 rows in set (0.001 sec)

## 新建 xiaoliu1 用户，并使此用户对 test01 数据库拥有 select、create、alter、delete 权限
MariaDB [mysql]>
grant select,create,alter,delete on test01.* to "xiaoliu1"@"localhost" identified by "qwer1234";
Query OK, 0 rows affected (0.107 sec)

## 新建 xiaoliu2 用户，并使此用户对 test01 数据库拥有所有权限
MariaDB [mysql]>
grant all on test01.* to "xiaoliu2"@"localhost" identified by "qwer1234";
Query OK, 0 rows affected (0.001 sec)

## 新建 xiaoliu3 用户，并使此用户对所有数据库拥有所有权限（管理员用户）
MariaDB [mysql]>
grant all on *.* to "xiaoliu3"@"localhost" identified by "qwer1234";
Query OK, 0 rows affected (0.001 sec)

## 新建 xiaoliu4 用户，并使此用户对所有数据库拥有 select、create、alter、delete 权限
MariaDB [mysql]>
grant select,create,alter,delete on *.* to "xiaoliu4"@"localhost" identified by "qwer1234";
Query OK, 0 rows affected (0.000 sec)

MariaDB [mysql]>
flush privileges; ## 每次创建用户或更新权限之后记得刷新权限表
Query OK, 0 rows affected (0.000 sec)

通过上述案例可以看到，通过 GRANT 语句创建的 xiaoliu1 和 xiaoliu2 用户属于数据库级别，而之后创建的 xiaoliu3 和 xiaoliu4 用户属于全局级别。

在mysql.user表中添加用户

mysql 系统数据库的 user 表中存储着用户账户信息以及全局级别（所有数据库）权限，查看用户权限有以下这几种方法：
1) 查看全局级别用户的权限信息，语法格式为：

SELECT * FROM mysql.user WHERE user='用户名'\G;

2) 查看数据库级别用户的权限信息，语法格式为：

SELECT * FROM mysql.db WHERE user='用户名'\G;

3) 通用查看用户权限信息，语法格式为：

SHOW GRANTS FOR '用户名'@'主机地址';

【实例】使用 3 种方法查看用户权限。

MariaDB [mysql]>
use mysql;
Database changed

MariaDB [mysql]>
SELECT * FROM mysql.db WHERE user='xiaoliu1' \G;
*************************** 1. row ***************************
            Host: localhost
              Db: test01
            User: xiaoliu1
      Select_priv: Y
      Insert_priv: N
      Update_priv: N
      Delete_priv: Y
      Create_priv: Y
      Drop_priv: N
1 row in set (0.001 sec)

MariaDB [mysql]>
SELECT * FROM mysql.user WHERE user='xiaoliu3' \G;
*************************** 1. row ***************************
            Host: localhost
            User: xiaoliu3
        Password: *D75CC763C5551AA420D28A227AC294FADE26A2FF2
      Select_priv: Y
      Insert_priv: Y
      Update_priv: Y
      Delete_priv: Y
      Create_priv: Y
      Drop_priv: Y
1 row in set (0.000 sec)

MariaDB [mysql]>
show grants for "xiaoliu4"@"localhost";
+-------------------------------------------------------+
| Grants for xiaoliu4@localhost                         |
+-------------------------------------------------------+
| GRANT SELECT, DELETE, CREATE, ALTER ON *.*            |
| TO `xiaoliu4`@`localhost` IDENTIFIED BY               |
| PASSWORD '*D75CC763C5551AA420D28A227AC294FADE26A2FF2' |
+-------------------------------------------------------+
1 row in set (0.000 sec)

在 MySQL/MariaDB 数据库中，可以通过 RENAME USER 语句对一个或多个已经存在的用户账号进行重命名操作，其语法格式为：

RENAME USER 旧用户 TO 新用户；

各字段的含义如下：旧用户，数据库中已经存在的用户名；新用户，新用户名。

【实例】修改用户名。

MariaDB [mysql]>
use mysql;
Database changed

MariaDB [mysql]>
select user,host from user;
+---------+-----------+
| user    | host      |
+---------+-----------+
| root    | 127.0.0.1 |
| root    | ::1       |
| root    | localhost |
| xiaoliu1| localhost |
| xiaoliu2| localhost |
| xiaoliu3| localhost |
| xiaoliu4| localhost |
| xiaosun | localhost |
| xiaosun1| localhost |
| xiaosun2| localhost |
| xiaosun3| localhost |
| xiaosun4| localhost |
| root    | clinux  |
+---------+-----------+
13 rows in set (0.001 sec)

MariaDB [mysql]>
rename user "xiaosun"@"localhost" to "xiaosun666"@"localhost";
Query OK, 0 rows affected (0.000 sec)

MariaDB [mysql]>
select user,host from user;
+-----------+-----------+
| user      | host      |
+-----------+-----------+
| root      | 127.0.0.1 |
| root      | ::1       |
| root      | localhost |
| xiaoliu1  | localhost |
| xiaoliu2  | localhost |
| xiaoliu3  | localhost |
| xiaoliu4  | localhost |
| xiaosun   | localhost |
| xiaosun1  | localhost |
| xiaosun2  | localhost |
| xiaosun3  | localhost |
| xiaosun4  | localhost |
| xiaosun666| localhost |
| root      | clinux  |
+-----------+-----------+
13 rows in set (0.000 sec)

不止修改用户的语法简单，删除用户的语法格式也非常简单，在 MySQL/MariaDB 数据库中，可以通过 DROP USER 语句删除一个或多个用户，并撤销其权限，其语法格式为：

DROP USER 用户名1 [, 用户名2, 用户名3, 用户名n];

其中，用户名就是要删除的用户账号，若需要通过 DROP USER 语句一次性删除多个用户，用户名与用户名之间通过英文逗号进行分隔。

【实例】同时删除多个用户。

MariaDB [mysql]>use mysql;
Database changed
MariaDB [mysql]>select user,host from user;
+----------+-----------+
| user     | host      |
+----------+-----------+
| root     | 127.0.0.1 |
| root     | ::1       |
| root     | localhost |
| xiaoliu1 | localhost |
| xiaoliu2 | localhost |
| xiaoliu3 | localhost |
| xiaoliu4 | localhost |
+----------+-----------+
13 rows in set (0.001 sec)

MariaDB [mysql]>drop user "xiaosun1"@"localhost",
    -> "xiaosun2"@"localhost",
    -> "xiaosun3"@"localhost",
    -> "xiaosun4"@"localhost";
Query OK, 0 rows affected (0.000 sec)

MariaDB [mysql]>select user,host from user;
+------------+-----------+
| user       | host      |
+------------+-----------+
| root       | 127.0.0.1 |
| root       | ::1       |
| root       | localhost |
| xiaoliu1   | localhost |
| xiaoliu2   | localhost |
| xiaoliu3   | localhost |
| xiaoliu4   | localhost |
| xiaosun666 | localhost |
| root       | clinux  |
+------------+-----------+
9 rows in set (0.000 sec)

假如数据库管理员在给某个用户授权完成后突然发现赋予的权限有点过多，因为普通用户的权限越大，误操作的概率也就越高。例如，在企业中数据库管理员基本上不会给普通用户赋予删除（DELETE）权限，因为那样会在一定程度上影响到数据库的安全性。

有没有什么方法能够撤销用户现有的权限呢？在 MySQL/MariaDB 数据库中，可以通过 REVOKE 语句撤销用户目前已被赋予的某些权限，其语法格式为：

REVOKE 权限 ON 数据库.表 FROM‘username’@‘hostname’；

可以看出，REVOKE 语句与 GRANT 语句的语法差不多，只不过将关键字“TO”换成了“FROM”。如果要撤销多个权限，则权限与权限之间需要通过英文逗号进行分隔。

【实例】通过 REVOKE 语句撤销某个用户权限。

MariaDB [mysql]>use mysql;
Database changed
MariaDB [mysql]>
SELECT * FROM mysql.user WHERE user='xiaoliu4' \G;
*************************** 1. row ***************************
            Host: localhost
            User: xiaoliu4
       Password: *D75CC763C5551A420D28A227AC294FADEF26A2FF2
  Select_priv: Y
  Insert_priv: N
  Update_priv: N
  Delete_priv: Y
  Create_priv: Y
  Drop_priv: N
1 row in set (0.001 sec)

MariaDB [mysql]>revoke delete on *.* from "xiaoliu4"@"localhost";
Query OK, 0 rows affected (0.000 sec)

MariaDB [mysql]>flush privileges;  ##建议刷新一下权限表
Query OK, 0 rows affected (0.000 sec)

MariaDB [mysql]>SELECT * FROM mysql.user WHERE user='xiaoliu4' \G;
*************************** 1. row ***************************
            Host: localhost
            User: xiaoliu4
       Password: *D75CC763C5551A420D28A227AC294FADEF26A2FF2
  Select_priv: Y
  Insert_priv: N
  Update_priv: N
  Delete_priv: N  ##此用户的删除权限已被删除
  Create_priv: Y
  Drop_priv: N
1 row in set (0.001 sec)

若想要将某个用户的所有权限都给删除，就使用下面的语法格式：

REVOKE ALL PRIVILEGES,GRANT OPTION FROM'username'@'hostname';

【实例】撤销用户现有的所有权限。

MariaDB [(none)]>
use mysql;
Database changed

##查看之前创建的 xiaoliu3 用户
MariaDB [mysql]>SELECT * FROM mysql.user WHERE user='xiaoliu3' \G;
*************************** 1. row ***************************
            Host: localhost
            User: xiaoliu3
       Password: *D75CC763C5551A420D28A227AC294FADEF26A2FF2
  Select_priv: Y
  Insert_priv: Y
  Update_priv: Y
  Delete_priv: Y
  Create_priv: Y
  Drop_priv: Y
  Reload_priv: Y
  Shutdown_priv: Y
  Process_priv: Y
1 row in set (0.00 sec)

MariaDB [mysql]>revoke all privileges,grant option from "xiaoliu3"@"localhost";
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]>
SELECT * FROM mysql.user WHERE user='xiaoliu3' \G;
*************************** 1. row ***************************
            Host: localhost
            User: xiaoliu3
       Password: *D75CC763C5551A420D28A227AC294FADEF26A2FF2
  Select_priv: N
  Insert_priv: N
  Update_priv: N
  Delete_priv: N
  Create_priv: N
  Drop_priv: N
  Reload_priv: N
  Shutdown_priv: N
  Process_priv: N
1 row in set (0.00 sec)

MySQL创建用户的3种方法（非常详细，附带实例）

MySQL GRANT语句

在mysql.user表中添加用户

相关文章