How to use MySQL commands for Account Management

Today, we want to learn how to use MySQL commands to manage accounts. We all know that MySQL commands can complete various tasks in actual applications, the following article describes how to manage accounts using MySQL commands.

In the manual, "the GRANT statement allows the system administrator to create a MySQL user account and GRANT permissions .". However, I cannot use this command to create a user account.

To add a new test user, run the following command:

 
 

  
  
MySQL> GRANT ALL ON qyoa.* TO chy@localhost INDENTIFIED BY "123456";   
  
  
ERROR 1046 (3D000): No database selected 
 
 

Later, I found that this was an error when I searched for information on the Internet and changed it:

 
 

  
  
MySQL> GRANT ALL ON qyoa.* TO chy@localhost INDENTIFIED BY "123456";   
  
  
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to   
  
  
your MySQL server version for the right syntax to use near 'INDEN TIFIED BY "123456"' at line 1 
 
 

Some people have encountered such errors on the Internet, and some people have some answers: MySQL. user MySQL Command library should be selected. because the operation you perform is to modify the permission table... you can directly operate the table. haha
Http://www.phpchina.com/html/54/t-51354.html)

It may be that I am stupid and cannot understand what it means.

I had to try again and again.

 
 

  
  
MySQL> use MySQL;   
  
  
MySQL> GRANT ALL ON qyoa.* TO chy@localhost INDENTIFIED BY "123456";  
 
 

The error message is the same as above.

After one or two hours, the problem could not be found. I had to change the implementation method,

 
 

  
  
MySQL> CREATE USER chy@localhost IDENTIFIED BY '123456';  
  
  
MySQL> grant all on qyoa.* to chy@localhost; 
 
 

Finally, users are created and permissions are allocated. Can I assign permissions only after an account is created?

My guess later confirmed that it was really necessary to create an account before assigning permissions. Cup! This wasted me several hours.

 
 

  
  
MySQL> GRANT ALL ON qyoa.* TO chy@localhost INDENTIFIED BY "123456";  
 
 

Running successful!

If no user account is created, run

 
 

  
  
MySQL> grant all on qyoa.* to chy@localhost; 
 
 

The following error message is used:

 
 

  
  
ERROR 1133(42000) can 't find any matching row in the user table 
 
 

Maybe there is a problem with my local settings. Execute it again today.
 

 
 

  
  
MySQL>GRANT ALL ON qyoa.* TO chy1@localhost identified by "123456"; 
 
 

It can be executed normally. I don't know why. Is it really my character?

However, if you change the statement to the following statement, the following problem occurs:

 
 

  
  
MySQL> grant all on qyoa.* to chy@localhost;  
  
  
ERROR 1133(42000) can 't find any matching row in the user table  
 
 

Check the MySQL Command manual to obtain the following information:

"SQL Server Mode
The mode defines which SQL syntaxes should be supported by MySQL and which data verification checks should be performed. You can use the SELECT @ SQL _mode statement to query the current mode.

 
 

  
  
· NO_AUTO_CREATE_USER 
 
 

Prevent GRANT from automatically creating new users unless a password is specified.

You can also use the SET [SESSION | GLOBAL] SQL _mode = 'modes 'statement to SET the SQL _mode variable after startup to change the SQL mode.

The SUPER permission is required when setting GLOBAL variables, and operations on all clients connected from that time will be affected. Setting SESSION variables only affects the current client.

Any client can change its own session SQL _mode value at any time. "

Originally, the SQL Server mode was set. The SQL _mode value is saved in my. ini. You can directly open the file and modify it before restarting the service. You can also use

Set session SQL _mode = 'strict _ TRANS_TABLES, NO_ENGINE_SUBSTITUTION.

Test:

 
 

  
  
MySQL>SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';  
 
 

Then execute

 
 

  
  
MySQL> grant all on qyoa.* to chy@localhost; 
 
 

A new account with an empty password is created! The above content is an introduction to MySQL account management. Today, we will learn how to use MySQL commands to manage accounts.