《MySQL5.7用户权限---RENAME USER》
MySQL普通用户能够执行:rename user,应具备哪些条件?
MySQL版本:Oracle MySQL5.7.14
用户执行rename user:
应具备权限:1.具有全局create user权限 2.对mysql库有update权限 3.当
read_only参数enabled状态时,必须额外有supper权限。
eg:SQL语句
RENAME USER old_user TO new_user;
RENAME USER 'jeffrey'@'localhost' TO 'jeff'@'127.0.0.1';
测试用户:
test :对market库,有增删改查权限。
访问用户:
mark1:变量:create user权限,恒量:有无mysql库的update权限,其他都有。
mark2:变量:对mysql库有update权限 恒量:有无create user权限
mark3:设置系统参数read_only=ON 变量:有无super权限,其他都有。
【测试过程1:】
create:
create user mark1@'localhost' identified by 'mark1';
grants:
grant all privileges on market.* to mark1@'localhost';
revoke:
revoke super on *.* from user1@'localhost';
revoke create user on *.* from user1@'localhost';
查看当前状态:
mysql> select user,host,Create_user_priv,Super_priv from mysql.user where
user='user1';
+-------+-----------+------------------+------------+
| user | host | Create_user_priv | Super_priv |
+-------+-----------+------------------+------------+
| mark1 | localhost | N | N |
+-------+-----------+------------------+------------+
1 row in set (0.01 sec)
用mark1用户登录:
1.对mysql库,没权限
mysql> select user,host from mysql.user;
ERROR 1142 (42000): SELECT command denied to user 'mark1'@'localhost' for
table 'user'
2.rename user:没有更改权限
mysql> rename user test@'localhost' to test1@'localhost';
ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE
USER privilege(s) for this operation
3.用root登录: 添加create user 权限:
mysql> grant create user on *.* to mark1@'localhost';
4.用mark1【重新登录】:对mysql库没有访问权限。
mysql> select user,host from mysql.user;
ERROR 1142 (42000): SELECT command denied to user 'mark1'@'localhost' for
table 'user'
5.再次rename user:成功
mysql> rename user test@'localhost' to test1@'localhost';
Query OK, 0 rows affected (0.05 sec)
6.root登录:查看当前用户:
mysql> select user,host,Create_user_priv,Super_priv from mysql.user where
user='mark1' or user like '%test%';
+-------+-----------+------------------+------------+
| user | host | Create_user_priv | Super_priv |
+-------+-----------+------------------+------------+
| mark1 | localhost | Y | N |
| test1 | localhost | N | N |
+-------+-----------+------------------+------------+
2 rows in set (0.16 sec)
【测试1:结论】
【结论1】:super、create user权限属于全局权限,不能对单个库执行附权操作
【结论2】:具有create user权限,就能更改用户,即使对mysql库没有权限。
【结论3】:具有create user权限,能够ALTER USER,CREATE USER,DROP
USER, AND REVOKE ALL PRIVILEGES; --
【问题】
执行:revoke/grant super和create user权限时:只对market库回收/附权
报错:ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL
PRIVILEGES
【发现】:super,create user 属于全局权限,不能对单个库执行
【测试过程2:】
前提:mark2用户不具备create user 和super权限。
验证:mark2用户对mysql库 [没有] update权限,不能更改用户操作
验证:mark2用户对mysql库 [有] update权限,能更改用户操作
1.root登录:创建mark2:
mysql> create user mark2@'localhost' identified by 'mark2';
mysql> grant all privileges on *.* to mark2@'localhost';
mysql> flush privileges;
2.查看用户状态:全局
mysql> select user,host,Update_priv,Create_user_priv,Super_priv from
mysql.user where user='mark2' or user like '%test%';
+-------+-----------+-------------+------------------+------------+
| user | host | Update_priv | Create_user_priv | Super_priv |
+-------+-----------+-------------+------------------+------------+
| mark2 | localhost | Y | Y | Y |
| test1 | localhost | N | N | N |
+-------+-----------+-------------+------------------+------------+
3.回收update,super,create user权限
mysql> revoke update on mysql.* from 'mark2'@'localhost' ;
mysql> revoke create user,super on *.* from 'mark2'@'localhost';
4.查看当前状态:
mysql> select user,host,Update_priv,Create_user_priv,Super_priv from
mysql.user where user='mark2' or user like '%test%';
+-------+-----------+-------------+------------------+------------+
| user | host | Update_priv | Create_user_priv | Super_priv |
+-------+-----------+-------------+------------------+------------+
| mark2 | localhost | N | N | N |
| test1 | localhost | N | N | N |
+-------+-----------+-------------+------------------+------------+
2 rows in set (0.00 sec)
mysql> select Host,db,User,Update_priv from mysql.db where User='mark2';
Empty set (0.00 sec)
5. mark2登录:执行:rename user:
mysql> rename user test1@'localhost' to test2@'localhost';
ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE
USER privilege(s) for this operation
6.root登录:附权:对mysql库的update操作权限
mysql> grant update on mysql.* to mark2@'localhost';
mysql> flush privileges;
7. 查看mark2当前权限:
mysql> show grants for mark2@'localhost'; #具备update权限
查看用户状态:全局
mysql> select user,host,Update_priv,Create_user_priv,Super_priv from
mysql.user where user='mark2' or user like '%test%';
+-------+-----------+-------------+------------------+------------+
| user | host | Update_priv | Create_user_priv | Super_priv |
+-------+-----------+-------------+------------------+------------+
| mark2 | localhost | N | N | N |
| test1 | localhost | N | N | N |
+-------+-----------+-------------+------------------+------------+
查看用户状态:某个库:具备update权限
mysql> select Host,db,User,Update_priv from mysql.db where User='mark2';
+-----------+-------+-------+-------------+
| Host | db | User | Update_priv |
+-----------+-------+-------+-------------+
| localhost | mysql | mark2 | Y |
+-----------+-------+-------+-------------+
8.更改用户:test1--->test2
mysql> rename user test1@'localhost' to test2@'localhost';
Query OK, 0 rows affected (0.05 sec)
mysql> select user,host,Update_priv,Create_user_priv,Super_priv from
mysql.user where user='mark2' or user like '%test%';
+-------+-----------+-------------+------------------+------------+
| user | host | Update_priv | Create_user_priv | Super_priv |
+-------+-----------+-------------+------------------+------------+
| mark2 | localhost | N | N | N |
| test2 | localhost | N | N | N |
+-------+-----------+-------------+------------------+------------+
9.测试验证
mysql> rename user test2@'localhost' to test2@'127.0.0.1';
Query OK, 0 rows affected (0.06 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.06 sec)
mysql> alter user test2@'127.0.0.1' identified by 'test222';
Query OK, 0 rows affected (0.36 sec)
mysql> create user test3@'localhost' identified by 'test3';
Query OK, 0 rows affected (0.03 sec)
mysql> drop user test3@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql> select user,host,Update_priv,Create_user_priv,Super_priv from
mysql.user where user='mark2' or user like '%test%';
+-------+-----------+-------------+------------------+------------+
| user | host | Update_priv | Create_user_priv | Super_priv |
+-------+-----------+-------------+------------------+------------+
| mark2 | localhost | N | N | N |
| test2 | 127.0.0.1 | N | N | N |
+-------+-----------+-------------+------------------+------------+
10.验证下mark2是否可以回收test2的权限
root登录:
mysql> select user,host,Update_priv,Create_user_priv,Super_priv from
mysql.user where user like '%test%';
+-------+-----------+-------------+------------------+------------+
| user | host | Update_priv | Create_user_priv | Super_priv |
+-------+-----------+-------------+------------------+------------+
| test2 | 127.0.0.1 | Y | Y | Y |
+-------+-----------+-------------+------------------+------------+
mark2登录:
mysql> revoke super on *.* from test2@'127.0.0.1';
ERROR 1045 (28000): Access denied for user 'mark2'@'localhost' (using
password: YES)
【测试2:结论】
【结论1】:对单个库的权限更改,体现在mysql.db表中,全局属性的更改体现在
mysql.user表中
【结论2】:没有create user权限,有对mysql库的update权限,依然能更改用户
【结论3】:有mysql库的update权限,等同于有create user 权限的(alter user
,create user,drop user,rename user),至于revoke all privileges 权限,
初步验证不能回收。
【测试过程3:】
验证:read_only=ON 时,具有super权限可以rename user:
影响条件只有super:
1.创建用户回收权限:
mysql> create user mark3@'localhost' identified by 'mark3';
mysql> grant all privileges on *.* to mark3@'localhost';
mysql> revoke super on *.* from mark3@'localhost';
mysql> select user,host,Update_priv,Create_user_priv,Super_priv from
mysql.user where user='mark3' or user ='test2' ;
+-------+-----------+-------------+------------------+------------+
| user | host | Update_priv | Create_user_priv | Super_priv |
+-------+-----------+-------------+------------------+------------+
| mark3 | localhost | Y | Y | N |
| test2 | 127.0.0.1 | Y | Y | Y |
+-------+-----------+-------------+------------------+------------+
2.root登录:设置read_only=ON
mysql> set global read_only = ON ;
mysql> show variables like 'read_only%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | ON |
+---------------+-------+
3.【重新登录】mark3:
mysql> rename user test2@'127.0.0.1' to test2@'localhost';
mysql> rename user test2@'127.0.0.1' to test2@'localhost';
ERROR 1290 (HY000): The MySQL server is running with the --read-only
option so it cannot execute this statement
4.root登录: 附权;super权限
mysql> grant super on *.* to mark3@'localhost';
mysql> select user,host,Update_priv,Create_user_priv,Super_priv from
mysql.user where user='mark3' or user ='test2' ;
+-------+-----------+-------------+------------------+------------+
| user | host | Update_priv | Create_user_priv | Super_priv |
+-------+-----------+-------------+------------------+------------+
| mark3 | localhost | Y | Y | Y |
| test2 | 127.0.0.1 | Y | Y | Y |
+-------+-----------+-------------+------------------+------------+
2 rows in set (0.00 sec)
5.【重新登录】mark3:rename user
操作成功:
mysql> rename user test2@'127.0.0.1' to test2@'localhost';
Query OK, 0 rows affected (0.06 sec)
【测试3:结论】
【结论1】:read_only=ON时,具备super权限,是rename user的前提条件。
【备注:阅读MySQL5.7官方文档时对rename user权限的测试实践】