MySQL User management

User management mainly has the following functions:
1. You can restrict which libraries a user accesses, which tables
2. You can restrict which tables the user performs for select, CREATE, delete, delete, alter, etc.
3. The IP or domain name that can restrict user login
4. You can restrict whether the user's own permissions can be authorized to other users

One. Create a MySQL User:

There are three ways to create a MySQL user:

1. Create a user by using the "Create user" command

Syntax: CREATE USER ' [email protected] ' [identified by ' PASSWORD '] where the password is optional;

mysql> Create user ' [email protected] ' identified by ' 123456 '; Query OK, 0 rows Affected (0.00 sec) mysql> Create user ' [email protected]% ' identified by ' 123456 '; Query OK, 0 rows Affected (0.00 sec) mysql> Create user ' [email protected] '; Query OK, 0 rows Affected (0.00 sec) Description: This method is created by the user only to connect to the database permissions, need to follow-up authorization;

2. Create a user with the GRANT command (common method)
This method is created by the user, Grant will authorize the user when the database exists, but when the database does not exist, the user is created and authorized.

Syntax: GRANT < permissions > on < database. Table name > to < user name @ Login host > identified by ' Password ' [with GRANT OPTION];

Description
Priv represents permission Select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process, File, etc. 14 permissions
On: Indicates which databases and tables are valid for these permissions, in the format: Database name. Table name, write ' *. * ' to represent all tables for all databases.
To: Which user to grant permissions to. Format: "User name" @ "Login IP or domain name". % means no limit and can be logged in on any host.
Identified by: Specifies the user's login password.
With GRANT option: Allows the user to grant his or her permission to another user as an option.

1.> creates a user www and assigns all permissions to all databases and tables, allowing all hosts to log on with a password of 123456.

Mysql>grant all privileges on * * to ' [e-mail protected]% ' identified by ' 123456 ' with GRANT OPTION;

2.> to the host 192.168.10.1 user www assigns permissions to all operations on the database TestDB all tables, and sets the password to 123456.

Mysql>grant all privileges in testdb.* to ' [e-mail protected] ' identified by ' 123456 ';

3. Insert records directly into the Mysql.user table (this method is rarely used by individuals)

Mysql>insert into Mysql.user (Host,user,password) VALUES ('% ', ' www ', Password (' 123456 ')); Mysql>flush Privileges ;

Two. Refresh Permissions
After you have made permission changes to the user, you must remember to reload the permissions and write the permissions information from memory to the database.

mysql> flush Privileges;

Three. View User permissions

To view the use rights of a MySQL User: Show grants for   Dbuser, for example: show grants for [email protected]; View the parameters used to create the database. Show Create Databases dbname; View the parameters used to create the table. Show create table table name;

Four. Reclaim User rights

Mysql> REVOKE Create on * * from ' [email protected] '; Query OK, 0 rows Affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows Affected (0.00 sec)

Five. Delete a user

Mysql>delete from user Where user= ' www ' and host= ' localhost '; mysql>flush privileges; #删除账户及权限mysql >drop User [ email protected];

Six. User Rename

mysql> rename user ' www ' @ ' localhost ' to ' user1 ' @ ' localhost '; Query OK, 0 rows Affected (0.00 sec)

Seven. Modify User password

1. Using the Set password command

Syntax: Set password for ' username ' @ ' login address ' =password (' password ')

mysql> set password for ' www ' @ ' localhost ' = password (' 123 ');  Query OK, 0 rows Affected (0.00 sec)

2. Update the Mysql.user table

mysql> Update user Set Authentication_string=password (' 123456 ') where user= ' www '; Query OK, 1 row Affected (0.00 sec) Rows matched:1  changed:1  warnings:0

3. Using the Mysqladmin command

Syntax: mysqladmin-u user name-p old password password new password

mysql> mysqladmin-uroot-p123456 Password 1234ABCD

Eight. Forgot password

1. Add Login Skip Permission Check configuration
Modify MY.CNF, add skip-grant-tables configuration in MYSQLD configuration node

[Mysqld]skip-grant-tables

2> restarting the MySQL service

#service mysqld Restart

3> Change Password

At this point in the terminal with the MySQL command to log in without the user password, and then follow the first way to change the password to modify the password.

Note: The user table of the MySQL library, 5.7 The following version of the password field is password,5.7 above the version password field authentication_string

4> Restore logon permissions Skip Check configuration

Remove the Skip-grant-tables configuration of the mysqld node in my.cnf, and then restart the service.

Reference Document: Http://www.toxingwang.com/database/mysql/1468.html

http://blog.csdn.net/xyang81/article/details/51822252

　　

　　

MySQL User management