MySQL用户管理和权限控制详述

最近朋友问我一些MySQL用户的问题,就做了一些测试,记录如下:

一:用户的创建(两种方法):

方法一:CREATE USER 'username'@'%' IDENTIFIED BY 'password';

方法二:GRANT select ON databasename.tablename TO 'username'@'%' ;

二:mysql root用户密码设置以及修改。

方法1: 用SET PASSWORD命令

  mysql -u root

  mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass');

方法2:用mysqladmin

  mysqladmin -u root password "newpass"

  如果root已经设置过密码,采用如下方法

  mysqladmin -u root password oldpass "newpass"

方法3: 用UPDATE直接编辑user表

  mysql -u root

  mysql> use mysql;

  mysql> UPDATE user SET Password = PASSWORD('newpass') WHERE user = 'root';

  mysql> FLUSH PRIVILEGES;

在丢失root密码的时候,可以这样

  mysqld_safe --skip-grant-tables&

  mysql -u root mysql

  mysql> UPDATE user SET password=PASSWORD("new password") WHERE user='root';

  mysql> FLUSH PRIVILEGES;

三:重点讲解创建mysql用户时,@后面的ip的意义就是为了限制登陆mysql的ip,具体有如下:

1)只允许在本地登录;

mysql> CREATE USER 'liuwenhe'@'localhost' IDENTIFIED BY 'liuwenhelocal';

Query OK, 0 rows affected (0.00 sec)

2)允许在192.168.0网段登陆mysql;

mysql> CREATE USER 'liuwenhe'@'192.168.0.%' IDENTIFIED BY 'liuwenhe0';

Query OK, 0 rows affected (0.00 sec)

3)允许在192.168.8网段登陆mysql;

mysql> CREATE USER 'liuwenhe'@'192.168.8.%' IDENTIFIED BY 'liuwenhe8';

Query OK, 0 rows affected (0.00 sec)

4)没有限制,也就是可以在任何网络段登陆(前提是网络得通);

mysql> CREATE USER 'liuwenhe'@'%' IDENTIFIED BY 'liuwenheall';

Query OK, 0 rows affected (0.00 sec)

针对上面这几个liuwenhe用户做了一些测试,结果如下:

1) 'liuwenhe'@'192.168.0.%'这类的用户是不能在本地登录的,要想在本地登录,需要有localhost或者127.0.0.1的登陆权限;

需要注意的是,如果你只创建了用户 'liuwenhe'@'localhost' ,

1.mysql> CREATE USER 'liuwenhe'@'localhost' IDENTIFIED BY 'liuwenhelocal';

Query OK, 0 rows affected (0.00 sec)

mysql> select host,user from mysql.user;

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

| host | user |

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

| % | ogg |

| % | root |

| 127.0.0.1 | root |

| 192.168.0.% | ncms |

| 192.168.0.13 | rep |

| localhost | liuwenhe |

| localhost | ncms |

| localhost | ogg |

| localhost | root |

| server01 | root |

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

10 rows in set (0.00 sec)

如下两种登陆方式都能成功:

[root@server02 ~]# mysql -uliuwenhe -pliuwenhelocal -hlocalhost

[root@server02 ~]# mysql -uliuwenhe -pliuwenhelocal -h127.0.0.1

2.如果你只创建了liuwenhe'@'l127.0.0.1',

mysql> select host,user from mysql.user;

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

| host | user |

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

| % | ogg |

| % | root |

| 127.0.0.1 | liuwenhe |

| 127.0.0.1 | root |

| 192.168.0.% | ncms |

| 192.168.0.13 | rep |

| localhost | ncms |

| localhost | ogg |

| localhost | root |

| server01 | root |

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

10 rows in set (0.00 sec)

只能通过mysql -uliuwenhe -pliuwenhelocal -h127.0.0.1登陆,不能通过 mysql -uliuwenhe -pliuwenhelocal -hlocalhost登陆;

[root@server02 ~]# mysql -uliuwenhe -pliuwenhelocal -h127.0.0.1

Warning: Using a password on the command line interface can be insecure.

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

Your MySQL connection id is 3628

Server version: 5.6.26-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, 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>

不能通过localhost登陆,如下报错:

[root@server02 ~]# mysql -uliuwenhe -pliuwenhelocal -hlocalhost

Warning: Using a password on the command line interface can be insecure.

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

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/55f54dbc5adad2963717be053aa613b3.html