Mysql用户授权(GRANT)语法及示例解读

2025-04-23 17:50
文章标签 示例 mysql grant 语法 解读 授权 用户 database

本文主要是介绍Mysql用户授权(GRANT)语法及示例解读，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

《Mysql用户授权(GRANT)语法及示例解读》：本文主要介绍Mysql用户授权(GRANT)语法及示例,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教...

Mysql用户授权（GRANT）语法

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

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

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

授予用户权限语法

对于新建的 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）<权限级别>

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

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

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

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

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）最有效率的权限是用户权限。

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

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

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

mysql> GRANT SELECT,INSERT ON *.*
    -> TO 'testUser'@'localhost'
    -> IDENTIFIED BY 'tejsstPwd'
    -> 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)

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的权限，可以不加这句

使用 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   javascript       |
| 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)

mysql授权GRANT ALL PRIVILEGES三种示例

改表法

可能是你的帐号不允许从远程登陆，只能在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;

授权法

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

1-如果你想允许用户myuser从ip为UQZSLr192.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;否则无法立即生效。

另外一种方法

在安装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身份登录php啦！

其他

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作为密码

MySQL增删改查报（INSERT、DROP、UPDATE、SELECT、CREATE）command denied to user ‘xx’@‘localhost’ for table ‘test’

查看用php户权限

show grants;

结果

+-------------------------------------------------------------+
| Grants for root@%                                           |
+-------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION |
+-------------------------------------------------------------+
1 row in set (0.01 sec)
————————————————

创建mysql用户

mysql> CREATE USER 'username'@'host' IDENTIFIED BY 'password';

命令说明：

（1）username：新建的用户名，用于链接数据库的登录名

（2）host：指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%

（3）password：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器

列举一些常见的创建例子：

mysql> CREATE USER 'mumu'@'localhost' IDENTIFIED BY '123456';  # 只能本地登录
mysql> CREATE USER 'thomas'@'192.168.1.73' IDENDIFIED BY '123456';# 仅限192.168.1.73登录
mysql> CREATE USER 'lin'@'%' IDENTIFIED BY '123456'; # 任意远程主机，需要密码
mysql> CREATE USER 'huea'@'%' IDENTIFIED BY '';# 任意远程主机，无需密码
mysql> CREATE USER 'thomas'@'%';# 任意远程主机，无需密码

以上就完成了用户的创建，用户也能成功连接上，但是一旦对数据库增删改查操作均会报错：

（INSERT、DROP、UPDATE、SELECT、CREATE, ALTER等）command denied to user 'xxx'@'localhost' for table 'table'

出现这个问题原因在于该用户没有这些权限，解决方式就是需要给用户授权增删改查的权限。

用户权限授权

mysql> GRANT privileges ON databasename.tablename TO 'username'@'host'

命令说明：

  • privileges: 要赋予用户的权限，如INSERT、DROP、UPDATE、SELECT、CREATE, ALTER等
  • databasename: 数据库，如果要授予该用户对所有数据库和表的相应操作权限则可用表示，如.*
  • tablename: 数据表，如果要授予该用户对所有数据库和表的相应操作权限则可用表示，如.*
  • username: 用户名，用于链接数据库的登录名
  • host: 指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%

列举一些常见的创建例子：

mysql> GRANT SELECT, INSERT ON test.user TO 'thomas'@'%'; # 给thomas分配test库的user表查询、插入权限 
mysql> GRANT ALL ON *.* TO 'thomas'@'%'; # 给thomas分配所有库所有表的所有权限  
mysql> GRANT ALL ON test.* TO 'thomas'@'%'; # 给thomas分配test库的所有表的所有权限

注意：以上面这种授权方式给用户授权权限，该用户没有权限创建新用户，更没权限授权用户的权限

（1）创建用户

mysql> CREATE USER 'lin'@'%' IDENTIFIED BY '123456';
1227 - Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation

（2）用户授权

mysql> GRANT INSERT ON `comment`.* TO 'lin'@'%';
1044 - Access denied for user 'lin'@'%' to database 'comment'

如果想让创建的用户经过授权也可以创建并授权的权限，那么需要用以下命令：

mysql> GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;

也就是在常规授权的命令后加上’WITH GRANT OPTION’即可,如果想让执行后的命令立即生效，可以在执行语句后执行以下命令：

mysql> FLUSH PRIVILEGES;

FLUSH PRIVILEGES 命令本质上的作用是将当前user和privilige表中的用户信息/权限设置从mysql库(MySQL数据库的内置库)中提取到内存里。MySQL用户数据和权限有修改后，希望在"不重启MySQL服务"的情况下直接生效，那么就需要执行这个命令。通常是在修改ROOT帐号的设置后，怕重启后无法再登录进来，那么直接flush之后就可以看权限设置是否生效。而不必冒太大风险!!

设置与更改用户密码

SET PASSWORD FOR 'username'@'host' = '123456';
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

如果是当前登陆用户用：

SET PASSWORD = '123456';

撤销用户权限

