mysql all_同样是MySQL的all privileges有啥不同？

最新推荐文章于 2024-06-18 12:51:48 发布
最新推荐文章于 2024-06-18 12:51:48 发布
阅读量609 收藏
点赞数
文章标签： mysql all
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_39860123/article/details/113115625
版权

db.* 和 . 上面的all privileges 有啥不一样。

咱当兵的人，有啥不一样...(一起唱)

首先安装MySQL启动

root@pts/0 $ wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm

--2018-08-02 18:13:58-- http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm

Resolving repo.mysql.com (repo.mysql.com)... 23.36.193.224

Connecting to repo.mysql.com (repo.mysql.com)|23.36.193.224|:80... connected.

HTTP request sent, awaiting response... 200 OK

Length: 6140 (6.0K) [application/x-redhat-package-manager]

Saving to: ‘mysql-community-release-el7-5.noarch.rpm’

100%[====================================================================================================================================================================================================================================>] 6,140 16.6KB/s in 0.4s r

2018-08-02 18:13:59 (16.6 KB/s) - ‘mysql-community-release-el7-5.noarch.rpm’ saved [6140/6140]

vpc-devops-ossimport [~] 2018-08-02 18:13:59

root@pts/0 $ rpm -ivh mysql-community-release-el7-5.noarch.rpm

Preparing... ################################# [100%]

Updating / installing...

1:mysql-community-release-el7-5 ################################# [100%]

vpc-devops-ossimport [~] 2018-08-02 18:14:04

root@pts/0 $ yum install mysql-community-server mysql-community-client mysql-community-devel

Loaded plugins: fastestmirror, priorities

Loading mirror speeds from cached hostfile

mysql-connectors-community | 2.5 kB 00:00:00

mysql-tools-community | 2.5 kB 00:00:00

mysql56-community

root@pts/0 $ systemctl enable mysqld

vpc-devops-ossimport [~] 2018-08-02 18:15:18

root@pts/0 $ systemctl start mysqld

vpc-devops-ossimport [~] 2018-08-02 18:15:30

root@pts/0 $ ps -ef|grep msyql

root 1112 630 0 18:15 pts/0 00:00:00 grep --color msyql

vpc-devops-ossimport [~] 2018-08-02 18:15:44

root@pts/0 $ ps -ef|grep mysql

mysql 898 1 0 18:15 ? 00:00:00 /bin/sh /usr/bin/mysqld_safe --basedir=/usr

mysql 1079 898 1 18:15 ? 00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock

root 1120 630 0 18:15 pts/0 00:00:00 grep --color mysql

初始化数据库登录。看到三个系统默认的数据库 和 初始的账号情况

mysql> show databases ;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

+--------------------+

3 rows in set (0.00 sec)

mysql> use mysql

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select user,host from mysql.user ;

+------+----------------------+

| user | host |

+------+----------------------+

| root | 127.0.0.1 |

| root | ::1 |

| | localhost |

| root | localhost |

| | vpc-devops-ossimport |

| root | vpc-devops-ossimport |

+------+----------------------+

6 rows in set (0.00 sec)

验证过程

平时创建账号的时候，可以分为两大类，一类是业务系统的账号，基于具体的数据库上面做的操作。一类是管理员账号，会涉及到 像 mysql、information_schema、performance_schema 用户做统计、分析等

所以创建一个新的数据库。模拟业务数据库。

mysql> create database devops ;

Query OK, 1 row affected (0.00 sec)

分别创建基于“业务”和基于“管理员”的所有权限“all privileges”

grant all privileges

mysql> grant all privileges on devops.* to ops1@'%' identified by 'devops1';

Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on devops.* to ops2@'%' identified by 'devops2' with grant option;

Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to ops3@'%' identified by 'devops3';

Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to ops4@'%' identified by 'devops4' with grant option ;

Query OK, 0 rows affected (0.00 sec)

check all privileges

mysql> show grants for ops1;

+-----------------------------------------------------------------------------------------------------+

| Grants for ops1@% |

+-----------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'ops1'@'%' IDENTIFIED BY PASSWORD '*52048CCECC477DB7138C2CBCF04AAD3E0397A913' |

| GRANT ALL PRIVILEGES ON `devops`.* TO 'ops1'@'%' |

+-----------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

mysql> show grants for ops2;

+-----------------------------------------------------------------------------------------------------+

| Grants for ops2@% |

+-----------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'ops2'@'%' IDENTIFIED BY PASSWORD '*C6ADDF202AF316082C3193C296860A468B4B87B4' |

| GRANT ALL PRIVILEGES ON `devops`.* TO 'ops2'@'%' WITH GRANT OPTION |

+-----------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

mysql> show grants for ops3;

+--------------------------------------------------------------------------------------------------------------+

| Grants for ops3@% |

+--------------------------------------------------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'ops3'@'%' IDENTIFIED BY PASSWORD '*8FE43EF11171F6BD1E6B6DEF0B70B72B40698D43' |

