MySQL User management

      mainly summarizes the basic implementation of MySQL user management, including MySQL login, add users, delete users, assign permissions to users, remove a user's permissions, change passwords, view permissions and other basic operations, All commands are implemented in a pro-test. This blog is my labor results, in the Blog Park summary sharing, welcome reprint. The statement of this paragraph must be retained without the written instructions of the author. itred   e-mail: [email protected]    Blog Links: http://www.cnblogs.com/ itred    GitHub Link: http://github.com/itred

First, log in to MySQL

Command format: mysql–u (user name) –p (password)

　　mysql>mysql–uroot–p123456

Second, create the user

Generally, the Insert method is used to add the user, and the login password is specified when it is added, but as far as I am concerned, the following error is reported in insert mode, although it can still be implemented, but a configuration file needs to be modified. But it looks more troublesome! Therefore, the use of the Create command will be quite simple, practical!

If you use this command:mysql> insert into user (Host,user,password) VALUES ("localhost", "Red", Password ("123456")), the error will be as follows during execution: ERROR 1364 (HY000): Field ' ssl_cipher ' doesn ' t has a default value ...

The use of this create can be implemented, command format: Create user (username);

　　mysql>create user Red This command creates a login password for red users

To set the password required for the red user to log in, the command format:

Update MySQL. ' User ' u SET u. ' Password ' = Password (login password) WHERE u. ' User ' = (username);

FLUSH privileges; Make the change take effect; must be added.

　　mysql>update mysql. ' User ' u SET u. ' Password ' = Password ("123456") WHERE U. ' User ' = ' red ';

mysql> FLUSH privileges;

Iii. Assignment of Rights

Basic common permissions operations include: Select,delete,update,create,drop and so on.

Grant all permissions to the specified user:

Command format: Grant all privileges the on database. * To user name @localhost identified by user password;

　　Mysql> Grant all privileges the pythondb.* to [e-mail protected] identified by ' 123456 ';

Authorize partial permissions to the specified user:

Command format: Grant Select,update on database. * To user name @localhost identified by ' 123456 ';

　Mysql> GRANT select,update on pythondb.* to ' red ' @ ' localhost ' identified by ' 123456 ';

Comments:

@localhost Local Access

@ "%" indicates authorization for all non-local hosts, excluding localhost. (The localhost address is set to 127.0.0.1, if set to the real local address, do not know whether it can, no authentication.) ）

"localhost" here means that the user can only log on locally and cannot telnet to another machine. If you want to telnet, change "localhost" to "%", which means you can log on on any computer. You can also specify that a machine can log on remotely.

Iv. Deleting users

　Mysql>delete from user Where user= ' test ' and host= ' localhost ';

Mysql>flush privileges;

Delete account and permissions: Mysql>drop user username @ '% ';

Mysql>drop user username @ localhost;

V. Modify the login password of the specified user

　　Mysql>update Mysql.user Set Password=password (' New password ') where user= "test" and host= "localhost";

Mysql>flush privileges;

Six, PIN permissions

Command format: Revoke permissions on database. Table from ' username ' @ ' login host ';//change to from

　　　　Mysql>revoke all on * * from ' red ' @ '% '

Vii. Viewing permissions

Command format: Show grants;//yourself

　　　　mysql>show grants for [email protected]; Specifies that the user specifies the host

Permissions	Table column Name	Comments	Scope of Use
Select	Select_priv	Retrieving from a table	Table
Insert	Insert_priv	Insert a new record into a table that exists	Table
Update	Update_priv	Update column fields for rows in a saved table	Table
Delete	Delete_priv	Delete a record in a table	Table
Create	Create_priv	Create a new database and table	databases, tables, indexes
Drop	Drop_priv	Discard (delete) databases and tables that already exist	Database, table
Reload	Reload_priv	Re-read into authorization form	Server Management
Shutdown	Shutdown_priv	may be abused (by terminating the server to deny service to other users)	Server Management
Process	Process_priv	View the normal text of the currently executing query, including setting or changing the password query	Server Management
File	File_priv	Read any readable file to the database on the server	File access on the server
Grant	Grant_priv	Delegate your privileges to other users	Database, table
Reference	Reference_priv	Open or close a record file	Database, table
Alter	Alter_priv	Change the table to override the permission system by renaming the table	Table
Index	Index_priv	Create or delete an index	Table

  itred  Blog:http://itred.cnblogs.com
GitHub:https://github.com/itRed Copyright notice: This article is copyrighted by the author and the blog Park is shared, welcome reprint, but without the author's consent must retain this paragraph of the instructions,
      And in the article obvious position to give the original link, otherwise reserves the right to pursue legal responsibility.

MySQL User management