MySQL权限管理

最新推荐文章于 2024-04-11 10:00:36 发布
最新推荐文章于 2024-04-11 10:00:36 发布
阅读量671 收藏 2
点赞数
分类专栏： MySQL 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_42612223/article/details/113854502
版权

文章目录

前言

本篇主要介绍创建用户和赋予用户权限的基本使用。

一、账号管理

1.创建账号并赋予权限

创建用户
基本语法：
create user username@'[%|ip|localhost]' [identified by 'password'];
其中@符后面表示运行连接的地址,mysql数据库中是通过user表的host字段来进行控制，host可以是以下类型的赋值：

  • 可以是主机名或者ip,或’localhost’
  • 可以在host列指定通配符“%”和“_”
  • host通配符“%”表示匹配任何主机。空host等价于%，它们的含义与LIKE操作符的模式匹配操作相同。如“%.mysql.com”匹配mysq.com域的所有主机。

创建用户是可以指定登陆密码，可以不指定

mysql> create user nopsd_user;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT * FROM user WHERE User='nopsd_user'\G;
*************************** 1. row ***************************
                  Host: %
                  User: nopsd_user
           Select_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N    
           Select_priv: N
           Insert_priv: N
           ....
 authentication_string:
 		   ....
-- 登陆
[root@localhost temp]# mysql -unopsd_user
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.24-log MySQL Community Server (GPL)
...
mysql>

-- 创建有密码用户
mysql> create user psd_user identified by '123456';
Query OK, 0 rows affected (0.00 sec)
-- 查看mysql库user表
ysql> SELECT * FROM user WHERE User='psd_user'\G;
*************************** 1. row ***************************
                  Host: %
                  User: psd_user
           Select_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
            ...
                plugin: mysql_native_password
 authentication_string: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
    		...

[root@localhost temp]# mysql -upsd_user
ERROR 1045 (28000): Access denied for user 'psd_user'@'localhost' (using password: NO)
[root@localhost temp]# mysql -upsd_user -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.7.24-log MySQL Community Server (GPL)
...
mysql>

# 指定ip
mysql> create user local_user @'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM user WHERE User='local_user'\G;
*************************** 1. row ***************************
                  Host: localhost
                  User: local_user
           Select_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
				...

从上面看出，这种方式创建出的用户默认所有ip都能连接，还没有权限操作数据库跟表资源。
用户信息的查看在mysql.user表中

权限设置
基本语法：
GRANT piv_type[column_list][,piv_type[column_list]]... ON [object_type]{tabl_name|*|*.*|db_name.*|db_name.tab_name} TO user username[@'ip|%']

为psd_user设置所有权限

-- 注意：psd_user用户在mysql.user表中host列存储的是'%'
mysql> grant all privileges on *.* to psd_user@'localhost';
ERROR 1133 (42000): Can't find any matching row in the user table
mysql> grant all privileges on *.* to psd_user;
Query OK, 0 rows affected (0.01 sec)

mysql> grant all privileges on *.* to psd_user@'%';
Query OK, 0 rows affected (0.00 sec)
-- 查看mysql.user
mysql> SELECT * FROM user WHERE user='psd_user'\G;
*************************** 1. row ***************************
                  Host: %
                  User: psd_user
           Select_priv: Y
           Insert_priv: Y
           ...
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: N
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
 		...
   Create_routine_priv: Y
		...
                plugin: mysql_native_password
 authentication_string: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
    	...
1 row in set (0.00 sec)

除了Grant_priv权限外，所有权限在user表中都是’Y’.也可通过下面的语法赋予grant权限
grant all privileges on *.* to psd_user@'%' with grant option;

赋予nopsd_user用户sakila库的指定权限

mysql> grant select,insert,update,delete on sakila.* to nopsd_user;
Query OK, 0 rows affected (0.01 sec)

从mysql库的user表和db表查看权限信息

