MySQL 设置用户权限

柳风123

已于 2024-05-04 20:39:51 修改

阅读量7.3k

点赞数 3

分类专栏： mysql 文章标签： mysql

于 2021-11-25 19:43:22 首次发布

本文链接：https://blog.csdn.net/yamadeee/article/details/83989106

版权

mysql 专栏收录该内容

10 篇文章 2 订阅

订阅专栏

文章目录

- - 用户权限

用户权限

mysql的user表中保存用户和一些权限信息。

当使用 CREATE USER 创建一个用户时，此时新增用户没有数据访问操作权限的。需要借助 grant 授予用户权限

注意:

要授权的用户，必须有授权的权限，还必须拥有你将要授予其他用户权限的所有权限
以下语句执行基于 MySQL版本8.0。返回结果，sql 可能存在些许不一致

一创建用户

create user 'nacos'@'%' identified by '123456';

创建一个用户nacos。可以使用任意客户端IP连接mysql 服务器。

‘nacos’@‘localhost’：表示nacos用户仅能通过服务器本机连接（127.0.0.1）服务器。无法远程访问

相当于MySQL的白名单，例如我们本地地址192.168.202.128，需要支持这个IP段内的都可以访问MYSQL服务器。

create user 'nacos'@'192.168.202.%' identified by '123456';

此时，MySQL服务器本机客户端时无法连接的。可以额外创建本地用户

create user 'nacos'@localhost' identified by '123456';

没错，一个用户名可以配置多个主机，他们作为一个整体保证账户的唯一

错误

如果客户端IP非MYSQL白名单，会提示

Host ‘192.168.202.128’ is not allowed to connect to this MySQL server

二查询用户权限

新增用户以后，就需要授权。否则仅如下两个表的权限。
在这里插入图片描述

2.1 查询用户

首先进入MySQL数据库，查询用户

mysql> use mysql;
mysql> select host,user from user;
+---------------+------------------+
| host          | user             |
+---------------+------------------+
| %             | root             |
| 192.168.202.% | nacos            |
| localhost     | mysql.infoschema |
| localhost     | mysql.sys        |
+---------------+------------------+

这一步是因为，查询用户权限时，需要同时指定host 和 user。

2.2 查询用户权限

mysql> show grants for 'nacos'@'192.168.202.%';
+-----------------------------------------------+
| Grants for nacos@192.168.202.%                |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO `nacos`@`192.168.202.%` |
+-----------------------------------------------+

USAGE 相当于没有权限

错误

如果host未指定，或者不正确。会提示如下的错误信息

ERROR 1141 (42000): There is no such grant defined for user ‘nacos’ on host ‘%’

mysql> show grants for 'nacos';
ERROR 1141 (42000): There is no such grant defined for user 'nacos' on host '%'

三用户授权

3.1 语法

给用户授予权限的一般语法

GRANT
    priv_type  ON [object_type] priv_level
	TO user_or_role [, user_or_role] ...

priv_type ：权限类型，表示授予什么权限。 以下时常见的，完整版在文章最后

	select,
	update,
	delete,
	insert,
	ALL,

ALL: 授予所有的操作权限

GRANT ALL

select: 仅查查询权限

GRANT select

设置多个权限使用 ,分割

GRANT select,uodate,insert

object_type：对象类型。操作的是表（视图）/ 函数还是存储过程

    TABLE
  | FUNCTION
  | PROCEDURE

一般不指定，默认就是所有类型

priv_level：权限级别。全局，数据库，表，列

    *
  | *.*
  | db_name.*
  | db_name.tbl_name
  | tbl_name
  | db_name.routine_name

全局级别（所有数据库）
ON *: 仅有默认数据库的权限
ON *.* ：所有数据库的权限
数据库级别
ON nacos.*
表级别
ON tbl_name：默认数据库中表权限
ON nacos.config_info：指定库的表权限

user_or_role：

user 
| role

user：账户名称。语法 'user_name'@'host_name'。由用户名+连接主机。标识唯一的账户信息。当然 host_name 省略时，默认 %

‘user_name’ 等价于 ‘user_name’@%

role : 角色名。语法 role_name@'host_name'

3.2 示例

