Shulou(Shulou.com)06/01 Report--

-- December 10, 2014

Yesterday, according to the needs of the developer, several users were created, but an error was reported when authorizing:

ERROR 1044 (42000): Access denied for user 'root'@'%' to database' mdm'

After searching the Internet, there are probably the following statements:

1. Insufficient permissions to the database.

2. Parameter problem.

3. There is a null record in the user column of user.

Basically, the above solutions need to restart MySQL, and they are all one-sided.

Here is an explanation of why this happens.

First of all, the root user is used to operate here, so the permissions on this database are absolutely the highest, but if it is a non-root user operation, it may be a matter of permissions, which requires root user authorization.

It is very possible that the above error occurred. In fact, it should be said that it is basically because there is a skip-name-resolve parameter in the my.cnf file, which leads to the inability to parse hostname or other login methods, so login any user, do not use root@'localhost', or 127.0.0.1 or:: 1, but keep on using root@'%'. when matching.

Let's take a look at the specific permissions of root users in each mode:

Mysql > select * from user\\ G

* * 1. Row *

Host: bidevedw\\ _ db

User: root

Password: * D013A4E3A5BB01E4239D18D7E93B59B7D2B767AD

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: Y

Drop_priv: Y

Reload_priv: Y

Shutdown_priv: Y

Process_priv: Y

File_priv: Y

Grant_priv: Y

References_priv: Y

Index_priv: Y

Alter_priv: Y

Show_db_priv: Y

Super_priv: Y

Create_tmp_table_priv: Y

Lock_tables_priv: Y

Execute_priv: Y

Repl_slave_priv: Y

Repl_client_priv: Y

Create_view_priv: Y

Show_view_priv: Y

Create_routine_priv: Y

Alter_routine_priv: Y

Create_user_priv: Y

Event_priv: Y

Trigger_priv: Y

Create_tablespace_priv: Y

Ssl_type:

Ssl_cipher:

X509_issuer:

X509_subject:

Max_questions: 0

Max_updates: 0

Max_connections: 0

Max_user_connections: 0

Plugin:

Authentication_string:

Password_expired: N

* 2. Row * *

Host: 1

User: root

Password: * D013A4E3A5BB01E4239D18D7E93B59B7D2B767AD

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: Y

Drop_priv: Y

Reload_priv: Y

Shutdown_priv: Y

Process_priv: Y

File_priv: Y

Grant_priv: Y

References_priv: Y

Index_priv: Y

Alter_priv: Y

Show_db_priv: Y

Super_priv: Y

Create_tmp_table_priv: Y

Lock_tables_priv: Y

Execute_priv: Y

Repl_slave_priv: Y

Repl_client_priv: Y

Create_view_priv: Y

Show_view_priv: Y

Create_routine_priv: Y

Alter_routine_priv: Y

Create_user_priv: Y

Event_priv: Y

Trigger_priv: Y

Create_tablespace_priv: Y

Ssl_type:

Ssl_cipher:

X509_issuer:

X509_subject:

Max_questions: 0

Max_updates: 0

Max_connections: 0

Max_user_connections: 0

Plugin:

Authentication_string:

Password_expired: N

* 3. Row * *

Host:%

User: root

Password: * D013A4E3A5BB01E4239D18D7E93B59B7D2B767AD

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: Y

Drop_priv: Y

Reload_priv: Y

Shutdown_priv: Y

Process_priv: Y

File_priv: Y

Grant_priv: N

References_priv: Y

Index_priv: Y

Alter_priv: Y

Show_db_priv: Y

Super_priv: Y

Create_tmp_table_priv: Y

Lock_tables_priv: Y

Execute_priv: Y

Repl_slave_priv: Y

Repl_client_priv: Y

Create_view_priv: Y

Show_view_priv: Y

Create_routine_priv: Y

Alter_routine_priv: Y

Create_user_priv: Y

Event_priv: Y

Trigger_priv: Y

Create_tablespace_priv: Y

Ssl_type:

Ssl_cipher:

X509_issuer:

X509_subject:

Max_questions: 0

Max_updates: 0

Max_connections: 0

Max_user_connections: 0

Plugin: mysql_native_password

Authentication_string:

Password_expired: N

* * 4. Row *

Host:%

User: u_mdm_r2

Password: * CCB51F609423C18692BB961A7A83342B8311B6F4

Select_priv: N

Insert_priv: N

Update_priv: N

Delete_priv: N

Create_priv: N

Drop_priv: N

Reload_priv: N

Shutdown_priv: N

Process_priv: N

File_priv: N

Grant_priv: N

References_priv: N

Index_priv: N

Alter_priv: N

Show_db_priv: N

Super_priv: N

Create_tmp_table_priv: N

Lock_tables_priv: N

Execute_priv: N

Repl_slave_priv: N

Repl_client_priv: N

Create_view_priv: N

Show_view_priv: N

Create_routine_priv: N

Alter_routine_priv: N

Create_user_priv: N

Event_priv: N

Trigger_priv: N

Create_tablespace_priv: N

Ssl_type:

Ssl_cipher:

X509_issuer:

X509_subject:

Max_questions: 0

Max_updates: 0

Max_connections: 0

Max_user_connections: 0

Plugin: mysql_native_password

Authentication_string:

Password_expired: N

ERROR:

No query specified

Mysql > I have three analytical login methods for root: Host: bidevedw\ _ db, Host: 1, Host:%

Generally speaking, there are also Host:127.0.0.1 and Host:localhost which two kinds have been deleted in mine.

Attention, please!

Notice the red font portion of each of the above records Grant_priv: y

This means that whether the logged-in root user has grant privileges is resolved in this way, Y means that authorization is limited to other users, and N means no.

This is exactly why we are looking for, because my / my.cnf file has the skip-name-resolve parameter, so the root is resolved to @'%'to log in, so there is no grant_priv permission.

Solution:

1. If the MySQL service is not restarted, you only need to add the-h parameter when logging in.

For example: (a) / usr/local/mysql/bin/mysql-uroot-p123456-Hazzard 1

(B). / usr/local/mysql/bin/mysql-uroot-p123456-h227.0.0.1

2. You need to restart MySQL. Remove the skip-name-resolve parameter-it hasn't been verified yet. But I remember that if it was removed, there would be a lot of warnings in the log. I added this parameter because of those warnings.

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.