mysql权限管理（实例）

mysql权限管理实例

本文并没有很详细的介绍对具体的对象授权，只是简单的限制了大的权限。

1.目前现状：
研发一直使用root用户进行操作。不变修改代码，因此我们采用新建一个超级用户，回收root的部分权限来实现对研发用户的限制

mysql> select user,host from user;
+-----------+---------------+
| user      | host          |
+-----------+---------------+
| mydba     | %             |  ---我新建的超级用户
| root      | %             |  ---安装就自带
| server    | %             |  ---无用用户
| repli     | 192.168.1.3   |  ---我创建的主从复制的用户
| root      | 192.168.1.9   |  ---无用用户
| mysql.sys | localhost     | 
| root      | localhost     |  ---安装就自带
+-----------+---------------+
7 rows in set (0.00 sec)


回收前，先用root进入，创建一个超级用户：
grant all privileges on *.* to mydba@'%' identified by 'tina' with grant option;
grant all privileges on mysql.* to mydba@'%' identified by 'tina' with grant option;


2.删除无用的用户：（上班时先回收所有权限，下班后再删除)
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> show grants for server;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for server@%                                                                                                                                                                                                                                                                                                     |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT 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 ON *.* TO 'server'@'%' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `mysql`.* TO 'server'@'%' WITH GRANT OPTION  

mysql> revoke all privileges on *.* from 'server'@'%';
Query OK, 0 rows affected (0.07 sec)

mysql> show grants for server;
+---------------------------------------------------------------------+
| Grants for server@%                                                 |
+---------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'server'@'%' WITH GRANT OPTION                |
| GRANT ALL PRIVILEGES ON `mysql`.* TO 'server'@'%' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> revoke all privileges on mysql.* from 'server'@'%';    ---操作会同步到从库，因此不需要到从库执行，因为主从同步的库包括mysql库
Query OK, 0 rows affected (0.10 sec)

mysql> show grants for server;
+------------------------------------------------------------+
| Grants for server@%                                        |
+------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'server'@'%' WITH GRANT OPTION       |
| GRANT USAGE ON `mysql`.* TO 'server'@'%' WITH GRANT OPTION |
+------------------------------------------------------------+
2 rows in set (0.00 sec)

删除：delete from mysql.user where user='server' and host='%';
      delete from mysql.user where user='root' and host='192.168.1.9';

3.主从复制用户权限
mysql> show grants for repli@'192.168.1.3';
+--------------------------------------------------------------------------------+
| Grants for repli@192.168.1.3                                                   |
+--------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repli'@'192.168.1.3'    |             --repli因为是用于主从复制的，因此需要这两个权限。
+--------------------------------------------------------------------------------+
1 row in set (0.01 sec)



4.回收root用户的file,process,super,drop,create 权限，依然可以创建、删除临时表

原始权限：
--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@%                                                                                                                                                                                                                                                                                                                                                 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, 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 'root'@'%' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `mysql`.* TO 'root'@'%' WITH GRANT OPTION                                                                                                                                                                                                                                                                                                 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

| Grants for root@localhost                                                                                                                                                                                                                                                                                                                                                 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, 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 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION             

回收了root远程主机的部分权限：（process权限保留了，因为要用来监控主从同步状态）
revoke file,process,super,drop,create,create view,reload, shutdown,index, alter, replication slave, replication client, create view,create routine,
alter routine, create user,create tablespace on *.* from 'root'@'%';

回收本地root对mysql库的所有权限:
revoke all privileges on mysql.* from 'root'@'localhost'; 

测试一下：
mysql> select * from t1 into outfile '/tmp/a.txt' fields terminated by ',';
ERROR 1045 (28000): Access denied for user 'root'@'%' (using password: YES)    --load的权限被禁用了

mysql> create view v_2 as select id from t2 where id<5;
ERROR 1142 (42000): CREATE VIEW command denied to user 'root'@'192.168.1.4' for table 'v_2';  ---不能创建视图

mysql> create index i_2 on t2(id);
ERROR 1142 (42000): INDEX command denied to user 'root'@'192.168.1.4' for table 't2'  --不能创建索引

mysql> show index from t1;          --可以查看索引
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1    |          1 | i_1      |            1 | id          | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.03 sec)

6.刷新权限
flush privileges;


7.回收后：
mysql> select user,host from user;
+-----------+---------------+
| user      | host          |
+-----------+---------------+
| mydba     | %             |
| root      | %             |
| repli     | 192.168.1.4 |
| mysql.sys | localhost     |
| root      | localhost     |
+-----------+---------------+
5 rows in set (0.00 sec)

mysql> show grants for root;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@%                                                                                                                                                                 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES,process ，SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'root'@'%' WITH GRANT OPTION |
| GRANT USAGE ON `mysql`.* TO 'root'@'%' WITH GRANT OPTION                                                                                                                          |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for root@'localhost';
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                                                                                 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES,process， SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                                                                              |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for mydba; --超级用户：
+--------------------------------------------------------------------+
| Grants for mydba@%                                                 |
+--------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'mydba'@'%' WITH GRANT OPTION       |
| GRANT ALL PRIVILEGES ON `mysql`.* TO 'mydba'@'%' WITH GRANT OPTION |
+--------------------------------------------------------------------+
2 rows in set (0.00 sec)


补充说明：
1.usage权限：可以连接db，可以show databases和tables，但是没有其他权限，select都不行
2.mysql的权限是会叠加的，如果第一次授予了这个用户select权限，第二次授予了全部权限，当你回收所有权限后，会发现还有select权限，必须逐条回收