因为上面创建的sql账户就是 ‘nacos’@‘192.168.202.%’。
通常允许所有客户端访问（测试，开发环境），所以就是 ‘nacos’@‘%’ , 可以简写’nacos’

授予用户所有的权限（全局权限）

GRANT ALL ON *.* TO 'nacos'@'192.168.202.%';

授予用户查询，更新权限

GRANT select,update ON *.* TO 'nacos'@'192.168.202.%' ;

授予某个数据库的任何权限 (数据库权限)

GRANT ALL ON nacos.* TO 'nacos'@'192.168.202.%';

授予某个表权限(表权限)

GRANT ALL ON nacos.config_info TO 'nacos'@'192.168.202.%';

授予表某些列的权限(列权限)

GRANT SELECT(id,data_id,group_id) ON nacos.config_info TO 'nacos'@'192.168.202.%';

注意：
ON *: 仅有默认数据库的权限
ON *.* ：所有数据库的权限
ON tbl_name：默认数据库中表权限
ON db_name.tbl_name：指定库的表权限

2.查询用户权限

mysql> show grants for 'nacos'@'192.168.202.%';
+--------------------------------------------------------------+
| Grants for nacos@192.168.202.%                               |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO `nacos`@`192.168.202.%`                |
| GRANT ALL PRIVILEGES ON `nacos`.* TO `nacos`@`192.168.202.%` |
+--------------------------------------------------------------+

四用户授权-角色

4.1 语法

GRANT role [, role] ...
    TO user_or_role [, user_or_role] ...
    [WITH ADMIN OPTION]

跟权限授予类似，区别是不需要 ON

GRANT 'role1', 'role2' TO 'user1'@'localhost', 'user2'@'localhost';

没用过角色，就不展开了

五撤销用户授权

To use this syntax, you must have the global privilege, or the privilege for the mysql system schema.

5.1 语法

撤销一些权限

REVOKE [IF EXISTS]
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user_or_role [, user_or_role] ...
    [IGNORE UNKNOWN USER]

IF EXISTS ：如果撤销不存在的权限仅打印警告信息

撤销所有权限

REVOKE ALL PRIVILEGES, GRANT OPTION
  FROM user_or_role [, user_or_role] ...

操作上述语法，需要拥有对mysql 数据库的 CREATE USER 或者 UPDATE权限。

5.2 示例

当前是对数据库级别的权限。

mysql> show grants for 'nacos'@'192.168.202.%';
+--------------------------------------------------------------+
| Grants for nacos@192.168.202.%                               |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO `nacos`@`192.168.202.%`                |
| GRANT ALL PRIVILEGES ON `nacos`.* TO `nacos`@`192.168.202.%` |
+--------------------------------------------------------------+

撤销某个表的权限

REVOKE ALL ON nacos.config_info FROM 'nacos'@'192.168.202.%';

mysql> REVOKE ALL ON nacos.config_info FROM 'nacos'@'192.168.202.%';
	
ERROR 1147 (42000): There is no such grant defined for user 'nacos' on host '192.168.202.%' on table 'config_info'

撤销查询权限

 REVOKE DELETE ON nacos.*  FROM 'nacos'@'192.168.202.%';

mysql> show grants for  'nacos'@'192.168.202.%';
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for nacos@192.168.202.%                                                                                                                                                                                                     |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `nacos`@`192.168.202.%`                                                                                                                                                                                      |
| GRANT SELECT, INSERT, UPDATE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `nacos`.* TO `nacos`@`192.168.202.%` |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

撤销某个数据库权限

 REVOKE ALL ON nacos.*  FROM 'nacos'@'192.168.202.%';

撤销所有的权限

REVOKE ALL PRIVILEGES, GRANT OPTION  FROM 'nacos'@'192.168.202.%';

错误

ERROR 1147 (42000): There is no such grant defined for user ‘nacos’ on host ‘192.168.202.%’ on table ‘config_info’

授予的是数据库级别的权限，是无法对表级别的权限做撤销的。

六问题汇总

如果客户端IP非MYSQL白名单，会提示

Host ‘192.168.202.128’ is not allowed to connect to this MySQL server

