mysql用户授权与权限撤销

mysql用户授权与权限撤销


实验练习:


1.允许root从192.168.4.0/24网段访问,对所有库/表有完全控制权限,需要验证的密码为xujunxian。


2.建立一个管理账号dba007,对所有库完全控制,并赋予其授权的权限。


3.撤销root从本机访问的权限,然后恢复。


4.允许webuser从任意客户机登录,只对webdb库有完全权限,密码为 888888。


5.撤销webuser的完全权限,改为查询权限。



实验方案:


  使用2台RHEL 6.4虚拟机,如图-1所示。其中192.168.4.10是MySQL服务器,授权及撤销操作均在此服务器上执行;而192.168.4.120作为测试客户机,需要安装好MySQL-client软件包,以便提供mysql命令。


同时,MySQL服务器本身(192.168.4.10)也可以作为测试客户机。



1.用户授权及撤销的基本操作


1)认识GRANT授权的指令用法


格式:

mysql> GRANT 权限列表.. ON 库名.表名 TO '用户名'@'客户端地址' [ IDENTIFIED BY '密码' ] [ WITH GRANT OPTION ];



2)验证不提供IDENTIFIED BY、访问权限控制


 授予用户nopass可匿名查询test库里的所有表,可使用CREATE指令:


mysql> GRANT select,create ON test.* TO nopass@localhost;

Query OK, 0 rows affected (0.04 sec)



# mysql -u nopass //以用户nopass登入测试,不需要密码


Welcome to the MySQL monitor. Commands end with ; or \g.



mysql>


用户nopass只能看到test库和系统库information_schema,其他库(mysql等)将不可见:


mysql> SHOW DATABASES; //其他的数据库不可见

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

| Database |

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

| information_schema |

| test |

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

2 rows in set (0.15 sec)



 用户nopass也可以列出test库中的所有表:


//test库中的表权限不收影响,可查、建、删

mysql> SHOW TABLES;

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

| Tables_in_test |

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

| biao01 |

| biao02 |

| gz |

| stu_info |


用户nopass可以使用SELECT语句:


mysql> SELECT * FROM stu_info LIMIT 3; //可以查看

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

| name | gender | age |

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

| Jim | girl | 24 |

| Tom | boy | 21 |

| Lily | girl | 20 |

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



用户nopass可以使用CREATE语句:



mysql>CREATE TABLE mytable(id int(4));//可以创建


Query OK, 0 rows affected (0.10 sec)


3)验证IDENTIFIED BY访问密码


 修改前一步中用户nopass从localhost访问的授权,设置密码为123123:


mysql> GRANT select,create ON test.* TO nopass@localhost

-> IDENTIFIED BY '123123'; //设置密码123123

Query OK, 0 rows affected (0.00 sec)


重新以nopass用户登录mysql> 环境,不提供密码时将被拒绝:


# mysql -u nopass

.ERROR 1045 (28000): Access denied for user 'nopass'@'localhost' (using password: NO)//不提供密码将会拒绝


只有正确提供密码123123,才能够成功登入:

[root@dbsvr1 ~]# mysql -u nopass -p

Enter password:                                 //输入密码123123

Welcome to the MySQL monitor. Commands end with ; or \g.


4)验证提供WITH GRANT OPTION


  授予用户rooty可查询、更新test库里的所有表,密码设置为pwd123,允许此用户将权限转授给其他用户:

mysql> GRANT select,update ON test.* TO rooty@localhost

-> IDENTIFIED BY 'pwd123'

-> WITH GRANT OPTION;

Query OK, 0 rows affected (0.00 sec)


 以用户rooty登入测试


# mysql -u rooty -p

Enter password:                                 //输入密码pwd123


mysql>show grants ;//查看当前登录用户的权限

mysql>show grants for rooty@"localhost";  //查看授权用户的权限

           会看到有grant字样



5)查看指定用户的授权


mysql> SHOW GRANTS FOR nopass@localhost;             //查看指定用户nopass的权限


| GRANT USAGE ON *.* TO 'nopass'@'localhost' IDENTIFIED BY PASSWORD '*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1' |

| GRANT SELECT, UPDATE, CREATE ON `test`.* TO 'nopass'@'localhost' |




mysql> SHOW GRANTS FOR rooty@localhost;             //查看rooty的权限

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

| Grants for rooty@localhost |

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

| GRANT USAGE ON *.* TO 'rooty'@'localhost' IDENTIFIED BY PASSWORD '*353C33BC20A4B4B2281F3DAAE901DBD0A5224E24' |

