MySQL three ways to create a user (and authorize) to go

The method of creating a user in MySQL is divided into three ways: The method of INSERT user table, the method of create user, and the method of Grant.

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 ' [email protected] ' [identified by ' PASSWORD '] where the password is optional;

Example: CREATE USER ' [email protected] ' identified by ' 123 ';

CREATE USER ' [email protected]% ' identified by ' 123 ';

CREATE USER ' [email protected] ';

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

Note: The user and after the host address is one, with a semicolon connection, or will error, error 1396 (HY000): Operation CREATE USER failed for ' remote ' @ '% '

Iii. creating a user with the grant command

When a user is present in the database, grant authorizes the user, but when the database does not exist, the user is 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: 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>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.

To view permissions:

show Grants for your users; show Grants for[email protected] ' localhost ' Show grants for [email protected]; show createdatabase dbname; This allows you to see some of the parameters used to create the database. Showcreatetabletickets; You can see some of the parameters used to create the table

Revoke permissions:

Revoke all on * * from [email protected];

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

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

Mysql>flush 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 principles of the database.

MySQL three ways to create a user (and authorize) to go