Shulou(Shulou.com)05/31 Report--

This article mainly introduces the example analysis of user management and access control in mysql, which is very detailed and has a certain reference value. Friends who are interested must read it!

One: user creation (two methods):

Method one: CREATE USER 'username'@'%' IDENTIFIED BY' password'

Method 2: GRANT select ON databasename.tablename TO 'username'@'%'

Second: mysql root user password setting and modification.

Method 1: use the SET PASSWORD command

Mysql-u root

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

Method 2: use mysqladmin

Mysqladmin-u root password "newpass"

If root has already set a password, use the following methods

Mysqladmin-u root password oldpass "newpass"

Method 3: edit the user table directly with UPDATE

Mysql-u root

Mysql > use mysql

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

Mysql > FLUSH PRIVILEGES

You can do this when you lose your root password

Mysqld_safe-skip-grant-tables&

Mysql-u root mysql

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

Mysql > FLUSH PRIVILEGES

3: focus on the meaning of the ip after @ when creating mysql users: it is to limit the ip that logs in to mysql, as shown below:

1) only local login is allowed

Mysql > CREATE USER 'liuwenhe'@'localhost' IDENTIFIED BY' liuwenhelocal'

Query OK, 0 rows affected (0.00 sec)

2) allow login to mysql on 192.168.0 network segment

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

Query OK, 0 rows affected (0.00 sec)

3) allow login to mysql on 192.168.8 network segment

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

Query OK, 0 rows affected (0.00 sec)

4) there is no restriction, that is, you can log in at any network segment (as long as the network is accessible)

Mysql > CREATE USER 'liuwenhe'@'%' IDENTIFIED BY' liuwenheall'

Query OK, 0 rows affected (0.00 sec)

Some tests are done for the above liuwenhe users, and the results are as follows:

1) users such as' liuwenhe'@'192.168.0.%' cannot log in locally. To log in locally, you need to have localhost or 127.0.0.1 login rights.

It is important to note that if you only create the user '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)

The following two login methods can be successful:

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

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

two。 If you only create 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)

You can only log in through mysql-uliuwenhe-pliuwenhelocal-h227.0.0.1, not through mysql-uliuwenhe-pliuwenhelocal-hlocalhost

[root@server02] # mysql-uliuwenhe-pliuwenhelocal-h227.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 >

Unable to log in via localhost, the error is as follows:

[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)

2) if you create both 'liuwenhe'@'192.168.0.%'' and 'liuwenhe'@'%'' users, when you log in to the database from the 192.168.0 network segment, 'liuwenhe'@'%' users cannot log in to the database, they can only log in through' liuwenhe'@'192.168.0.%', but when you delete the 'liuwenhe'@'192.168.0.%' users' Liuwenhe'@'%' users can log in, which can be understood as mysql priority and can only verify users with high matching degree

The specific verification process is as follows:

Mysql > select host,user from mysql.user

+-+ +

| | host | user |

+-+ +

| |% | liuwenhe |

| |% | ogg |

| |% | root |

| | 127.0.0.1 | root |

| | 192.168.0.% | liuwenhe | |

| | 192.168.0.% | ncms | |

| | 192.168.0.13 | rep | |

| | localhost | ncms |

| | localhost | ogg |

| | localhost | root |

| | server01 | root |

+-+ +

11 rows in set (0.00 sec)

Try to log in to mysql on another machine, S244 (192.168.0.244):

Login failed with 'liuwenhe'@'%' user: as follows

[root@S244] # mysql-uliuwenhe-pliuwenheall-h292.168.0.12

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

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

Log in successfully with the 'liuwenhe'@'192.168.0.%' user, as follows:

[root@S244] # mysql-uliuwenhe-pliuwenhe0-h292.168.0.12

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 3679

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

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

When you delete the 'liuwenhe'@'192.168.0.%' user,' the liuwenhe'@'%' user can log in, as follows:

Mysql > delete from mysql.user where user='liuwenhe' and host='192.168.0.%'

Query OK, 1 row affected (0.00 sec)

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

Still try to log in to mysql using 'liuwenhe'@'%' user on another machine S244 (192.168.0.244), and it is successful:

[root@S244] # mysql-uliuwenhe-pliuwenheall-h292.168.0.12

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 3681

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

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

3) the server where my mysql resides has two ip, as follows:

Now I have created a 'liuwenhe'@'192.168.8.%''

Then we can only pass through

Mysql-uliuwenhe-pliuwenhe8-h292.168.238 login, can not log in through mysql-uliuwenhe-pliuwenhe8-h292.168.0.12, in the same way to create a 'liuwenhe'@'192.168.0.%', only through

Mysql-uliuwenhe-pliuwenhe0-h292.168.0.12 login, not via mysql-uliuwenhe-pliuwenhe0-h292.168.238

Verify as follows:

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

Query OK, 0 rows affected (0.00 sec)

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

Mysql > exit

Bye

[root@server02] # mysql-uliuwenhe-pliuwenhe0-h292.168.0.12

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 3704

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 > exit

Bye

[root@server02] # mysql-uliuwenhe-pliuwenhe0-h292.168.238

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

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

The above is all the contents of the article "sample Analysis of user Management and access Control in mysql". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.