MySQL three ways to create users

MySQL creates users in three ways: INSERT User table method, create user method, Grant method.

I. Form of account name

Account Composition: User name + host (so duplicate user names can appear, unlike other databases)

User name: within 16 characters.

Host Name: You can use the hostname and IP address, or you can use a wildcard character

Wildcard Description: 172.18.10.% (IP address is 172.18. All IP addresses in paragraph 10 are accessible)

Second, create user by creating users command

Script: CREATE USER ' username ' @ ' host ' [Identified by ' PASSWORD '] where the password is optional;

Example: CREATE USER ' john ' @ ' 192.168.189.71 ' identified by "123";

CREATE USER ' john ' @ ' 192.168.189.% ' identified by "123";

CREATE USER ' john ' @ '% ';

Note: This method is created by the user only to connect to the database permissions, need to follow-up authorization;

Iii. 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. (indicates that the above step is superfluous)

Script:

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>grant select,insert,update,delete,create,drop on test.hr to [e-mail protected] identified 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>grant all privileges in test.* to [e-mail protected] identified by ' 123 ';

Description: For the 192.168.10.1 user, John assigns permissions to all operations on the database test all tables and sets the password to 123.

Mysql>grant all privileges on * * to [e-mail protected] identified 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>grant all privileges on * * to [e-mail protected] identified by ' 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

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> INSERT INTO User (Host,user,password) values ('% ', ' John ', Password (' 123 '));

Five, complete the user's creation, refresh the system permissions table;

Mysql>flush privileges;

MySQL three ways to create users