| GRANT SELECT, UPDATE ON `test`.* TO 'rooty'@'localhost' WITH GRANT OPTION |



  所有用户都可执行SHOW GRANTS来查看自己的权限。


 比如,root用户查看自己的权限:


mysql> SHOW GRANTS;

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

| Grants for root@localhost |

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

| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*6A7A490FB9DC8C33C2B025A91737077A7E9CC5E5' WITH GRANT OPTION |

| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |

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

2 rows in set (0.00 sec)



普通用户没有权限查看其它用户的权限


 例:

mysql> SHOW GRANTS FOR nopass@localhost;

ERROR 1044 (42000): Access denied for user 'rooty'@'localhost' to database 'mysql'


//普通用户没有权限查看其它用户的权限



6)撤销指定用户的授权


撤销用户nopass对test库的所有权限:

mysql> SHOW GRANTS FOR nopass@localhost;

//撤销之前先查看下都有哪些权限


mysql> REVOKE all ON test.* FROM nopass@localhost;

Query OK, 0 rows affected (0.04 sec)//撤销指定用户的权限


确认撤销结果,仍会保留用户nopass,但对所有库仅有基本的USAGE权限:

mysql> SHOW GRANTS FOR nopass@localhost//确认撤销结果

GRANT USAGE ON *.* TO 'nopass'@'localhost' IDENTIFIED BY PASSWORD '*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1' |



2.完成任务要求的授权相关操作



1)允许root从192.168.4.0/24访问,对所有库表有完全权限,密码为tarena。

授权之前,从192.168.4.0/24网段的客户机访问时,将会被拒绝:

[root@host120 ~]# mysql -u root -p -h 192.168.4.10

Enter password:                                 //输入正确的密码

ERROR 1130 (HY000): Host '192.168.4.120' is not allowed to connect to this MySQL server

授权操作,此处可设置与从localhost访问时不同的密码:

mysql> GRANT all ON *.* TO root@'192.168.4.%' IDENTIFIED BY 'tarena';

Query OK, 0 rows affected (0.00 sec)

再次从192.168.4.0/24网段的客户机访问时,输入正确的密码后可登入:

[root@host120 ~]# mysql -u root -p -h 192.168.4.10

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 8

Server version: 5.6.15 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its

.affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

从网络登入后,测试新建一个库、查看所有库:

mysql> CREATE DATABASE rootdb;                 //创建新库rootdb

Query OK, 1 row affected (0.06 sec)

mysql> SHOW DATABASES;

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

| Database |

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

| information_schema |

| home |

| mydb |

.| mysql |

| performance_schema |

| rootdb |                     //新建的rootdb库

| test |

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

7 rows in set (0.08 sec)

2)建立一个管理账号dba007,对所有库完全控制,并赋予其授权的权限

新建账号并授权:

mysql> GRANT all ON *.* TO dba007@localhost

.-> IDENTIFIED BY '1234567'

-> WITH GRANT OPTION;

Query OK, 0 rows affected (0.00 sec)

查看dba007的权限:

mysql> SHOW GRANTS FOR dba007@localhost;

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

| Grants for dba007@localhost |

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

.| GRANT ALL PRIVILEGES ON *.* TO 'dba007'@'localhost' IDENTIFIED BY PASSWORD '*6A7A490FB9DC8C33C2B025A91737077A7E9CC5E5' WITH GRANT OPTION |

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

1 row in set (0.00 sec)

3)撤销root从本机访问的权限,然后恢复

撤销root对数据库的操作权限:

特别提示:如果没有事先建立其他管理账号,请不要轻易撤销root用户的本地访问权限,否则恢复起来会比较困难,或者不得不重装数据库。

mysql> REVOKE all ON *.* FROM root@localhost;

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR root@localhost;

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

| Grants for root@localhost |

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

| GRANT USAGE ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*6A7A490FB9DC8C33C2B025A91737077A7E9CC5E5' WITH GRANT OPTION |

| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |

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

2 rows in set (0.00 sec)

验证撤销后的权限效果:

mysql> exit                                     //退出当前MySQL连接

Bye

[root@dbsvr1 ~]# mysql -u root -p                 //重新以root从本地登入

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 6

Server version: 5.6.15 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE newdb2014;                 //尝试新建库失败

ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'newdb2014'

mysql> DROP DATABASE rootdb;                     //尝试删除库失败

ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'rootdb'

尝试以当前的root用户恢复权限,也会失败(无权更新授权表):

