MySQL create user and authorization, modify password

Source: Internet
Author: User
Tags anonymous flush mysql create mysql manual mysql create user

First, create the user:

Create user creates a new MySQL account. To use create user, you must have the global Create user permission for the MySQL database or have insert permissions. For each account, create user creates a new record in the Mysql.user table with no permissions. If the account already exists, an error occurs.

Using the identified by clause of your choice, you can give your account a password. The user value and the given method of the password are the same as the GRANT statement. In particular, to specify a password in plain text, you need to ignore password keywords. To specify the password as a mixed value returned by the password () function, you need to include the keyword password

The code is as follows Copy Code

The Create user command:

Mysql> CREATE USER yy identified by ' 123 ';

YY indicates the username you want to create, and the following 123 indicates the password.

The user built above can log in anywhere.

If you want to restrict landing at a fixed address, such as localhost:

The code is as follows Copy Code

Mysql> CREATE USER yy@localhost identified by ' 123 ';


Second, authorize:

Command: GRANT privileges on databasename.tablename to ' username ' @ ' host '

Description: Privileges-user's operation rights, such as SELECT, INSERT, UPDATE, etc. use all if you want to grant the permissions you have. DatabaseName-database name, tablename-table name, available * representations, such as *.*, if you want to give the user the appropriate action rights for all databases and tables.


Example:

The code is as follows Copy Code
GRANT SELECT, INSERT on Test.user to ' pig ' @ '% ';
GRANT all on *.* to ' pig ' @ '% ';


Note: Users authorized by the above command cannot authorize other users, and if they want the user to be authorized to do so, use the command:

The code is as follows Copy Code

Grant privileges on Databasename.tablename to ' username ' @ ' host ' with Grant OPTION;
Refresh System Permission Table

Flush privileges;

The account created with the GRANT statement has the following properties:

· Two of these accounts have the same username Monty and password Some_pass. Two accounts are superuser accounts with full privileges to do anything. An account (' Monty ' @ ' localhost ') is used only when connecting from the local computer. Another account (' Monty ' @ '% ') can be used to connect from other hosts. Please note that the two accounts of Monty must be able to connect Monty from any host. Without a localhost account, the localhost anonymous user account created by mysql_install_db will be preempted when Monty is connected from the local computer. As a result, Monty will be treated as an anonymous user. The reason is that the host column value for the anonymous user account is more specific than the ' Monty ' @ '% ' account, which is listed first in the User table sort order. (The user table sort discussion should refer to the MySQL manual).

· An account has user name Admin, no password. This account is only used to connect from the local computer. Reload and process management permissions are granted. These permissions allow the Admin user to perform mysqladmin reload, mysqladmin refresh and mysqladmin flush-xxx commands, and Mysqladmin processlist. Permission to access the database is not granted. You can add such permissions through the GRANT statement.

· An account has user name dummy, no password. This account is only used to connect from the local computer. Permission not granted. With the usage permission in the GRANT statement, you can create an account without granting any permissions. It can set all global permissions to ' N '. Assume that you will later grant specific permissions to the account.


Three. Set up and change user password

Command: Set PASSWORD for ' username ' @ ' host ' = PASSWORD (' newpassword '); If the current login user uses SET PASSWORD = PASSWORD ("NewPassword");

The code is as follows Copy Code

Example: SET PASSWORD for ' pig ' @ '% ' = PASSWORD ("123456");

Or: Update mysql.user set Password=password (' New password ') where user= "Phplamp" and host= "localhost";

Four. Revoke user rights

The code is as follows Copy Code

Command: REVOKE privilege on databasename.tablename from ' username ' @ ' host ';

Description: Privilege, DatabaseName, TableName-with authorization section.

Example: REVOKE SELECT on *.* from ' pig ' @ '% ';

Note: If you are giving the user ' pig ' @ '% ' authorization (or similar): GRANT select on Test.user to ' pig '% ', then use REVOKE SELECT on *.* ' pig ' @ '% '; Revokes a SELECT action for the user table in the test database. Conversely, if the authorization uses the grant SELECT on *.* to ' pig '% ', then REVOKE Select on Test.user from ' pig ' @ '% '; Can revoke Select permissions for the user table in the test database.

Specific information can be used to show the grants for ' pig ' @ '% '; View.

Five. Delete User

The code is as follows Copy Code

Command: DROP USER ' username ' @ ' host ';

Or: DELETE from user WHERE user= "phplamp" and host= "localhost";

Delete a user's database
Mysql>drop database phplampdb;

Six. Change Password:

  code is as follows copy code

 
<!---->mysql> grant   all   privileges   on   pureftpd.*    to   koko@localhost   identified   by   ' Mimi ';   

Flush:

<!---->mysql> flush privileges;
 
View user information:

<!---->mysql> Select Host,user from mysql.user; 

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.