mysql> SELECT * FROM user WHERE user='nopsd_user'\G;
*************************** 1. row ***************************
                  Host: %
                  User: nopsd_user
           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
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: N
            Alter_priv: N
          Show_db_priv: N
            Super_priv: N
 Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: N
      Repl_client_priv: N
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
              ssl_type:
            ssl_cipher:
           x509_issuer:
          x509_subject:
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string:
      password_expired: N
 password_last_changed: 2021-02-18 07:12:11
     password_lifetime: NULL
        account_locked: N
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> SELECT * FROM db WHERE user='nopsd_user'\G;
*************************** 1. row ***************************
                 Host: %
                   Db: sakila
                 User: nopsd_user
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: N
         Trigger_priv: N
1 row in set (0.00 sec)

在db表中，可以看到可以设置drop,alter等权限。

2.查看账号权限

基本语法：
SHOW GRANTS FOR user@host

mysql> SHOW GRANTS FOR nopsd_user;
+------------------------------------------------------------------------+
| Grants for nopsd_user@%                                                |
+------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'nopsd_user'@'%'                                 |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `sakila`.* TO 'nopsd_user'@'%' |
+------------------------------------------------------------------------+

host不填，默认是%

mysql> SHOW GRANTS FOR local_user;
ERROR 1141 (42000): There is no such grant defined for user 'local_user' on host '%'
mysql> SHOW GRANTS FOR local_user@'localhost';
+------------------------------------------------+
| Grants for local_user@localhost                |
+------------------------------------------------+
| GRANT USAGE ON *.* TO 'local_user'@'localhost' |
+------------------------------------------------+

mysql> SHOW GRANTS FOR nopsd_user;
+------------------------------------------------------------------------------+
| Grants for nopsd_user@%                                                      |
+------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'nopsd_user'@'%'                                       |
| GRANT SELECT, INSERT, UPDATE, DELETE, DROP ON `sakila`.* TO 'nopsd_user'@'%' |
+------------------------------------------------------------------------------+

对于MySQL5.0以后的版本，也可以通过查看information_schema数据库查看权限。

mysql> use information_schema;
mysql> SELECT * FROM SCHEMA_PRIVILEGES WHERE GRANTEE="'nopsd_user'@'%'";
+------------------+---------------+--------------+----------------+--------------+
| GRANTEE          | TABLE_CATALOG | TABLE_SCHEMA | PRIVILEGE_TYPE | IS_GRANTABLE |
+------------------+---------------+--------------+----------------+--------------+
| 'nopsd_user'@'%' | def           | sakila       | SELECT         | NO           |
| 'nopsd_user'@'%' | def           | sakila       | INSERT         | NO           |
| 'nopsd_user'@'%' | def           | sakila       | UPDATE         | NO           |
| 'nopsd_user'@'%' | def           | sakila       | DELETE         | NO           |
| 'nopsd_user'@'%' | def           | sakila       | DROP           | NO           |
+------------------+---------------+--------------+----------------+--------------+

3.更改账号权限

可以进行权限的新增和回收。权限变更有两种方法，使用GRANT(新增)和REVOKE(回收)语句
GRANT使用
前面已经使用过了

mysql> SHOW GRANTS FOR local_user@'localhost';
+------------------------------------------------+
| Grants for local_user@localhost                |
+------------------------------------------------+
| GRANT USAGE ON *.* TO 'local_user'@'localhost' |
+------------------------------------------------+
mysql> GRANT SELECT ON *.* to local_user@'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW GRANTS FOR local_user@'localhost';
+-------------------------------------------------+
| Grants for local_user@localhost                 |
+-------------------------------------------------+
| GRANT SELECT ON *.* TO 'local_user'@'localhost' |
+-------------------------------------------------+

继续给local_user@'localhost赋予SELECT,INSERT权限，和已有的SELECT权限合并。

mysql> GRANT SELECT,INSERT ON *.* TO local_user@'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW GRANTS FOR local_user@'localhost';
+---------------------------------------------------------+
| Grants for local_user@localhost                         |
+---------------------------------------------------------+
| GRANT SELECT, INSERT ON *.* TO 'local_user'@'localhost' |
+---------------------------------------------------------+
1 row in set (0.00 sec)

REVOKE使用
基本语法：
REVOKE pri_type[(column_list)][,priv_type[(column_list)]]...ON [object_type]{tab_name|*|*.*|dbname.*} FROM user[,user]...
REVOKE ALL PRIVILEGES,GRANT OPTION FROM user[,user]...

