MySQL用户授权（GRANT）语法介绍和示例

1、MySQL用户授权（GRANT）语法

当成功创建用户账户后，还不能执行任何操作，需要为该用户分配适当的访问权限。可以使用 SHOW GRANT FOR 语句来查询用户的权限。

注意：新创建的用户只有登录 MySQL 服务器的权限，没有任何其他权限，不能进行其他操作。

USAGE ON*.* 表示该用户对任何数据库和任何表都没有权限。

1.1、授予用户权限语法

对于新建的 MySQL 用户，必须给它授权，可以用 GRANT 语句来实现对新建用户的授权。

语法格式：

GRANT
<权限类型> [ ( <列名> ) ] [ , <权限类型> [ ( <列名> ) ] ]
ON <对象> <权限级别> TO <用户>
其中<用户>的格式：
<用户名> [ IDENTIFIED ] BY [ PASSWORD ] <口令>
[ WITH GRANT OPTION]
| MAX_QUERIES_PER_HOUR <次数>
| MAX_UPDATES_PER_HOUR <次数>
| MAX_CONNECTIONS_PER_HOUR <次数>
| MAX_USER_CONNECTIONS <次数>

语法说明如下：

1) <列名>

可选项。用于指定权限要授予给表中哪些具体的列。

2) ON 子句

用于指定权限授予的对象和级别，如在 ON 关键字后面给出要授予权限的数据库名或表名等。

3) <权限级别>

用于指定权限的级别。可以授予的权限有如下几组：

• 列权限，和表中的一个具体列相关。例如，可以使用 UPDATE 语句更新表 students 中 student_name 列的值的权限。
• 表权限，和一个具体表中的所有数据相关。例如，可以使用 SELECT 语句查询表 students 的所有数据的权限。
• 数据库权限，和一个具体的数据库中的所有表相关。例如，可以在已有的数据库 mytest 中创建新表的权限。
• 用户权限，和 MySQL 中所有的数据库相关。例如，可以删除已有的数据库或者创建一个新的数据库的权限。

对应地，在 GRANT 语句中可用于指定权限级别的值有以下几类格式：

• *：表示当前数据库中的所有表。
• *.*：表示所有数据库中的所有表。
• db_name.*：表示某个数据库中的所有表，db_name 指定数据库名。
• db_name.tbl_name：表示某个数据库中的某个表或视图，db_name 指定数据库名，tbl_name 指定表名或视图名。
• tbl_name：表示某个表或视图，tbl_name 指定表名或视图名。
• db_name.routine_name：表示某个数据库中的某个存储过程或函数，routine_name 指定存储过程名或函数名。
• TO 子句：用来设定用户口令，以及指定被赋予权限的用户 user。若在 TO 子句中给系统中存在的用户指定口令，则新密码会将原密码覆盖；如果权限被授予给一个不存在的用户，MySQL 会自动执行一条 CREATE USER 语句来创建这个用户，但同时必须为该用户指定口令。

1.2、GRANT语句中的<权限类型>的使用说明如下：

1) 授予数据库权限时，<权限类型>可以指定为以下值：

• SELECT：表示授予用户可以使用 SELECT 语句访问特定数据库中所有表和视图的权限。
• INSERT：表示授予用户可以使用 INSERT 语句向特定数据库中所有表添加数据行的权限。
• DELETE：表示授予用户可以使用 DELETE 语句删除特定数据库中所有表的数据行的权限。
• UPDATE：表示授予用户可以使用 UPDATE 语句更新特定数据库中所有数据表的值的权限。
• REFERENCES：表示授予用户可以创建指向特定的数据库中的表外键的权限。
• CREATE：表示授权用户可以使用 CREATE TABLE 语句在特定数据库中创建新表的权限。
• ALTER：表示授予用户可以使用 ALTER TABLE 语句修改特定数据库中所有数据表的权限。
• SHOW VIEW：表示授予用户可以查看特定数据库中已有视图的视图定义的权限。
• CREATE ROUTINE：表示授予用户可以为特定的数据库创建存储过程和存储函数的权限。
• ALTER ROUTINE：表示授予用户可以更新和删除数据库中已有的存储过程和存储函数的权限。
• INDEX：表示授予用户可以在特定数据库中的所有数据表上定义和删除索引的权限。
• DROP：表示授予用户可以删除特定数据库中所有表和视图的权限。
• CREATE TEMPORARY TABLES：表示授予用户可以在特定数据库中创建临时表的权限。
• CREATE VIEW：表示授予用户可以在特定数据库中创建新的视图的权限。
• EXECUTE ROUTINE：表示授予用户可以调用特定数据库的存储过程和存储函数的权限。
• LOCK TABLES：表示授予用户可以锁定特定数据库的已有数据表的权限。
• ALL 或 ALL PRIVILEGES：表示以上所有权限。

2) 授予表权限时，<权限类型>可以指定为以下值：

• SELECT：授予用户可以使用 SELECT 语句进行访问特定表的权限。
• INSERT：授予用户可以使用 INSERT 语句向一个特定表中添加数据行的权限。
• DELETE：授予用户可以使用 DELETE 语句从一个特定表中删除数据行的权限。
• DROP：授予用户可以删除数据表的权限。
• UPDATE：授予用户可以使用 UPDATE 语句更新特定数据表的权限。
• ALTER：授予用户可以使用 ALTER TABLE 语句修改数据表的权限。
• REFERENCES：授予用户可以创建一个外键来参照特定数据表的权限。
• CREATE：授予用户可以使用特定的名字创建一个数据表的权限。
• INDEX：授予用户可以在表上定义索引的权限。
• ALL 或 ALL PRIVILEGES：所有的权限名。

