同样是MySQL的all privileges有啥不同？
2018-08-03 本文已影响38人
菩提老鹰
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
