MySQL Create user command-grant

In the process of using MySQL, we often need to authorize the user (add, modify, delete), in MySQL there are three ways to implement the method of INSERT user table, CREATE user method, Grant method. Let's take a look at how the grant method is implemented today.

In two cases, the first creates a user with the Create user command, then grant grants, and the second creates and authorizes the direct use of grant; let's first look at how to implement

View User Permissions

Show grants for your users

Like what:
Show grants for [email protected] ' localhost ';

One. Create user command:

CREATE USER ' username '@'host'by'password';

Description

• Username: The user name you will create
• Host: Specifies on which host the user can log on, if localhost is available to the local user, if you want the user to be able to log in from any remote host , you can use a wildcard character%
• Password: The user's login password, password can be empty, if it is empty, the user can not need password login server

Example:

CREATE USER 'Dog'@'localhost'Identified by '123456';CREATE USER 'Pig'@'192.168.1.101_'Idendified by '123456';CREATE USER 'Pig'@'%'Identified by '123456';CREATE USER 'Pig'@'%'Identified by "';CREATE USER 'Pig'@'%';

Two. Authorization:

Command:

GRANT Privileges  on  to ' username '@'host'

Description

• Privileges: User's operation permissions, such as,, SELECT INSERT UPDATE etc., if the permission to be granted is usedALL
• DatabaseName: Database name
• TableName: Table name, if you want to grant the user the appropriate operation permissions on all databases and tables * , the representation is available, such as *.*

Example:

 grant  select , insert  on  test. user  to   " pig   " @"  %   "  grant  all  on  * . *  to   " pig   " @"  %  ; 

The first line of code above represents the user-authorized PID users in the test library, authorizes the insert and select operations, and the second line indicates that the authorized PID user can manipulate all permissions;

Attention:

A user authorized with the above command cannot authorize another user, and if you want the user to be authorized to do so, use the following command:

GRANT Privileges  on  to ' username '@'host'withGRANTOPTION;

Three. setting and changing user passwords

SET  for ' username '@'host'= PASSWORD ('newpassword');

If the current user is logged in with:

SET = PASSWORD ("NewPassword");

Example:

SET  for ' Pig '@'%'= PASSWORD ("123456");

Four. Revoke User Rights command:

REVOKE  on  from ' username '@'host';

Description

Privilege, DatabaseName, TableName: With the authorization section

Example:

REVOKE SELECT  on *. *  from ' Pig '@'%';

Attention:

If you ‘pig‘@‘%‘ are doing this (or the like) when authorizing a user, the use of a GRANT SELECT ON test.user TO ‘pig‘@‘%‘ command does REVOKE SELECT ON *.* FROM ‘pig‘@‘%‘; not revoke the user's action on the users table in the test database SELECT . Conversely, if authorization is used, GRANT SELECT ON *.* TO ‘pig‘@‘%‘; the REVOKE SELECT ON test.user FROM ‘pig‘@‘%‘; command cannot revoke the user's permissions to the users table in the test database Select .

Specific information can be viewed with commands SHOW GRANTS FOR ‘pig‘@‘%‘; .

To delete a user command:

DROP USER ' username '@'host';

The first one is done, and we're looking at the second. Create and authorize user actions directly using grant

Authorization format: Grant permissions on database. * To User name @ login host identified by "password";

Creating a user with the grant command

Personal habits generally use this method to create a user, grant will authorize the user when the database exists, but when the database does not exist, the user will be created and authorized.

 grant  <all|  Priv1,priv2,..... privn>  on  [ object  ]  [ identified by ' password '  ]  [ with GRANT OPTION  ]  ; Max_queries_per_hour  count  max_updates_ Per_hour  count  max_connections_per_hour  count  max_user_connections  count  

Description: Priv represents permission Select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process, File, etc. 14 permissions

Example:

MySQL>Grantselect,insert,update,delete,create ,drop on to John@192. 168.10. 1  by ' 123 ';

Description: Give the host 192.168.10.1 user John the ability to perform operations such as Select,insert,update,delete,create,drop on the HR table of the database test, and set the password to 123.

MySQL>Grantallprivileges on test. *  to Joe@192. 168.10. 1  by ' 123 ';

Description: Give the host 192.168.10.1 user John the ability to perform all operations on the database test all tables and set the password to 123.

MySQL>Grantallprivilegeson*. *  to John@192. 168.10. 1  by ' 123 ';

Description: Give the host 192.168.10.1 user John the ability to perform all operations on all tables in all databases and set the password to 123.

MySQL>Grantallprivilegeson*. *  to John@localhostby'123';

Description: User John assigns permissions to all operations on all tables in all databases and sets the password to 123.

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

Because the database user information is saved in mysql.user this table, so directly to the table INSERT statement, you can complete the user's creation;

MySQL>insertintouser ,uservalues ('%  ','John', password ('123');

After the completion of the user's creation, please remember to refresh the System permissions table;

MySQL>privileges;

Summary: Although there are three ways to create a user, individuals are inclined to the second method, one step, simple and clear;

The other two methods only help to understand the principle of the database;

MySQL Create user command-grant