REVOKE跟GRANT的语法差不多，只需要把关键字 “to” 换成 “from” 即可：

REVOKE privilege ON databasename.tablename FROM 'lin'@'host';

删除用户

DROP USER 'lin'@'host';

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持China编程(www.chinasem.cn)。

这篇关于Mysql用户授权(GRANT)语法及示例解读的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/1154339。

相关文章

Android实现在线预览office文档的示例详解

Android实现在线预览office文档的示例详解

《Android实现在线预览office文档的示例详解》在移动端展示在线Office文档（如Word、Excel、PPT）是一项常见需求,这篇文章为大家重点介绍了两种方案的实现方法,希望对大家有一定的... 目录一、项目概述二、相关技术知识三、实现思路3.1 方案一：WebView + Office Onl
阅读更多...
Java中Scanner的用法示例小结

Java中Scanner的用法示例小结

《Java中Scanner的用法示例小结》有时候我们在编写代码的时候可能会使用输入和输出,那Java也有自己的输入和输出,今天我们来探究一下,对JavaScanner用法相关知识感兴趣的朋友一起看看吧... 目录前言一 输出二 输入Scanner的使用多组输入三 综合练习：猜数字游戏猜数字前言有时候我们在
阅读更多...
Mysql如何解决死锁问题

Mysql如何解决死锁问题

《Mysql如何解决死锁问题》：本文主要介绍Mysql如何解决死锁问题,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录【一】mysql中锁分类和加锁情况【1】按锁的粒度分类全局锁表级锁行级锁【2】按锁的模式分类【二】加锁方式的影响因素【三】Mysql的死锁情况【1
阅读更多...
html5的响应式布局的方法示例详解

html5的响应式布局的方法示例详解

《html5的响应式布局的方法示例详解》：本文主要介绍了HTML5中使用媒体查询和Flexbox进行响应式布局的方法，简要介绍了CSSGrid布局的基础知识和如何实现自动换行的网格布局，详细内容请阅读本文，希望能对你有所帮助... 一 使用媒体查询响应式布局        使用的参数@media这是常用的
阅读更多...
HTML5表格语法格式详解

HTML5表格语法格式详解

《HTML5表格语法格式详解》在HTML语法中，表格主要通过table、tr和td3个标签构成,本文通过实例代码讲解HTML5表格语法格式，感兴趣的朋友一起看看吧... 目录一、表格1.表格语法格式2.表格属性 3.例子二、不规则表格1.跨行2.跨列3.例子一、表格在html语法中，表格主要通过< tab
阅读更多...
SQL BETWEEN 的常见用法小结

SQL BETWEEN 的常见用法小结

《SQLBETWEEN的常见用法小结》BETWEEN操作符是SQL中非常有用的工具,它允许你快速选取某个范围内的值,本文给大家介绍SQLBETWEEN的常见用法,感兴趣的朋友一起看看吧... 在SQL中，BETWEEN是一个操作符，用于选取介于两个值之间的数据。它包含这两个边界值。BETWEEN操作符常用
阅读更多...
MySQL索引的优化之LIKE模糊查询功能实现

MySQL索引的优化之LIKE模糊查询功能实现

《MySQL索引的优化之LIKE模糊查询功能实现》：本文主要介绍MySQL索引的优化之LIKE模糊查询功能实现,本文通过示例代码给大家介绍的非常详细,感兴趣的朋友一起看看吧... 目录一、前缀匹配优化二、后缀匹配优化三、中间匹配优化四、覆盖索引优化五、减少查询范围六、避免通配符开头七、使用外部搜索引擎八、分
阅读更多...
MySql match against工具详细用法

MySql match against工具详细用法

《MySqlmatchagainst工具详细用法》在MySQL中,MATCH……AGAINST是全文索引（Full-Textindex）的查询语法,它允许你对文本进行高效的全文搜素,支持自然语言搜... 目录一、全文索引的基本概念二、创建全文索引三、自然语言搜索四、布尔搜索五、相关性排序六、全文索引的限制七
阅读更多...
Java使用SLF4J记录不同级别日志的示例详解

Java使用SLF4J记录不同级别日志的示例详解

《Java使用SLF4J记录不同级别日志的示例详解》SLF4J是一个简单的日志门面,它允许在运行时选择不同的日志实现,这篇文章主要为大家详细介绍了如何使用SLF4J记录不同级别日志,感兴趣的可以了解下... 目录一、SLF4J简介二、添加依赖三、配置Logback四、记录不同级别的日志五、总结一、SLF4J
阅读更多...
Java使用ANTLR4对Lua脚本语法校验详解

Java使用ANTLR4对Lua脚本语法校验详解

《Java使用ANTLR4对Lua脚本语法校验详解》ANTLR是一个强大的解析器生成器,用于读取、处理、执行或翻译结构化文本或二进制文件,下面就跟随小编一起看看Java如何使用ANTLR4对Lua脚本... 目录什么是ANTLR？第一个例子ANTLR4 的工作流程Lua脚本语法校验准备一个Lua Gramm
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2