3) 授予列权限时，<权限类型>的值只能指定为 SELECT、INSERT 和 UPDATE，同时权限后面需要加上列名列表 column-list。

4) 最有效率的权限是用户权限。

授予用户权限时，<权限类型>除了可以指定为授予数据库权限时的所有值之外，还可以是下面这些值：

• CREATE USER：表示授予用户可以创建和删除新用户的权限。
• SHOW DATABASES：表示授予用户可以使用 SHOW DATABASES 语句查看所有已有的数据库的定义的权限。

【实例】使用 GRANT 语句创建一个新的用户 testUser，密码为 testPwd。用户 testUser 对所有的数据有查询、插入权限，并授予 GRANT 权限。输入的 SQL 语句和执行过程如下所示。

mysql> GRANT SELECT,INSERT ON *.*-> TO 'testUser'@'localhost'-> IDENTIFIED BY 'testPwd'-> WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.05 sec)

使用 SELECT 语句查询用户 testUser 的权限，如下所示。

mysql> SELECT Host,User,Select_priv,Grant_priv-> FROM mysql.user-> WHERE User='testUser';
+-----------+----------+-------------+------------+
| Host      | User     | Select_priv | Grant_priv |
+-----------+----------+-------------+------------+
| localhost | testUser | Y           | Y          |
+-----------+----------+-------------+------------+
1 row in set (0.01 sec)

1.3、WITH GRANT OPTION的作用

数据库添加用户语句：

grant all privileges on testdb.* to ‘test_user’@’localhost’ identified by “jack” with grant option;

WITH GRANT OPTION 这个选项表示该用户可以将自己拥有的权限授权给别人。注意：经常有人在创建操作用户的时候不指定WITH GRANT OPTION选项导致后来该用户不能使用GRANT命令创建用户或者给其它用户授权。

如果不想这个用户有这个grant的权限，可以不加这句

1.3、使用 SELECT 语句查询所有用户的权限，如下所示。

MySQL [mysql]>  SELECT Host,Db,User,Select_priv,Grant_priv FROM mysql.db ; 
+--------------+--------------------+---------------+-------------+------------+
| Host         | Db                 | User          | Select_priv | Grant_priv |
+--------------+--------------------+---------------+-------------+------------+
| localhost    | performance_schema | mysql.session | Y           | N          |
| localhost    | sys                | mysql.sys     | N           | N          |
| %            | cloud_manager_v2   | feeduser      | Y           | N          |
| %            | xiaojin_airflow    | rw_airflow    | Y           | N          |
| 10.20.250.12 | xiaojin_airflow    | rw_airflow    | Y           | N          |
| 10.20.250.13 | xiaojin_airflow    | rw_airflow    | Y           | N          |
| 10.20.250.12 | data_center        | rw_airflow    | Y           | N          |
| 10.20.250.13 | data_center        | rw_airflow    | Y           | N          |
+--------------+--------------------+---------------+-------------+------------+
8 rows in set (0.00 sec)MySQL [mysql]>  SELECT Host,User,Select_priv,Grant_priv FROM mysql.user ; 
+--------------+---------------+-------------+------------+
| Host         | User          | Select_priv | Grant_priv |
+--------------+---------------+-------------+------------+
| localhost    | root          | Y           | Y          |
| localhost    | mysql.session | N           | N          |
| localhost    | mysql.sys     | N           | N          |
| %            | root          | Y           | Y          |
| %            | feeduser      | N           | N          |
| %            | rw_airflow    | N           | N          |
| 10.20.250.12 | rw_airflow    | N           | N          |
| 10.20.250.13 | rw_airflow    | N           | N          |
+--------------+---------------+-------------+------------+
8 rows in set (0.00 sec)

2、mysql授权GRANT ALL PRIVILEGES三种示例

2.1、 改表法

可能是你的帐号不允许从远程登陆，只能在localhost。这个时候只要在localhost的那台电脑，登入mysql后，更改 "mysql" 数据库里的 "user" 表里的 "host" 项，从"localhost"改成"%" 

•  mysql -u root -p vmware
• mysql>use mysql;  
•  mysql>update user set host = '%' where user = 'root';  
•  mysql>select host, user from user; 

2.2、 授权法。

例如，你想myuser使用mypassword从任何主机连接到mysql服务器的话。 

 

1）、如果你想允许用户myuser从ip为192.168.1.6的主机连接到mysql服务器，并使用mypassword作为密码 

• GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY  'mypassword' WITH GRANT OPTION; 
• FLUSH   PRIVILEGES; 

2）、如果你想允许用户myuser从ip为192.168.1.6的主机连接到mysql服务器的dk数据库，并使用mypassword作为密码 

•     GRANT ALL PRIVILEGES ON dk.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;   
•     FLUSH   PRIVILEGES;  

注意授权后必须FLUSH PRIVILEGES;否则无法立即生效。 

2.3、另外一种方法. 

在安装mysql的机器上运行： 

1）、d:\mysql\bin\>mysql -h localhost -u root 

//这样应该可以进入MySQL服务器 

2）、mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION 

//赋予任何主机访问数据的权限 

3）、mysql>FLUSH PRIVILEGES 

//修改生效 

4）、mysql>EXIT 

//退出MySQL服务器 

这样就可以在其它任何的主机上以root身份登录啦！

2.4、其它：

mysql> grant all privileges on *.* to 'energy_pf'@'192.168.2.65' identified by 'energy_pf' with grant option;

Query OK, 0 rows affected (0.00 sec)

mysql> plush privileges;

允许用户energy_pf从ip为192.168.2.65的主机连接到mysql服务器的任意数据库（*.*），并使用energy_pf作为密码 

参考：https://blog.csdn.net/yuhan61659/article/details/80590227