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。

相关文章

Python中的Walrus运算符分析示例详解

Python中的Walrus运算符分析示例详解

《Python中的Walrus运算符分析示例详解》Python中的Walrus运算符（:=）是Python3.8引入的一个新特性,允许在表达式中同时赋值和返回值,它的核心作用是减少重复计算,提升代码简... 目录1. 在循环中避免重复计算2. 在条件判断中同时赋值变量3. 在列表推导式或字典推导式中简化逻辑
阅读更多...
Python位移操作和位运算的实现示例

Python位移操作和位运算的实现示例

《Python位移操作和位运算的实现示例》本文主要介绍了Python位移操作和位运算的实现示例,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一... 目录1. 位移操作1.1 左移操作 (<<)1.2 右移操作 (>>)注意事项：2. 位运算2.1
阅读更多...
SpringBoot中配置文件的加载顺序解读

SpringBoot中配置文件的加载顺序解读

《SpringBoot中配置文件的加载顺序解读》：本文主要介绍SpringBoot中配置文件的加载顺序,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录SpringBoot配置文件的加载顺序1、命令⾏参数2、Java系统属性3、操作系统环境变量5、项目【外部】的ap
阅读更多...
SpringBoot UserAgentUtils获取用户浏览器的用法

SpringBoot UserAgentUtils获取用户浏览器的用法

《SpringBootUserAgentUtils获取用户浏览器的用法》UserAgentUtils是于处理用户代理（User-Agent）字符串的工具类,一般用于解析和处理浏览器、操作系统以及设备... 目录介绍效果图依赖封装客户端工具封装IP工具实体类获取设备信息入库介绍UserAgentUtils
阅读更多...
MySQL 中的 JSON 查询案例详解

MySQL 中的 JSON 查询案例详解

《MySQL中的JSON查询案例详解》：本文主要介绍MySQL的JSON查询的相关知识,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录mysql 的 jsON 路径格式基本结构路径组件详解特殊语法元素实际示例简单路径复杂路径简写操作符注意MySQL 的 J
阅读更多...
pandas中位数填充空值的实现示例

pandas中位数填充空值的实现示例

《pandas中位数填充空值的实现示例》中位数填充是一种简单而有效的方法,用于填充数据集中缺失的值,本文就来介绍一下pandas中位数填充空值的实现,具有一定的参考价值,感兴趣的可以了解一下... 目录什么是中位数填充？为什么选择中位数填充？示例数据结果分析完整代码总结在数据分析和机器学习过程中，处理缺失数
阅读更多...
Pandas统计每行数据中的空值的方法示例

Pandas统计每行数据中的空值的方法示例

《Pandas统计每行数据中的空值的方法示例》处理缺失数据（NaN值）是一个非常常见的问题,本文主要介绍了Pandas统计每行数据中的空值的方法示例,具有一定的参考价值,感兴趣的可以了解一下... 目录什么是空值？为什么要统计空值？准备工作创建示例数据统计每行空值数量进一步分析www.chinasem.cn处
阅读更多...
利用Python调试串口的示例代码

利用Python调试串口的示例代码

《利用Python调试串口的示例代码》在嵌入式开发、物联网设备调试过程中,串口通信是最基础的调试手段本文将带你用Python+ttkbootstrap打造一款高颜值、多功能的串口调试助手,需要的可以了... 目录概述：为什么需要专业的串口调试工具项目架构设计1.1 技术栈选型1.2 关键类说明1.3 线程模
阅读更多...
Windows 上如果忘记了 MySQL 密码 重置密码的两种方法

Windows 上如果忘记了 MySQL 密码 重置密码的两种方法

《Windows上如果忘记了MySQL密码重置密码的两种方法》：本文主要介绍Windows上如果忘记了MySQL密码重置密码的两种方法,本文通过两种方法结合实例代码给大家介绍的非常详细,感... 目录方法 1：以跳过权限验证模式启动 mysql 并重置密码方法 2：使用 my.ini 文件的临时配置在 Wi
阅读更多...
MySQL重复数据处理的七种高效方法

MySQL重复数据处理的七种高效方法

《MySQL重复数据处理的七种高效方法》你是不是也曾遇到过这样的烦恼：明明系统测试时一切正常,上线后却频频出现重复数据,大批量导数据时,总有那么几条不听话的记录导致整个事务莫名回滚,今天,我就跟大家分... 目录1. 重复数据插入问题分析1.1 问题本质1.2 常见场景图2. 基础解决方案：使用异常捕获3.
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2