收回local_user@'localhost用户的INSERT权限。

mysql> REVOKE INSERT ON *.* FROM 'local_user'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR local_user@'localhost';
+-------------------------------------------------+
| Grants for local_user@localhost                 |
+-------------------------------------------------+
| GRANT SELECT ON *.* TO 'local_user'@'localhost' |
+-------------------------------------------------+
1 row in set (0.00 sec)

4.修改账号密码

方法一：可以用mysqladmin命令在命令行指定密码
[root@localhost bin]# mysqladmin -u local_user -h localhost password '456789'
方法二：执行set password语句
mysql> SET PASSWORD FOR 'local_user'@'localhost'=PASSWORD('123456');
方法三：在全局级别使用GRANT USAGE 语句来指定某个账户的密码而不影响账户当前的权限

mysql> GRANT USAGE ON *.* TO 'psd_user'@'%' IDENTIFIED BY '456789';
Query OK, 0 rows affected, 1 warning (0.00 sec)

方法四：密码的产生跟设置一般来说不会在同一个客户端。通过下面这种方式设置相对上面的方式会更加安全。

-- 
mysql> SELECT PASSWORD('123466');
+-------------------------------------------+
| PASSWORD('123466')                        |
+-------------------------------------------+
| *B09DB121B7DAFA9B5336BEDB0672DB999F846FA3 |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> GRANT USAGE ON *.* TO 'psd_user'@'%' IDENTIFIED BY
    -> PASSWORD '*B09DB121B7DAFA9B5336BEDB0672DB999F846FA3';
Query OK, 0 rows affected, 2 warnings (0.00 sec)

5.删除账号

要彻底删除账号，也有两种实现方法：即DROP USER命令和修改权限表。下面介绍第一种
基本语法：
DROP USER user[,user]
将用户nopsd_user删除

mysql> SHOW GRANTS FOR nopsd_user;
+------------------------------------------------------------------------------+
| Grants for nopsd_user@%                                                      |
+------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'nopsd_user'@'%'                                       |
| GRANT SELECT, INSERT, UPDATE, DELETE, DROP ON `sakila`.* TO 'nopsd_user'@'%' |
+------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> DROP USER nopsd_user;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR nopsd_user;
ERROR 1141 (42000): There is no such grant defined for user 'nopsd_user' on host '%'

二、权限系统的工作原理

对于身份的确认，MySQL是通过IP地址和用户名联合进行确认的。例如MySQL安装后默认创建的用户root@localhost表示用户root只能从本地进行连接才可以通过认证。此用户从其他任何主机对数据库进行的连接都将被拒接。
MySQL的权限表是在数据库启动的时候载入内存，当用户通过身份认证后，就在内存中进行相应权限的存取，这样用户就可以在数据库中做权限范围内的操作。

三、权限表的存取

在权限存取这两个过程，系统会用到“mysql”数据库中的user,db这两个重要的表，它们的定义如下
在这里插入图片描述
在这里插入图片描述

当用户进行连接时，权限表的存钱过程如下：

  • 先从user表中的host,user,password这3个字段中验证连接信息
  • 如果验证通过，则按照user,db,tables_priv,columns_priv的顺序得到数据库权限
    其中，user表中的每个权限代表了多所有数据库都有的权限，db表保存的是具体数据库的权限。

总结

本篇主键介绍了管理数据库账号的方法，包括账号创建，权限管理和密码的修改。