修改user表中的host字段，比如改为%
update user set host = '%' where user='xxxx' and host = 'xxxx'
如果host未指定，或者不正确。会提示如下的错误信息

ERROR 1141 (42000): There is no such grant defined for user ‘nacos’ on host ‘%’

通过select host,user from user; 查询出完整的主机名
撤销级别和授予时不一致

ERROR 1147 (42000): There is no such grant defined for user ‘nacos’ on host ‘192.168.202.%’ on table ‘config_info’

授予的是数据库级别的权限，是无法对表级别的权限做撤销的。

MySQL中的权限

权限	解释和授权等级
ALL	[PRIVILEGES] Grant all privileges at specified access level except GRANT OPTION and PROXY.
ALTER	修改表的权限 . Levels: Global, database, table.
ALTER ROUTINE	Enable stored routines to be altered or dropped. Levels: Global, database, routine.
CREATE	创建表或者数据库的权限。 Levels: Global, database, table.
CREATE ROUTINE	Enable stored routine creation. Levels: Global, database.
CREATE TABLESPACE	Enable tablespaces and log file groups to be created, altered, or dropped. Level: Global.
CREATE TEMPORARY TABLES	Enable use of CREATE TEMPORARY TABLE. Levels: Global, database.
CREATE USER	Enable use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES. Level: Global.
CREATE VIEW	创建或者修改试图的权限 Levels: Global, database, table.
DELETE	删除数据库或者表的权限. Level: Global, database, table.
DROP	删除数据库，表或者试图的权限. Levels: Global, database, table.
EVENT	Enable use of events for the Event Scheduler. Levels: Global, database.
EXECUTE	Enable the user to execute stored routines. Levels: Global, database, routine.
FILE	Enable the user to cause the server to read or write files. Level: Global.
GRANT OPTION	允许给其他账号授权或者取消授权 . Levels: Global, database, table, routine, proxy.
INDEX	Enable indexes to be created or dropped. Levels: Global, database, table.
INSERT	使用insert的权限. Levels: Global, database, table, column.
LOCK TABLES	Enable use of LOCK TABLES on tables for which you have the SELECT privilege. Levels: Global, database.
PROCESS	Enable the user to see all processes with SHOW PROCESSLIST. Level: Global.
PROXY	Enable user proxying. Level: From user to user.
REFERENCES	Enable foreign key creation. Levels: Global, database, table, column.
RELOAD	Enable use of FLUSH operations. Level: Global.
REPLICATION CLIENT	Enable the user to ask where master or slave servers are. Level: Global.
REPLICATION SLAVE	Enable replication slaves to read binary log events from the master. Level: Global.
SELECT	使用select 查询的权限 Levels: Global, database, table, column.
SHOW DATABASES	可以使用 SHOW DATABASES 命令显示所有数据库的权限. Level: Global.
SHOW VIEW	使用SHOW CREATE VIEW显示所有试图的权限. Levels: Global, database, table.
SHUTDOWN	Enable use of mysqladmin shutdown. Level: Global.
SUPER	Enable use of other administrative operations such as CHANGE MASTER TO, KILL, PURGE BINARY LOGS, SET GLOBAL, and mysqladmin debug command. Level: Global.
TRIGGER	Enable trigger operations. Levels: Global, database, table.
UPDATE	Enable use of UPDATE. Levels: Global, database, table, column.
USAGE	“no privileges”的同义词

Levels ：对应授权或者撤销的级别。
priv_level：权限级别。全局，数据库，表，列

    *
  | *.*
  | db_name.*
  | db_name.tbl_name
  | tbl_name
  | db_name.routine_name

oracle官网 mysql 指南

柳风123

关注

3
点赞
踩
10

收藏

觉得还不错? 一键收藏
1
评论
MySQL 设置用户权限

用户权限mysql的user表中保存一些权限信息，那是用户的操作权限，比如说select,delete,update等权限。但是刚创建的用户是没有操作其他已经存在数据库及表的权限，需要其他高级用户分配权限给userName用户操作dbname数据库所有表的权限。注意:要授权的用户，必须有授权的权限，还必须拥有你将要授予其他用户权限的所有权限1. 给用户授予权限给用户授予权限的一般语法，下...
复制链接

扫一扫