+--------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> show grants for ops4;

+--------------------------------------------------------------------------------------------------------------------------------+

| Grants for ops4@% |

+--------------------------------------------------------------------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'ops4'@'%' IDENTIFIED BY PASSWORD '*A5CC13BEC1112C49147BE1FABD75849ECD2647A4' WITH GRANT OPTION |

+--------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

分析

从上面看到大家显示的都会all privilges，实际看不出来什么，所以我们可以反向考虑。我回收一个基本的select 权限。看看剩余的权限都有哪些。

为啥这样呢。可以把all privileges 看成一个整体，拿走一个就不是整体了那就会把其余的全部列出来展现。

revoke select

mysql> revoke select on devops.* from 'ops1'@'%' ;

Query OK, 0 rows affected (0.00 sec)

mysql> revoke select on devops.* from 'ops2'@'%' ;

Query OK, 0 rows affected (0.00 sec)

mysql> revoke select on *.* from 'ops3'@'%' ;

Query OK, 0 rows affected (0.00 sec)

mysql> revoke select on *.* from 'ops4'@'%' ;

Query OK, 0 rows affected (0.00 sec)

check all privileges again

mysql> show grants for ops1;

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Grants for ops1@% |

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'ops1'@'%' IDENTIFIED BY PASSWORD '*52048CCECC477DB7138C2CBCF04AAD3E0397A913' |

| GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `devops`.* TO 'ops1'@'%' |

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

mysql> show grants for ops2;

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Grants for ops2@% |

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'ops2'@'%' IDENTIFIED BY PASSWORD '*C6ADDF202AF316082C3193C296860A468B4B87B4' |

| GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `devops`.* TO 'ops2'@'%' WITH GRANT OPTION |

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

mysql> show grants for ops3;

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Grants for ops3@% |

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| GRANT INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'ops3'@'%' IDENTIFIED BY PASSWORD '*8FE43EF11171F6BD1E6B6DEF0B70B72B40698D43' |

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> show grants for ops4;

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Grants for ops4@% |

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| GRANT INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'ops4'@'%' IDENTIFIED BY PASSWORD '*A5CC13BEC1112C49147BE1FABD75849ECD2647A4' WITH GRANT OPTION |

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

整理对比

因为上面四种情况最终的显示不是很对应。这里认为整理下如下

## 基于业务的 all privileges

SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER

## 基于管理员的all privileges

SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER, RELOAD, SHUTDOWN, PROCESS, FILE, SHOW DATABASES, SUPER, REPLICATION SLAVE, REPLICATION CLIENT, CREATE USER, CREATE TABLESPACE

结论

对比之后发现，基于管理员(.) 的所有权限比基于业务库(somedb.*) 上的所有权限多出了一下权限

RELOAD, SHUTDOWN, PROCESS, FILE, SHOW DATABASES, SUPER, REPLICATION SLAVE, REPLICATION CLIENT, CREATE USER, CREATE TABLESPACE