他叫阿来
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Mysql出现问题：ERROR 1141 ( 42000 (ER_NONEXISTING_GRANT)): There is no such grant defined for user 解决方案
小虚竹的专栏
04-04 4157
Mysql出现问题：ERROR 1141 ( 42000 (ER_NONEXISTING_GRANT)): There is no such grant defined for user '%s' on host '%s'解决方案
关于 ERROR 1141 (42000): There is no such grant defined for user ‘xxx‘ on host ‘yyy‘ 的一种原因
m0_47505062的博客
01-28 1万+
今天做了一个实验， mysql> select * from tables_priv; ±----------±---------±------±-----------±---------------±--------------------±-----------±------------+ | Host | Db | User | Table_name | Grantor | Timestamp | Table_priv | Column_
连接MySQL数据库时常见故障问题的分析与解决
lgxzzz的博客
12-25 4222
初学的mysql网友好象经常会碰到mysql无法连接的错误。特开贴收集这样问题的现象和原因。 先自己扔块砖头出来。 归纳如下： 故障现象 : 无法连接 mysql ============================================================================= 错误信息1 ：ERROR 1045 (28000): Access denied for user 'usera'@'localhost' (using password:YES...
代码执行时出现错误[42000][1141] There is no such grant defined for user ‘heima‘ on host ‘localhost’
csdnaking的博客
02-14 748
[42000][1141] There is no such grant defined for user 'heima' on host 'localhost'
mysql（杂）
qq_41672817的博客
03-04 341
数据库 https://blog.csdn.net/c361604199/article/details/79479398（常用） # 创建一个数据库 ：CREATE DATABASE 库名; # 创建一个表 ： CREATE TABLE 表名(id INT, name VARCHAR(10)); # 添加列 ： alter table 表名 add column 列名 v...
mysql授权管理1
08-08
总的来说，MySQL的授权管理涉及到用户创建、权限分配以及权限刷新。理解并正确应用这些命令对于管理和保护数据库系统至关重要。在进行这些操作时，务必谨慎，确保只有授权的用户才能访问敏感数据，同时也要确保关键...
JSP+mysql权限管理系统
最新发布
06-10
【JSP+MySQL权限管理系统】是一个基于Java Server Pages（JSP）技术和MySQL数据库构建的权限管理系统。这个系统的核心目标是实现对用户权限的精细化管理，确保不同用户在系统中的操作范围受到适当的限制，以保障系统...
mysql用户权限管理实例分析
09-08
MySQL用户权限管理是数据库系统中一个至关重要的环节，它确保了数据的安全性和访问控制，防止未经授权的用户访问或操作敏感信息。在MySQL中，权限管理是通过定义不同级别的权限给不同用户来实现的，以便限制或允许...
MySQL用户账户管理和权限管理深入讲解
09-09
MySQL用户账户管理和权限管理是数据库系统中至关重要的环节，确保数据安全和控制不同用户对数据库系统的访问权限。MySQL的权限体系结构精细且层次分明，主要分为五个层级：全局层级、数据库层级、表层级、列层级和子...
MySQL用户管理和用户授权详解.pdf
09-09
### 授权管理 7. **授予权限**： 可以使用`GRANT`语句为用户分配不同的权限： - **1.1 普通授权**： ```sql GRANT privileges ON database_name.table_name TO 'username'@'login_host'; ``` - **1.2 转授予...
navicat远程连接mysql出现1045问题解决！
weixin_41220970的博客
04-17 3374
Navicat连接mysql数据库报错：1045 - Access denied for user ‘root’@‘localhost’ (using password: YES)。 这是数据库权限的问题，workbench等也会同样报错 问题所在：未给root用户分配所有权限，本地客户端无权限登录root用户。 以root用户登录mysql，运行show grants for ‘user’@‘I...
《MySQL必知必会》| 查看用户权限报错：ERROR 1141 (42000): There is no such grant defined for user 'user' on host '%'
热门推荐
BaoYi的博客
10-19 1万+
ERROR 1141 (42000): There is no such grant defined for user 'user' on host '%'
Mysql常见错误分析
happyness_home的博客
08-06 532
Access denied for user: 'roota@localhost' (Using password: YES)翻译：用户 roota 访问 localhost 被拒绝（没有允许通过）分析：造成这个错误一般数据库用户名和密码相对mysql服务器不正确解决：仔细检查自己config.inc.php文件找到dbuser、dbuser、dbpw核实后重新设置保存即可。 You have ...
Mysql撤销用户权限报错 | [Err] 1141 - There is no such grant defined for user 'dbadmin' on host '%'
qq_43690482的博客
04-27 8998
在学习Mysql用户权限赋予与撤销的时候遇到了一个问题如下： 用的Navicat赋予了一个普通用户超级权限 查看权限下图 进行撤销权限时报错 网上搜索了一下，大概意识是： 在你赋予用户权限后想到撤销权限时，你赋予什么权限，就收回什么权限，当然你收回的时候可以放大，但是不能缩小。。 比如你赋予了database. 那么你收回的时候可以写all，* 修改如下成功。 ...
“撩”一下mysql权限的一个细枝但不末节
weixin_42851042的博客
06-30 492
业务需求：要对一个用户收回一个表的所有权限，但是之前对这个用户是授权表所在库的整个授权。 比如：存在库 test01 库下存在表T1 首先整库授权： grant all privilges on test01.* to test0629; > show grants for test0629; +-----------------------------------------------------+ | Grants for test0629@% ...
error 1141(4200):There is no such grant defined for user ‘xxx‘ on host‘xxx‘问题处理
niuwj666的博客
04-11 582
原因在于：在MySQL中，为了正确撤销指定表的权限，必须首先确保该用户对该数据库至少具备一定的权限。只有在用户已获得数据库表的某一层次权限后，才能对其进一步精细化管理，包括撤销特定表的权限。执行该撤销操作时遇到错误提示：“error 1141(4200): There is no such grant defined for user ‘xxx’ on host ‘xxx’”，这意味着用户’user’可以从指定的主机’host’访问数据库cs18下的所有表和视图。1、登录root账号查看user表。
MySQL常用DDL语法总结
他叫阿来
02-13 5976
文章目录前言一、DDL语句简述二、数据库操作三、表操作总结 前言 本篇主要总结MySQL的数据库操作，表操作方面的常用DDL(Data Definition Language)语句。 一、DDL语句简述 DDL是数据定义语言的缩写，简单来说就是对数据库内部的对象进行创建、删除、修改等操作的语句。它和DML(Data Manipulation Language)语句最大的区别是DML只是对表内部的数据进行操作，而不涉及表的定义、结构的修改。 二、数据库操作 -- 创建数据库 create database
mysql权限管理实例
06-03
好的，这里为您提供一个MySQL权限管理的实例。 1. 创建用户并授权 首先，我们需要创建一个用户，并授予该用户可以操作某个数据库的权限。假设我们要创建一个名为 "test_user" 的用户，并授予该用户可以操作 "test_db" 数据库的权限，可以按照以下步骤操作： - 登录MySQL，并使用管理员账号进行操作： ``` mysql -u root -p ``` - 创建用户： ``` CREATE USER 'test_user'@'localhost' IDENTIFIED BY 'password'; ``` 其中，'test_user'为用户名，'localhost'表示该用户只能从本地连接MySQL，'password'为该用户的密码。 - 授予权限： ``` GRANT ALL PRIVILEGES ON test_db.* TO 'test_user'@'localhost'; ``` 其中，'test_db.*'表示我们授予该用户可以操作 "test_db" 数据库中的所有表；'test_user'为用户名，'localhost'表示该用户只能从本地连接MySQL。 2. 查看用户权限 我们可以使用以下命令查看某个用户的权限： ``` SHOW GRANTS FOR 'test_user'@'localhost'; ``` 其中，'test_user'为用户名，'localhost'表示该用户只能从本地连接MySQL。 3. 修改用户权限 我们可以使用以下命令修改某个用户的权限： - 授予权限： ``` GRANT SELECT, INSERT ON test_db.* TO 'test_user'@'localhost'; ``` 其中，'SELECT, INSERT'表示我们授予该用户可以执行SELECT和INSERT操作；'test_db.*'表示我们授予该用户可以操作 "test_db" 数据库中的所有表；'test_user'为用户名，'localhost'表示该用户只能从本地连接MySQL。 - 撤销权限： ``` REVOKE SELECT, INSERT ON test_db.* FROM 'test_user'@'localhost'; ``` 其中，'SELECT, INSERT'表示我们撤销该用户的SELECT和INSERT操作权限；'test_db.*'表示我们撤销该用户可以操作 "test_db" 数据库中的所有表的权限；'test_user'为用户名，'localhost'表示该用户只能从本地连接MySQL。 以上就是MySQL权限管理的一个实例，您可以根据自己的实际需求进行修改和操作。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值