How to use MySQL commands for Account Management

Source: Internet
Author: User
Tags mysql commands

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:

 
 
  1. MySQL> GRANT ALL ON qyoa.* TO chy@localhost INDENTIFIED BY "123456";   
  2. 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:

 
 
  1. MySQL> GRANT ALL ON qyoa.* TO chy@localhost INDENTIFIED BY "123456";   
  2. ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to   
  3. 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.

 
 
  1. MySQL> use MySQL;   
  2. 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,

 
 
  1. MySQL> CREATE USER chy@localhost IDENTIFIED BY '123456';  
  2. 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.

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

Running successful!

If no user account is created, run

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

The following error message is used:

 
 
  1. 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.
 

 
 
  1. 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:

 
 
  1. MySQL> grant all on qyoa.* to chy@localhost;  
  2. 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.

 
 
  1. · 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:

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

Then execute

 
 
  1. 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.


 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.