weixin_39860123
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL 用户和权限管理命令汇总
-
09-04 1624
一、创建用户 CREATE USER ‘user_name’@‘host’ IDENTIFIED BY ‘password’； 各项参数解析： 各项参数解析 例子展示 user_name:用户名 create user "newuser"@"localhost" indentifed by "abc123" 创建只能在本机登录的用户名为newuser的用户账号，这个用户的密码为abc123 host：这个用户在哪台机中登陆，只允许从本机登录则填写“localhost”，如允许远程
mysql.rar_linux mysql_linux mysql_mysql linux
07-13
GRANT ALL PRIVILEGES ON app_db.* TO 'app_user'@'%'; FLUSH PRIVILEGES; ``` 别忘了将`app_user_password`替换为安全的密码，并根据需要调整访问权限。 至此，我们已涵盖了在Linux系统中安装MySQL、设置用户、...
MySQL权限详解
joshua317的博客
09-09 2261
本文为joshua317原创文章,转载请注明：转载自joshua317博客https://www.joshua317.com/article/55 MySQL提供了哪些权限 MySQL提供的权限列表如图所示（其中，All或者Allprivileges代表权限列表中除Grant option权限之外的所有权限）。 在上图所示的权限列表中，Context字段显示了该权限的使用环境（或者叫权限的作用域）。根据Context字段内容的不同，权限分为如下三类。 按照系统权限划分 1、管理权.
mysql中all privileges包含哪些权限
sylalak123的博客
08-04 1万+
mysql> grant all privileges on test.* to 'ybb'@'%' identified by 'ybb'; Query OK, 0 rows affected (0.07 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> select host,user,password from mysql.user where user='ybb' and host='%';
MySQL权限USAGE和ALL PRIVILEGES的含义和用法
最新发布
jkzyx123的博客
06-18 578
MySQL权限USAGE和ALL PRIVILEGES的含义和用法
mysql all privileges_mysql中all privileges包含哪些权限
weixin_32571169的博客
01-19 1432
mysql> grant all privileges on test.* to 'ybb'@'%' identified by 'ybb';Query OK, 0 rows affected (0.07 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)mysql> select host,user,...
mysql all privilege权限
lucky404的博客
03-03 3885
授权用户权限是 all privilege。这个all privilege 都有哪些权限？ all privilege 权限如下： insert (插入数据) select (查询数据) update (更新表的数据) delete (删除表中数据) create (创建库，表) drop （删除库，表） refernces index （建立索引） alter （更改表...
mysql.rar_MYSQL_linux mysql
09-21
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost'; FLUSH PRIVILEGES; ``` 六、备份与恢复 1. 备份数据库： ```bash mysqldump -u root -p database_name > backup.sql ``` 2. 恢复数据库： ```...
mysql_linux.rar_linux mysql_linux 数据库
09-21
GRANT ALL PRIVILEGES ON mydatabase.* TO 'newuser'@'localhost'; FLUSH PRIVILEGES; ``` 在实际工作中，你可能还需要关注性能优化、日志管理、复制与集群、安全策略等高级话题。确保定期更新MySQL到最新版本，...
MySQL基础_MYSQL_usuallydss_
09-29
MySQL提供了适用于不同操作系统的安装包。安装完成后，你需要配置服务器，包括设置root用户的密码、端口、数据存储位置等。 ## 2. SQL简介 SQL（Structured Query Language）是用于管理关系数据库的标准语言。...
mysql.rar_MYSQL_linux c mysql_linux mysql_mysql linux_mysql 操作
09-23
GRANT ALL PRIVILEGES ON mydatabase.* TO 'myuser'@'localhost' IDENTIFIED BY 'mypassword'; FLUSH PRIVILEGES; ``` 这里创建了一个名为`mydatabase`的数据库，并赋予名为`myuser`的用户所有权限。 6. **数据...
MySQL用户权限及管理
人走茶良的博客
01-15 446
一、mysql创建用户 　　1、创建用户 mysql> create user test@'%' identified by '123456'; 创建了用户，但是没有任何权限 Query OK, 0 rows affected (0.00 sec) mysql> grant all on *.* to root@'localhost' identified by '123456'; 没有用户，则创建用户，用户存在，就赋予权限 Query OK,
mysql授予用户新建数据库的权限
lxw1844912514的博客
04-13 1万+
好久不用mysql了，今天拾起来，新建用户，用Navicat连接之后，发现没有新建数据库的权限。找了好久才找到方法，那就是新建用户之后授权的的时候授予用户在所有数据库上的所有权限，（当然有可能有别的方法，希望知道的前辈们指导我）语句如下： #后面的密码123456是demo用户的密码,不是root用户的密码,如果有同名的用户,会修改demo用户的密码 grant all privileges...
mysql grant 用户权限
DN_XIAOXIAO的博客
04-08 6650
mysql grant 用户权限总结 用户权限管理主要有以下作用： 1. 可以限制用户访问哪些库、哪些表 2. 可以限制用户对哪些表执行SELECT、CREATE、DELETE、DELETE、ALTER等操作 3. 可以限制用户登录的IP或域名 4. 可以限制用户自己的权限是否可以授权给别的用户 一、用户授权 mysql> grant all privileges on *.* to 'yangxin'@'%' identified by 'yangxin123456' with grant op
mysql 授权 all_mysql使用grant ALL privileges命令给用户授权
weixin_32848231的博客
02-07 7432
mysql用户想连到mysql,需要使用grant命令给用户授权,有一个简单的办法,可以使用grant ALL privileges 命令给用户授予所有权限, 你想myuser使用mypassword从任何主机连接到mysql服务器的话。主要使用:GRANT ALL PRIVILEGES ON *.* to 'root'@'%'; 命令授权.--grant给root使用root密码从任何主机连接到...
mysql grant 用户权限总结
热门推荐
anzhen0429的博客
10-20 11万+
 用户权限管理主要有以下作用： 1. 可以限制用户访问哪些库、哪些表 2. 可以限制用户对哪些表执行SELECT、CREATE、DELETE、DELETE、ALTER等操作 3. 可以限制用户登录的IP或域名 4. 可以限制用户自己的权限是否可以授权给别的用户 一、用户授权 mysql> grant all privileges on *.* to 'yangxin'@'
mysql all privileges
weixin_30552635的博客
06-26 112
mysql赋予全部权限注意事项：grant all privileges on *.* to 'userName'@localhost identified by 'password';要注意的是：grant all privilege并不能把“grant” 的权限赋给用户，如果要加，可以直接在mysql中使用use mysql; update user set Grant_priv ='Y'来达...
mysql grant all privileges_Mysql详解
05-14
其中，privileges表示要授予的权限，可以是ALL PRIVILEGES或者具体的操作权限，如SELECT、INSERT、UPDATE等；database.table表示要授权的数据库和表名，使用*表示所有数据库和表；user@host表示要授权的用户和主机名...

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值