mysql> GRANT all ON *.* TO root@localhost IDENTIFIED BY '1234567';

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

怎么办呢?

退出当前MySQL连接,以上一步添加的管理账号dba007登入:

mysql> exit                                     //退出当前MySQL连接

Bye

[root@dbsvr1 ~]# mysql -u dba007 -p             //以另一个管理账号登入

.Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 7

Server version: 5.6.15 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

.owners.

.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

由管理账号dba007重新为root添加本地访问权限:

mysql> GRANT all ON *.* TO root@localhost IDENTIFIED BY '1234567';

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR root@localhost;             //查看恢复结果

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

| Grants for root@localhost |

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

.| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*6A7A490FB9DC8C33C2B025A91737077A7E9CC5E5' WITH GRANT OPTION |

| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |

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

2 rows in set (0.00 sec)

退出,再重新以root登入,测试一下看看,权限又恢复了吧:

mysql> exit                                     //退出当前MySQL连接

Bye

[root@dbsvr1 ~]# mysql -u root -p                 //重新以root登入

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 8

Server version: 5.6.15 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

.

.Oracle is a registered trademark of Oracle Corporation and/or its

.affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

.mysql> CREATE DATABASE newdb2014;                 //成功创建新库

Query OK, 1 row affected (0.00 sec)

4)允许webuser从任意客户机登录,只对webdb库有完全权限,密码为 888888

添加授权:

1.mysql> GRANT all ON webdb.* TO webuser@'%' IDENTIFIED BY '888888';

2.Query OK, 0 rows affected (0.00 sec)

查看授权结果:

mysql> SHOW GRANTS FOR webuser@'%';

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

| Grants for webuser@% |

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

| GRANT USAGE ON *.* TO 'webuser'@'%' IDENTIFIED BY PASSWORD '*DA28842831B3C40F4BC1D3C76CF9AD8CBFDAE1CB' |

| GRANT ALL PRIVILEGES ON `webdb`.* TO 'webuser'@'%' |

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

.2 rows in set (0.00 sec)

4)撤销webuser的完全权限,改为查询权限

撤销所有权限:

1.mysql> REVOKE all ON webdb.* FROM webuser@'%';

2.Query OK, 0 rows affected (0.03 sec)

只赋予查询权限:

mysql> GRANT select ON webdb.* TO webuser@'%';

Query OK, 0 rows affected (0.00 sec)

确认授权更改结果:

mysql> SHOW GRANTS FOR webuser@'%';

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

| Grants for webuser@% |

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

.| GRANT USAGE ON *.* TO 'webuser'@'%' IDENTIFIED BY PASSWORD '*DA28842831B3C40F4BC1D3C76CF9AD8CBFDAE1CB' |

| GRANT SELECT ON `webdb`.* TO 'webuser'@'%' |

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

2 rows in set (0.00 sec)




实验总结:

1、授权时,当授权的用户不存在时,会自动创建此用户的记录。

其中,IDENTIFIED BY是可选的(一般会提供),若不提供则无密码即可登录;WITH GRANT OPTION也是可


选的(一般不提供),若提供则用户可将拥有的权限转授给其他用户,不提供则无权转授。



2、注意:MYSQL用户授权、撤销等管理操作应尽量由root负责,不要交给普通用户。


3、权限撤销 revoke all on 数据名 ... 不能把grant option授权权限撤销掉,要删除授权权限还要用revoke grant option on 数据名...


4、撤销所有权限之后的用户还是可以登录的,要禁止没有任何权限的用户登录,要删除用户


  用命令:delete from mysql.user where user="用户名";


5、删除用户之后要刷新权限,用命令:flush privileges;


6总结完全撤销某用户权限的步骤:

  1)先查看用户所拥有的权限:show grants for 用户名@“地址段”;

  2)再撤销权限:revoke all on 数据名 from 用户名@“地址段”;

  3)撤销后再去除grant 权限:revoke grant option on数据名 from 用户名@“地址段” ;

  4)再删除用户:delete  from  mysql.user where user=“用户名“ ;

  5)最后刷新权限:flush privileges;




7、特别提示:如果没有事先建立其他管理账号,请不要轻易撤销root用户的本地访问权限,否则恢复起来会比较困难,或者不得不重装数据库。






      本文转自Jx战壕  51CTO博客,原文链接:http://blog.51cto.com/xujpxm/1384783,如需转载请自行联系原作者



上一篇:MogileFS安装配置实战


下一篇:【阿里云镜像】使用阿里巴巴开源镜像站镜像——Kubernetes 镜像